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

August 16, 2004

Procedure - Creating a Derived Measure

We will next create a derived measure, whose role will be to improve performance from the perspective of querying, and thus from that of the information consumers. The derived measure will be directly based upon columns in the FoodMart database, upon which we will use SQL to perform the required logic, as we shall see. The key reason, we will recall, for creating the derived measure is that the measure is calculated as a part of cube processing, and is stored completely in MSAS for rapid retrieval, versus being generated at runtime like the calculated measure.

We will name our derived measure DM_Cost with Promo Alloc, and, once we verify that it stores the desired values, will replace the MSAS Admin's calculated member that we recreated in the last section.

1.  In the tree pane of the Cube Editor, right click the Measures folder.

2.  Select New Measure from the context menu that appears, as shown in Illustration 14.

Click for larger image

Illustration 14: Select New Measure ...

The Insert Measure dialog box appears, offering us the appropriate columns in the designated fact table for the cube, sales_fact_1997.

3.  Click the store_cost column to select it, as depicted in Illustration 15.

Illustration 15: Select Store_Cost (Circled) ...

4.  Click OK to accept the selection, and to close the Insert Measure dialog.

The new measure appears in the tree, named Store Cost 1 by default (because a measure named Store Cost already exists in the cube), as shown in Illustration 16.

Illustration 16: The New Measure Appears ...

5.  Click the new Store Cost 1 measure to select it, if necessary.

We will now rename the measure, and then add the syntax to the Source Column property to enable the same logic in the measure that we saw in the original calculated measure, and which we have confirmed with management to be correct.

6.  Expand the Properties pane that appears below the tree if required, by clicking Properties, as depicted in Illustration 17.

Illustration 17: Upward Arrow Indicates Properties Pane is Collapsed ...

The Properties pane, Basic tab, appears, expanded, as shown in Illustration 18.

Illustration 18: Expanded Properties - Basic Tab

7.  In the Name property, replace the existing name, Store Cost 1, with the following name:

DM_Cost with Promo Alloc 

8.  Type the following directly into the Source Column property, replacing "sales_fact_1997"."store_cost":

IIf("sales_fact_1997"."promotion_id" = 0, 
  "sales_fact_1997"."store_cost"* 1.15 )

The Properties pane, Basic tab, with our modifications, appears as depicted in Illustration 19.

Illustration 19: Properties Pane - Basics Tab, with our Modifications