- Introduction to the Series
- What You Will Need to Complete the Series Tutorials
- Building an Excel PivotTable Report on OLAP Data Source
- Connecting Excel to the OLAP Cube
- Layout and Navigation of the PivotTable Report
- Browsing the Cube
- Drilling to Member Details
- Going Multidimensional
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Connecting Excel to the OLAP Cube
The PivotTable Wizard provides a guided process for connecting Excel to the OLAP cube. We begin by taking the following steps:
Open a new Excel 2000 workbook.
On the Data menu, choose PivotTable and PivotChart Report (see Figure 1) to start the PivotTable and PivotChart Wizard.
Figure 1 Starting the PivotTable and PivotChart Wizard.In the Step 1 of 3 dialog box, select the External Data Source option (see Figure 2) and then click Next.
Figure 2 The Step 1 of 3 dialog box.The Step 2 of 3 dialog appears (see Figure 3). Here we specify the source of external data. For this tutorial, we'll use the sample OLAP cube called Sales.
Figure 3 The Step 2 of 3 dialog box.Click the Get Data button. Microsoft Query starts and then presents the Choose Data Source dialog box.
Click the OLAP Cubes tab (see Figure 4).
Figure 4 The Choose Data Source dialog box.Select <New Data Source> and click OK.
In the Create New Data Source dialog box, type Sales Cube in the box 1 and select Microsoft OLE DB Provider for Olap Services 8.0 in box 2 (see Figure 5).
Figure 5 The Create New Data Source dialog box.Click the Connect button. The Multidimensional Connection dialog box appears.
Make sure that the Analysis Server option is selected as the location of the multidimensional data source.
In the Server text box, type the name of the server. If Excel and the cube share the same server machine, you can just use the name localhost. (As Figure 6 shows, my server PC is named MOTHER.) Then click Next.
Figure 6 The Multidimensional Connection dialog box.Next you select the target database/OLAP data source. For this example, select the FoodMart 2000 database that accompanies the Analysis Server installation (see Figure 7). Then click Finish.
Figure 7 Selecting the FoodMart 2000 database.The Create New Data Source dialog reappears, with the new target data source indicated to the right of the Connect button. Select the Sales cube in box 4 of the dialog box (see Figure 8).
Figure 8 The completed Create New Data Source dialog box.The options available for selection depend on which cubes are present in the database. The FoodMart 2000 sample database includes several cubes, any of which could be selected here as a data source.
Click OK. When the Choose Data Source dialog reappears, make sure that the Sales Cube data source is selected (see Figure 9), and then click OK to return to the Step 2 of 3 dialog, where we left off with the PivotTable and PivotChart Wizard.
Figure 9 Sales Cube is selected as the data source.Notice that Data fields have been retrieved now appears to the right of the Get Data button in the Step 2 of 3 dialog box (see Figure 10). Click Finish to complete the process.
Figure 10 Data fields have been retrieved.
)
Figure 1)
Figure 2)
Figure 3)
Figure 4)
Figure 5)
Figure 6)
Figure 7)
Figure 8)
Figure 9)
Figure 10At this point, an empty PivotTable report appears, allowing you to begin browsing the cube or designing the report immediately. In addition to the PivotTable report template, the PivotTable toolbar appears, providing a selection of report-building components (discussed in the next section), as well as serving as proof that you've connected to the Analysis Server, which is providing the dimension and measures information to the PivotTable report directly from the cube.