Working with OLAP Data in Excel

Import OLAP Data into a Workbook Using an Existing Query (.oqy) or Offline Data Cube (.cub) File

1. On the Data menu, point to Import External Data and then click Import Data. The Select Data Source dialog box appears.

2. In the Files Of Type list, select OLAP Queries/Cube Files.

3. Locate and select the .oqy or .cub file you want to import, and then click Open.

4. In the PivotTable And PivotChart Wizard, select an option for where to place the PivotTable report that references the OLAP data, in either a new worksheet or the existing worksheet (beginning at the cell referenced in the list directly below the Existing Worksheet option).

5. Click Finish. The OLAP data is presented as a PivotTable report in the current workbook.

Import OLAP Data into a Workbook Using a New Connection to an OLAP Database Server or an Offline Cube File

1. On the Data menu, point to Get External Data and then click New Database Query. The Choose Data Source dialog box appears.

2. On the OLAP Cubes tab, click New Data Source and then click OK.

3. In the What Name Do You Want To Give Your Data Source list, type a name for the data source.

4. In the Select An OLAP Provider For The Database You Want To Access list, select Microsoft OLE DB Provider For OLAP Services 8.0 (for data sources using Microsoft SQL Server 2000 Analysis Services) or Microsoft OLE DB Provider For OLAP Services (for data sources using Microsoft SQL Server 7.0 OLAP Services).

5. Click the Connect button.

6. Select the Analysis Server option if you want to connect to an OLAP server; select the Cube File option if you want to connect to an offline cube file.

7. If you selected the Analysis Server option, type the name of the OLAP server in the Server box, click Next, click the name of the database that you want to access, and then click Finish.

   If you selected the Cube File option, type the path and file name for the offline cube file and then click Finish.

8. If the cube is not already selected in the Select The Cube That Contains The Data You Want List, select the cube you need and then click OK.

9. Select the name of the data source that you just added, and then click OK.

10. In the PivotTable And PivotChart Wizard, select an option for where to place the PivotTable report that references the OLAP data, in either a new worksheet or the existing worksheet (beginning at the cell referenced in the list directly below the Existing Worksheet option).

11. Click Finish. The OLAP data is presented as a PivotTable report in the current workbook.

Open an Offline Cube File in Excel

1. On the File menu, click Open.

2. In the Files Of Type list, select All Files.

3. Locate and select the .cub file, and then click Open. The OLAP data is presented as a PivotTable report in the current workbook.

   Note

   NoteThe previous procedure cannot be performed with Excel 2000.