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:
Open a new Excel 2002
Click Data (top menu),
then select PivotTable and PivotChart Report, to initialize the
PivotTable and PivotChart Wizard, as shown below:
1: Menu Item for Initializing the PivotTable / PivotChart Wizard
The Step 1 of 3 Wizard dialog
Select the External Data
Source radio button, as shown in the following illustration:
2: The Step 1 0f 3 Wizard Dialog
- Ensuring that the "kind of report" selection is set
to "PivotTable," click Next.
The Step 2 of 3
dialog appears, as shown below:
Illustration 3: The Step 2 0f 3 Wizard Dialog
Here we specify the
source of our data. For this tutorial, we will use the sample OLAP cube called
Click the Get Data
Microsoft Query starts,
and presents the Choose Data Source dialog.
Click the OLAP Cubes
The dialog box appears
as shown in Illustration 4 below.
4: The Choose Data Source Dialog
Click and highlight <New
Type Sales Cube
in Box 1.
Select Microsoft OLE DB
Provider for OLAP Services 8.0 in Box 2.
The Create New Data
Source dialog appears as shown below.
5: The Create New Data Source Dialog
Click the Connect...
Connection dialog appears.
Ensure that the Analysis
server radio button is selected as the location of the multidimensional
data source we wish to access.