Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes

Wednesday Dec 11th 2002 by William Pearson
Share:

Join author Bill Pearson in a hands-on introduction to virtual cubes in the SQL Server 2000 Analysis Services series. Learn how virtual cubes provide options to extend cube functionality, act as an adjunct to security strategy, and otherwise add value to information products.


About the Series ...

This is the sixth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("Analysis Services"), with each installment progressively adding features designed to meet specific real - world needs. For more information on the series, as well as the hardware/software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.


Introduction

In the first article of the series, we used the Cube Wizard to build an initial cube with the assistance of the Dimension Wizard. We progressed through subsequent articles, creating similar dimensions to those we built with the Wizard, focusing largely in our second article on using the Dimension Editor to illustrate options for building a more customized cube. We continued this examination of dimensions in Article Three, where we recreated the calendar time dimension, this time focusing on the process through which the Dimension Wizard converts existing time/date fields to a time dimension, along with its hierarchy of levels and members. Article Three also exposed ways to customize the predefined, time-related properties that the wizard establishes in building the time dimension, suggesting options for customization of these properties to enhance the cube, from the dual perspectives of user-friendliness and the reporting needs of the organization. We created an example of an alternate time dimension for fiscal time reporting, and then we discussed some of the considerations surrounding the simultaneous housing of both hierarchies in the same OLAP cube structure.

In Article Four, we examined another special type of dimension, the Parent-Child dimension, and explored the attributes that make it different from a regular dimension. We discussed the considerations that surround Parent-Child dimensions, such as the recursive nature of their data sources, and various actions that must be handled differently in their creation and maintenance. We created a parent-child dimension using the Dimension Wizard, within which we worked with levels and properties. Finally, we enabled values at the parent level of our newly created parent-child dimension. In Article Five, Working with the Cube Editor, we reviewed, summarized and integrated many of the concepts and components that we had previously constructed individually in earlier lessons. We undertook a complete cube build "from scratch," pulling together all that we had learned, to demonstrate the assembly of a cube more sophisticated than the cube we generated in our first lesson with the Cube Wizard.

In this article, Exploring Virtual Cubes, we will introduce the concept of virtual cubes, and practice their creation and use. We will discuss the options that virtual cubes provide, from the intermingling perspectives of consolidation of multiple data sources, presentation enhancement and control, and other functionality.

In this article, we will:

  • Discuss potential uses of virtual cubes to offer options that extend the functionality and capabilities of individual OLAP cubes;
  • Create virtual cubes to practice their development for:
    • Consolidation of data within multiple OLAP cubes;
    • Comparison of data between individual cubes;
    • Control /customization of information presentation.
  • Modify the structure of a virtual cube;
  • Add a calculated member within a virtual cube;
  • Discuss limitations and strengths in the use of virtual cubes where appropriate.


Page 2: Exploring Virtual Cubes



Exploring Virtual Cubes

Much as a "view" can logically combine tables, as well as other views, within a relational database, a virtual cube is a "logical cube" that is created through the combination of multiple cubes. We select the dimensions and measures of the virtual cube from the "pooled" dimensions and measures that exist within the cubes that we are consolidating; we do not need to use all dimensions and measures but can select those that provide us the most ideal "view" of the data that we are attempting to produce from the disparate data sources. As a logical cube, the virtual cube appears as a single cube from the perspective of the information consumer.

While virtual cubes often consist of selected dimensions and measures from multiple cubes, we will likely encounter situations in our organizations where, rather than combining the data from multiple OLAP data sources, we wish to restrict the data we present to information consumers to a subset of the dimensions and measures within a single cube. This represents yet another scenario where a virtual cube might provide an excellent option.

Other scenarios exist that are well addressed by the flexible functionality that virtual cubes readily offer. These include the use of linked cubes in addition to normal cubes in their creation.

Strengths of virtual cubes include their flexibility, portability and ease of creation and modification. In addition, the storage space required by a virtual cube is modest: since virtual cubes store only their own definitions, leaving the storage of the data of the cubes that underlie them strictly to the cubes within which the data resides, physical storage space is almost negligible. The minimal storage requirement adds handsomely to the flexibility factor, as it enables us to create combinations and variations of existing cubes without the introduction of material overhead into the equation.

The capacity of a virtual cube to act as an adjunct to an overall security strategy is also a strength. The physical security provided by the virtual cube lies in the options it provides to restrict access for selected information consumer groups to sensitive or other selected information.

