|Calculating Ages In MDX|
|Written by Chris Webb|
|Tuesday, 10 June 2014 20:01|
Reposted from Chris Webb's blog with the author's permission.
One area where I see people have a lot of problems when they're learning MDX is when they are working with dates. SQL has a lot of great functionality for working with dates while MDX, to be honest, does not - therefore people tie themselves in knots writing complex MDX expressions (which often don't perform all that well) to recreate what they would do in SQL. However, as is often the case with MDX, approaching the questions from a completely different angle yields much better results.
For example, let's imagine that you wanted to calculate the age in years of any customer on any given date. Using the Adventure Works cube you have a Date dimension, which will be how you want to select the date that the age is to be calculated at; there is also, on the Customer dimension, an attribute called Birth Date that gives you each customer's date of birth. You need a calculated measure that calculates the age of each customer for any date selected on the Date dimension.
If you think about this as a SQL date problem, you'll probably want to use functions like DateDiff() - one of the VBA functions that is available in MDX (but is not native MDX). The following article shows how DateDiff() and other VBA functions can be used to calculate ages in VBA code:
Here's a query showing how this calculation can be implemented in MDX:
The output of the query is this:
On my laptop this query, which returns 365 columns and 106 rows, returns in around 3.5 seconds. A few things to point out:
Performance here is ok, but we can do better. The most important thing to do when tuning any MDX calculation is to try to pre-calculate as much as possible. In this case there are two calculations we can move out of MDX into the structure of the cube:
Here's the rewritten version of the calculation, assuming that the following new attributes have been added to the Customer and Date dimensions:
This query runs in around 1.2 seconds, a significant improvement. Of course if you were to create a completely new fact table that contained the age of every customer on every date in the Date dimension then you wouldn't need to use any calculated measures and performance would be even better, but that may not be practical if you have a lot of customers and a lot of dates.