CASE statements now supported by MDX script in SQL Server 2005

MDX in SQL Server 2005 Analysis Services brings exciting improvements including query support and expression/calculation language.

MDX now supports CASE statements, which are considerably easier to code and maintain than the nested IIF statements we had to write with previous versions. As with many other programming languages, CASE construct is used to examine various criteria and change the flow of the MDX script accordingly. The syntax of a CASE statement resembles the same construct in Transact-SQL, as follows:

For example, we could examine the levels of a particular hierarchy and apply a different calculation for a specific measure at each level. Using an Adventure Works sample cube, the following statement could define the calculation for "growth in reseller sales":

The first clause of this expression sets the value of the calculated member to "NA" for the first member of the fiscal hierarchy within the date dimension. Obviously, you can't calculate the growth if there are no prior fiscal periods; you don't have anything to compare with the current period. Next, the expression checks whether the previous member has an empty value. Again, it doesn't make sense to compare the current period with an empty value; so we set the value of the calculated member to NULL.

Finally, if neither of the conditions is met, we must be defining growth of the customer base for a member that isn't the first member and the prior member must have a non-empty value. So we subtract the customer count of the previous member from the customer count of the current member, and divide the result by the customer count of the previous member. How would we write the same expression in the previous version of Analysis Services? Like this:

Although nested IIF syntax is still supported, you can probably tell that the first format is much cleaner than the second. Now consider what would happen if we had to examine 10 different conditions instead of two! We can examine results of both calculations using the following query:

 SELECT {[date].fiscal.[month].members} ON 1, {measures.[reseller sales amount], measures.[growth in reseller sales amount], measures.[growth in reseller sales amount IIF]} ON 0 FROM [adventure works]

As the results show, both calculations return the same values (I have abbreviated the results; the query actually returns 38 rows):

  Reseller Sales Amount Growth in reseller sales amount Growth in reseller sales amount IIF
Jul-01 $489,328.58 (null) (null)
Aug-01 $1,538,408.31 214.39% 214.39%
Sep-01 $1,165,897.08 -24.21% -24.21%
Oct-01 $844,721.00 -27.55% -27.55%
Nov-01 $2,324,135.80 175.14% 175.14%
Dec-01 $1,702,944.54 -26.73% -26.73%
Jan-02 $713,116.69 -58.12% -58.12%
Feb-02 $1,900,788.93 166.55% 166.55%
Mar-02 $1,455,280.41 -23.44% -23.44%


More powerful Analysis Services MDX in SQL Server 2005
 Introduction: Introduction
 Part 1: CASE statements now supported by MDX script
 Part 2: Sub-queries supported in Analysis Services MDX
 Part 3: MDX functions: SCOPE, THIS and FREEZE

Baya Pavliashvili is a DBA manager overseeing database operations that support more than one million users. Pavliashvili's primary areas of expertise are performance tuning, replication and data warehousing.

Dig Deeper on Microsoft SQL Server 2005