Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes

Monday May 17th 2004 by William Pearson

Put local cubes to work in an integrated MSAS / Office 2003 environment. Join MSAS Architect Bill Pearson in a hands-on introduction to local cubes and approaches to their creation.

About the Series ...

This is the twenty-third 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, with each installment progressively adding features and techniques 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.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("MSAS") (and uses MS Office 2000 and above, in cases where MS Office components are presented in the article.)


In our last article, Actions in Virtual Cubes, we extended the examination of Actions that we began in the article before it, Putting Actions to Work in Regular Cubes, to the creation and use of Actions within virtual cubes. After considering the differences between regular and virtual cube scenarios, we created an Action in a sample virtual cube we cloned from an existing sample. We then performed the import of an example Action into our cloned virtual cube. Throughout the hands-on practice exercises, we commented upon the results we obtained, to reinforce our understanding of the concepts involved.

In this article, we will look beyond the confines of Analysis Manager to introduce the creation and use of local cubes with Microsoft Office 2003 ("MS Office"). In conjunction with MSAS, we will overview the concepts involved with our topic, and then we will "drill down" into the practical aspects involved in putting the functionality to work immediately. This offers us an opportunity to explore the integration of MSAS OLAP with MS Office, a topic that is highly valuable to the business community. While continuing to work with the Analysis Server, we will examine, in this and the subsequent article, ways that we can conduct much design and development outside MSAS and within MS Office.

This article will focus upon the creation of a local cube from an existing Excel PivotTable report. In the article that follows this one, we will shift gears and explore a more MSAS-centric route, through the use of the OLAP Cube Wizard, to accomplish cube creation in a more flexible manner. The intent of these articles is not to demonstrate making us independent of MSAS within the creation phase, but to offer options for more independence from the perspective of the information consumer. The objective is also to make the fruits of MSAS OLAP available to enterprise team members through the conduits of applications that are pervasive in the desktop population we find in business today.

In this lesson, we will:

  • Introduce local cubes, discussing how they differ from server-based cubes (the focus of virtually all the articles in this series so far), and some scenarios in which they are appropriate;
  • Create a local cube from an existing server-based cube;
  • Discuss design and deployment considerations, and the advantages and other value that local cubes can provide the organization, both as remote production data sources and as development prototypes.

An Introduction to Local Cubes

The number of mobile information consumers in today's business environment is growing at exponential rates. More consumers are using portable computers than ever before, with the trends indicating a significant preference for portables over stationary desktops because they can be transported easily. Many of my clients have built, or are in the process of building, large sales organizations with mobile computing platforms, and even those that are not as "sales intensive" have witnessed a huge increase in the use of mobile PCs by internal staff and management alike.

The rise in the general population of "disconnected users" is placing large demands on the OLAP technologies that currently exist, and support of these mobile information consumers requires the capability to grant them access to organizational data without active connections. Local (or "offline") cubes offer opportunities for analysts, be they salespeople, distributed management or the host of other interested parties that cannot always be "wired in" to the central corporate data stores, to be able to carry and work with business intelligence applications and perform "disconnected analysis" from virtually any location.

Understanding Local Cubes

Local cubes provide a means of answering many of the requirements of the mobile user, as they are appropriate in situations in which an Analysis Server is unavailable. A local cube consists of a single, highly portable file that can live as easily on a laptop computer as it can on a server. Information consumers with local cubes on their PCs can browse and report from the cubes without the need for a connection to an Analysis Server, or to the cube's original data source. Of the many general types of cubes available via Analysis Services, local cubes are the sole data sources that are truly portable.

MS Office is self-contained in its capacity to support our needs to create local cubes, as well as to enable us to use these portable OLAP data sources for analytical purposes. Like the cubes we typically encounter within Analysis Services, local cubes have dimensions with members, as well as measures. Local cubes are, as one would expect, smaller than server-based cubes, and are not designed with a few of the capabilities that we find in their server cousins. Nevertheless, local cubes still retain many robust features that make them ideal for the "road warrior" analyst, as well as for other, less obvious applications, including the provision of capabilities to work when a network is down or inordinately slow, and so forth.

Local cubes do not offer the following features / capabilities that are typically found in server cubes:

  • aggregations
  • partitions
  • member properties
  • shared dimensions
  • virtual dimensions
  • permanent write-back storage capacity and structures
  • capability to create parent-child dimensions.

The storage mode for a local cube can be either multidimensional OLAP (MOLAP) or relational OLAP (ROLAP). Using the ROLAP mode, the cube structure is created, and the dimensions populated, but measure data is not saved locally within the cube. Once created, we still need to be connected to the cube's data source (and not simply Analysis Services) to analyze the data it contains. Since ROLAP-mode local cubes store no measure data, queries against them require more time to execute in comparison to the same queries based upon MOLAP-mode local cubes.

