Automating Partition Creation

By Bryan C. Smith

 

The use of multiple partitions per measure group is promoted to improve both query and processing performance as well as the overall manageability of cubes. Partitioning strategies are often time-based, so that a single partition may represent a specific year, quarter, month, or some other unit of time.

One of the central challenges of partitioning a measure group on time (or any other non-static dimension) is that new partitions may be required over the life of the cube. If it can be predicted when these partitions might be needed, the required partitions can be manually created in advance by the admin. Where values cannot be predicted, an "overflow" partition can be created to catch any values not attributed to other, more precisely defined partitions.

Still, having the ETL layer query the database to determine the partitions required and then create those partitions on your behalf is a much more attractive approach. Below is a simplistic script demonstrating the use of AMO to do just that.

The script is written for execution within an SSIS Script task and requires a reference to the AMO library. Its goal is to add any year-based partitions to the Reseller Sales measure group within the Adventure Works cube of the Adventure Works DW multidimensional database. It is tested by dropping any partition from the Reseller Sales measure group.

Here is a quick rundown of the logic.

The script starts by establishing references to the Adventure Works cube and the Reseller Sales measure group within the Adventure Works DW multidimensional database. A query is issued against the DimTime table in the AdventureWorksDW relational database to determine which years exist within the table and the key ranges of the records associated with those years. (There is a critical assumption here that records in the DimTime table are sequenced by date. This may not be true within your data warehouse.)

The routine then loops through each partition in the Reseller Sales measure group to determine is a partition for that year exists. If not, a new partition is created with the appropriate query bindings. The first aggregation design used within the measure group is assigned to the new partition. (This is not a recommended approach but simply serves to demonstrate how aggregations can be assigned to new partitions.) The partition is then associated with the measure group and the change is submitted to SSAS. (It is important to note, the new partition is unprocessed at this time.)

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

 

Public
Class ScriptMain

 

Public
Sub Main()

 

'Locate Measure Group of Interest (and a Known Partition)

'---------------------------------------------

Dim MyServer As
New Server

MyServer.Connect("localhost")

 

Dim MyDatabase As Database = _

MyServer.Databases.GetByName("Adventure Works DW")

Dim MyCube As Cube = MyDatabase.Cubes.GetByName("Adventure Works")

Dim MyMeasureGroup As MeasureGroup = _

MyCube.MeasureGroups.GetByName("Reseller Sales")

 

'Retrieve Years in Dimension

'---------------------------------------------

Dim MyConnectionString As
String

MyConnectionString = "Server=localhost;" + _

"Database=AdventureWorksDW;Integrated Security=SSPI;"

Dim MySqlConnection As
New SqlClient.SqlConnection(MyConnectionString)

Dim MySqlCommand As
New SqlClient.SqlCommand( _

MySqlQueryString, MySqlConnection)

 

MySqlConnection.Open()

Dim MySqlReader As SqlClient.SqlDataReader = MySqlCommand.ExecuteReader

 

'Add Partitions That Don't Exist

'---------------------------------------------

While MySqlReader.Read

 

Dim MyYear As
String = MySqlReader.GetString(0)

Dim MyStartKey As Int32 = MySqlReader.GetInt32(1)

Dim MyEndKey As Int32 = MySqlReader.GetInt32(2)

 

Dim MyPartitionName As
String = "Reseller_Sales_" + MyYear

Try

Dim MyPartition As Partition = _

MyMeasureGroup.Partitions.GetByName(MyPartitionName)

Catch

Dim MyPartition As
New Partition

With MyPartition

.Name = MyPartitionName

If MyMeasureGroup.AggregationDesigns.Count > 0 Then

.AggregationDesignID = _

MyMeasureGroup.AggregationDesigns.Item(0).ID

End
If

.Source = New QueryBinding(MyCube.DataSource.ID, _

MyParitionQueryString(MyStartKey, MyEndKey))

End
With

MyMeasureGroup.Partitions.Add(MyPartition)

MyPartition.Update()

End
Try

End
While

MySqlReader.Close()

 

MySqlConnection.Close()

MyServer.Disconnect()

 

Dts.TaskResult = Dts.Results.Success

End
Sub

 

Private
Function MySqlQueryString() As
String

Dim Ret As
String

Ret = "select CalendarYear," + _

"min(TimeKey),max(TimeKey) " + _

"from dbo.DimTime " + _

