Point in time analysis, using the many to many approach

Being able to perform a point in time analysis is a common business requirement. Most of the business users I know use Microsoft Excel 2007 / 2010 as their main client application for analysis (with Microsoft Analysis Services). The business users expect Excel to support point in time analysis. Unfortunately this can not be done in Excel in a straightforward way.

What is point in time analysis? Imagine we record the planning per project per resource per day. Each change in the planning is recorded as a seperate line in our data warehouse fact table and tagged with a start and end date, the start and end date represent the period in which the record (planning) was current. This design pattern is also known as “Slowly Changing Fact” and is similar to the Slowly Changing Dimension pattern. 

Using the start and end date we can see what the current planning was on a certain date. For example; I have recorded the planning (and its changes) for december 2010:

Planning Facts

I would like to know what my planning was on 12 december 2010 for project “Student Lifecycle Intelligence”. In Excel this means stating the following: “Current from >= 12 december and Current to <= 12 december”. This is rather technical and not intuitive for the user. The user wants to be able to say “Give me the current planning on 12 december 2010″.

The solution can be found in the design of the underlying dimensional model. This design is based on the “many to many revolution” article by Marco Russo. This is a must read for any dimensional modeler.

The proposed design pattern takes all unique “valid from” and “valid to” combinations and places these in a seperate dimension. This dimension will not be shown to the user. A factless table called “ValidFromToTime” is placed between the newly created (hidden) dimension “ValidFromTo” and the known dimension “Time”.

This model can be used in SSAS and exposes a different time dimension (role played) from which the user can select a particular date. In our example the user would select 12 december 2010 as the date.

There are still a few drawbacks to this approach, for example, what is keeping the user from selecting a month instead of a particular date and thus accumulating planned hours which should not be accumulated. You could restrict this using dimension – attribute security in SSAS.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s