As we mentioned before, a virtual cube can be based upon a linked cube, but we need to keep in mind the limitations when this is the case, including the fact that the virtual cube does not support (for the linked cube):

  • cell calculations
  • custom member formulas
  • custom rollup operators
  • custom rollup formulas

Creating Virtual Cubes

We will create virtual cubes for a couple of the main reasons that they are typically created: to consolidate information that exists in more than one cube, and to create a subset of information in a cube/combined cubes as a means of controlling its presentation/accessibility to information consumers. We will point out the reasons that these options might be valuable to information consumers or management of the organization as we undertake the steps involved.

Let's get started with an example that mirrors the primary reason for creating a virtual cube: to consolidate information. One common reason we might want to do this could be to allow for comparison between measures that exist in two separate cubes.


Page 3: Creating a Virtual Cube



Creating a Virtual Cube to Consolidate Information

One of the main reasons for creating a virtual cube is to compare values that exist within two OLAP databases against each other. We will create a virtual cube that accomplishes this and also effectively demonstrates the general steps of design and creation.

Our example will involve the Sales and Budget cubes, both of which are installed as sample with a Typical MSSQL Server 2000 Analysis Services installation. As one might conclude from the names, the Sales cube houses actual sales data, while the Budget cube contains budgeted values for many of the corresponding accounts. The primary objective in creating our first virtual cube will be to provide information consumers a means of comparing actual and budget values, in our case expense values.

  1. Start Analysis Manager, then expand the Analysis Server name (shown below as MOTHER) to see the underlying databases as shown below:



Illustration 1: Expand the Analysis Server


  1. Expand the FoodMart 2000 database to see the underlying folders as displayed in Illustration 2:



Illustration 2: Expand the FoodMart 2000 Database


  1. Right-click the Cubes folder.
  2. Click New Virtual Cube on the context menu, as shown below.



Illustration 3: Select New Virtual Cube


The Virtual Cube Wizard Welcome Dialog appears as shown in Illustration 4.



Illustration 4: The Virtual Cube Wizard Welcome Dialog


  1. Click Next.


Page 4: Creating a Virtual Cube (Continued)



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

  1. Double-click the Sales cube, to select and move it from the Available Cubes list to the Virtual Cubes Include list.
  1. Double-click the Budget cube to add it to the virtual cube, as well.

The dialog appears as shown below, after our selections.



Illustration 5: Select the Cubes to Include in the Virtual Cube


  1. Click Next.
  2. Select (by double-clicking) the Store Cost (from the Sales cube) and the Amount (from the Budget cube) measures.

The Select the Measures for the Virtual Cube dialog appears as shown in Illustration 6, with our selections appearing in the Selected Measures list.



Illustration 6: Our Measures Selections Appear


  1. Click the Store Cost measure once, then again after it is selected, to enter the edit mode.
  2. Change the Store Cost measure name to Actual Expense.
  3. Click the Amount measure once to select it.
  4. Press F2 as an alternative means of entering the edit mode.
  5. Change the Amount measure name to Budget Expense.

A unique name is required for each measure within a virtual cube. Once we make our modifications, the Select the Measures for the Virtual Cube dialog appears as shown below.



Illustration 7: The Measures as Modified


  1. Click Next.

The Select the Dimensions for the Virtual Cube dialog appears.

  1. Select the dimensions (from the Available Dimensions list) by double-clicking each of the following:
  • Time
  • Product
  • Store
  • Account

Selection can also be made by highlighting and clicking the single arrow ">" to move the dimension to the Selected Dimensions list.

The Select the Dimensions for the Virtual Cube dialog appears as shown in Illustration 8, with our selections appearing in the Selected Dimensions list.



Illustration 8: Our Dimensions Selections Appear


  1. Click Next.


Page 5: Processing a Virtual Cube



Processing a Virtual Cube

After we create a virtual cube, we must process it before client applications can browse it. The necessary internal links to the specified dimensions and measures in the underlying cubes are established through processing the virtual cube. While the linking operation that is involved in processing is typically quick in itself, we need to keep in mind that initialization of processing our virtual cube will automatically trigger the processing of any underlying cubes that themselves require processing. This can add significant time to the process, and needs to be included in planning when taking the virtual cube route. Ideally, the underlying cubes will be preprocessed, but this is certainly not a requirement, and may not be the best strategy in certain situations.

