Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

In my previous blog, I discussed the approach for "Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip". [Watch for the difference, Inter and Intra] The approach discuss the situation where members from one dimension are valid for a few members on other dimension. The typical example would be that for Sales Accounts, normally,only the sales department would be applicable and Sales data wont exists for say, "vehicle expenses department".

But what if there exists a relation between the members on a given "single" dimension? During the cube modeling phase, many a times designers ignore this aspect and later, many of the needed business reports can not be generated by the MDX reporting clients and complex MDX queries need to be hand coded.

Let us explain this with a simple business example of a consulting company. The typical chart of account of our fictitious consulting company is:

The data for two years are:

 

The resultant fact table structure is:

The desired report that needs to be generated is:

However, what seems like a simple report can not be generated by the client applications since they don't understand the relationship that exists between the dimension members. The resultant MDX query would look something like below:

with member Measures.[USD/HR] as ' IIF(Account.currentmember is [Sales - Product 1],[Sales - Product 1]/[Revenue Hours - Product 1],     IIF(Account.currentmember is [Sales - Product 2],[Sales - Product 2]/[Revenue Hours - Product 2],         IIF(Account.currentmember is [Sales - Product 3],[Sales - Product 3]/[Revenue Hours - Product 3],             IIF(Account.currentmember is [Sales - Product 4],[Sales - Product 1]/[Revenue Hours - Product 4],0))))' select { [Sales - Product 1], [Sales - Product 2], [Sales - Product 3], [Sales - Product 4], } on rows, Crossjoin({[2007],[2008]},{Measures.[Amount],Measures.[USD/HR]} on columns from cube

Not a very elegant MDX, huh.

However, we know that the following relation exist among the dimension members:

The key is to relate these dimension members in the cube so that in the MDX queries, we can take advantage of these relations and make the queries faster as well as the simpler.

In the below paragraph, we would use the following terminology:

Attribute & parent dimension members: The dimension members which provide the details to the parent dimension members to which it is related. E.g., in above example, the "Revenue Hours - Product X" dimension members, Attribute dimension members, provide the number of hours for "Sales - Product X" dimension members, parent dimension members.

There are two possible approaches to define the relation between these dimension members:

Approach 1: Define the attribute dimension member as the "measure" of the parent dimension members

Approach 2 :  Define the attribute dimension member as the "Attribute" of the parent dimension members

Approach 1: Define the attribute dimension member as the "measure" of the parent dimension members

If we know these relations beforehand, then we can modify the dimensional members and load the value of related dimension member as additional "Measures" in the fact table at the time of data load.

We can load the same data as below:

The above structure lends itself more for analysis purpose and the end users can now make the desired report using MDX client tools. The representative MDX query would look something like below:

with member Measures.[USD/HR] as 'Measures.[Amount]/Measures.[Hours]' select { [Sales - Product 1], [Sales - Product 2], [Sales - Product 3], [Sales - Product 4], } on rows, Crossjoin({[2007],[2008]},{Measures.[Amount],Measures.[USD/HR]} on columns from cube

 

Approach 2 :  Define the attribute dimension member as the "Attribute" of the parent dimension members

This approach can be taken if the earlier approach of "converting attribute dimension members as measures" is not feasible. Approach #2 is much less efficient approach than the Approach #1, but it is still elegant than the approach of lots of nested IIF statements.

In this approach, on account dimension, we create a property called "Hours" and for parent dimension members, we can store the reference of attribute dimension members.

E.g., suppose, in this case, we store the Member Keys as the Hours attribute for "Sales - Product X" dimension members. Now, the MDX query to generate the same report would be something like below:

with member Measures.[USD/HR] as 'Measures.[Amount]/(StrToMember("[Account].&[" + [Account].Currentmember.properties('Hours') + "]"))' select { [Sales - Product 1], [Sales - Product 2], [Sales - Product 3], [Sales - Product 4], } on rows, Crossjoin({[2007],[2008]},{Measures.[Amount],Measures.[USD/HR]} on columns from cube