Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances - Page 3
December 13, 2004
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.
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.
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.
The Calculated Member Builder appears.
9. Type the following into the Member name box:
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.
12. Click OK, to close the message box.
The Calculated Member Builder appears as depicted in Illustration 11.
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.
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.
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.
(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.
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.