Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

Chapter 10. Advanced Topics in MDX

This chapter follows up on and extends what you learned back in Chapter 3: the basic concepts of the MDX language and how to write MDX queries. You also learned about the MDX operators and functions that are supported by the MDX language; including calculated member and named sets creation. If you are thinking, "that was several chapters ago, I already forgot everything!" you might want to go back and review the material before continuing here. In Analysis Services 2008, the majority of calculations are defined in a dedicated location, called the MDX script. (Technically, there can be multiple scripts per cube, though the UI only surfaces one.)

MDX scripts can contain complex calculations on multidimensional data and consist of various types of MDX statements and commands, each separated by semicolons. CALCULATE, SCOPE, IF-THEN-ELSE, and CASE are just a few of the MDX statements that can be used within an MDX script. MDX scripts are meant to be structured in a way that the flow of the statements is simple and readable. The scripting language itself is based on a procedural programming model and although it may sound complex, it is actually simpler to use than certain predecessor technologies. This is due to simplification of syntax. You can actually step through statements in MDX scripts and see results formulated in real-time, which is a real boon to the debugging process, as you saw in Chapter 9.

Of particular importance for successful MDX ...