Procedure
Add
Semi-Additive Calculated Members to Support Inventory Requirements
We now have a cube
within which to build the calculated member which will act as a semi-additive
inventory measure. Since no inventory account exists, and since the business
requirement is to create a "quick and dirty" inventory level measure
for purposes of demonstrating the behavior of a semi-additive measure within
MSAS, we will rely upon the Product measures in place to serve as a basis
for deriving our new measure.
The DBJ_SemiAdd cube contains two measures, Units
Shipped and Units Ordered, which we will use as the basis for our
product stocking levels. The measures are depicted in Illustration 7.
Illustration 7: Two
Inventory-Related Measures in the Clone Cube
Although it is,
admittedly, a rough means of coming to an inventory balance, we remind
ourselves that our focus is to produce a conceptual environment to demonstrate
the general behavior of semi-additive measures. We can assume for our purposes
that Units Ordered less Units Shipped for a given month (as an
example time period), equals units that remain, or, in effect, Product
stock on hand. Let's create a calculated member based upon this logic, and
delve into the considerations surrounding semi-additive measures as we proceed.
1.
Right-click
the DBJ_SemiAdd cube, in the cube tree in Analysis
Manager.
2.
Select Edit
to open the Cube Editor.
3.
Within the Cube
Editor, click the Data tab.
Cube data
is retrieved, and we are able to see the values that appear for all measures,
including the two inventory-related measures we have identified.
4.
Click the Time
dimension button in the Filter area of the Data tab.
5.
Drag the Time
button onto the top of the Product Family row heading in the Data
Grid area.
6.
Drop the Time
button onto the Product Family row heading, to swap the Time
dimension for the Product dimension in the Row axis.
The "swap"
procedure we are undertaking is shown in Illustration 8.
Illustration 8: Swapping
the Time Dimension into the Row Axis (Data Grid - Partial View)
The Time
dimension now appears on the Row Axis.
7.
Select Insert
from the main menu of the Cube Editor.
8.
Select Calculated
Member from the cascading menu that appears, as depicted in Illustration
9.
Illustration 9: Select
Insert --> Calculated Member
The Calculated
Member Builder appears.
9.
Type the
following into the Member name box:
Stock Level
10.
Type the
following into the Value expression box:
[Measures].[Units Ordered] - [Measures].[Units Shipped]
11.
Click the Check
button, at the upper right of the Value expression box, to perform a
simple syntax check.
Analysis
Manager generates a message box, indicating that "Syntax is OK," as
shown in Illustration 10.
Illustration 10: Simple
Syntax Check Is Positive ...
12.
Click OK,
to close the message box.
The Calculated
Member Builder appears as depicted in Illustration 11.
Illustration 11: Calculated
Member Builder with Our Additions (Compact View)
13.
Click OK,
to accept our input and close the Calculated Member Builder.
The Calculated
Member Builder closes, and we are returned to the Data view of the Cube
Editor.
The Stock
Level calculated member appears in the Data Grid, as shown circled
in Illustration 12.
Illustration 12: Stock
Level Calculated Member Appears in Data Grid (Partial View)
Now, let's
refine the Data Grid to reflect our immediate concentration.
14.
Click the Warehouse
dimension button in the Filter area of the Data tab.
15.
Drag the Warehouse
button onto the top of the MeasuresLevel row heading in the Data
Grid area.
16.
Drop the Warehouse
button onto the MeasuresLevel row heading, to swap the Warehouse
dimension with MeauresLevel in the Data Grid.
The "swap"
procedure we are undertaking is depicted in Illustration 13.
[DJ_ANSYS30-013]
Illustration 13: Swapping
the Warehouse Dimension With MeasuresLevel
(Data Grid - Partial
View)
The Warehouse
dimension now appears (manifested as the Country column heading),
as shown (circled) in Illustration 14.
Illustration 14: Warehouse
Dimension in the Column Heading
(Data Grid - Partial
View)
Let's
collapse the Warehouse dimension, which defaulted to expanded mode upon its
placement in the steps above, again to refine the view to our immediate focus,
the behavior of the Stock Level calculated member.
17.
Right-click
the All Warehouses heading that appears immediately underneath the Country
heading we noted above.
18.
Select Drill
Up from the context menu that appears, as depicted in Illustration 15.
Illustration 15: Select
Drill Up to Contract the Warehouse Dimension - Top Level
The Warehouse
dimension collapses to a single column. We now need to filter the values that
appear in the Data Grid. The values currently represent an aggregate of
all measures. We wish to filter the values to isolate our new Stock Level
measure.
19.
Select Stock
Level from Measures, which we moved to the Filter section in
the swap we made with the Warehouse dimension in the immediately
preceding steps, as shown in Illustration 16.
Illustration 16: Filtering
by the New Stock Level (Calculated) Measure
» 