Data Retrieval and Refresh DifferencesOLAP 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 QueryYou cannot enable the Background query option in the PivotTable Options dialog box when your PivotTable report is based on an OLAP data source.
Parameter QueriesPivotTable reports based on an OLAP data source do not support the use of parameter queries.
Optimize MemoryThe 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 SettingsIn 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.
Summary FunctionsYou 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 ItemsYou cannot create a calculated field or a calculated item in a PivotTable based on OLAP source data.
SubtotalsThe 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. MeasuresWhen 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:
Renaming FieldsExcel 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 ItemsIn Excel 2000, you cannot group items in a PivotTable report based on OLAP source data; however, you can do this in Excel 2002.
Detail DataPivotTable 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 OrderFor 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 CommandThe Show Pages command is not available on PivotTable reports based on OLAP source data.
Show Items With No DataThe Show items with no data option in the PivotTable Field dialog box is not available on PivotTable reports based on OLAP source data.
Article ID: 234700 - Last Review: Jan 24, 2007 - Revision: 1