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

We mentioned earlier that another strength of a virtual cube lies in its capacity to act as an adjunct to an overall security strategy. This attribute makes a virtual cube useful when a part (or parts) of a cube's information is (are) sensitive and not suitable for all users. A virtual cube (which, as we have seen, we can easily create from an existing cube) provides a means of omitting the sensitive information.

We can then create two security roles: the first role containing the users permitted to see the sensitive information, and the second containing the remaining (disallowed) users. We then simply grant the first role access to the original (full) cube and the second role access to the virtual cube. In this manner, both sets of information consumers have access to the information they need to perform their respective functions but are restricted from, or given access to, the sensitive data as is appropriate.

First, let's set the stage with a realistic scenario: The Warehouse cube (another of the sample cubes that are put in place with the Typical Analysis Services implementation) contains six cube dimensions, a larger number of shared dimensions, and seven measures. Let's say we want to present the general information in the cube to two general sets of information consumers: those who are allowed to see all data (we'll call them the Corporate group); and those (the Operations group) who are allowed to see only enough of the operational data to complete their daily tasks (the cost and logistic information), but who do not need to be concerned with the revenue information (which we will assume for our immediate purposes to be included in the Store Invoice, Warehouse Sales, and Warehouse Profit measures).

We will create a subset of the existing Warehouse cube to meet the needs of the Operations group, for starters.

1. From the Analysis Manager console, right-click the Cubes folder within the FoodMart 2000 database.

2. Click New Virtual Cube on the context menu, as we did in our first exercise.

The Virtual Cube Wizard Welcome Dialog appears, once again.

3. Click Next.

The Select the Cubes to Include in the Virtual Cube dialog appears.

4. Double-click the Warehouse cube to select and move it from the Available Cubes list to the Virtual Cubes Include list.

The dialog appears as shown below, after our selection.

Illustration 19: Our Selection: Cubes to Include in the Virtual Cube Dialog

5. Click Next.
6. Select the following measures:

• Supply Time
• Warehouse Cost
• Units Shipped
• Units Ordered

Our selections appear in the Selected Measures list of the dialog.

7. Click the Supply Time measure once.
8. Press F2 to enter the Edit mode.
9. Change the Supply Time measure name to Lead Time.
10. Click the Warehouse Cost measure once.
11. Press F2 to enter the Edit mode.
12. Change the Warehouse Cost measure name to simply Cost.

The Select the Measures for the Virtual Cube dialog appears as shown below, after we have made our changes.

Illustration 20: The Measures as Modified

13. Click Next.