Connecting to an OLAP Cube

Now that your DBA has the company data flowing into an OLAP cube, you want to view that information. To work through the examples in this book, you can download a local version of an OLAP cube from http://www.mrexcel.com/pivotbookdata.html. Or, if your company has SQL Server 2000 and a DBA, you can ask your DBA to set up the Sales cube for the Foodmart database using MS Analysis Services. Analysis Services can be installed from the SQL Server 2000 CD. Analysis Manager contains an excellent tutorial for setting up this demo cube.

Make the Connection to a Local Cube

Select PivotTable and PivotChart Report from the Data menu. In step 1 of 3 of the wizard, select External Data Source as you did in Chapter 9 (see Figure 10.4).

Figure 10.4. In step 1 of the wizard, select External Data Source.

In step 2 of the wizard, click the Get Data button.

In the Choose Data Source dialog box, select the OLAP Cubes tab. If this is your first time using the local Cube file, select New Data Source and click OK.

As shown in Figure 10.5, the Create New Data Source dialog box will be displayed. Give your cube a name, such as Sales. In the OLAP Provider field, choose the Microsoft OLE DB provider. In step 3, click the Connect button.

Figure 10.5. Even though the cube exists, you must give the connection to the cube a name.

In the Multidimensional Connection dialog box, choose the Cube File option. Use the browse button to locate the Sales.cub file. When your dialog box looks like the one shown in Figure 10.6, click Finish.

Figure 10.6. To use a local cube, simply browse to the .cub file.

Click OK to close the Create Data Source and Choose Data Source dialog boxes. You will be taken back to step 2 of the PivotTable Wizard. Click Next.

You will then be at step 3 of the wizard, as shown in Figure 10.7. Because you don't have an existing worksheet with transactional data, the default is to place the pivot table on the blank existing worksheet. Click Finish.

Figure 10.7. In step 3 of the wizard, create the pivot table on the existing blank worksheet.

Make the Connection to a Server Cube

If you have a real OLAP server, you will want to connect to the server instead of a local cube file. In Figure 10.6 (shown earlier), you would instead select the Analysis Server option. You then have to supply a server name, user ID, and password. Your DBA should be able to provide this information, as shown in Figure 10.8.

Figure 10.8. Enter information for the server name, your user ID, and password.

When connecting to a server-based cube, you will have an extra step of specifying the database. In the Foodmart example, the only option is the Foodmart database, as shown in Figure 10.9. Click the Finish button.

Figure 10.9. Choose the database. In this example, there is only one choice.

Continue with the steps to return to the PivotTable Wizard and then click Finish in step 3 of the wizard.