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% |
| ABOUT THE AUTHOR: |
|
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.
Copyright 2007 TechTarget
|
|
More on this topic:
Use cube partitions to improve Analysis Services performance
Analysis Services 2005 in SQL Server has improved security
SQL Server Business Intelligence toolbox
Introduction: 
