Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures - Page 3

August 16, 2004

Hands-On Procedure

Preparation - Retracing the Steps of the New MSAS Administrator

Let's first create a copy of the original calculated measure, so as to provide a basis of comparison for the derived measure, which we will next create to take its place. To repeat the original requirement, we need to generate an adjusted store cost value, which contains an additional, fixed allocation of marketing costs. Specifically, we will add fifteen percent to the store cost value in any case where a specific marketing promotion was involved.

We will be able to "flag" the use of promotions using the promotion_id in the sales_fact_1997 table, which we can see within the Schema view of the FoodMart Sales cube. It appears, with the promotion_id column circled in red, as shown in Illustration 1.

Click for larger image

Illustration 1: The Promotion_Id Column in the Fact Table

A quick browse of the promotion table (which is joined to the sales_fact_1997 table via the promotion_id column), the results of which are partially depicted in Illustration 2, reveals that a promotion_id of 0, and a corresponding promotion name of "No Promotion," indicates the absence of a promotion.

Illustration 2: Partial Browse of the Promotion Table - Promotion_Id of 0

The MSAS administrator reasoned that this field could serve as the basis of a conditional expression, to the effect of "if the promotion_id equals zero, we need not apply any percentage increase to the existing store_cost value (and can accept its current value in our new measure)," as no additional promotion was performed for any associated items. If, however, the promotion_id equals anything except zero, she could apply the mandated fifteen percent upward adjustment to store_cost. The operation could then be handled using a relatively straightforward "IF-THEN" construct, as we shall see in the following steps, where we recreate the original calculated measure.

1.  Open Analysis Manager.

2.  Expand the Analysis Servers folder in the management console.

3.  Expand the Analysis Server with which you are working by clicking the "+" sign to its left.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder inside the FoodMart 2000 database.

6.  Right-click the Sales cube.

7.  Select Edit ... from the context menu that appears, as depicted in Illustration 3.

Illustration 3: Select Edit from the Context Menu

The Cube Editor opens.

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

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

Illustration 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.



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.

Table 1: Expression Components Summary

NOTE: For an explanation of the .CurrentMember function, see my article MDX Essentials: MDX Member Functions: "Relative" Member Functions in the MDX Essentials series at Database Journal.

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

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