"Group by CalendarYear"

Return Ret

End
Function

 

Private
Function MyParitionQueryString(ByVal StartKey As Int32, _

ByVal EndKey As Int32) As
String

Dim Ret As
String

Ret = "SELECT " + _

"[dbo].[FactResellerSales].[ProductKey]," + _

"[dbo].[FactResellerSales].[OrderDateKey]," + _

"[dbo].[FactResellerSales].[DueDateKey]," + _

"[dbo].[FactResellerSales].[ShipDateKey]," + _

"[dbo].[FactResellerSales].[ResellerKey], " + _

"[dbo].[FactResellerSales].[EmployeeKey]," + _

"[dbo].[FactResellerSales].[PromotionKey]," + _

"[dbo].[FactResellerSales].[CurrencyKey]," + _

"[dbo].[FactResellerSales].[SalesTerritoryKey]," + _

"[dbo].[FactResellerSales].[SalesOrderNumber]," + _

"[dbo].[FactResellerSales].[SalesOrderLineNumber]," + _

"[dbo].[FactResellerSales].[RevisionNumber]," + _

"[dbo].[FactResellerSales].[OrderQuantity]," + _

"[dbo].[FactResellerSales].[UnitPrice]," + _

"[dbo].[FactResellerSales].[ExtendedAmount], " + _

"[dbo].[FactResellerSales].[UnitPriceDiscountPct], " + _

"[dbo].[FactResellerSales].[DiscountAmount], " + _

"[dbo].[FactResellerSales].[ProductStandardCost], " + _

"[dbo].[FactResellerSales].[TotalProductCost], " + _

"[dbo].[FactResellerSales].[SalesAmount], " + _

"[dbo].[FactResellerSales].[TaxAmt], " + _

"[dbo].[FactResellerSales].[Freight], " + _

"[dbo].[FactResellerSales].[CarrierTrackingNumber], " + _

"[dbo].[FactResellerSales].[CustomerPONumber]" + _

"FROM [dbo].[FactResellerSales] " + _

"WHERE OrderDateKey BETWEEN " + StartKey.ToString + " AND " + _

EndKey.ToString

 

Return Ret

End
Function

End
Class

AMO Script for Automating SSAS Database Backups

By Bryan C. Smith

I apologize in advance, but I'm afraid I need to get up on my soapbox for this one. Bear with me.

START SOAPBOX

I have not seen folks pursuing regular backups of multidimensional databases as aggressively as they do relational databases. Part of this is an attitude that multidimensional databases are simply redundant copies of their relational data sources. Still another part of this is a lack of familiarity with the SSAS technology and the tools available to support regular backups. Whatever the reason, the "zero data loss" mentality has not spread to the multidimensional landscape.

So, let's clearly state a few things. Multidimensional databases do occasionally get corrupted, developers do introduce errors, and systems do fail. Projects housing the database definition do get lost and corrupted as well, even in Visual Source Safe and other related systems.

Though you may have a good set of project files available with which to rebuild a multidimensional database, important changes can be introduced through management activities that are not reflected in the BIDS project. Furthermore, the time it takes to reprocess a cube following a failure is often not acceptable to end-users, especially when those databases support critical business processes.

It is important we insure regular backups are taken of our multidimensional databases and are managed in a manner commensurate with our relational data sources.

END SOAPBOX

So, I've taken a look at a few means for handling regular backups of multidimensional databases. In the end, I find AMO the most appealing tool for this.

Below is a sample script that you may wish to explore for your own purposes. This was implemented in the Script task of an SSIS package. If you wish to test the script out, be sure to add a reference to the Analysis Management Objects library in your script editor.

Also, I need to note that this is not intended to be deployed to production as is. If you wish to use this script in a production setting, please thoroughly test it, add appropriate error handling, etc. If you use all or part of this script, you assume responsibility for its behavior. Sorry, but I've got to say it.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public
Class ScriptMain



Public
Sub Main()



'Get TimeStamp in form YYYYMMDDhhmmss


'------------------------------------------------------


Dim MyTimestamp As
String

MyTimestamp = CStr(Year(Now()) * 10000 + _

Month(Now()) * 100 + Day(Now()))

MyTimestamp += CStr(Hour(Now()) * 10000 + _

Minute(Now()) * 100 + Second(Now()))


 

'Connect to SSAS Instance


