Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures - Page 3August 16, 2004 Hands-On ProcedurePreparation - 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 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.
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.
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.
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.
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.
|
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
