 |
|
| SQL Server Tips: |
|
|
|
DATABASE MANAGEMENT AND ADMINISTRATION
CASE statements now supported by MDX script in SQL Server 2005
Baya Pavliashvili, Contributor
02.23.2007
Rating: -4.00- (out of 5)
|
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:
[IMAGE]
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":
[IMAGE]
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:
[IMAGE]
Although nested IIF syntax is still supported, you can probably tell that the first ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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:
As the results show, both calculations return the same values (I have abbreviated the results; the query actually returns 38 rows):
Reseller Sales AmountGrowth in reseller sales amount Growth in reseller sales amount IIF
Jul-01$489,328.58(null)(null)
Aug-01$1,538,408.31214.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.80175.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.93166.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
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
