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([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.
IIF([Promotions].CurrentMember.Name =”No Promotion”,
|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.
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.|