SSAS - MDX: Time intelligence dimensions

In SSAS cube there is a frequent need to calculate statistical functions like sum, average, growth, growth percentage across various time periods. Some examples are listed below:

• Average growth over last year
• Total debt in previous financial year
• Year on year growth for each month in financial year
• Monthly rolling sum
• Year to date sales
• Quarter on quarter order count growth

Such calculations can be coded in a SSAS cube for each of the measures separately, but this is a time consuming and repeating task. It also makes the script much longer and difficult to understand.

There are some alternative approaches which enable us to code these calculations in a generic way so that they are applied across all measures automatically. This approach is explained at:

https://devinknightsql.com/2014/08/14/mdx-time-calculations-built-to-scale/

https://www.purplefrogsystems.com/blog/2009/11/scope-problems-with-mdx-calculated-members/

And finally there is a very good approach using which we can code in calculations in "two dimensions". First dimension lists "time aggregation" functions and the second dimension lists "calculation" to be done,  and then they are cross joined to provide the final set of calculations. For example the list of above calculations can be divided in two dimensions like:

• Aggregations
• Last Year
• Current Quarter
• Current Month
• Current Year to Date
• Rolling Last 12 months
• Quarter over Last Year Quarter
• Calculations
• Sum
• Average
• Growth
• Growth %

This remarkable approach makes the calculation script concise and well organized. It is little more difficult to imagine however. The blog below explains this method and we can also download Adventure works cube solution.

http://www.sqlbi.com/articles/datetool-dimension-an-alternative-time-intelligence-implementation/

AND
http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx

Also, this whitepaper has another approach if we want to specify the calculations in scoped hierarchies

http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf