Time Calculation Hierarchies, MDX, and DAX
We are in the midst of change in the world of Microsoft Business intelligence. The familiar SQL Server Analysis Services multidimensional model has been supplemented with the new tabular model. Alongside of the multidimensional MDX language we have the tabular DAX language.
This past year I have heard several excellent presentations at conferences showing the benefits of the tabular model and DAX. But I’ve decided to continue my focus on the multidimensional model and MDX.
Why have I chosen to do that?
There are some things – a lot of things in fact – that you can do with the multidimensional model and MDX that you can’t do with the tabular model and DAX. One of the best things is the ability to create time calculation hierarchies.
It’s one thing to create time-based calculations. You can do that in both MDX and DAX. But you can’t create a time calculation hierarchy in the tabular model.
A time calculation hierarchy is a set of members where each member applies a calculation to the measures that are being displayed. With both MDX and DAX you can create calculations for Year-To-Date, Month-To-Date, and 6-Month Running Average to individual measures. But only in MDX can you create a hierarchy that has members for each of these calculations and can apply those calculations to any measure you want.
Here’s what it looks like when you have individual calculations. The Sales Trend is calculated from the past 3 years of Internet Sales. The Sales Goal is calculated from the Sales Trend. You can do this with MDX or DAX:
Here’s what you can do with a time calculation hierarchy. You have calculations built into the members and those calculations can be applied to any measure you choose. You can do this with MDX and the multidimensional model, but you can’t do this with the tabular model and DAX:
In the first example I had to create 8 calculations, but in the second example only 2 calculations. And those 2 calculations could be used for any number of measures.
I have written an entire module on time calculations in my new Advanced MDX course. I show you how to create different types of time calculation members:
Current Period Calculations
Relative Date Period Calculations
Period To Date and Rolling Average Calculations
Comparison, Ratio, and Forecasting Calculations
I also show how to create a second hierarchy for choosing the date that is to be used in the time calculation hierarchy. For one of my clients I have created 30 members in a Time Calculation hierarchy, each of which can be applied with any of 15 different dates in the Select Date hierarchy. So each of the 100 measures in the cube can be used with any of the combinations from the two hierarchies – for a combined total of 45,000 possible calculations (30 X 15 X 100). I’m sure glad I didn’t have to code tens of thousands of calculations separately!
The multidimensional model allows you to leverage date calculations because it’s truly multidimensional. You can apply calculations in members of a hierarchy. The tabular model does a lot of wonderful things – some of them better than the multidimensional model – but it’s limited because it’s tabular.
The tabular model lives half-way between the relational world and the multidimensional world. That’s its strength – but it’s also a weakness. SQL and the relational world may be familiar to database developers, but MDX and multidimensional cubes do a much better job modeling the real world of business. Business people think multidimensionally and hierarchically – and that’s what we do in the multidimensional world of MDX.