Take a slice of cube data on the road
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.
Prev. page
[1]
2
next page 
