So far in this book you have worked with relatively small data sources such as the Northwind sample database that comes with Microsoft Access. In the business world, however, it is common to work with data sources that are much larger: from hundreds of thousands of records to millions, even billions of records. You cannot place such a huge data source on a worksheet, and even trying to manipulate all that data via a regular external data source is extremely time consuming and resource intensive. Fortunately, such huge data sources often reside on special servers that use a technology called online analytical processing, or OLAP. OLAP enables you to retrieve and summarize immense and complex data sources. When combined with Excel, OLAP enables you to view the data in a PivotTable or PivotChart report and manipulate the data quickly and easily.

Before you can use an OLAP cube as the underlying data for a PivotTable, you must first create a data source that points to either a database on an OLAP server or to an offline cube file.

If you are on a network that runs an OLAP server — such as Microsoft SQL Server with Analysis Services, SAS OLAP Server, or Oracle OLAP Server — then you can get the most flexibility by connecting to the server and working with a database that has one or more OLAP cubes defined. This ensures that you are always working with the most recent data. Check with your database administrator to learn how to find the OLAP server and whether you need a separate login username and password to access the server.

Create an OLAP Cube Data Source

START THE DATA SOURCE

Note

Portions of this chapter use the SalesCube.cub offline cube file, available at www.wiley.com/go/pivottablesvb, or you can create your own sample database.

The Choose Data Source dialog box appears.

The Create New Data Source dialog box appears.

The Multidimensional Connection dialog box appears.

Note

If you want to use an OLAP server, follow the steps in the section "Connect to an OLAP Server." If you want to use a cube file, follow the steps in the section "Connect to a Cube File."

CONNECT TO AN OLAP SERVER

A list of databases on the OLAP server appears.

The Create New Data Source dialog box appears.

Note

To finish the data source, follow the steps in the section "Complete the Data Source."

Apply It

You can also create OLAP cubes from relational data by creating a query for, say, an Access object, and then saving it as an OLAP cube. First set up a data source that points to the Access database; see the Appendix B task, "Define a Data Source." Then select the data source using the Query Wizard; see the Appendix B task, "Select a Data Source." In the final Query Wizard dialog box, select "Create an OLAP Cube from the query" (

The OLAP Cube Wizard has three steps. In the first step, you can select the measures you want to include in the cube. In the second step, you can specify the dimensions and levels you want to include in the cube — drag a dimension field and then drag its levels onto it. In the third step, you can either create a cube file or you can create an online cube.

Instead of connecting to an OLAP server, you may have access to a cube file, which is a version of an OLAP cube that has been saved to a local or network folder. A cube file is "offline" in the sense that the data is not connected to an OLAP server, so it is a static snapshot of the data. This is useful if you are working out of the office and do not have access to the OLAP server. However, you may also want to work with a cube file while you are connected to the network. If you know the data is not going to change soon, working with a cube file tends to be faster than working online with a server because network traffic may slow down the server connection.

Your database administrator may be able to create a cube file for you. Alternatively, you can use the OLAP Cube Wizard to create a cube file, as described in the tip on the previous page. See also the task "Create an Offline OLAP Cube," later in this chapter.

CONNECT TO A CUBE FILE

The Open dialog box appears.

The Create New Data Source dialog box appears.

Note

To finish the data source, follow the steps in the section "Complete the Data Source."

COMPLETE THE DATA SOURCE

The Choose Data Source dialog box appears.

You can now use the cube data source to create a PivotTable.

Note

If you want to create the PivotTable right away, click OK instead of Cancel in the Choose Data Source dialog box.

Apply It

If your network uses SQL Server with Analysis Services as its OLAP server, then you should also have access to the sample Foodmart database. This is a relatively large database of transactions based on a fictional grocery store chain. The OLAP database is based on an Access database named Foodmart 2000.mdb, which is normally stored in the following folder:

C:Program FilesMicrosoft Analysis ServicesSamples

The database includes a number of fact tables, such as sales_fact_1998, which contains more than 160,000 records. On the OLAP server, there are several built-in cubes, including Sales, HR, and Budget. If you do not see the Foodmart database when you access your OLAP server, ask your database administrator whether the Foodmart sample can be made available.

After you define a data source for the OLAP cube you want to work with, you can then use that data source as the basis of a PivotTable.

By definition, an OLAP cube is already a summary of the underlying data in the OLAP database. When creating an OLAP cube, the database administrator specifies one or more measures, and then the OLAP server applies those measures to every dimension, level, and member. So every possible combination of measure and dimension is already part of the cube. This is what makes cubes so powerful. Because all the summarizing work has already been done and the results are part of the cube, Excel requires very little processing power to pivot, filter, and summarize the data.

Because the OLAP cube is already a summary of data, it can only appear in Excel as part of a PivotTable or a PivotChart. So if you want to work with an OLAP cube within Excel, you must do it as part of a PivotTable.

Create a PivotTable from an OLAP Cube

The Choose Data Source dialog box appears.

The PivotTable and PivotChart Wizard - Step 3 of 3 dialog box appears.

Excel creates an empty PivotTable and displays the PivotTable toolbar and the PivotTable Field List.

Excel displays the completed PivotTable.

Extra

If your network uses SQL Server Analysis Services and you are working with OLAP cubes extensively, consider downloading and installing the Excel 2002/2003 Add-in for SQL Server Analysis Services from www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx.

After you install this add-in, restart Excel and you will see a new Cube Analysis menu. You can use the options on this menu to connect to a cube and to build special reports that enable you to analyze the data in more detail than you can with a PivotTable report. For example, you can use the Drillthrough command to drill down into the measure values to see the underlying data. There is also a What-If Analysis feature that enables you to perform what-if analysis on the report data.

You can enhance your data analysis by displaying the details for one or more of the dimensions and levels in an OLAP PivotTable.

When you add a dimension to an OLAP PivotTable, Excel shows the members that comprise the top level of the dimension's hierarchy. For example, if you add the Product dimension in the Foodmart Sales cube, Excel displays the members of the Product Family level: Drink, Food, and Non-Consumable. However, the dimension hierarchy may have more levels. For example, in the Foodmart Sales cube, the Product Family's Drink member has another level that includes the items Alcoholic Beverages, Beverages, and Dairy. Similarly, the Beverages member has another level that includes Carbonated Beverages, Drinks, and Hot Beverages.

Some dimensions have only a single level, but others can have four or five. For these multilevel dimension hierarchies, you can drill down into the dimension's details to see more specific slices of the cube data.

In this task, you learn how to move up and down through a dimension's hierarchy one level at a time. An OLAP PivotTable also enables you to display only selected levels and members, and you learn how to do that in the task "Display Selected Levels and Members," later in this chapter.

Show and Hide Details for Dimensions and Levels

SHOW DETAILS

You can also click the Show Detail button (

HIDE DETAILS

You can also click the Hide Detail button (

Excel hides the detail.

Apply It

You can use VBA to control whether a cube field displays details by setting the PivotField object's DrilledDown property to True. You can access all the members in a cube field by using the PivotField object's CubeField.PivotFields collection, as shown in the following macro, which shows details for up to five levels of the cube field in the active cell:

Example:

Sub ShowAllDetails()
    Dim objPF1 As PivotField, objPF2 As PivotField
    Dim objPF3 As PivotField, objPF4 As PivotField, objPF5 As PivotField
    On Error Resume Next
    Set objPF1 = ActiveCell.PivotField
    For Each objPF2 In objPF1.CubeField.PivotFields
        objPF2.DrilledDown = True
        For Each objPF3 In objPF2.CubeField.PivotFields
            objPF3.DrilledDown = True
            For Each objPF4 In objPF3.CubeField.PivotFields
                objPF4.DrilledDown = True
                For Each objPF5 In objPF4.CubeField.PivotFields
                    objPF5.DrilledDown = True
                Next 'objPF5
            Next 'objPF4
        Next 'objPF3
    Next 'objPF2
End Sub

After you show the details for one or more levels in a hierarchical cube field, you can reconfigure the PivotTable to hide all the levels above a specified level in the hierarchy.

Displaying details is useful because it enables you to see more specific slices of the OLAP cube data. However, the extra levels can sometimes make the PivotTable more difficult to read. You can work around that problem by hiding the levels you do not want to see. For example, in the Foodmart Sales cube, the Product dimension has six levels: Product Family, Product Department, Product Category, Product Subcategory, Brand Name, and Product Name. If you want to view some measure with respect to the Product Name level, you must display details for all the upper levels. The resulting PivotTable is difficult to read and cumbersome to navigate, but you can make it easier by hiding the upper levels — the ones from Product Family to Brand Name.

You can control the number of levels a cube field displays by setting the HiddenLevels property. For example, if you set this property to 5, Excel hides the five top levels in the cube field hierarchy:

ActiveCell.PivotField.CubeField.
HiddenLevels = 5

To show all levels, set HiddenLevels to 0:

ActiveCell.PivotField.CubeField.
HiddenLevels = 0

Hide Levels

Excel hides the levels above and including the level that you clicked.

To show the levels again, click the field button for any remaining level and then click PivotTable→Show Levels.

You can control exactly which levels and members Excel displays for a dimension that you have added to a PivotTable.

By default, Excel displays all the members of the top level when you add a dimension to the PivotTable. If you click the level button and then run the Show Details command, Excel displays all the members of the next level. If, on the other hand, you only want to display the members associated with a particular item in a level, then you can click the item and run the Show Details command.

However, your data analysis might require even more control over the display of levels and members. Excel enables you to hide members of any level, and this task shows you how to do that.

Extra

When you are working in the dimension drop-down list, you see the following check box states:

BUTTON	DESCRIPTION
	The level or member is displayed; for a level, none of its lower levels or members are displayed.
	The level or member is not displayed; for a level, none of its lower levels or members are displayed.
	The level or member is displayed; for a level, some or all of its lower levels or members are displayed.

Display Selected Levels and Members

Excel displays a hierarchical list of the levels and members in the dimension.

Excel displays just the levels and members that you selected.

With an OLAP PivotTable, you can filter the report using two or more items in the page field.

A major component of good PivotTable-based data analysis is the ability to filter the report so that you see only the data you want to work with. In a regular PivotTable, you can filter the report by selecting an item from the page field. However, filtering on a single item is often not exactly what you want. For example, suppose your report shows the store sales of beverages to consumers in various income groups and you want to filter that report based on the education level of the consumers. Filtering the report to show just those consumers with a bachelor's degree or a graduate degree is useful, but your analysis may require that you examine both types of consumers together. In a regular PivotTable, it is possible to filter on multiple page field items, but it requires hiding all the items you do not want to include in the report. In an OLAP PivotTable, you can do this more directly by selecting just the items you want to include in the report filter.

Display Multiple Page Field Items

Excel displays a hierarchical list of the levels and members in the dimension.

Excel displays the results for just the page field items that you selected.

Apply It

A quick way to reset the report to display the data for all the page field items is to use the drop-down page field list and check "Select multiple items" (

You can also do this via VBA by setting the CubeField object's EnableMultiplePageItems property to True. The following macro toggles this property on and off:

Example:

Sub ToggleMultiplePageFieldItems()
    Dim objPF As PivotField
    ' Work with the first page field in the active PivotTable
    Set objPF = ActiveCell.PivotTable.PageFields(1)
    ' Work with the CubeField object
    With objPF.CubeField
        ' Toggle the EnableMultiplePageItems property
        .EnableMultiplePageItems = Not .EnableMultiplePageItems
    End With
End Sub

You can work with an OLAP PivotTable while your computer is not connected to the network by creating and using an offline version of the cube that includes some or all of the data stored on the OLAP server.

We live in a world of mobile computing where the "desktop" is any reasonably flat surface upon which you can balance your notebook or handheld computer. Unfortunately, although your computer may be quite portable, your data is not always so prepared to travel. This is particularly true when that data resides on a network server. After you disconnect from the network, you lose access to the server and, hence, to your data.

Many solutions exist that enable a roaming computer to make a remote connection to the network. But technologies such as Virtual Private Networking and dial-up connections are often expensive, difficult to set up, and too slow for heavy-duty data work.

A better solution is to take some or all the data on the road with you. By storing a version of the data on your traveling computer, you can work with the data at any time and without needing a remote connection. For most OLAP data, Excel offers the Create Cube File Wizard, which takes you step by step through the process of creating an offline cube file. Note, however, that this wizard does not work with all data warehouse software. You can then take that cube file with you when you travel and work with the cube's associated OLAP PivotTable, just as though it resided on an OLAP server.

Create an Offline OLAP Cube

The Offline OLAP Settings dialog box appears.

The Create Cube File Wizard appears.

The second Create Cube File Wizard dialog box appears.

Apply It

If you simply want to create a cube file that uses the same dimensions, levels, members, and measures that are in the current PivotTable, VBA gives you an easy way to do it. You can use the PivotTable object's CreateCubeFile method and specify only the File parameter, which is a string that specifies the location and name of the cube file. The following macro prompts the user for a file path and name and then uses the active PivotTable to create just such a cube file:

Example:

Sub CreateDefaultCubeFile()
    Dim strFilePath As String
    ' Build the default path from the USERPROFILE environment variable
    strFilePath = Environ("USERPROFILE") & "My Documents"
    ' Get the user's file path and name
    strFilePath = InputBox("Type the location of the cube (.cub) file:", _
                           "Create Default Cube File", _
                           strFilePath)
    ' Did the user click Cancel?
    If strFilePath <> "" Then
        ' If not, create the cube file with the specified pathname
        ActiveCell.PivotTable.CreateCubeFile _
            File:=strFilePath
    End If
End Sub

You can use the Create Cube File Wizard to specify exactly the data that you require while you work with the OLAP PivotTable offline.

Depending on how much data is stored in the online OLAP cube, the offline version could be massive. In fact, most OLAP cubes contain enough multidimensional data to create offline cube files that are dozens or even hundreds of megabytes in size. If you are not transferring the cube file to a different computer, such as your notebook PC, if you are not e-mailing the cube file, and if your computer contains lots of free hard disk space, then the size of the OLAP cube is probably not a concern.

However, it is common to require a cube file transfer — either to a portable machine or via e-mail. Alternatively, you may be running low on hard disk space and cannot store a huge file. For these situations, you need to restrict the size of the offline cube file. You can do this in the Create Cube File Wizard by specifying the data that you are certain you need while disconnected from the network. The wizard gives you two ways to do this. First, you can select for inclusion in the cube file just those dimensions that you want to work with. Second, you can select just those top level items that you want to work with. These top level items include all the measures in the OLAP cube and all the items in the dimensions that you selected for inclusion in the cube file.

The third Create Cube File Wizard dialog box appears.

The fourth Create Cube File Wizard dialog box appears.

Alternatively, click Browse and use the Save As dialog box to specify a folder and filename for the cube file.

Excel creates the cube file and returns you to the Offline OLAP Settings dialog box.

You can now use the OLAP PivotTable while disconnected from the network.

Extra

When you are offline and using the cube file for your PivotTable, you are free to create other PivotTables using the same cube file. First, you need to create a data source that connects to the cube file. In the task "Create an OLAP Cube Data Source," see the section "Connect to a Cube File." When that is done, you can then create the new PivotTable using the cube file data source; see the task "Create a PivotTable from an OLAP Cube."

When you reconnect to the network, you should switch to using the online OLAP data so that you can refresh the PivotTable report to get the latest data. To switch from the offline OLAP cube, click any cell in the PivotTable report and then click PivotTable→Offline OLAP. In the Offline OLAP Settings dialog box, select On-line OLAP (