As one might have guessed from our discussion so far, the MOLAP variety will be the focus of our lesson with local cubes. The MOLAP storage option does mean that the cube takes longer to create, and that more disk space will be required to store it compared to its ROLAP counterpart. However, the tradeoff is most likely acceptable when we consider that our cubes have the data we need, locally accessible and immediately ready for efficient and straightforward analysis; indeed, this is the largest driver for the presence of the cubes in the first place. To minimize creation time, as well as the space requirements that result from our local cube design, we will need to plan judiciously and be selective in our choices of what data needs to be included in our proposed cubes.

Creating a Local Cube

In this and the subsequent article, we will explore and practice the two primary means of creating a local cube within Office 2003. Both approaches will rely upon the PivotTable Service to create the cubes, but we will follow two significantly different paths to reach the same destination. We will undertake cube creation following each path, and discuss the differences and nuances as we go.

Creating a Local Cube from an Existing Server Cube

The first approach for creating our local cube, and the focus of this article, will be to connect to the Analysis Server, as we did in our article Reporting Options for Analysis Services Cubes: MS Excel 2002, through the Excel PivotTable report. We will then create a cube that represents a subset of the data in a larger, server-based cube. In effect, we will create a PivotTable report that is based upon source data from an OLAP cube on a server, and then we will copy the source data to a separate file, called an offline cube (.cub) file, that will be stored on our local disks.

As we stated earlier, the local cube will allow us to perform analysis and write reports on the data in the new cube without being connected to a network, or, as a variation, to continue working when the OLAP server is unavailable. We can also use our local cube to make data from the OLAP database available on a network share so that other users can create reports from it, if such action becomes useful.

We will start by creating a connection from MS Excel to the data source. This will parallel the steps we took in our article Reporting Options for Analysis Services Cubes: MS Excel 2002, and will serve as an excellent "quick refresher" of the procedure.

Connecting MS Excel to the OLAP Cube

The PivotTable Wizard provides a guided process for connecting MS Excel to the OLAP cube. We begin by taking the following steps:

1.  Open a new MS Excel 2003 workbook.

2.  Click the top left cell (cell A1) of the new spreadsheet, to ensure that it is selected, before beginning our procedures with the PivotTable and PivotChart Wizard.

3.  Click Data --> PivotTable and PivotChart Report, on the main menu, to initialize the PivotTable and PivotChart Wizard, as shown in Illustration 1:

Illustration 1: Initialize the PivotTable / PivotChart Wizard

The Step 1 of 3 Wizard dialog appears.

4.  Select the External Data Source radio button.

5.  Select the PivotTable radio button under "What kind of report do you want to create?" (The default) as shown in Illustration 2.

Illustration 2: The Step 1 0f 3 Wizard Dialog

6.   Click Next.

The Step 2 of 3 Wizard dialog appears, as shown in Illustration 3.

Illustration 3: The Step 2 0f 3 Wizard Dialog

We specify the source of external data from this dialog. For this practice session, we will use the sample OLAP cube called Warehouse and Sales (primarily because it deals with dimensions and measures that might be of interest to remote sales teams and so forth, such as customer and lead time data).

(The Warehouse and Sales cube is one of several great training cubes that are installed with a Typical installation of MSSQL 2000 Analysis Services. While virtually all the documentation and training material available these days seems to focus on the Sales cube, I have found the "undiscovered" cubes to hold value in many specialized instances.)

7.  Click the Get Data button.

If this is the first time we have accessed Microsoft Query (which the button attempts to launch) we may be informed, via a message box, that MS Query has not been installed. If this is the case, we are presented the option of installing it - an option that we must take to complete this exercise. If applicable, give consent to the installation, which occurs relatively rapidly, then rejoin the exercise at this point.

Microsoft Query starts, and presents the Choose Data Source dialog.

8.  Click the OLAP Cubes tab.

The dialog box appears a shown in Illustration 4.

Click for larger image

Illustration 4: The Choose Data Source Dialog

9.  Click and highlight <New Data Source>.

10.  Click OK.

11.  Type the following into Box 1 of the dialog.

Offline Cube 1

12.  Select Microsoft OLE DB Provider for OLAP Services 8.0 in Box 2.

The Create New Data Source dialog appears as shown in Illustration 5.

Illustration 5: The Create New Data Source Dialog

13.  Click the Connect... button.

The Multidimensional Connection dialog appears.

