Optimizing Microsoft SQL Server Analysis Services: Optimize Query Performance with a Derived Measure

8. Right-click the Calculated Members folder within Cube Editor.


9. Select New Calculated Member … from the context menu that appears, as depicted in Figure 4.


Figure 4: Select New Calculated Member from the Context Menu


The Calculated Member Builder opens.


10. Ensure that the Parent dimension is set at Measures, the default.


11. Type the following into the Member name box:

CM_Cost with Promo Alloc


12. Type the following MDX into the Value Expression section of the Calculated Member Builder:


IIF([Measures].[Unit Sales]=0,0,

(IIF([Promotions].CurrentMember.Name =”No Promotion”,

([Measures].[Store Cost]),([Measures].[Store Cost]*1.15))))

The MDX expression above returns, via the calculated measure, the correctly adjusted Sales Cost value. An explanation of the components of the above expression appears in Table 1.


Table 1: Expression Components Summary

The Calculated Member Builder appears with our input as depicted in Figure 5.


Figure 5: Calculated Member Builder with Complete MDX Expression
(Compressed View)

Continues…

Leave a comment

Your email address will not be published.

Expression Meaning
 

IIF([Promotions].CurrentMember.Name =”No Promotion”,

[Measures].[Store Cost],

[Measures].[Store Cost]*1.15))

Conditional logic is applied via the core IIF function. The IIF function in MDX can be used to perform simple, yes-or-no decisions. Here, if “No Promotion” (a zero promotion_id value) appears as the Promotions.CurrentMember value, the unadjusted Store_Cost measure is returned.


If the Promotions.CurrentMember is any value besides “No Promotion” (a non-zero promotion_id value), the Store_Cost value is multiplied by 1.15, returning the Store_Cost value, adjusted upward by fifteen percent, to add the mandated fixed promotion allocation to the total.
 

IIF([Measures].[Unit Sales]=0,0, …. )

The “external” IIF function is merely in place to handle the situations that arise when no sales data exists for a given item, and to prevent error indications from appearing. The calculated measure thus returns a zero if no unit sales occurred, or the results of the inner, primary function if sales data does, indeed, exist.