Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes - Page 3
May 17, 2004
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:
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.
6. Click Next.
The Step 2 of 3 Wizard dialog appears, as shown in Illustration 3.
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).