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.