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.
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",
"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
» 
Comment and Contribute
