Rolling period with shell dimension SSAS

Reposted from Jens Vestergaard's blog with the authors permission.

Often I meet the business requirement of listing data for the last X months. In this blog post, I will try to give my shot at, how this is done elegantly and with maximum flexibility.

We start of backwards and enhance the Data Source View in the cube with a custom Named Calculation called UtilityKey (or whatever you like). The query is probably the most simple Named Calculation you’ll ever write, since it should be looking awfully a lot like the next figure:

When this Named Calculation is ready to go, we need to make use of the fact that having a dimension with the key = 0, means we have a hold of every row in that fact table! Enter shell dimension.
We create a table in SQL Server that holds the dimension we want to be able to slice by, using the utility key; In this case: A periodicity dimension. A dimension that hold the members by which we want to slice data. The table in this example looks as follows:

We then populate with data that matches our business requirements, in this case we need the members All, 1 Month, 3 Months, 6 Months and 12 Months:

This dimension table, we then use to create a dimension in the cube, based on the key and name columns.

Ending up with a result, after trimming the names, setting IsAggregatable = False etc:

The clever part of this shell dimension is that we then define a SCOPE in the cube that allows us to slice the data as we see fit. We use the regular time dimension present in the cube to navigate through the five (5) different members of our Periodicity dimension, like this:

Slicing the cube by Periodicity and not by the regular Calendar dimension yields the desired result to the business requirement.

Jens has been working in the IT Industry since mid 90s. He started focusing in on the SQL Server Stack around 2003, mainly Analysis Services and the RDBMS. His blog is at http://www.t-sql.dk and he currently works for http://www.rehfeld.dk as a senior BI consultant.