Home > Business Intelligence > Time Calculation Hierarchies, MDX, and DAX

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.

Categories: Business Intelligence
  1. NA
    October 18, 2012 at 1:48 pm

    Sounds interesting, but no details are provided, your post is purely an advertisement for your advanced mdx course … a simple example with code would have been nice.

  2. October 20, 2012 at 2:46 am

    And I hope it is a good advertisement! We have invested a lot of time and money in developing an Advanced MDX course and we think we have created a course that will be useful for many developers.

    For a starting example of a time calculation hierarchy, use the Microsoft Data Tools (BIDS in SSAS2008), go to the MDX Script tab and run the Business Intelligence Wizard to create time intelligence. That will create a time calculation hierarchy and you can see in the MDX Script how the code works. There’s a lot more to learn about time calculations then that, of course, and I will be providing additional blog posts discussing different aspects in creating them.

    I think the value of this blog post (besides the advertisement!) is in emphasizing how you can do some things with MDX and the multidimensional model that are not supported with DAX and the tabular model. I think that time calculation hierarchies are very useful and the ability to create them should be a factor when you choose between SSAS multidimensional and SSAS tabular. That was the main point I was trying to make in my post. Any views, opinions, disagreements, agreements, perspectives, ideas, etc. are welcome.


  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: