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 rows
FROM 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.
End of Article
Prev. page
1
[2]
next page -->
