- Creating a Derived Measure
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
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.
In the tree
pane of the Cube Editor, right click the Measures folder.
Measure from the context menu that appears, as shown in Illustration 14.
Measure dialog box appears, offering us the appropriate columns in the
designated fact table for the cube, sales_fact_1997.
Click the store_cost
column to select it, as depicted in Illustration 15.
Illustration 15: Select
Store_Cost (Circled) ...
to accept the selection, and to close the Insert Measure dialog.
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
Illustration 16: The New
Measure Appears ...
Click the new Store
Cost 1 measure to select it, if necessary.
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.
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 ...
pane, Basic tab, appears, expanded, as shown in Illustration 18.
Illustration 18: Expanded
Properties - Basic Tab
In the Name
property, replace the existing name, Store Cost 1, with the following
DM_Cost with Promo Alloc
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 )
pane, Basic tab, with our modifications, appears as depicted in Illustration
Illustration 19: Properties
Pane - Basics Tab, with our Modifications