Reporting Options for Analysis Services Cubes: MS Excel 2002 - Page 3

April 7, 2003

Connecting Excel to the Cube

The PivotTable Wizard walks us through a guided process for connecting Excel 2002 to a given Analysis Services cube. We begin by taking the following steps:

1.      Open a new Excel 2002 workbook.

2.      Click Data (top menu), then select PivotTable and PivotChart Report, to initialize the PivotTable and PivotChart Wizard, as shown below:

The Step 1 of 3 Wizard dialog appears.

3.      Select the External Data Source radio button, as shown in the following illustration:

4. Ensuring that the "kind of report" selection is set to "PivotTable," click Next.

The Step 2 of 3 dialog appears, as shown below:

Here we specify the source of our data. For this tutorial, we will use the sample OLAP cube called Sales.

5.      Click the Get Data button.

Microsoft Query starts, and presents the Choose Data Source dialog.

6.      Click the OLAP Cubes tab.

The dialog box appears as shown in Illustration 4 below.

7.      Click and highlight <New Data Source>.

8.      Click OK.

9.      Type Sales Cube in Box 1.

10.  Select Microsoft OLE DB Provider for OLAP Services 8.0 in Box 2.

The Create New Data Source dialog appears as shown below.

11.  Click the Connect... button.

The Multidimensional Connection dialog appears.

12.  Ensure that the Analysis server radio button is selected as the location of the multidimensional data source we wish to access.