Browsing Analysis Services OLAP Cubes with Excel - Microsoft Business Intelligence Tools for Excel Analysts - page 252

Understanding the Limitations
of OLAP PivotTables
For the most part, PivotTables that are based on OLAP data sources look, feel, and act like standard
PivotTables.

An OLAP data source is ultimately controlled by the database administrator, who is
responsible for maintaining the Analysis Services server. That control encompasses
every aspect of the OLAP cube's behavior, from the dimensions and measures included
in the cube to the ability to drill into the details of a dimension. As the consumer of the
OLAP data source, you have limited control on how the OLAP cube ultimately looks and
feels.

Note

This limited control translates into some limitations to the actions you can take with your OLAP-
based PivotTables. You should take these limitations into account before moving forward with an
OLAP-based reporting solution.

When your PivotTable report is based on an OLAP data source

➤ You cannot place any field other than measures into the Values area of the PivotTable.

➤ You cannot change the function used to summarize a data field.

➤ The Show Report Filter Pages command is disabled.

➤ The Show Items with No Data option is disabled.

➤ The Subtotal Hidden Page Items setting is disabled.

➤ The Background Query option is not available.

➤ Double-clicking in the Values field returns only the first 1,000 records of the pivot cache.

➤ The Optimize Memory check box in the PivotTable Options dialog box is disabled.

Creating Offline Cubes
With a standard PivotTable, the source data is typically stored on your local drive. This way, you can
work with and analyze your data while disconnected from the network. However, this is not the case
with OLAP PivotTables. With an OLAP PivotTable, the pivot cache is never brought to your local drive.
This means that while you're disconnected from the network, your PivotTable is out of commission.
You can't even move a field while disconnected.

Offline cubes are files that locally store portions of the source data found in an OLAP data source for
browsing while you're disconnected from the network. These types of cubes are useful when you