The Finish the Virtual Cube Wizard dialog appears.

  1. Name the virtual cube Budget vs Actual by typing into the Virtual Cube Name box.

NOTE: Period characters ("."), among others, are restricted from use in the virtual cube name.

  1. Ensure that the Process Now radio button is selected (the default).

This, the last step of the Virtual Cube Wizard, presents us with the option to process the virtual cube now or at a later time. As we have stated earlier, a virtual cube must be processed before its data can be viewed. Virtual cube processing is much faster if all the underlying cubes have already been processed. Depending upon the size of any unprocessed cubes, processing time for the virtual cube can become significant.

The Finish the Virtual Cube Wizard dialog, with our selections, appears as shown below.



Illustration 9: The Completed Finish the Virtual Cube Wizard Dialog


  1. Click Finish.

After clicking Finish above, we see that the Process dialog appears, logging the significant processing events and then presenting a green Processing Completed Successfully message at the bottom of the dialog, as depicted in Illustration 10.



Illustration 10: The Process Dialog (Compressed View) Indicates Completion


We need to be aware that information consumers can only view data from a newly processed virtual cube after they reconnect to the server computer. We also need to note that we must process dependent virtual cubes after we make structural and nonstructural changes to the underlying source cube(s) so that end users continue to have access to them. (If the structures of the underlying cubes have not changed since they were last processed, the processing is faster than processing the component cubes individually.) The changes to which I refer include:

  • Adding or changing a calculated member;
  • Adding or changing a calculated measure;
  • Changing security roles;
  • Changing the Description property value.

Virtual cube users connected to the server computer are not affected, as long as they remain connected while we are making nonstructural changes. Until we process the dependent virtual cubes, however, end users who connect cannot see them.

  1. Click Close.

The Process dialog closes and the new Virtual Cube Editor (a customized version of the Cube Editor) appears, as shown.



Illustration 11: The Virtual Cube Editor (Compressed View)


Note that the Virtual Cube Editor does not have a Schema tab, as does the standard Cube Editor.

  1. Close the Virtual Cube Editor.

We are returned to the main Analysis Manager console window.

  1. Expand the Cubes folder, if necessary, once again.

We see the Budget vs Actual Cube appear, as shown in the illustration below.



Illustration 12: The new Budget vs Actual Cube Appears (Compressed View)


Analysis Manager identifies virtual cubes with the "double" cube icon, as shown above (circled in red).


Page 6: Making Modifications to Virtual Cubes



Making Modifications to Virtual Cubes

Even though there is no schema tab in the Virtual Cube Editor, we can modify virtual cubes readily. Let's illustrate with a change to our new cube.

  1. Right-click the Budget vs Actual cube.
  2. Select Edit from the context menu, as shown in Illustration 13.



Illustration 13: Select Edit from the Context Menu


The Virtual Cube Editor again appears, as shown.



Illustration 14: The Virtual Cube Editor (Compressed View)


Even though we have returned to the Virtual Cube Editor, we see that no means of structural modification is apparent at this juncture, because of the missing schema tab. Although a bit confusing in its redundancy, we again select the virtual cube, and get to the Edit feature (this time, it returns us to the Virtual Cube Wizard) via a right-click.

  1. Right-click the Budget vs Actual cube, from the present position within the Virtual Cube Editor.
  2. Select Edit Structure (Wizard) from the context menu, as shown in Illustration 15.



Illustration 15: Select Edit Structure (Wizard) from the Context Menu


The Virtual Cube Wizard again appears, with the dialog shown.



Illustration 16: The Return of the Virtual Cube Wizard


  1. Click Next.

We arrive at the Select Measures ... dialog once more.

  1. Select the Sales Count measure for addition to the virtual cube.
  2. Modify the Sales Count measure name to read Units Sold, as shown below:



Illustration 17: Our New Measure, with Modified Name, Appears


  1. Click Next.
  2. Click Next.
  3. Ensuring that the Process Now radio button is selected once again, click Finish.

The cube again processes successfully in short order.

  1. Close the Process dialog.

We are returned to the Virtual Cube Editor, where we note the appearance of the new Units Sold measure, as depicted in Illustration 18.



Illustration 18: The New Measure Appears in the Cube Tree


  1. Select File -> Exit to close the Virtual Cube Editor.

We have designed and processed a virtual cube to consolidate the information found in two separate cubes. Let's examine the creation of a virtual cube for what may seem to be the exact opposite reason: to remove dimensions and/or measures from a cube for purposes of limiting the information available to a given set of information consumers.


