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.

Retrieving OLAP data    You can connect to OLAP data sources (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) 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 (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.). You can save OLAP PivotTable and PivotChart reports in report templates (report template: An Excel template (.xlt file) that includes one or more queries or PivotTable reports that are based on external data. When you save a report template, Excel saves the query definition but doesn't store the queried data in the template.), and you can create Office Data Connection (.odc) files to connect to OLAP databases and query files (.oqy) for OLAP queries (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.). 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 (OLAP provider: A set of software that provides access to a particular type of OLAP database. This software can include a data source driver and other client software that is necessary to connect to a database.), 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.

An OLAP provider    To set up OLAP data sources (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) for Microsoft Excel, you need one of the following OLAP providers (OLAP provider: A set of software that provides access to a particular type of OLAP database. This software can include a data source driver and other client software that is necessary to connect to a database.):

• Microsoft OLAP provider    Excel includes the data source driver (data source driver: A program file used to connect to a specific database. Each database program or management system requires a different 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 (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) containing OLAP data or a cube definition (cube definition: Information, stored by the OLAP Cube Wizard in an .oqy file, that defines how to construct an OLAP cube in memory using data retrieved from a relational database.) 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 (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.) file.

If you work with PivotTable and PivotChart reports from both OLAP source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) 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 (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.). You also have several other options for refreshing (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) the report.

In reports based on OLAP source data, the page field (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.) settings are unavailable, background query is unavailable, and the optimize memory setting is not available.

Field types    For OLAP source data, dimension (dimension: An OLAP structure that organizes data into levels, such as Country/Region/City for a Geography dimension. In a PivotTable or PivotChart report, each dimension becomes a set of fields where you can expand and collapse detail.) fields have icons in the field list and can be used only as row (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.) (series), column (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.) (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 (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) 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 (property fields: Independent attributes associated with items, or members, in an OLAP cube. For example, if city items have size and population properties stored in the server cube, a PivotTable report can display the size and population of each city.) 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 (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) 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 (custom calculation: A method of summarizing values in the data area of a PivotTable report by using the values in other cells in the data area. Use the Show data as list on the PivotTable Field dialog for a data field to create custom calculations.).

You cannot create calculated fields (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) or calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) 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.