Home > Articles > Data > SQL Server

  • Print
  • + Share This
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:

  1. Open a new Excel 2000 workbook.

  2. On the Data menu, choose PivotTable and PivotChart Report (see Figure 1) to start the PivotTable and PivotChart Wizard.

    Figure 1Figure 1 Starting the PivotTable and PivotChart Wizard.

  3. In the Step 1 of 3 dialog box, select the External Data Source option (see Figure 2) and then click Next.

    Figure 2Figure 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 3Figure 3 The Step 2 of 3 dialog box.

  4. Click the Get Data button. Microsoft Query starts and then presents the Choose Data Source dialog box.

  5. Click the OLAP Cubes tab (see Figure 4).

    Figure 4Figure 4 The Choose Data Source dialog box.

  6. Select <New Data Source> and click OK.

  7. 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 5Figure 5 The Create New Data Source dialog box.

  8. Click the Connect button. The Multidimensional Connection dialog box appears.

  9. Make sure that the Analysis Server option is selected as the location of the multidimensional data source.

  10. 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 6Figure 6 The Multidimensional Connection dialog box.

  11. 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 7Figure 7 Selecting the FoodMart 2000 database.

  12. 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 8Figure 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.

  13. 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 9Figure 9 Sales Cube is selected as the data source.

  14. 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 10Figure 10 Data fields have been retrieved.

At 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.

  • + Share This
  • 🔖 Save To Your Account