Page 7: Creating a Virtual Cube to Control Presentation and Augment Security



Creating a Virtual Cube to Control Presentation and Augment Security

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.
  1. Click New Virtual Cube on the context menu, as we did in our first exercise.

The Virtual Cube Wizard Welcome Dialog appears, once again.

  1. Click Next.

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

  1. 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


  1. Click Next.
  2. Select the following measures:
  • Supply Time
  • Warehouse Cost
  • Units Shipped
  • Units Ordered

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

  1. Click the Supply Time measure once.
  2. Press F2 to enter the Edit mode.
  3. Change the Supply Time measure name to Lead Time.
  4. Click the Warehouse Cost measure once.
  5. Press F2 to enter the Edit mode.
  6. 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


  1. Click Next.


Page 8: Creating a Virtual Cube (Continued)



The Select the Dimensions for the Virtual Cube dialog appears.

  1. Select all dimensions (from the Available Dimensions list) by clicking the double arrow ">>".

As we saw earlier, selection can also be made by highlighting and clicking the single arrow ">" to move an individual dimension to the Selected Dimensions list. Double-clicking individual dimensions achieves the same result.

The Select the Dimensions for the Virtual Cube dialog appears as shown below, after our additions.



Illustration 21: The Selected Dimensions List


  1. Click Next.

The Finish the Virtual Cube Wizard dialog appears, signaling that we are near end of the processing cycle.

  1. Name the virtual cube Operations.
  2. Ensuring that the Process Now radio button is selected once again, click Finish.

The cube again processes successfully in short order, and the Process dialog indicates successful completion.

  1. Close the Process dialog.

We are returned to the Virtual Cube Editor, where we note the appearance of our new virtual cube's structure, as depicted in Illustration 22.



Illustration 22: The New Virtual Cube Appears


Our cube contains only the measures that we wish to be accessible to the Operations information consumers. We have succeeded in generating a fully functional "view" of the data, complete with all dimensions and the measures that we deemed appropriate, while securing the information that we sought to restrict. All that remains is to create two security roles for the Operations and Corporate groups, and to assign the Operations role to this cube, and the Corporate role to the original, unrestricted cube. We have augmented physical and other security through the use of a virtual cube that is quite simple to create, once the requirements and restrictions for the component roles is understood.

  1. Close the Virtual Cube Editor.


Page 9: Working with Calculated Members in a Virtual Cube



Working with Calculated Members in a Virtual Cube

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.

  1. In the Virtual Cube Editor, click Insert in the top menu.
  2. 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.

  1. Type Variance into the Member Name box:
  2. Expand Measures in the Data tree of the Calculated Member builder.
  3. 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


  1. Double-click Actual Expense to place the associated MDX syntax in the Value Expression box.
  2. 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.
  3. 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


  1. 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.

  1. Drag the Product dimension from the top of the pane onto the Level 02 heading atop the rows axis.
  2. 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).

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


Page 10: Next in Our Series



Next in Our Series ...

In this article, we introduced the concept of virtual cubes and then practiced their creation and use. We discussed some of the options that virtual cubes provide, from the perspectives of consolidation of multiple data sources, presentation enhancement and control, and other functionality. We also introduced potential uses of virtual cubes to extend the functionality and capabilities of individual OLAP cubes. We then practiced the creation of two virtual cubes.

Within our first practice exercise, we demonstrated the development of a virtual cube for the consolidation of data within multiple OLAP cubes, where comparison of data between individual cubes might have proven useful. Our second exercise provided practice creating a virtual cube to explore another pervasive use of virtual cubes, the control and customization of information presentation. We discussed limitations and strengths in the use of virtual cubes throughout the lesson where appropriate. We also practiced the modification of the structure of a virtual cube that had been created earlier. Finally, we added a calculated member within a virtual cube to practice the process and to focus upon the options that are offered.

In our next article, Custom Cubes: Financial Reporting, we will build a simple cube to meet the illustrative business requirements of hypothetical financial reporting professionals. We will expand upon the "budget vs. actual" concepts we introduced in the current lesson, then discuss some of the challenges that accompany cube design for financial reporting. These topics include rollup / aggregation considerations, sign control in calculations and presentation, and the provision for the cube to store "footnote"/"information only" data for specific reporting needs. Finally, formatting and other considerations will also be addressed as we create a cube to meet the needs of our information consumers.


See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved