Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Specification Made Simple: Single Dimensions
Let's shift from the Warehouse cube we used in Part 1 to the Budget cube that, like the Warehouse cube, is supplied as a sample with the Typical installation of Microsoft SQL Server 2000 Analysis Services (hereafter simply called Analysis Services). Create a new calculated member within the Budgets cube by following these steps:
In the Analysis Manager console, expand the Cubes folder within the FoodMart 2000 database sample (see Figure 1).
Figure 1 Sample cubes provided with the Analysis Services
installation.Right-click the Budget cube and click Edit on the context menu.
When the Cube Editor opens, click the Data tab in the lower-left corner to display the data viewing pane (see Figure 2).
Figure 2 The Cube Editor's data viewing pane for the Budget
cube.Drag the Store dimension from the top pane down to replace the Category dimension in the row axis. Figure 3 shows the result.
Figure 3 The data viewing pane after the swap.NOTE
To "swap" Category to the top and Store below, you can drop the icon that appears over the Category Description heading (which becomes the Level02 heading) in Category's old place. A small, double-headed arrow appears at the drop point.
From the menu, choose Insert, Calculated Member. (For more details on creating calculated members, refer to Part 1 of this series, "Basic Concepts and Navigation.")
When the Calculated Member Builder appears, type MyCalcMem2 in the Member Name box. In the Value Expression box, type the following expression (see Figure 4):
[Amount]/12
Figure 4 The new calculation is inserted in the Value Expression
box.Click OK. The newly created MyCalcMem2 column should look like the one in Figure 5.
Figure 5 The MyCalcMem2 value is based on the Store dimension's
current member.The value of MyCalcMem2 is one-twelfth of the Amount of the current row. The calculation is performed independently in each cell as shown, and bases its value on the current member for the Store dimension.
In the Cube tree, select MyCalcMem2 in the Calculated Members folder for the Budget cube. The Basic tab of the Properties pane appears underneath MyCalcMem2.
On the Basic tab, select the Parent Dimension property of MyCalcMem2.
Select the Store dimension from the drop-down list (see Figure 6).
Figure 6 Select Store as the Parent Dimension.Press Enter. The dynamic caption at the bottom of the Properties pane shows that we're selecting "the dimension that contains the calculated member." An error appears as the value for the current row, MyCalcMem2 member cell, because the Amount member has no value for the current row.
Change the Value property for the MyCalcMem2 calculated member to the following value, either by directly typing into the Value box or by clicking the ellipsis (...) button to open the Calculated Member Builder dialog box:
[USA]/12
Click OK and compare your results to Figure 7.
Figure 7 The value for MyCalcMem2 reflects the entered formula.The value in the MyCalcMem2 row equals the value shown earlier (refer to Figure 5), and is one-twelfth of the USA row above it.
Select 1998 in the filter field for the Year dimension at the top of the data viewing pane. Figure 8 shows the result.
Figure 8 Result set for 1998.The amounts are now negative because the sample Budget cube only reflects actual expense items in its measures for 1998 (the "current year" of the cube). We'll see this more clearly as we work with lower levels of the Account dimension hierarchy in later articles.
Drag the Account dimension from the top pane down to replace the Measures dimension in the column axis.
The expression for MyCalcMem2, and therefore the value of MyCalcMem2, is automatically filled with the contents of the corresponding Net Income cell. The value for the Net Income member of the current account has been retrieved and displayed.
NOTE
The Assets and Liabilities amounts are not populated in the Budget cube. Our focus here is not the composition of the Net Income number, but the interactions of the various cube members among themselves.
Drag the Measures dimension to the row axis, replacing the Store dimension.
Click the drop-down arrow for the Store dimension and select MyCalcMem2. Figure 9 shows the results.
Figure 9 The result set (Assets and Liabilities columns hidden), filtering for MyCalcMem2.The value for MyCalcMem2 is again displayed, demonstrating that the MDX expression is contextually sensitive to the cell in which it's actually calculated. For Net Income, whose value is reflected as ($18,153.64)—again, negative because only expenses are included in the cube—the current member of the Measures dimension ([Amount]) is derived from the row label, while the current member of the Account dimension ([Net Income]) is derived from the column label. All other dimensions that define the context come from the filter fields in the top pane.
MyCalcMem2 represents the current member of the Store dimension, as we can see from the filter fields, but MyCalcMem2 is actually acting as a formula ([USA]/12) that points to the [USA] member. A member from each dimension is required to return a value from the cube, but keep in mind that the current member is an acceptable (and default) surrogate for most dimensions.
)
Figure 1)
Figure 2)
Figure 3)
Figure 4)
Figure 5)
Figure 6)
Figure 7)
Figure 8)
Figure 9