What OLAP Can Do for Excel

OLAP is the name for a type of database technology that stores information in cubes, rather than in lists. A company might keep its general ledger accounting data in a simple OLAP cube that includes three dimensions: account, division, and month. At the intersection of any particular account, division, and month you would find one number. Most cubes have more than three dimensions, and they typically contain a wide variety of business data, not merely G/L data. Users with access to an OLAP cube of corporate information can define any consolidation hierarchy for the cube's dimensions. For example, in the "month" dimension, every month could roll up into quarters, which could roll up into years. Or months could roll up into year-to-date numbers. The individual data points (the cube's "leaf members") and the consolidated numbers would be equivalent sources of data. So users generating a report could choose data from a leaf member like Mar-2007 just as easily as they could choose from a consolidated member like Mar-2007-YTD.

There are probably 50 OLAP products on the market. While many can exchange data with spreadsheets, only a few offer spreadsheet functions that read data from OLAP cubes into Excel and write information from Excel to the OLAP database (see Excel-Friendly OLAP Products). The difference between this type of "pull" technology and other OLAP products' "push" functionality is significant to users.

Database products with push functionality typically require users to choose data through their OLAP product's user interface, then write that information as a block of numbers to Excel. If a report relies on five different views of data, users must take this action five times. Worse, when the data is imported into Excel, it typically isn't placed where it's needed within the body of the report. It's parked in one area of the spreadsheet for use somewhere else. It really doesn't matter whether the data is imported as a text file, copied and pasted, generated by a PivotTable, or pushed to a spreadsheet by some other means; the data lands in Excel in the same inconvenient format.

In contrast, OLAP products that I call "Excel-friendly" pull data into Excel. Rather than writing numbers to a spreadsheet when a user issues a command, Excel-friendly OLAP provides current data to the spreadsheet whenever Excel users recalculate the workbook. Spreadsheets linked to Excel-friendly OLAP databases don't contain data; they contain only formulas linked to data on the server or in a local database. Suppose that an Excel dashboard presents performance information for a specific corporate division for a specific month. And suppose that users want to show the performance of each division in the company in this same format, and they want to be able to easily update the spreadsheet with new numbers at the end of every month. To do so, they might designate a "month" cell and a "division" cell, then reference these cells in every formula throughout the spreadsheet that links to the OLAP database. With this design, they could change the month cell from "Feb-2007" to "Mar-2007" and the division cell from "Northeast" to "Southwest." Then, by simply recalculating the workbook, users could update the report to reflect the new settings. They even could automate the process so that Excel would print a report for every division for a given month.

Using this type of pull technology, spreadsheet users can write formulas that reference any number of cells in any number of cubes in the database. A single cell in the spreadsheet can contain a formula that pulls data from several cubes. To illustrate, one formula could show labor costs (from the G/L cube) per full-time-equivalent employee (from the head-count cube). Another formula could show the ratio of total company sales (from the G/L cube) to the sales of its publicly traded competitor (from a competitor cube). Excel-friendly OLAP also typically allows users to write from their spreadsheets to the database.