Levels of detail OLAP databases organize data by level of detail, using the same categories you use to analyze the data. For example, a sales database might have fields for the country, region, city, and site of each sale. This information could be organized from least to greatest level of detail, like this:
Levels in a geography dimension
This organization makes it easy for a PivotTable or PivotChart report to display high-level summaries, such as sales totals across an entire country or region, and also display the details for sites where sales are particularly strong or weak.
Dimensions and cubes A set of levels that encompass one aspect of the data, such as geographic locations, is called a dimension. Similarly, information about when sales were made could be organized in a time dimension with levels for year, quarter, month, and day. OLAP databases are called cubes because they combine several dimensions, such as time, geography, and product lines, with summarized data, such as sales or inventory figures.
OLAP features in Microsoft Excel
Retrieving OLAP data You can connect to OLAP data sources just as you do to other external data sources. You can work with databases created with Microsoft SQL Server OLAP Services, the Microsoft OLAP server product. Excel can also work with third-party OLAP products that are compatible with OLE-DB for OLAP.
You can display OLAP data only as a PivotTable or PivotChart report, not as an external data range. You can save OLAP PivotTable and PivotChart reports in report templates, and you can create Office Data Connection (.odc) files to connect to OLAP databases and query files (.oqy) for OLAP queries. When you open an .odc or .oqy file, Excel displays a blank PivotTable report, ready for you to lay out.
Creating cube files for offline use You can use the Excel Offline Cube Wizard to create files with a subset of the data from an OLAP server database. Offline cube files let you work with OLAP data when you are not connected to your network. You can create cube files only if you use an OLAP provider, such as Microsoft SQL Server OLAP Services, that supports this feature.
Creating cubes from relational databases Another wizard, the OLAP Cube Wizard, allow you to organize data queried from relational databases, such as Microsoft SQL Server, into OLAP cubes. This wizard is available from Microsoft Query, which you access from Excel. A cube lets you work with larger amounts of data in a PivotTable or PivotChart report than you could otherwise, and speeds retrieval of the data.
Software components you need for OLAP access
An OLAP provider To set up OLAP data sources for Microsoft Excel, you need one of the following OLAP providers:
- Microsoft OLAP provider Excel includes the data source driver and client software you need to access databases created with the Microsoft OLAP product, Microsoft SQL Server OLAP Services. The driver provided with Excel 2002 and later supports both versions 7.0 and 8.0 of this product. If you have the version 7.0 driver that was provided with Excel 2000, you can use this driver to access version 7.0 databases, but for version 8.0 databases you must use the version 8.0 driver.
- Third-party OLAP providers For other OLAP products, you need to install additional drivers and client software. To use the Excel features for working with OLAP data, the third-party product must conform to the OLE-DB for OLAP standard and be Microsoft Office compatible. For information about installing and using a third-party OLAP provider, consult your system administrator or the vendor for your OLAP product.
Server databases and cube files The Excel OLAP client software supports connections to two types of OLAP databases. If a database on an OLAP server is available on your network, you can retrieve source data from it directly. If you have an offline cube file containing OLAP data or a cube definition file, you can connect to that file and retrieve source data from it.
Data sources A data source gives you access to all data in the OLAP database or offline cube file. After you have created an OLAP data source, you can base reports on it, and return the OLAP data to Excel in the form of a PivotTable or PivotChart report. You can create a data source while you are using the PivotTable and PivotChart Wizard to create a new report, or you can create a data source in Microsoft Query and use it to create reports in Excel.
Microsoft Query Microsoft Query is an optional Microsoft Office component that you can install and access from Excel. You can use Query to retrieve data from an external database such as Microsoft SQL or Microsoft Access. You do not need to use Query to retrieve data from an OLAP PivotTable that is connected to a cube file.
Feature differences for reports with OLAP source data
If you work with PivotTable and PivotChart reports from both OLAP source data and other types of source data, you will notice some feature differences.
Data retrieval An OLAP server returns new data to Microsoft Excel every time you change the layout of the report. With other types of external source data, you query for all the source data at once, or you can set options to query only when you display different page field items. You also have several other options for refreshing the report.
In reports based on OLAP source data, the page field settings are unavailable, background query is unavailable, and the optimize memory setting is not available.
Field types For OLAP source data, dimension fields have 
icons in the field list and can be used only as row (series), column (category), or page fields. Fields with 
icons can be used only as data fields. For other types of source data, all fields have 
icons and can be used in any part of a report.
Renamed fields and items For OLAP source data, renamed fields and items that you hide revert to their original names when you redisplay them. For other types of source data, fields and items retain their new names in these situations.
Access to detail data For OLAP source data, the server determines what levels of detail are available and calculates summary values, so the detail records that make up summary values usually aren't available, and you can't show items with no data. The server may, however, provide property fields that you can display. Other types of source data don't have property fields, but you can display the underlying detail for data field values and for items, and you can show items with no data.
OLAP page fields may not have an All item, and the Show Pages command is unavailable.
Initial sort order 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. For other types of source data, the items in a new report first appear sorted in ascending order by item name.
Calculations OLAP servers provide summarized values directly for a report, so you cannot change the summary functions for data fields. For other types of source data, you can change the summary function for a data field and use multiple summary functions for the same data field.
Both types of source data support custom calculations.
You cannot create calculated fields or calculated items in reports with OLAP source data.
Subtotals In reports with OLAP source data, you cannot change the summary function for subtotals, and you cannot display subtotals for inner row or column fields. With other types of source data, you can change subtotal summary functions and show or hide subtotals for all row and column fields.
For OLAP source data, you can include or exclude hidden items when you calculate subtotals and grand totals. For other types of source data, you can include hidden page field items in subtotals, but hidden items in other fields are excluded by default.