Hands-On Procedure
Preparation
- 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.
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.
Illustration 2: Partial
Browse of the Promotion Table - Promotion_Id of 0
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.
Illustration 3: Select
Edit from the Context Menu
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.
Illustration 4: Select
New Calculated Member from the Context Menu
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.
|
Expression
|
Meaning
|
IIF([Promotions].CurrentMember.Name ="No Promotion",
[Measures].[Store Cost],
[Measures].[Store Cost]*1.15))
|
Conditional
logic is applied via the core IIF function. The IIF function in
MDX can be used to perform simple, yes-or-no decisions. Here, if "No
Promotion" (a zero promotion_id value) appears as the Promotions.CurrentMember
value, the unadjusted Store_Cost measure is returned.
If
the Promotions.CurrentMember is any value besides "No
Promotion" (a non-zero promotion_id value), the Store_Cost
value is multiplied by 1.15, returning the Store_Cost value,
adjusted upward by fifteen percent, to add the mandated fixed promotion
allocation to the total.
|
IIF([Measures].[Unit Sales]=0,0, .... )
|
The "external" IIF function is merely
in place to handle the situations that arise when no sales data exists for a
given item, and to prevent error indications from appearing. The calculated
measure thus returns a zero if no unit sales occurred, or the results of the
inner, primary function if sales data does, indeed, exist.
|
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.
Illustration 5:
Calculated Member Builder with Complete MDX Expression (Compressed View)
» 
Comment and Contribute
