Universal calculated measures in MDX queries was the title of my session in SQLBits IV conference held in Manchester on 28th of March, 2009. Abstract of it follows.
Among many of its functions, MDX language has one special set function – Axis() function. That function allows creation of calculated measures that are fully context aware and, if wanted, don’t need to refer to any dimension or hierarchy in the cube. In other words, such measures are universal or independent, which means they can be used in any MDX query.
In this session we will present such measures and explain how they work. We’ll also show the way how to design them for various scenarios and discuss their potentials and weaknesses.
The presentation together with all the queries, SSAS projects (based on Adventure Works 2008 sample) and other materials can be downloaded here.
Since this topic of using Axis() function is much bigger than what can fit in an hour or so, my intention is to write a paper on this subject, similar to papers about utility (shell) dimension found here or many-to-many dimensional modeling found here, in order to present and explain all the findings in a proper manner, so that they can be used in various BI projects. The plan is to publish it here, in this blog by May 2009. Working title? Universal calculations in MDX.
I think one thing that will need some research is the impact that Axis() has on performance. I’m fairly sure any use of it will prevent use of the formula cache, and I don’t know whether it forces cell-by-cell evaluation too…
The use of Axis() can indeed have a very bad impact on performance. It does prevent formula cache. Most likely it also prevents bulk evaluation. But, it’s not the only MDX function with such behaviour. String handling functions, to name one kind of them, have the same negative impact on performance, yet people use them in implementation, sometimes because they don’t know how else to solve things, sometimes because the situation really requires so.
On the other side, there were situations (some MDX puzzles on SSAS forum) when I used string handling functions and achieved the fastest query result compared to other, set-based or recursion-based calculations made by others. So it really depends on a particular case and what is being tried to accomplish.
In general, it’s a trade-off. One looses some performance while achieving more flexibility on the other side. The amount of loss is very important and that is something I intend to investigate for sure and give recommendations (and code of course) on how to minimize that loss.
I’ve already presented in my session how the blind use of Axis() in a search-replace manner dramatically kills performance. I also presented some ideas on how to regain it by rethinking about each calculation and what it does in its essence. Finally, I emphasised that it is necessary to fine-tune all calculations that use Axis() function since they tends to slow the query response, sometimes even notoriously.
Putting away all the pluses and minuses one gains by using Axis() function, the bottom line is that this is an area not properly explored and as such – a call for adventure.
Do I read between the lines that you’re interested in taking part in this? If so, consider yourself invited :-).
Oh, and thanks for the first comment in my blog so far (by anyone)!