Differences between OLAP and non-OLAP PivotTables in Excel

Microsoft Excel allows you to create PivotTable reports based on Online Analytical Processing (OLAP) source data. If you work with PivotTable reports that are based on OLAP source data and with reports based on non-OLAP source data, you will notice differences both in the features available and in how the features work. This article discusses some of the major differences between PivotTable reports based on OLAP source data and PivotTable reports based on non-OLAP source data.

Data Retrieval and Refresh Differences

OLAP databases are organized to facilitate the retrieval and analysis of large amounts of data. Before Excel displays summarized data in a PivotTable report, an OLAP server performs calculations to summarize the data. Only the summarized data is returned to Excel, on an as-needed basis. With non-OLAP external databases, all the individual source records are returned, and then Excel does the summarizing. Consequently, OLAP databases can provide Excel with the ability to analyze much larger amounts of external data.

An OLAP server returns new data to Excel every time you change the view or layout of the PivotTable or PivotChart report. When you use non-OLAP source data, the data is refreshed differently, and various refresh options are available in the PivotTable Options dialog box.

Non-OLAP data can be returned to Excel as an external data range or a PivotTable or PivotChart report. OLAP data can be returned to Excel only in the form of a PivotTable or PivotChart report.

Background Query

You cannot enable the Background query option in the PivotTable Options dialog box when your PivotTable report is based on an OLAP data source.

Parameter Queries

PivotTable reports based on an OLAP data source do not support the use of parameter queries.

Optimize Memory

The Optimize memory check box in the PivotTable Options dialog box is not available when your PivotTable report is based on an OLAP data source.

Page Field Settings

In PivotTable reports that are based on non-OLAP source data, you can use page field settings to retrieve data for each page field item individually or for all items at once. These page field settings are not available in reports that are based on OLAP source data. OLAP source data is always retrieved for each item as needed, which allows reports to display information from large OLAP databases.

Calculation Differences

Summary Functions

You cannot change the function used to summarize a data field in a PivotTable report based on OLAP source data. This limitation results from the fact that the totals are calculated on the OLAP server.

Calculated Fields and Calculated Items

You cannot create a calculated field or a calculated item in a PivotTable based on OLAP source data.

Subtotals

The following limitations apply when you are working with subtotals in a PivotTable report based on OLAP source data:

• You cannot change the summary function for subtotals in your PivotTable report.
• You cannot display subtotals for inner row or inner column fields in your PivotTable report.
• Because the totals are calculated on the OLAP server, you cannot change the Subtotal hidden page items setting in the PivotTable Options dialog box.

Mark Totals with *

The Mark Totals with * option in the PivotTable Options dialog box is available only in PivotTable reports based on OLAP source data. This option marks every subtotal and grand total with an asterisk (*) to indicate that these values contain any hidden items as well as the displayed items.

Layout and Design Differences

Dimensions vs. Measures

When you are working with a PivotTable report based on OLAP source data, dimensions can be used only as row, column, or page fields. Measures can be used only as data fields. When you drag a dimension to the data field drop area, or a measure to the row, column, or page field drop area, you receive the following error message: When a PivotTable report based on OLAP source data is active, the PivotTable toolbar displays an icon next to each row of fields. The icon indicates where Excel will allow you to place the field in your PivotTable report. If the icon is darker in the upper left, then the field is a dimension that you can drag to the row, column, or page field drop areas. If the icon is darker in the lower right, then the field is a measure that you can drag to the data field drop area.

Renaming Fields

Excel allows you to rename fields that you add to your PivotTable. When your PivotTable report is based on OLAP source data, you will lose your custom name if you remove the field from your PivotTable.

Grouping and Ungrouping Items

In Excel 2000, you cannot group items in a PivotTable report based on OLAP source data; however, you can do this in Excel 2002.

Detail Data

PivotTable reports based on OLAP source data allow you to display the lowest level of data available on the OLAP server. However, you cannot display the underlying detail records that make up the summary values.

Initial Sort Order

For non-OLAP source data, the items in a new PivotTable report first appear sorted in ascending order by item name. For OLAP source data, items first appear in the order in which the OLAP server returns them. You can then sort or manually rearrange the items if you want them in a different order.

Show Pages Command

The Show Pages command is not available on PivotTable reports based on OLAP source data.

Show Items With No Data

The Show items with no data option in the PivotTable Field dialog box is not available on PivotTable reports based on OLAP source data.

For more information about using OLAP data sources in Excel, click Microsoft Excel Help on the Help menu, type OLAP in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.