'------------------------------------------------------


Dim MyServer As
New Server

MyServer.Connect("localhost")


 

'Backup Databases


'------------------------------------------------------


Dim BackupFileName As
String


Dim AllowOverwrite As
Boolean = True


Dim BackupRemotePartitions As
Boolean = False


Dim MyLocation() As BackupLocation


Dim ApplyCompression As
Boolean = True


 

For
Each MyDatabase As Database In MyServer.Databases

BackupFileName = "c:\temp\" + MyDatabase.Name + _

"_" + MyTimestamp + ".abf"

MyDatabase.Backup(_

BackupFileName, AllowOverwrite, _

BackupRemotePartitions, MyLocation, _

ApplyCompression)

Next

 

'Disconnect from SSAS Instance


'------------------------------------------------------

MyServer.Disconnect()


Dts.TaskResult = Dts.Results.Success


End
Sub

End
Class

Implementing User-Specific Security in SSAS

By Bryan C. Smith

 

You often have a requirement to restrict access to members of a dimension on a user-specific basis. You can accomplish this through the use of what is referred to as "dynamic security". The purpose of this blog entry is to demonstrate a standard technique for implementing this.

In this demonstration, you will limit access to the Reseller dimension of the Adventure Works DW multidimensional database. You will implement structures in both this database and the associated AdventureWorksDW relational database. It is assumed these databases are hosted locally, and it is recommended you backup both databases prior to making any of the required changes.

In addition, you will leverage two local user accounts, TestUser1 and TestUser2, created for the purposes of this demonstration. The name of your local system is assumed to be MyLaptop so that the full names of these accounts are MyLaptop\TestUser1 and MyLaptop\TestUser2. When these exercises are completed, these two accounts should be dropped from your system.

 

The User Dimension

Your first objective is to create the User dimension. This dimension simply houses a list of end-user accounts. You start by implementing the DimUser table in the relational database:

create
table DimUser (

    UserKey int identity(1,1) not
null,

    UserName varchar(256)
not
null

    )

alter
table DimUser add

    constraint PK_DimUser primary
key
(userkey),

    constraint AK_DimUser unique
(username)

 

We then add entries for our two test accounts:

insert
into DimUser (UserName) values
('MyLaptop\TestUser1')

insert
into DimUser (UserName) values
('MyLaptop\TestUser2')

 

TestUser1 and TestUser2 should have received UserKey values of 1 and 2, respectively.

In the multidimensional database, you now add the DimUser table to the DSV and create a new dimension, User, from it. The User dimension has a single attribute hierarchy, User, whose key and name are the UserKey and UserName fields, respectively.

 

The ResellerUser Fact Table

With the User dimension in place, you now need to construct a list of which resellers your individual users are allowed to see. In the relational database, you implement this through the fact-less fact table, FactResellerUser:

create
table FactResellerUser (

    UserKey int
not
null,

    ResellerKey int
not
null

    )

alter
table FactResellerUser add

    constraint PK_FactRsellerUser

primary
key
(resellerkey,userkey),

    constraint FK_FactRsellerUser_UserKey

        foreign
key
(UserKey)

        references DimUser (UserKey),

    constraint FK_FactRsellerUser_ResellerKey

        foreign
key
(ResellerKey)

        references DimReseller (ResellerKey)

 

For the purposes of this demonstration, let's say TestUser1 (UserKey=1) may see reseller's with ResellerKey values of 1 to 5:

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,1)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,2)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,3)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,4)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,5)

Let's then say TestUser2 (UserKey=2) may see reseller's with ResellerKey values of 3 to 7:

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,3)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,4)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,5)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,6)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,7)

In the multidimensional database, you now add FactResellerUser to the DSV. The relationships between this table and the DimReseller and DimUser tables should be reflected in the DSV as well.

In the Adventure Works cube, you then create a new measure group, Reseller User, based on the FactResellerUser table. It will contain a single measure using the count aggregation function. The BIDS Cube Designer should automatically add the User dimension to the cube and associate the Reseller User measure group with it. The Cube Designer should also automatically associate our measure group with the Reseller dimension. You will need to confirm this and manually update the cube if necessary.

 

Cleaning Up the Cube

The Reseller User measure group and the User dimension will provide the basis for your user-specific security, but these do not represent items with which you intend to have your users directly interact. Your objective now is to hide these items.

