Connecting to OLAP Data | Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

Before you can work with OLAP data, you need to connect to it. Connecting to OLAP data with Microsoft data analysis software is very similar to connecting to other types of data; the only difference is that you connect to data that resides in a cube on an OLAP database server computer or in an offline cube file. To connect to an OLAP database, you need to know the OLAP server name, the OLAP database name, and the OLAP cube name. To connect to an offline cube file, you need to know the path to the offline cube file. Although OLAP data cubes can be built from any data source, including Oracle, DB2, and mainframe systems, in the examples in this chapter, we’ll assume that cubes are stored on computers running Microsoft SQL Server 2000 Analysis Services or are offline cube files stored on the local computer.

Note

Most OLAP databases house too much data to store in a single offline cube file. An offline cube file contains, in most cases, only a portion of the data stored in a cube on an OLAP database server or a portion of the data stored in a relational database on a network server computer.

Connecting to OLAP Data with Microsoft Excel

You can use Excel to connect to an OLAP data source and create a PivotTable report based on the data. The data can exist on an OLAP database server or in an offline cube file. The data is presented in a PivotTable report instead of as a simple series of records because OLAP data and OLAP database servers are optimized for fetching aggregated or summarized data—data that is presented best in a PivotTable—and not individual data records.

Note

Because OLAP data sources contain summarizations of typically tens of thousands or even millions of data records, you cannot import the individual data records from which an OLAP data source gets its data.

To connect to an OLAP server or offline cube file, follow these steps:

On the Data menu, point to Import External Data and then click New Database Query. The Choose Data Source dialog box appears.
On the OLAP Cubes tab, click New Data Source and then click OK.
Enter a name for the data source, and then select Microsoft OLE DB Provider For OLAP Services 8.0 in the Select An OLAP Provider For The Database You Want To Access list.

Note
If you are connecting to a Microsoft SQL Server 7.0 OLAP Services database, select the Microsoft OLE DB Provider For OLAP Services option.
Click the Connect button, and the Multidimensional Connection wizard appears.

Select the Analysis Server option if you want to connect to an OLAP server, or select the Cube File option if you want to connect to an offline cube file.

If you select the Analysis Server option, type the name of the OLAP server in the Server box, click Next, select the name of the database that you want to access, and then click Finish. If you select the Cube File option, type the path and file name of the offline cube file, and then click Finish. After clicking Finish, the Create New Data Source dialog box reappears.

Note

You may be able to connect to an OLAP server on the Web if your network administrator has enabled HTTP or HTTPS connectivity to an Analysis server running on Microsoft SQL Server 2000 Enterprise Edition. If so, type the user name and password assigned to you by your network administrator before you click Next. If you are connecting through a dedicated network, you do not need to provide a special user name and password because the Analysis server uses your Windows logon name and password to determine what level of access you have to the Analysis server.

Tip

SQL Server 2000 Analysis Services comes with a sample OLAP database named FoodMart 2000. If you want to experiment with OLAP data analysis but don’t yet have an OLAP database, the FoodMart 2000 OLAP database is a good place to start.

If the cube name is not already selected, select it in the Select The Cube That Contains The Data You Want list, and then click OK. The Choose Data Source dialog box reappears.
Click the name of the data source that you just created, and then click OK. The PivotTable And PivotChart Wizard–Step 3 Of 3 page appears.
Select the New Worksheet option or the Existing Worksheet option. If you select the Existing Worksheet option, the PivotTable report begins at the cell referenced in the list below the Existing Worksheet option. Click Finish.

Figure 8-1 shows the PivotTable report created after connecting Excel to the FoodMart 2000 sample OLAP database’s Sales cube.

Figure 8-1: PivotTable report based on the Sales cube.

Tip
If you connected to the sample FoodMart 2000 OLAP database, selecting the Sales or Sales and Warehouse cubes will give you a sizeable amount of OLAP data to experiment with.

If you want to connect to and open an offline cube file in fewer steps, and you’re using Excel 2002, you can skip the previous steps and use the following steps instead.

On the File menu, click Open.
In the Files Of Type list, select All Files.
Locate and select the .cub file you want to open, and then click Open. The OLAP cube data is presented as a PivotTable report in the current workbook. Figure 8-2 shows the PivotTable report set up after using Excel 2002 to open the CarSales.cub file in the Chap08 folder.