14.  Ensure that the Analysis Server radio button is selected as the location of the multidimensional data source.

15.  In the Server text box, type the name of the server, as shown in Illustration 6.

Illustration 6: The Multidimensional Connection Dialog

In the illustration above, I supplied MOTHER1 (the name of the server I used for preparing this article) in the Server box. The name localhost can be supplied if Excel and the cube share the same server machine.

16.  Supply authentication information if required.

17.  Click Next

The second Multidimensional Connection dialog appears, asking that we select the target database / OLAP Data Source. Here we will select the FoodMart 2000 database that accompanied the Analysis Server installation, as depicted in Illustration 7.

Click for larger image

Illustration 7: Select the FoodMart 2000 Database

18.  Click Finish.

The Create New Data Source dialog reappears, with the new target data source indicated to the right of the Connect... button.

19.  Select the Warehouse and Sales sample cube in Box 4.

The options available for selection will depend upon which cubes are present in the database. The FoodMart 2000 sample database supplies several other cubes, any of which could be selected here as a data source.

After selecting the Warehouse and Sales cube, the Create New Data Source dialog should resemble that shown in Illustration 8.

Illustration 8: The Completed Create New Data Source Dialog

20.  Click OK.

We return to the Choose Data Source dialog.

21.  Ensuring that the Offline Cube 1 data source is selected, click OK to return to the Step 2 of 3 dialog, where we left off with the PivotTable and PivotChart Wizard.

We return to the Step 2 of 3 dialog. We note, as depicted in Illustration 9, that "Data fields have been retrieved" now appears to the right of the Get Data button.

Illustration 9: Data Fields Have Been Retrieved ...

22.  Click Finish.

An empty PivotTable report appears, with PivotTable Field List in front, as shown in Illustration 10, allowing us to begin designing the PivotTable report we need, as a basis for creating our local cube, immediately.

Illustration 10: An Empty PivotTable Report Appears

The PivotTable Field List acts, effectively, to "prove" our connection to the data source (as it displays the dimensions and measures of same), in addition to providing all we need for report design. The PivotTable toolbar, if not yet apparent, can be "called" by going to View --> Toolbars --> PivotTable from the main menu.

Designing & Creating the Local Cube with the PivotTable Report

We now have a PivotTable report, with its connection to the Warehouse and Sales server cube, in place, from which we can create our local cube. The rest of the process is easy, although planning is critical to ascertain the needs of the information consumers for whose remote use we are designing and creating the local cube.

For more information on the general creation and use of a PivotTable report, see Reporting Options for Analysis Services Cubes: MS Excel 2002.

23.  Click-select the Customers dimension on the PivotTable Field List.

24.  In the lower section of the PivotTable Field List, select Row Area.

25.  Click the Add button to add Customers to the Row Area of the PivotTable report.

26.  Click-select the Time dimension on the PivotTable Field List.

27.  In the lower section of the PivotTable Field List, select Page Area.

28.  Click the Add button to add Customers to the Page Area of the PivotTable report.

29.  Click-select the Supply Time dimension on the PivotTable Field List.

30.  In the lower section of the PivotTable Field List, select Data Area.

31.  Click the Add button to add Customers to the Data Area of the PivotTable report.

The resulting, admittedly minimal, PivotTable report should resemble the one shown in Illustration 11.

Illustration 11: The Shell PivotTable Report

We will use the above as a basis for creating our local cube. We will see that this is enough to get the process underway in the next steps.

32.  Click the PivotTable report to select it.

33.  Click the downward pointing arrow on the right side of PivotTable, on the PivotTable toolbar.

34.  Select Offline OLAP ... from the dropdown menu, as depicted in Illustration 12.

Illustration 12: Select Offline OLAP ... from the PivotTable Menu

The Offline OLAP Settings dialog appears, as shown in Illustration 13.

Illustration 13: The Offline OLAP Settings Dialog

35.  Click the Create Offline Data File button on the Offline OLAP Settings dialog (circled in red in Illustration 13 above).

The Create Cube File - Step 1 of 4 dialog appears, as depicted in Illustration 14.

Illustration 14: The Create Cube File - Step 1 of 4 Dialog

36.  Click Next.

The Create Cube File - Step 2 of 4 dialog appears, as shown in Illustration 15.

Illustration 15: The Create Cube File - Step 2 of 4 Dialog

A comparison to the items on the PivotTable Toolbar will reveal that the Create Cube File Step 2 of 4 dialog contains all the dimensions that are available for selection in the cube residing on the server. The checked boxes indicate the dimensions that appear in the PivotTable report itself.

