- 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.
- Python for SQL Server Specialists Part 4: Python and SQL Server - April 24, 2018
- Python for SQL Server Specialists Part 3: Graphs and Machine Learning - April 11, 2018
- Python for SQL Server Specialists Part 2: Working with Data - March 22, 2018