To hide the measure group, set the Visible property on its single measure to False. With no visible measures, the measure group will not be displayed to end-users. To hide the User dimension in the cube, set the Visible property on the cube dimension to False.

It is important to keep in mind you have only hidden these items. End-user's aware of their presence can still query them.

 

Setting-Up the Role

Now it's time to set up the role in the multidimensional database. You create a new role, MyRole, with Read access to the Adventure Works cube. You add TestUser1 and TestUser2 as members.

Your next step is to limit access to the User dimension. You don't want end-users to query the User dimension and see a list of user accounts with access to the database. To secure it, you set an allowed set on the User attribute of the User database dimension as follows:

STRTOSET("[User].[User].["+Username+"]")

The Username function returns a string which is the full name of the end-user's user account. For example, when your TestUser1 user connects to the multidimensional database, the Username function will return "MyLaptop\TestUser1".

The Allowed Set requires you to specify a set of members in the User attribute hierarchy. You concatenate the string returned by the Username function with other strings to assemble a definition for a valid, one-member set. You then convert that string into an actual set with the STRTOSET function.

Now it's time focus your attention on the Reseller dimension. You will leverage the relationship between the User dimension and the Reseller dimension captured in the Reseller User measure group. Of course, this relationship exists within the Adventure Works cube so you will be working with the Reseller cube dimension.

For the Allowed Set on the Reseller attribute hierarchy, you specify the following:

EXISTS(

    [Reseller].[Reseller].Members,

    STRTOSET("[User].[User].["+Username+"]"),

    'Reseller User'

    )

The EXISTS function returns the set of members from the first set, [Reseller].[Reseller].Members, associated with the members in the second set, STRTOSET("[User].[User].["+Username+"]"), as determined by a specified measure group, 'Reseller User'. It is important to note that in indicating measure group, you are must state the measure group's name, not the name of a measure within the measure group, and that name must be enclosed in single-quotes. Many first time user's of this variation on the EXISTS function are frequently confused by these points.

 

Testing Security

Once the MyRole is deployed to the SSAS instance, you need to test the set up. To do this, you run SQL Server Management Studio as one of our test users, TestUser1. (From the Start menu, select Programs then Microsoft SQL Server 2005. Right-click the SQL Server Management Studio icon and select Run As from the context menu. Enter the appropriate account information to start SSMS as TestUser1.)

Once started, you open an MDX Query window and issue the following statement:

select

    {} on 0,

    [User].[User].Members
on 1

from    [Adventure Works]

;

All

MyLaptop\TestUser1

 

You then execute this statement:

with
member [Measures].[Reseller Key] as

    [Reseller].[Reseller].CurrentMember.UniqueName

select

    [Measures].[Reseller Key] on 0,

    [Reseller].[Reseller].Members
on 1

from    [Adventure Works]

;

	Reseller Key
All Resellers	[Reseller].[Reseller].[All Resellers]
A Bike Store	[Reseller].[Reseller].&[1]
Advanced Bike Components	[Reseller].[Reseller].&[3]
Metropolitan Sports Supply	[Reseller].[Reseller].&[5]
Modular Cycle Systems	[Reseller].[Reseller].&[4]
Progressive Sports	[Reseller].[Reseller].&[2]

Non-Deterministic Functions in Named Sets

By Bryan C. Smith

Named sets are a great way to encapsulate complex or frequently used set definitions. There are three ways to create a named set:

1. The WITH SET clause of an MDX SELECT statement
   
2. The CREATE SESSION SET statement
   
3. The CREATE SET statement within the cube calculations script
   

Which of these you select depends on your needs and determines how and when the set is resolved. This is critical to understand when using non-deterministic functions as demonstrated in the following exercises.

These exercises assume you have a copy of the Adventure Works DW database running on a local instance of SSAS 2005. It is also assumed you have the permissions needed to adjust the Date/Time settings of this system.

 

The WITH SET clause

Change your system's time to the year 2004. Open an MDX Query window in SSMS, connect to the local instance containing Adventure Works DW, and execute the following:

with
set [Current Year] as

    STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

select

    [Measures].[Reseller Sales Amount] on 0,

    [Current Year] on 1

from    [Adventure Works]

;

	Reseller Sales Amount
CY 2004	$16,038,062.60

 

Change your system's time to the year 2003 and re-execute the same query:

	Reseller Sales Amount