Figure 8-2: PivotTable report based on the CarSales.cub file.

Connecting to OLAP Data with the Office Web Components

Just as you can use the Office Web Components to connect to data over an intranet, you can use the Office Web Components to connect to OLAP data. Connecting to OLAP data with the Office Web Components is helpful when you need to display data from an OLAP data source along with other types of data on a Web page. You might use such a configuration in a Web-based business management system. Connecting to OLAP data with the Office Web Components is similar to connecting to other types of data. You can connect to OLAP data using the PivotTable Component or the Chart Component. You can’t use the Spreadsheet Component because the individual data records that make up the OLAP data are not available, only the summarized data is.

To connect to OLAP data from a PivotTable Component or Chart Component, follow these steps:

Using Microsoft FrontPage (or another Web page editor), insert a PivotTable Component or a Chart Component on the Web page where you want to view the data. (This might be a blank Web page at this point.)
On the PivotTable Component’s toolbar, click the Commands And Options button. Or, on the Chart Component’s toolbar, click the Chart Wizard button. The Commands And Options dialog box appears.
For the PivotTable Component, click the Data Source tab, select the Connection option, and then click the Edit button. For the Chart Component, click the Data Source tab, select the Data From A Database Table Or Query option, click the Connection button, and then click the Edit button.
In the Select Data Source dialog box, select a data source and click Open. You can then build the chart or PivotTable report using the field list.

If you need to create a new data source, click the New Source button in the Select Data Source dialog box. The Welcome To The Data Connection Wizard page appears.
In the list of data sources, select Microsoft SQL Server OLAP Services and then click Next. The Connect To Database Server page appears.

In the Server Name box, type the name of your Microsoft SQL Server 2000 Analysis Services computer. Click the Use Windows 2000 Security option if your Analysis Services computer accepts your current network logon name and password for access to the OLAP data. Click the Use The Following User Name And Password option and enter your user name and password if your Analysis Services computer requires a different set of logon credentials.

Note

If you are not sure whether your Analysis Services computer accepts your network logon name and password or requires a special user name and password, see your computer network’s administrator.

Click Next. The Select Database And Table page appears.
In the Select The Database That Contains The Data You Want list, select the OLAP database.
Select the Connect To A Specific Cube Or Table check box. (If you do not select this check box now, you will prompted to select a specific cube or table before connecting to the data.)
Click the cube you want to connect to, and then click Next. The Save Data Connection File And Finish page appears.
Click Finish. The connection details are saved to an .odc file, and the Select Data Source dialog box reappears.

Click the .odc file that was generated in the previous step, and then click Open. The PivotTable Component or Chart Component connects to the OLAP data.

Note

In Office 2000, the Edit button is named the Connection Editor, and the Data Link Properties dialog box appears instead of the Select Data Source dialog box. However, the concepts behind this procedure are the same: you designate a connection type and the name of the OLAP server along with the database and cube name on the server, or the location of the offline cube file.

Data Connection File Types

There are various types of files that Office can use to get data connection information.

XML-based Office Database Connection (.odc) files are the recommended method of retrieving external data when you don’t need to combine data from more than one table in an external database or filter the data to select specific records before creating a report. This file type is also recommended for retrieving data from OLAP databases.
Universal Data Link (.udl) files are text-based files that contain information to connect to OLE DB data sources.
Open Database Connectivity (ODBC) File Data Source Name (.dsn) files are text-based files for the specific use of a local computer connecting to an ODBC data source.
Database Query (.dqy and .rqy) files are text-based files created with Microsoft Query, a graphical data query program often used with Excel.
Text-based OLAP Query (.oqy) files return data from any OLE DB for OLAP provider, such as Microsoft SQL Server 7.0 OLAP Services or Microsoft SQL Server 2000 Analysis Services.
OLAP Cube (.cub) files are binary files that actually hold OLAP data.

Using the Office Web Components to analyze OLAP data is similar to analyzing non-OLAP data. You can use the PivotTable Component and Chart Component to pivot, group, summarize, and chart OLAP data over the Web just as you would with relational or nonrelational data. See Chapter 6, “Analyzing Data with the Office Web Components,” for details on how to use the Office Web Components.