Creating Virtual Cubes

If you’re familiar with a relational database system, you’ve encountered views (or queries, as views are known in Microsoft Access). In a relational database, a view is a mechanism for combining values from multiple tables into what appears to be a single table, or it’s a mechanism for filtering the rows or columns of a table to see only a part of the table. In Analysis Services, a virtual cube provides some of the functions of a relational view.

A virtual cube, however, is much more restrictive than a relational view. In a relational view, you can combine essentially any two tables into a single view, and you can create extremely flexible filters to restrict the rows seen in the view. When you use a virtual cube to restrict the contents of a cube, you’re limited to including or excluding measures and entire dimensions. You cannot include a portion of a dimension. For example, you could not create a virtual cube to display only 1998 values from the Sales cube. Likewise, you could not create a virtual cube to display years and quarters but omit the month level in the Time.Fiscal dimension of the Sales cube. If you include any member of a dimension, you must include all the members of the dimension.

Nonetheless, you can use a virtual cube to effectively remove dimensions or measures from a cube. And you can also use a virtual cube to combine measures from more than one cube.

Remove Dimensions and Measures from a Cube

The Sales cube of the Chapter 4 database contains five dimensions, two of which are hierarchies of a single Time dimension. Five dimensions in a browser window can be confusing or intimidating to some people. Or perhaps you don’t want certain people to be able to see sales by employee. The Sales cube also contains six visible measures (plus Count, which is set as not visible). Once again, this many measures might be too confusing or too revealing, so you might create a virtual cube that is a subset of the physical Sales cube:

1.	In the Chapter 4 database, right-click the Cubes folder and click New Virtual Cube. Click Next to bypass the Virtual Cube Wizard’s welcome screen. The wizard shows the existing cubes in the database. [View full size image]
2.	Double-click the Sales cube in the Available Cubes list, and click Next. The wizard shows the regular measures (not calculated measures) in the selected database. [View full size image]
3.	Double-click both Sales Units and Sales Dollars, and then click Next. The wizard shows the dimensions available in the selected cube. [View full size image]
4.	Double-click Time.Calendar, Product, and State, and then click Next.
5.	Type Basic Sales as the name of the virtual cube, leave the Process Now option selected, and click Finish. Close the Process log window. [View full size image] The Virtual Cube Wizard leaves you in the Virtual Cube Editor, which is a modified version of the Cube Editor. There is no Schema tab in the Virtual Cube Editor. If you want to make structural changes to the virtual cube, click the Edit Structure (Wizard) toolbar button to redisplay the Virtual Cube Wizard. [View full size image]
6.	After reviewing the cube data, close the Virtual Cube Editor.

The Analysis server does need to process a virtual cube—processing creates enough files to identify the cube and indicate how to relate the virtual cube to the original cube—but unless it has to process the source cube, this processing is almost instantaneous.

Combine Measures from Multiple Cubes

The Sales cube contains actual sales information. The Sales Forecast cube contains forecasted values that you added earlier in this chapter. Suppose that you want to compare forecast units with sales units. You can use a virtual cube to compare measures from two cubes:

1.	Right-click the Cubes folder, and click New Virtual Cube. Click Next to bypass the welcome screen. In the list of available cubes, double-click both Sales and Sales Forecast.
2.	Click Next. The Sales cube and the Sales Forecast cube both contain a measure named Sales Units. These are the two measures you want to compare. The virtual cube requires a unique name for each measure.
3.	In the Available Measures list, double-click Sales Units from the Sales cube. Then double-click Sales Units from the Sales Forecast cube. The wizard gives the name Sales Units 1 to the newly added member. [View full size image]
4.	Select the Sales Units 1 member name, and press F2. When the name becomes selected, type Forecast Units as the new name for the measure and then click Next.
5.	In the Available Dimensions list, double-click Product, Time.Calendar, and Scenario and then click Next. [View full size image]
6.	Type Actual And Forecast as the name of the virtual cube, leave the Process Now option selected, and click Finish. Then close the Process log window.

When you use a virtual cube to compare measures from multiple cubes, you must select a measure from each cube you want to include in the virtual cube. The wizard automatically removes any cubes that do not have a measure. It’s technically possible to combine two cubes that do not have a common shared dimension, but none of the cells in the virtual cube will contain a value.

Create Calculated Members in a Virtual Cube

The Actual And Forecast virtual cube contains measures from the Sales cube and the Sales Forecast cube. Both cubes contain the Product dimension and the Time.Calendar dimension. In the Sales Forecast cube, however, the Subcategory and Product levels of the Product dimension were disabled, as was the Month level of the Time.Calendar dimension. In addition, only the Sales Forecast cube includes the Scenario dimension. You can see what happens when you combine cubes with dimensions that don’t match.

1.	Click the drop-down arrow next to the Time.Calendar dimension. Expand the All Time member, and select the 1998 member. Then double-click the Bread member. Both Forecast Units and Sales Units are available for the 1998 member at the Product Category level. The Forecast Units values, however, do not extend to the Subcategory level. The All Product Total and Bread Total members shows values for both Forecast Units and Sales Units, but the children—Bagels, Muffins, and Sliced Bread—show only Sales Units; the Forecast Units cells are empty. A virtual cube often includes members or dimensions for one cube that are not included in the other cube. In that case, the browser simply displays empty cells for the measures that don’t have values in the underlying cube. When you create a virtual cube that combines two or more cubes, you include at least one measure from each cube. It often makes sense to create a calculated measure to explicitly compare those measure values. Suppose, for example, that you want to create a Delta Units calculated measure that subtracts Sales Units from Forecast Units.
2.	Click the Insert Calculated Member toolbar button. This displays the same Calculated Member Builder that appears when you create a calculated member in the Cube Editor. Type Delta Units in the Member Name box.
3.	In the Data tree, expand Measures and MeasuresLevel. Double-click Forecast Units, click the minus sign (-) button, and then double-click Sales Units. [View full size image]
4.	Click OK.
5.	Click the drop-down button for the Scenario dimension, select Fourth Pass, and then collapse Category in the grid. The Sales Units column stays the same, but the Forecast Units and Delta Units columns change. [View full size image] The Virtual Cube Editor also allows you to import calculated members from a source cube into a virtual cube. The virtual cube must, naturally, include any members used by the calculated member. For example, the Sales cube contains a calculated member named Average Units, which uses both the Sales Units and the Count measures. If you attempt to import the Average Units calculated member into the Actual And Forecast cube without first adding the Count member to the virtual cube, you’ll get an error, so first add the Count measure, and then import the Average Units calculated member.
6.	Click the Edit Structure (Wizard) button, and agree to save the virtual cube. Click Next when asked to select the source cubes. In the Available Measures list, double-click Count. Then click Next twice, click Finish, and close the Process log window.
7.	Click the Import Calculated Members button. Select the check box next to Average Units, and click OK. The Average Units column appears in the grid.
8.	Close the Virtual Cube Editor, clicking Yes when asked if you want to save changes.

Virtual cubes can effectively remove dimensions or measures from a cube, combine measures from multiple cubes that share at least one common dimension, or both.