CY 2003	$32,202,669.43

 

The named set created with the WITH SET clause is query-scoped. It is compiled and resolved with each execution of the query but is not available to other queries unless the set definition is repeated.

 

The CREATE SESSION SET Statement

Set your system's time to the year 2004 and execute this query to create the named set:

create
session
set [Adventure Works].[Current Year] as

    STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

;

With execution completed, change your system's time to the year 2003. Now, execute this query:

select

    [Measures].[Reseller Sales Amount] on 0,

    [Current Year] on 1

from    [Adventure Works]

;

	Reseller Sales Amount
CY 2004	$16,038,062.60

 

A couple things are happening here. First, the CREATE SESSION SET statement is compiled separate from the query. Any query utilizing the connection within which the set was compiled can leverage it. Once the connection is terminated, the set is lost.

Next, the set is resolved to its members when the CREATE SESSION SET is submitted. Set membership does not change until the set is dropped and recreated.

 

The CREATE SET Statement in the Calculations Script

Change your system's time back to the year 2004. In BIDS, add the following to the calculations script and redeploy the cube:

create
set [Adventure Works].[Current Year] as

    STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

;    

 

Change your system's time to the year 2003, reconnect to the Adventure Works DW database, and execute the following query.

select

    [Measures].[Reseller Sales Amount] on 0,

    [Current Year] on 1

from    [Adventure Works]

;

	Reseller Sales Amount
CY 2003	$32,202,669.43

 

Now, reset your system's time to the year 2004 and re-execute the query from above:

	Reseller Sales Amount
CY 2003	$32,202,669.43

 

Open a new query window and execute the SELECT statement again:

	Reseller Sales Amount
CY 2003	$32,202,669.43

 

With your system's time still set to the year 2004, restart the SSAS 2005 service and reconnect. Execute the query again:

	Reseller Sales Amount
CY 2004	$16,038,062.60

 

Unlike the session-scoped set, the named set is not resolved until the first time it is used. The members of that set are then defined and used across all queries until the SSAS service is restarted. With the first query submitted against the restarted service, the set's membership is defined again.

Before going further, please reset your system to the current time.

 

Additional Thoughts

I originally wrote this blog entry to use the RND function as my non-deterministic function. I would randomly select a Reseller to demonstrate the patterns shown above. I found an unusual pattern when I deployed the named set as part of the calculations script. Restarting the SSAS service resulted in a random member being returned with the first query, but redeployment of the database reset the membership of the set to the same member every time. I guess the take home message here is when using non-deterministic functions in your named sets, be sure to understand the basic patterns and test thoroughly.

 

Data Member, Part 2

By Bryan C. Smith

In my previous post, you took a look at Amy, an employee who supervises three other employees, Jae, Rachel, and Ranjit.  Each of these four employees has sales quotas associated with them.  Since Amy is the supervisor of Jae, Rachel, and Ranjit, her sales quota reflects her personal sales quota as well as those from Jae, Rachel, and Ranjit.  You used the DataMember function to separate the sales quota directly attributable to Amy from her total sales quota figure:

