Fact table design for "State Workflow Analysis": Analysis Services Dimensional modeling

Note: This is a work in progress article and may be updated regularly based on feedback.

There are many business analytical needs whose data is in the form of “State Workflow”. A state workflow consists of a set of states. There are a valid number of “State Transitions”, i.e. changing of one state to another. The state transitions happen by some event. There can be a valid number of events responsible for state transition.

The typical business problem that conforms to above situation is “Sales Pipeline” analysis. The typical queries that need to be resolved are:

• How long it took to move from one state to another?
• How many items moved from one state to another?
• How much time spent in each state for a given period range?
• What is the count of items in a given state for a period range vs another period range?
• What is the aggregation of a related attribute with reference to state?

One typical analysis as presented by below graph is that for each day, what is the "% of Count of All opportunities" in each stages of sales pipeline.

Or analyze the data using additional dimension such as "by Industry"

Or by "Booked Revenue"

The question is how we design our OLAP system to do such kind of analysis. Well, the trick lies in creating an intelligent fact table where we capture many of details at the time of ETL process itself rather than importing the transaction system "AS-IS" and hoping that some OLAP magic wand would answer all of our queries.

Let us understand it with some simple example which needs similar kind of analysis. In today’s rising gasoline cost, what better example is to understand how I drive or use my car?

If you think about operating a car, you will immediately think of several states that could be modeled in a workflow. Here are the states that I’ve chosen for this example:

• Not Running: In this state, you are in the car but the engine is not running.
• Idling: You’ve now started the engine, but you’re not moving.
• Moving Forward: The car is moving forward.
• Moving in Reverse: The car is moving in reverse.
• Done with the Car: You are finished with the car.

There are many other states that you could model, but this list is enough to provide a substantial example. The next step is to identify the events that can occur while you are in each state.

Below table lists the events that are allowed for each state, along with the planned state transitions as each event is handled.

(Well, I still drive a manual gear car, for the sheer pleasure of it)

Lets look at our typical transactional system. Lets say I have an advanced tracking system installed in my car which records the states and events in a file. Typically, the transactional data recorded is very simple in the following structure:

Now, the typical analytical questions to understand my driving habits are:

On a given day, month or year:

• How many miles I drive in “Moving Forward”+ “Moving in Reverse” or individual state?
• How many times I “Apply Brake” while in “Moving Forward” state?
• How many miles I drive in “Gear Four” event in “Moving Forward” state? (higher the gear, better is the mileage)
• How much time is spent in “Idling” state for “Traffic Wait” event? (higher the time, higher is the fuel wastage)
• How many times, I do “Start the engine” event from “Not Running” state?
• How many times, I do “Beep Horn” even? (Am I compulsive honker?)
• How many times I change gears while in “Moving Forward” state?
• What is my average speed per trip?
• What is my average speed per trip while in “Moving Forward” state?
• Many more you can think of.

To answer the above analytical questions from our transactional data presented above is difficult for the end users. If a cube is created directly on the above transactional data, the MDX queries would be even more difficult. The point is, we want our end users to do more of analysis rather than spend effort on arranging the data prior to analysis.

The trick is to setup a ETL system which produces a synthesized fact table for data analysis. Lets take the below fact table which presents the same transactional data provided above:

From the above synthesized fact table, lets review if we can get the answers of our typical analytical questions to understand my driving habits. I think it should answers all of them.

We can create an OLAP cube on top of this fact table and using aggregation functions, answer the same analytical questions using MDX queries or expose the data to some OLAP client. Well, this is a topic for some other day.

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.

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

When a parent child hierarchy, which is essentially a unbalanced hierarchy is converted to level based hierarchy, it becomes a ragged hierarchy.

In the hierarchy, for many members, the parent members are not present in the immediate above level and we need to put placeholder members as parent in those levels. The HideMemberIf property of a level in a hierarchy is set appropriately to hide these placeholder or missing members from end users.

However, in the client applications, these placeholder members do not show properly as below:

The hierarchy in the client application can be displayed properly by using the MDX Compatibility property in the connection string the instance of Analysis Services which must be set to 2 to display ragged hierarchies correctly.

The MDX Compatibility property determines how placeholder members in a ragged or unbalanced hierarchy are treated. If you set the MDX Compatibility property value to 1, you expose a placeholder member in a ragged hierarchy.

Now the same hierarchy is displayed correctly: