Overview of Online Analytical Processing (OLAP)

Online Analytical Processing (OLAP) is a technology that is used to organize large business databases and support business intelligence. OLAP databases are divided into one or more cubes, and each cube is organized and designed by a cube administrator to fit the way that you retrieve and analyze data so that it is easier to create and use the PivotTable reports and PivotChart reports that you need.

In this article

What is business intelligence?

What is Online Analytical Processing (OLAP)?

OLAP features in Excel

Software components that you need to access OLAP data sources

Feature differences between OLAP and non-OLAP source data

What is business intelligence?

A business analyst often wants to get a big picture of the business, to see broader trends based on aggregated data, and to see these trends broken down by any number of variables. Business intelligence is the process of extracting data from an OLAP database and then analyzing that data for information that you can use to make informed business decisions and take action. For example, OLAP and business intelligence help answer the following types of questions about business data:

• How do the total sales of all products for 2007 compare with the total sales from 2006?

• How does our profitability to date compare with the same time period during the past five years?

• How much money did customers over the age of 35 spend last year, and how has that behavior changed over time?

• How many products were sold in two specific country/regions this month as opposed to the same month last year?

• For each customer age group, what is the breakdown of profitability (both margin percentage and total) by product category?

• Find top and bottom salespeople, distributors, vendors, clients, partners, or customers.

Top of Page

What is Online Analytical Processing (OLAP)?

Online Analytical Processing (OLAP) databases facilitate business-intelligence queries. OLAP is a database technology that has been optimized for querying and reporting, instead of processing transactions. The source data for OLAP is Online Transactional Processing (OLTP) databases that are commonly stored in data warehouses. OLAP data is derived from this historical data, and aggregated into structures that permit sophisticated analysis. OLAP data is also organized hierarchically and stored in cubes instead of tables. It is a sophisticated technology that uses multidimensional structures to provide rapid access to data for analysis. This organization makes it easy for a PivotTable report 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.

OLAP databases are designed to speed up the retrieval of data. Because the OLAP server, rather than Microsoft Office Excel, computes the summarized values, less data needs to be sent to Excel when you create or change a report. This approach enables you to work with much larger amounts of source data than you could if the data were organized in a traditional database, where Excel retrieves all of the individual records and then calculates the summarized values.

OLAP databases contain two basic types of data: measures, which are numeric data, the quantities and averages that you use to make informed business decisions, and dimensions, which are the categories that you use to organize these measures. OLAP databases help organize data by many levels of detail, using the same categories that you are familiar with to analyze the data.

The following sections describe each component in more detail:

Cube     A data structure that aggregates the measures by the levels and hierarchies of each of the dimensions that you want to analyze. Cubes combine several dimensions, such as time, geography, and product lines, with summarized data, such as sales or inventory figures. Cubes are not "cubes" in the strictly mathematical sense because they do not necessarily have equal sides. However, they are an apt metaphor for a complex concept.

Measure     A set of values in a cube that are based on a column in the cube's fact table and that are usually numeric values. Measures are the central values in the cube that are preprocessed, aggregated, and analyzed. Common examples include sales, profits, revenues, and costs.

Member      An item in a hierarchy representing one or more occurrences of data. A member can be either unique or nonunique. For example, 2007 and 2008 represent unique members in the year level of a time dimension, whereas January represents nonunique members in the month level because there can be more than one January in the time dimension if it contains data for more than one year.

Calculated member     A member of a dimension whose value is calculated at run time by using an expression. Calculated member values may be derived from other members' values. For example, a calculated member, Profit, can be determined by subtracting the value of the member, Costs, from the value of the member, Sales.

Dimension     A set of one or more organized hierarchies of levels in a cube that a user understands and uses as the base for data analysis. For example, a geography dimension might include levels for Country/Region, State/Province, and City. Or, a time dimension might include a hierarchy with levels for year, quarter, month, and day. In a PivotTable report or PivotChart report, each hierarchy becomes a set of fields that you can expand and collapse to reveal lower or higher levels.

Hierarchy     A logical tree structure that organizes the members of a dimension such that each member has one parent member and zero or more child members. A child is a member in the next lower level in a hierarchy that is directly related to the current member. For example, in a Time hierarchy containing the levels Quarter, Month, and Day, January is a child of Qtr1. A parent is a member in the next higher level in a hierarchy that is directly related to the current member. The parent value is usually a consolidation of the values of all of its children. For example, in a Time hierarchy that contains the levels Quarter, Month, and Day, Qtr1 is the parent of January.

Level     Within a hierarchy, data can be organized into lower and higher levels of detail, such as Year, Quarter, Month, and Day levels in a Time hierarchy.

Top of Page

OLAP features in 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 that are created with Microsoft SQL Server OLAP Services version 7.0, Microsoft SQL Server Analysis Services version 2000, and Microsoft SQL Server Analysis Services version 2005, the Microsoft OLAP server products. 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 report or PivotChart report or in a worksheet function converted from a PivotTable report, but not as an external data range. You can save OLAP PivotTable reports and PivotChart reports in report templates, and you can create Office Data Connection (ODC) files (.odc) to connect to OLAP databases for OLAP queries. When you open an ODC file, Excel displays a blank PivotTable report, which is ready for you to lay out.

Creating cube files for offline use     You can create an offline cube file (.cub) with a subset of the data from an OLAP server database. Use offline cube files to work with OLAP data when you are not connected to your network. A cube enables you to work with larger amounts of data in a PivotTable report or PivotChart report than you could otherwise, and speeds retrieval of the data. You can create cube files only if you use an OLAP provider, such as Microsoft SQL Analysis Services Server version 2005, which supports this feature.

Server Actions     A server action is an optional but useful feature that an OLAP cube administrator can define on a server that uses a cube member or measure as a parameter into a query to obtain details in the cube, or to start another application, such as a browser. Excel supports URL, Report, Rowset, Drill Through, and Expand to Detail server actions, but it does not support Proprietary, Statement, and Dataset. For more information, see Perform an OLAP server action in a PivotTable report .

KPIs      A KPI is a special calculated measure that is defined on the server that allows you to track "key performance indicators" including status (Does the current value meet a specific number?) and trend (what is the value over time?). When these are displayed, the Server can send related icons that are similar to the new Excel icon set to indicate above or below status levels (such as a Stop light icon) or whether a value is trending up or down (such as a directional arrow icon).

Server Formatting     Cube administrators can create measures and calculated members with color formatting, font formatting, and conditional formatting rules, that may be designated as a corporate standard business rule. For example, a server format for profit might be a number format of currency, a cell color of green if the value is greater than or equal to 30,000 and red if the value is less than 30,000, and a font style of bold if the value is less than 30,000 and regular if greater than or equal to 30,000. For more information, see Design the layout and format of a PivotTable report.

Office display language     A cube administrator can define translations for data and errors on the server for users who need to see PivotTable information in another language. This feature is defined as a file connection property and the user's computer country/regional setting must correspond to the display language.

Top of Page

Software components that you need to access OLAP data sources

An OLAP provider     To set up OLAP data sources for Excel, you need one of the following OLAP providers:

• Microsoft OLAP provider     Excel includes the data source driver and client software that you need to access databases created with Microsoft SQL Server OLAP Services version 7.0, Microsoft SQL Server OLAP Services version 2000 (8.0), and Microsoft SQL Server Analysis Services version 2005 (9.0).

• 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 that contains 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 of the data in the OLAP database or offline cube file. After you create an OLAP data source, you can base reports on it, and return the OLAP data to Excel in the form of a PivotTable report or PivotChart report, or in a worksheet function converted from a PivotTable report.

Microsoft Query     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. For more information, see Use Microsoft Query to retrieve external data.

Top of Page

Feature differences between OLAP and non-OLAP source data

If you work with PivotTable reports 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 Excel every time that 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 report filter field items. You also have several other options for refreshing the report.

In reports based on OLAP source data, the report filter field settings are unavailable, background query is unavailable, and the optimize memory setting is not available.

Note: The optimize memory setting is also not available for OLEDB data sources and for PivotTable reports baed on a cell range.

Field types    OLAP source data, dimension fields can be used only as row (series), column (category), or page fields. Measure fields can be used only as value fields. For other types of source data, all fields can be used in any part of a report.

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 may not be available. 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 report filter fields may not have an All item, and the Show Report Filter 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 value fields. For other types of source data, you can change the summary function for a value field and use multiple summary functions for the same value field. 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. 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 report filter field items in subtotals, but hidden items in other fields are excluded by default.

Top of Page