Note: This avenue of selection, within the Create Cube File - Step 2 of 4 dialog, means we can do all selection here, avoiding the need to create an elaborate PivotTable report that includes all the dimensions and measures we want to incorporate into our local cube. This is an opportunity to save time, and the reason that we put a minimal amount of design into the PivotTable report earlier. (There is a minimal requirement for data to enable the Offline OLAP Settings option on the dropdown menu, however.)

37.  Review the levels of the Customers dimensional hierarchy by expanding the Customers dimension (clicking on the "+" to the immediate left of the Customers dimension in the Create Cube File - Step 2 of 4 dialog).

38.  Expand the Product dimension, as well.

We see the hierarchical members with checkboxes.

39.  Select the following dimensions, making sure to click the top-level checkbox whether already checked or not, to ensure that the hierarchical members below the dimension are checked, as well:

  • Customers
  • Product
  • Store
  • Time
  • Warehouse

We see the selected (checked) hierarchical members and their top-level members, as partially displayed in Illustration 16.

Click for larger image

Illustration 16: Partial View of the Create Cube File - Step 2 of 4 Dialog, Expanded Dimension Selections

40.  Click Next.

41.  Check the following, expanding as a part of the process, to see the underlying members:

  • Measures:
    • Store Cost
    • Store Sales
    • Supply Time
    • Unit Sales
    • Units Ordered
    • Units Shipped
    • Warehouse Cost
    • Warehouse Sales
  • Country:
    • All
  • Product Family:
    • All
  • Store Country:
    • All
  • Year:
    • All
  • Country:
    • All

The Create Cube File - Step 3 of 4 dialog resembles that partially shown in Illustration 17.

Illustration 17: Partial View of the Create Cube File - Step 3 of 4 Dialog

This dialog presents the top level name for each dimension that we have, heretofore, selected, and, by allowing us to check more boxes, provides another chance to select from more levels and their expanded member sets. In addition, this is our first shot at selecting members of the Measures dimension (other than those we pre-selected in our initial design of the PivotTable report - in our case, the Supply Time measure only.

42.  Click Next.

The Create Cube File - Step 4 of 4 dialog appears.

43.  Click Browse, and select an appropriate location in which to store the local cube.

The Save As dialog appears.

44.  Type Offline_Whse_&_Sales into the File Name box to name the new local cube file.

45.  Click Save.

The Create Cube File - Step 4 of 4 dialog reflects our input, as shown in Illustration 18.

Illustration 18: The Create Cube File - Step 4 of 4 Dialog

Notice that the cube will be given a .cub extension.

46.  Click Finish to create the cube.

Cube processing begins, and we are provided general status messages as to the various stages of processing that are taking place, by the Create Cube - Progress message box that briefly appears. When the cube finishes processing, the Progress dialog disappears, and we are left with an altered version of the Offline OLAP Settings dialog, as depicted in Illustration 19.

Illustration 19: The Modified Offline OLAP Settings Dialog

We can alternate at this stage, or at any time, between the original server cube and the local cube file as a data source for the PivotTable report by making our selection via the appropriate radio button on the Offline OLAP Settings dialog. This dialog is always accessible from the PivotTable toolbar, as we saw in beginning the creation of our local cube.

A prominent difference between the dialog at this stage and at the beginning of the cube creation process occurs in the button at its center. What was earlier captioned Create Local Data File is now titled Edit Local Data File; this change in the caption reflects the readiness, at this point of the dialog, to allow us to redefine the structure of our local cube.

We are now at a position to begin reporting from the local cube, just as we did from a server-based MSAS cube in Reporting Options for Analysis Services Cubes: MS Excel 2002. In our next article, we will explore an alternative means of creating a local cube whose structure resembles the one we created in this article, allowing us to contrast the two methods of cube creation as we undertake the new one.

47.  Close the Excel worksheet, saving it as appropriate.

Summary ...

In this article, we ventured beyond earlier topics surrounding the retrieval and reporting of data from a server-based MSAS cube, and transitioned into the realm of remote, independent OLAP data source design and creation. We explored approaches to creating local cubes within MS Office, discussing many of the foundational concepts behind the architecture of multidimensional data sources. As a part of a hands-on practice exercise, we then created a local cube from an existing Excel PivotTable report, sourced initially from an MSAS server-based cube.

We explored many practical aspects of putting the functionality to work immediately, discussing ways that local (or "offline") cubes can meet the business requirements of distributed information consumers, and add value to the organization in general. Throughout the hands-on practice exercise we performed, in creating a local cube from an existing server cube, we commented upon the results we obtained, to reinforce our understanding of the concepts involved.

» See All Articles by Columnist William E. Pearson, III

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

Mobile Site | Full Site