I am somehow surprised on the small number of BI developers and BI DBAs with MDX knowledge. I have a feeling that this language is underestimated, or may I say missunderestimatedJ I know most of the OLAP analyses are done graphically, with client tools. However, there are many business questions and problems you simply cannot resolve graphically. This is the place where MDX strength comes in, with queries and expressions.Let me enlist couple of problems:

  • You need a report for top 10 customers, and then for each customer top 5 products purchased.
  • You have financial cube, with balance measure showing outstanding customers’ debts. Balance is a semi-additive measure, so you use LastNonEmpty aggregate function. However, most of customers have balance zero, so your aggregate shows zero, unless you find a specific customer with outstanding debt. Drilling down for each customer separately is tedious and far from regular OLAP experience.
  • You need to create a KPI. For any KPI, the trend value usually includes comparison of current with some historical data. How do you create a KPI?

The solution for all three problems is MDX. For the first problem, you can create a MDX query, i.e. a MDX SELECT statement. For the second one, you can create MDX expression to find only customers with outstanding debts and add it as a named set to your cube. With this named set, querying is simple again, and possible with graphical client tools like Excel 2007. For the third problem, you need, of course, a MDX expression that uses MDX functions that find historical members, and compare their value to the value of the current member.

All together, I think it is time for advanced BI implementers to learn MDX.

Dejan Sarka