Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes - Page 9

Let's conclude our examination of virtual cubes with a brief look at calculated members, to which we have been exposed in earlier lessons. Earlier, we created the Budget vs Actual virtual cube to illustrate options for using the virtual cube to consolidate information that occurs in different OLAP cubes. Let's extend this example to provide value in another way to the information consumers whom we support.

A simple illustration is in order: let's say that we have determined that the affected information consumers, while quite content with the virtual cube we have provided in general, have a business requirement that the virtual cube cannot, in its current configuration, directly meet. Management has asked that the Finance team determine how closely it is meeting budget within the realm of expense control. While the Finance consumers can certainly perform the math involved, they would like for us to make a simple enhancement to their data source, the virtual cube -- they would like to see a Variance column that displays the difference in the Actual Expense and the Budget Expense, so that at any applicable level of drilldown within the new reporting data source they can see directly where performance lies from all associated perspectives.

We will create a calculated member, which in this case is also a calculated measure, to subtract the Budget Expense from the Actual Expense. We will accomplish our objectives with the following steps:

1. From Analysis Manager, right-click the Budget vs Actual cube.
2. Select Edit from the context menu.

The Virtual Cube Editor for the Budget vs Actual virtual cube appears.

3. In the Virtual Cube Editor, click Insert in the top menu.
4. Select Calculated Member from the menu that appears, as shown in the illustration below.

Illustration 23: Insert -> Calculated Member

Alternatively, we might have chosen the Insert Calculated Member button (the small calculator icon atop the Analysis Manager toolbar, just above the cube tree) for the same effect.

The Calculated Member Builder dialog appears.

5. Type Variance into the Member Name box:
6. Expand Measures in the Data tree of the Calculated Member builder.
7. Expand MeasuresLevel in the Data tree of the Calculated Member builder.

The Data tree appears as shown in Illustration 24.

Illustration 24: Measures and MeasuresLevel in the Data Tree

8. Double-click Actual Expense to place the associated MDX syntax in the Value Expression box.
9. Click the minus "-" button in the "keypad" at the right of the Functions tree to place a "-" sign after the [Measures].[Actual Expense] syntax in the Value Expression box.
10. Double-click Budget Expense to place the associated MDX syntax in the Value Expression box.

The Value Expression box contents appear as shown below.

Illustration 25: The Calculation behind the Calculated Member

11. Click OK to accept the new calculated member definition.

Cube data is retrieved, and we are presented with a partially filled Data pane. Note that, because the sample cubes do not contain fully comparative information, or even complete expense information for all account/other dimension intersects, nulls appear throughout the default grid.

12. Drag the Product dimension from the top of the pane onto the Level 02 heading atop the rows axis.
13. Ensure that Year 1997 is selected in the dropdown selector for the Time dimension.

The Data pane contents appear as shown below.

Illustration 26: The Data Pane with Our Specifications

We can certainly see that various rearrangements, such as orders of measures, formatting, and so forth, might enhance our presentation, and its drilldown behavior and other considerations could be easily tweaked to anticipate and deliver options within the context of the business requirements of the information consumers involved. (To review how many of these operations can be accomplished, see the appropriate sections within the other tutorials of this series.)

The Variance column now appears, and after formatting to the specifications of the consumers, extends the value of their new virtual cube. Just as we created a calculated measure for the purposes of adding utility, we also have the option of importing existing calculated members into our cube from source cubes (the virtual cube would need, as might be expected, to include any members referenced by the virtual cube).

14. Select File -> Exit from the top menu to leave the Virtual Cube Editor.
15. Click Yes when prompted to confirm our desire to save the cube.
16. In the Analysis Manager console, select Console -> Exit to leave Analysis Services.