Mastering OLAP: Local Cube Files

Microsoft has been evangelizing lately about its support for a variety of computer user profiles, including profiles for users who work connected to a network or on a non-networked computer, and for road warriors who sometimes connect and sometimes work non-networked while they're traveling. Microsoft software such as Windows 2000 (Win2K) has many features targeting such user profiles. A feature called Offline Files lets you access networked folders and files without a network connection. With Offline Files, users can mark network folders or files as available offline, work offline, then synchronize the contents when they connect to the network again.

SQL Server 7.0 OLAP Services also handles road-warrior requirements, particularly if you use OLAP Services with Excel 2000 or another OLAP client tool that supports local cube files. Almost any OLAP client that can connect to OLAP Services can also connect to a local cube file.

Let's examine how to use OLAP Services and Excel 2000 to create a local cube file from an OLAP cube slice to work with while you're traveling. Also, let's look at how to implement this functionality with multidimensional ADO (ADO MD), for those of you who've created your own OLAP client application. (For the answers to the January MDX Puzzle, see "January MDX Puzzle Solution Revealed," page 59. For this month's puzzle, see "MDX Puzzle," page 60.)

Excel 2000 uses local cube files in Excel Pivot-Table reports when you don't have access to OLAP Services software on a networked PC. A local cube file is a self-contained OLAP cube without any stored aggre- gations. Typically, you use a local cube file as a subset of a server-based OLAP cube to take on the road. Local cube files have a .cub extension.

Let's create a PivotTable based on an OLAP cube, then create a local cube file. First, in Excel 2000, pull down the Data menu, and select PivotTable and PivotChart Report to start the PivotTable and PivotChart Wizard. On the first page of the wizard, select External Data Source, as Screen 1 shows. On the second page, click Get Data, which leads you to Microsoft Query if you have it installed; otherwise, Excel prompts you to insert an Office 2000 CD to install Microsoft Query.

Creating an OLAP Data Source

After you install Micro-soft Query, you'll see the Choose Data Source window. Select the OLAP Cubes tab and you'll see the view Screen 2 shows. Then select New Data Source, and click OK to open the Create New Data Source window, as Screen 3, page 58, shows. You need to fill in the first two fields in this window to create a New Data Source called FoodMart Sales, which connects to the FoodMart Sales cube. Then go to option 3, and click Connect. Select OLAP Server in this window, and type your OLAP server's machine name. If your OLAP Server is on your local machine, type localhost. Then click Next, select the FoodMart database, and click Finish, which returns you to the Create New Data Source window. Finish the steps in this window by selecting Sales in the last combo box. Click OK to return to the Choose Data Source window, select FoodMart Sales in the OLAP Cubes list, and click OK. Now you have an OLAP Data Source connected to a server-based cube with which to create your local cube file. Click Finish to skip the last page of the PivotTable and PivotChart Wizard. For now, you don't need to do anything with the placement of the PivotTable or other PivotTable options. The result is an empty PivotTable on your spreadsheet with a floating toolbar that has the names of the cube's dimensions and measures.

Making an Interactive PivotTable Report

To make the PivotTable report interactive, you must drag at least one dimension name to the report's column or rows. For this example, drag the Product dimension name from the floating toolbar to where the PivotTable says Drop row fields here, and drag the Time dimension to the Drop column fields here area. Then to complete your PivotTable report, drag the Unit Sales measure to the center of the report and release the mouse. You might need to use the scroll arrow on the right side of the floating toolbar to find the Unit Sales measure. The report will respond by filling in numbers. You now have an interactive PivotTable report that's connected to your OLAP Server. The next step is to create a local cube file with Excel 2000 so you can browse the PivotTable report when you're not connected to your OLAP Server PC.

Creating the Local Cube File in Excel 2000

To create the local cube file, open the PivotTable menu (in the upper left of the floating toolbar), and select Client-Server Settings. If you don't immediately see this choice, wait with the menu dropped down until the menu fully expands. In the resulting window, click Create local data file to open the title page of the Create Cube File Wizard window. Click Next to go to the second page, and choose the dimensions of the server-based cube that you want to include in your local cube file, as Screen 4 shows. Excel automatically selects the dimensions and dimension levels to include in your PivotTable report.

Click the plus sign left of the Product dimension to expose the Product dimension levels. The top level, Product Family, is checked (Excel automatically selects it), but check the next level, Product Department. Selecting this level lets you drill down a level in the Product dimension and view the contents of the Product Department dimension level in the local cube file. Be careful when you select levels on your cubes because if you select too many dimension levels, your resulting cube file could consume a lot of disk space.

Click Next to go to Step 3 in the Create Cube File Wizard. This step lets you further subset your selected data. For each dimension you select by drilling down and checking levels, you can choose which top-level dimension members to include in the local cube file. Because I chose the Product dimension, the wizard let me choose from the items in the Product Family level (the top level of the Product dimension). By default, Excel 2000 selects all of this level's members. This step also lets you select additional cube measures to include in the cube file. Click Next without changing anything to continue to Step 4 in the Create Cube File Wizard.

In Step 4, you can choose a file name for your new (almost local) cube file. Type in SimpleSales.cub after the prompt for the file name, and click OK, which returns Excel 2000 to the Client-Server Settings window, but now it selects the local data file instead of server-based data. To verify that you're connected to the local data file, click OK in this window, and double-click on the Drink item in the PivotTable report. The PivotTable report drills down as you would expect because you included the Product Department dimension level in the local cube. Now double-click on Alcoholic Beverages to try to drill down on it. The message You cannot show or hide detail for this selection appears. When you're connected to the server-based data, you can drill down a few more levels, but because you're now connected to the local cube, you can't.

Connecting to a Local Cube File with MDX

You can use a local cube file with applications other than Excel 2000. Almost any OLAP client software that can connect to Microsoft OLAP Services can also connect to a local cube file. I'll demonstrate this connection capability with the MDX Sample Application. You can find the MDX Sample Application in the OLAP Manager's program group. Run the MDX Sample Application, and at the prompt for a server name in the opening connection window called Connect, type the full file path name to the SimpleSales.cub file. Leave the provider field setting as MSOLAP, then click OK to continue. About halfway down the main window, in the Cube combo box, select the Sales cube. This setting lets you browse the meta data in your local cube file. Next, type the following MDX query in the MDX Sample Application edit box at the top of the screen, and press the green triangle on the toolbar to execute it.

SELECT Time.Members ON columns,     [Product].[All Products].Children ON rowsFROM Sales

A local cube file can contain more than one cube, but you can create only one cube in a local cube file from Excel 2000. Excel 2000 is good with the basic functionality (single cube, limited subsetting criteria, no calculated members) of local cube files, but you might want to take advantage of other available capabilities. For example, you can define calculated members to include in your local cube files and filter dimension members as you copy the dimension members to the local cube file with SQL-like expressions. Excel lets you subset the dimension members by level name or by including only members at a level that descends from top-level dimension members, which you specify. Also, if you have defined calculated members on your server, Excel won't download their member definitions to your local cube file.

Using ADO MD with Local Cube Files

You can develop your own application by using ADO MD to access more local cube features that are available in Excel 2000. If you need details on how to use ADO MD from Visual Basic (VB), refer to Mastering OLAP, "Writing Applications with ADO MD" (November 1999). Now, I'll focus on the capability of the local cube file I created with the code in Listing 1, page 60. If you want to run the program in Listing 1 or use it as a starting point to develop your own program, you can download the source code at http://www.sqlmag.com at the link to this article.

To develop an ADO MD program, you first need to reference the correct libraries. Pull down the Project menu in VB, and select References. Then select the ADO libraries Microsoft ActiveX Data Objects (Multidimensional) and Microsoft ActiveX Data Objects 2.1.

I wrote a subroutine called Form_Load, which executes when the main form loads. Form_Load executes after you launch the VB program and before you see the VB program's main form on the screen. The VB code passes the instructions for creating a local cube file to ADO MD as a single connection string. ADO MD creates the local cube file when it establishes the connection to an OLAP Server. Immediately after ADO MD opens the connection, you can close the connection and it creates the local cube file.

A closer look at the VB code reveals four properties to pass to the Microsoft ADO MD provider for creating and filling the cube file: LOCATION, SOURCE_DSN, CREATECUBE, and INSERTINTO. The LOCATION property specifies the destination cube file, and the SOURCE_DSN specifies the source database. (Think of creating a local cube file as a copy operation from the server cube to a local cube file.) The CREATECUBE property is similar to SQL Server's CREATE TABLE statement; CREATECUBE creates an empty cube with a given structure. The INSERTINTO property fills the local cube file. INSERTINTO specifies a SQL statement that retrieves fact table records from the source cube and establishes how they map to the destination local cube file's fact table. Notice that the SQL Server statement's WHERE clause can filter the fact table records from the source cube.

In this example, I used LocalHost as the DATA SOURCE name in the SOURCE_DSN because OLAP Services is running on my local PC. In a real implementation (i.e., not this example) of copying a local cube, it doesn't make sense to use LocalHost because you don't need to create a local cube file when OLAP Services is running on your PC.

I won't explain the other details in this connection string's syntax. You can find more documentation on command options for property names such as CREATECUBE and INSERTINTO in the index in the Microsoft Developer Network (MSDN) Reference Library.

If you're implementing a decision-support application based on OLAP Services, seriously consider adding support for local cube files. You know the benefits of taking a local cube file on the road. However, remember that local cube files can pose security risks, so you'll want to control how and when your users slice off portions of your company's sensitive information.