with
member [Measures].[Personal Quota] as

     ([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),

     format="Currency"

select

     {[Measures].[Sales Amount Quota],[Measures].[Personal Quota]} on 0,

     DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1

from [Adventure Works];

	Sales Amount Quota	Personal Quota
Amy E. Alberts	$24,202,000.00	$1,124,400.00
Jae B. Pak	$12,547,100.00	$12,547,100.00
Rachel B. Valdez	$3,269,800.00	$3,269,800.00
Ranjit R. Varkey Chudukatil	$7,260,700.00	$7,260,700.00

 

Jae, Rachel, and Ranjit are referred to as leaf members of the hierarchy while Amy is referred to as a non-leaf member. Leaf members have no children while non-leaf members do.  (At this point, you haven't actually proven Jae, Rachel, and Ranjit have no children, but you will in the next query.)

In a parent-child hierarchy it is common for both leaf and non-leaf members to have data in the fact table directly associated with them.  The hierarchy's MembersWithData property determines how SSAS presents this data to you. 

If the MembersWithData property is set to NonLeafMembersHidden, which is the default when designed in BIDS, you get the behavior we see above – the values directly attributed to a non-leaf member are simply presented as part of the total value for that member. 

You've seen you can get to the value associated with the non-leaf member with the DataMember function.  Still, SSAS has the ability to present this another way.

If you set the MembersWithData property to NonLeafMembersVisible, SSAS will present the value directly attributed to a non-leaf member as a child of that member.  (Changing the value will require a re-deploy but not a re-process.) The easiest way to explain this is to simply demonstrate it:

with
member [Measures].[Personal Quota] as

     ([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),

     format="Currency"

member [Measures].[Member Level] as

     [Employee].[Employees].CurrentMember.Level.Name

member [Measures].[Number of Children] as

     [Employee].[Employees].CurrentMember.Children.Count

select

     {[Measures].[Sales Amount Quota],

     [Measures].[Personal Quota],

     [Measures].[Member Level],

     [Measures].[Number of Children]} on 0,

     DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1

from [Adventure Works];

	Sales Amount Quota	Personal Quota	Member Level	Number of Children
Amy E. Alberts	$24,202,000.00	$1,124,400.00	Employee Level 04	4
Amy E. Alberts	$1,124,400.00	$1,124,400.00	Employee Level 05	0
Jae B. Pak	$12,547,100.00	$12,547,100.00	Employee Level 05	0
Rachel B. Valdez	$3,269,800.00	$3,269,800.00	Employee Level 05	0
Ranjit R. Varkey Chudukatil	$7,260,700.00	$7,260,700.00	Employee Level 05	0

 

Take a close look at the results.  You've calculated the level name along with the number of children for each member.  The number of children identifies non-leaf and leaf members.  The member level helps you identify how these members are represented in the hierarchy.

With the MembersWithData property set to NonLeafMembersVisible, a leaf member representation of Amy is added to our results as a child of the Amy non-leaf member.  The non-leaf Amy member is just as before and the leaf Amy member simply holds the quota value directly attributable to Amy.

Understanding the difference between these two representations of Amy without the benefit of a walk-through such as this is a little confusing.  The MembersWithDataCaption property exists to address this problem.

The property accepts a string that serves as a template for the name of the SSAS-generated non-leaf member. An asterisk (*) in the string serves as a placeholder for the original member name.  A common string used for MembersWithDataCaption property is * (data).  Here are the results from the query above with the MembersWithDataCaption property set to this value:

	Sales Amount Quota	Personal Quota	Member Level	Number of Children
Amy E. Alberts	$24,202,000.00	$1,124,400.00	Employee Level 04	4
Amy E. Alberts (data)	$1,124,400.00	$1,124,400.00	Employee Level 05	0
Jae B. Pak	$12,547,100.00	$12,547,100.00	Employee Level 05	0
Rachel B. Valdez	$3,269,800.00	$3,269,800.00	Employee Level 05	0
Ranjit R. Varkey Chudukatil	$7,260,700.00	$7,260,700.00	Employee Level 05	0

 

If you remember back to my previous blog entry, I started out with a simple breakdown of these sales quotas.  Using the MembersWithData and MembersWithDataCaption properties, we've come full circle having SSAS provide us the data in a similar structure:

• Amy $24M Total
  

• Amy (personal) $1M
  

• Jae $13M
  

• Rachel $3M
  
• Ranjit $7M
  

 

So, what are the right settings for the MembersWithData and MembersWithDataCaption properties? It really depends on your requirements.  I recommend fully understanding how data is presented in a parent-child hierarchy and then sitting down with your end-user representatives to discuss the options. 

By their nature, parent-child hierarchies can be slightly difficult for users to wrap their heads around.  Walking users through a common example of parent-child hierarchy, such as employees in an organization, helps to explain the basic concepts. Using sample data sets, such as the AdventureWorks DW database, to illustrate these concepts can also assist with their understanding. 

It's also important to keep in mind these properties can be changed relatively quickly should you decide at a later date you would prefer your data presented another way.  Remember that changing the MembersWithData property affects the number of children associated with a non-leaf member which can have implications for calculations across levels in the hierarchy.  Thoroughly test any calculated members before and after changing this property, but also keep in mind calculated members can be re-deployed to your cube without re-processing.

Data Member, Part 1

By Bryan C. Smith

Take a moment to think about a typical organization.  You have an employee.  That employee has a supervisor who is also an employee who has a supervisor (who is also an employee) and so on and so on.   (This is a classic parent-child hierarchy.)

If these employees are in a sales organization, sales quota may have been assigned.  Those employees that are supervisors are responsible for the sales quotas of the employees below them but may also have sales quotas of their own.

Let's say you have an employee in a sales organization named Amy.  She supervises three other employees, Jae, Rachel, and Ranjit.  Amy is responsible for making sure these guys make their sales quotas of $13M, $3M, and $7M, respectively.  Amy also has a personal sales target of $1M.  We can breakdown Amy's sales quota like this:

• Amy $24M Total
  
  • Amy (personal) $1M
    
  • Jae $13M
    
  • Rachel $3M
    
  • Ranjit $7M
    

 

The $24M figure represents the total sales for which Amy has responsibility while she is only directly responsible for $1M of that.  The remaining $23M represents the sales quotas she manages through her supervisees.

Let's now look at how SSAS 2005 handles all this. Open an MDX query window in SQL Server Management Studio, connect to the Adventure Works DW database, and execute the following query:

select

     [Measures].[Sales Amount Quota] on 0,

     DESCENDANTS([Employee].[Employees].[Amy E. Alberts], 1, SELF_AND_BEFORE) on 1

from [Adventure Works];

	Sales Amount Quota
Amy E. Alberts	$24,202,000.00
Jae B. Pak	$12,547,100.00
Rachel B. Valdez	$3,269,800.00
Ranjit R. Varkey Chudukatil	$7,260,700.00

 

Through the DESCENDANTS
function, you asked SSAS to return the Sales Amount Quota for those employees one level below Amy E. Alberts and Amy herself. If you total the values for Jae, Rachel, and Ranjit, we come up with $23,077,600.00.  That's $1,124,400.00 short of the total. 

From these results, you can deduce the approximately $1M deficit represents Amy's personal quota.  But how do you get SSAS to explicitly present this?  You do it with the DataMember function:

with
member [Measures].[Personal Quota] as

     ([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),

     format="Currency"

select

     {[Measures].[Sales Amount Quota],[Measures].[Personal Quota]} on 0,

     DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1

from [Adventure Works];

	Sales Amount Quota	Personal Quota
Amy E. Alberts	$24,202,000.00	$1,124,400.00
Jae B. Pak	$12,547,100.00	$12,547,100.00
Rachel B. Valdez	$3,269,800.00	$3,269,800.00
Ranjit R. Varkey Chudukatil	$7,260,700.00	$7,260,700.00

 

Your results now show definitively that Amy owns a $1M personal sales quota which contributes to the $24M overall quota she is responsible for delivering.  You also see that Jae, Rachel, and Ranjit, Amy's supervisees, are solely responsible for their own quotas.  You cannot say Jae, Rachel, and Ranjit have no supervisees of their own (without submitting additional queries), but you can say none of these three guys is managing quota values from any supervisees they may have.

So, when you request a value for a member in a parent-child hierarchy, such as the Employees hierarchy of the Employee dimension, you need to be aware SSAS combines those values directly tied to that member with values associated with that member's children. You need to use the DataMember function to get at the value directly tied to a member.

It needs to be pointed out that you can tell SSAS to display a member's directly attributable data a little differently when that member also has children with values. In my next blog entry, we'll revisit Amy's sales team and take a look at those settings and how they impact these results.

IgnoreUnrelatedDimensions

By Bryan C. Smith

Imagine you have a highly simplified Adventure Works cube. In this cube, you have two measure groups, Reseller Sales and Internet Sales. Reseller Sales has relationships with the Geography and Date dimensions and has one measure, Reseller Sales Amount. Internet Sales has a relationship with just the Date dimension and a single measure, Internet Sales Amount. To simplify things even further, imagine the Geography dimension has a single attribute hierarchy, Country, and Date has three attribute hierarchies, Calendar Year, Calendar Quarter, and Date.

Every reference to data within this cube is based on a coordinate system. Each attribute hierarchy in the cube represents an axis in the coordinate. For our purposes, let's say the coordinates are in the format of ([Geography].[Country], [Date].[Calendar Year], [Date].[Calendar Quarter], [Date].[Date]). (For the sake of brevity, I've removed the attribute hierarchy from the Measures dimension.) Our MDX SELECT statements then assemble coordinates which SSAS resolve to return a cellset:

select


[Measures].[Reseller Sales Amount] on 0,


Geography.Country.Members
on 1


from [Adventure Works]


where ([Date].[Calendar Year].[CY 2003])

;

	Reseller Sales Amount		Coordinates
All Geographies	$32,202,669.43	?	('All Geographies', 'CY 2003', 'All Times', 'All Times')
Australia	$847,430.96	?	('Australia', 'CY 2003', 'All Times', 'All Times')
Canada	$5,651,305.43	?	('Canada', 'CY 2003', 'All Times', 'All Times')
France	$2,373,804.04	?	('France', 'CY 2003', 'All Times', 'All Times')
Germany	$1,098,866.68	?	('Germany', 'CY 2003', 'All Times', 'All Times')
United Kingdom	$2,160,145.83	?	('United Kingdom', 'CY 2003', 'All Times', 'All Times')
United States	$20,071,116.48	?	('United States', 'CY 2003', 'All Times', 'All Times')

As was mentioned at the top of this entry, the Internet Sales Amount measure group does not have a relationship with the Geography dimension. Still, the cube's coordinate structure, ([Geography].[Country], [Date].[Calendar Year], [Date].[Calendar Quarter], [Date].[Date]), must be employed. With the measure group's IgnoreUnrelatedDimensions property set to True, which is the default setting for this property, the All member is substituted in place of any specified members from unrelated dimensions:

select

[Measures].[Internet Sales Amount] on 0,

Geography.Country.Members
on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Internet Sales Amount		Coordinates
All Geographies	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Australia	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Canada	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
France	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Germany	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
United Kingdom	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
United States	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')

When the IgnoreUnrelatedDimensions property is set to False, SSAS does not perform any substitutions:

select

[Measures].[Internet Sales Amount] on 0,

Geography.Country.Members
on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Internet Sales Amount		Coordinates
All Geographies	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Australia	(null)	?	('Australia', 'CY 2003', , 'All Times', 'All Times')
Canada	(null)	?	('Canada', 'CY 2003', , 'All Times', 'All Times')
France	(null)	?	('France', 'CY 2003', , 'All Times', 'All Times')
Germany	(null)	?	('Germany', 'CY 2003', , 'All Times', 'All Times')
United Kingdom	(null)	?	('United Kingdom', 'CY 2003', , 'All Times', 'All Times')
United States	(null)	?	('United States', 'CY 2003', , 'All Times', 'All Times')

So, what about the situation where a measure group is only partially related to the attribute hierarchies within a dimension? Let's expand this simple cube to include a Sales Targets measure group. The Sales Targets measure group has a single measure, Sales Amount Quota, and has a relationship to just the Date dimension. Unlike the other measure groups which are related to the Date dimension at the Date attribute level, the Sales Targets measure group is related at the Calendar Quarter attribute level.

With IgnoreUnrelatedDimensions set to False on the Sales Targets measure group, we see the following behavior. Note, the number of rows has been limited with the HEAD function for display purposes only:

select

[Measures].[Sales Amount Quota] on 0,

HEAD([Date].[Date].Members,10) on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Sales Amount Quota
All Periods	$38,782,000.00
January 1, 2003	(null)
January 2, 2003	(null)
January 3, 2003	(null)
January 4, 2003	(null)
January 5, 2003	(null)
January 6, 2003	(null)
January 7, 2003	(null)
January 8, 2003	(null)
January 9, 2003	(null)

Executing the same query with IngoreUnrelatedDimensions set to True, returns the following:

	Sales Amount Quota
All Periods	$38,782,000.00
January 1, 2003	$38,782,000.00
January 2, 2003	$38,782,000.00
January 3, 2003	$38,782,000.00
January 4, 2003	$38,782,000.00
January 5, 2003	$38,782,000.00
January 6, 2003	$38,782,000.00
January 7, 2003	$38,782,000.00
January 8, 2003	$38,782,000.00
January 9, 2003	$38,782,000.00

Retrieving data at the Calendar Quarter level at which the relationship exists returns the following:

select

[Measures].[Sales Amount Quota] on 0,

HEAD([Date].[Calendar Quarter].Members,10) on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Sales Amount Quota
All Periods	$38,782,000.00
Q1 CY 2003	$5,913,000.00
Q2 CY 2003	$8,039,000.00
Q3 CY 2003	$13,733,000.00
Q4 CY 2003	$11,097,000.00

What we can take from this is that when IgnoreUnrelatedDimensions is set to True, axis members associated with specific attribute hierarchies with which the measure group does not have a relationship are converted to the All member.