Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures - Page 5August 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 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.
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.
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.
The Properties pane, Basic tab, appears, expanded, as shown in Illustration 18.
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,
The Properties pane, Basic tab, with our modifications, appears as depicted in Illustration 19.
|
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
"sales_fact_1997"."store_cost",
