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

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Traditionally the cubes structure is designed based on either star or snowflake schema. The dimensions in the cube is totally independent of each other and in the results can be obtained for any dimension members to any dimension. This is a classic illusion of an ideal world, i.e. even though the intention is good but the performance suffers since the cube is very sparse.

High cube sparsity adversely affect the MDX query performance. A very sparse cube can take longer to resolve calculated members and calculated cells, and MDX functions involving empty cells, such as CoalesceEmpty or NonEmptyCrossjoin, take slightly longer to process because of the large volume of empty cells that must be considered by such functions.

For example, the following diagram indicates three dimension hierarchies used to construct a cube for tracking orders.

Each customer, product & time dimensions have 5 members each. In the above design, between customer, product and time, theoretically 125 cells are possible ( 5 customer X 5 product X 5 time members).

However, in reality, may times, the existence of a real measure of a given dimensions is dependent on other dimensions. E.g. Sales rep can handle a few given territories, the given customer is handled by a few sales rep and the customer purchase a given set of products, some products are not even sold in other territories. So, up front in the design, we are sure that there would be a lot of dimension combinations for which real data would not exist.

Suppose customer and product dimensions have dependency on each other and the following following 11 valid combinations exists:

And suppose, these 11 combinations have data existing for all the 5 time periods, then the total number of valid combinations are 55 (11 customer-product X 5 time members).

It means that the actual density of the cube is only 44 % (55 real measures / 125 theoretical measures).

This cube is a theoretical example; in reality, many dimensions are much more sparse than indicated in this example. E.g. we have assumed that 11 customer product combinations have purchase history for all the 5 time periods, but if they have on an average 2 purchases out of five, then the actual number of cells would drop to 22 (11 customer-product X 2 time period) and the sparsity would drop further to 17% (22 real measures / 125 theoretical measures).

By themselves, the dimensions do not appear overly sparse; each dimension has members with relevant fact table data. If these dimensions are used together in a cube, however, the sparsity of the cube increases exponentially with each dimension, because the introduction of each dimension exponentially increases the number of cells within the cube. The above diagram, the shaded cells on customer - product face indicate the cells that actually contain data.

Dimensions with unrelated data can also greatly increase cube sparsity, especially if the dimensions are included as part of an associative relationship. For example, a business case is designed to compare the sales from retail customers with the sales from vendors, so a cube with three dimensions representing sales, customers, and vendors is created. The Customers dimension organizes retail customers by location, the Vendors dimension organizes vendors by sales region and vendor type, and the Orders dimension organizes order quantities by date. Both the Customers and Vendors dimension share elements with the Orders dimension, but not with each other. Because customers and vendors do not directly relate, from the viewpoint of the underlying data source, the result is a very sparse cube. In this case, it is easier to construct two cubes, one for vendors and one for customers, which share the Orders dimension.

Conversely, if beforehand, we know that dimensions are dependent on each other, then we can reduce the cube sparsity at the cube design level itself. Analysis Services 2000 did not support "Reference Dimensions" but it is something we can utilize in Analysis Services 2005.

We can design an intermediate dimension which contains the valid combinations of customer and product members at leaf level. This intermediate dimension joins with the fact table. The Customer and product dimensions are joined to fact table as a "Reference Dimension" utilizing the intermediate dimension.

The below diagram illustrates the conversion of above star schema to a "Reference-Intermediate" dimension structure. The shaded region is the valid combination of Customer-Product intermediate dimension and the time dimension, i.e. 55 cells and the sparsity is zero.

 

The intermediate dimension can be made invisible so that to a external OLAP cube consumer, there is no difference in the cube browsing experience even though the underlying cube structure has been changed drastically. The MDX queries too would run faster since the cube sparsity is reduced drastically by use of intermediate dimension.

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

 

The other day, I was working on a level based dimension hierarchy and the fact table with rather very few records.

I had applied all the dimensional modeling recommendations such as,

1. for each attribute,

• disable unary operator and customrullupformula if not needed.
• remove unwanted attributes
• isaggregatable property = true
• attributehierarchyenabled = false if not needed
• attributehierarchyvisible = false if you need to access the attribute but not visible in dimension browser
• define attribute relationship

2. In cube, all dimension to cube relationship is "Regular"

3. In cube partition, updated all the statistics (which is evident in above screenshot)

Testing process:

1. I took a set of leaf fact table level data set of five rows. In the profiler , the "Query Dimension" went on and on

Finally the query aborted and a "fatal error" message came:

Analysis Services had taken entire available RAM for this small query:

It seems there is a post SP2 hotfix which sounds like to adddress the above problem. A similar issue was reported in Analyiss Services 2005 forum from where I got the below information.

938077 (http://support.microsoft.com/kb/938077/)
FIX: The client application stops responding, and the Msmdsrv.exe process uses all the available memory after you perform the filtering operation and the browsing operation against an instance of SQL Server 2005 Analysis Services

In fact, there are a bunch of problem addressed in post SP2 hotfix.

Since, these hotfixes are not fully regression tested by Microsoft as yet, it is available through request only here.

Once, I apply the hotfix, would check if the above get resoled. I would update this blog then.

MDX Troubleshooting: comparing large numbers of MDX resultsets

Often I need to verify if the MDX results are correct after I fine tune the query or change the approach.

Usually, I copy and paste the cellset results in Excel and then compare them.

Recently, I got hold of a useful post from Chris Webb where he imported the cellset to a SQL table using SSIS and then used a Tablediff tool to compare the values. If we can make it a application, that would be great but till then, the approach serves the purpose.

You can read the blog here.