Home >  BI for Excel >

What the Heck Is OLAP?
When Is It Excel-Friendly?

Excel-friendly OLAP databases give Excel users significant
analytical power and flexibility...while reducing the work load. 

by Charley Kyd
Updated December, 2006

An OLAP database is a type of technology, just as a Relational database is a type of technology.

"OLAP" stands for On-Line Analytical Processing". But this unfortunate term provides no hint about the power that OLAP offers to Excel users.

You know what relational files are. Oversimplified, they're lists of stuff. OLAP databases are cubes of stuff. 

To illustrate, you could import a simple General Ledger database into an OLAP cube. You might have three dimensions: Account, Department, and Month. One number would be found at the intersection of any specific member of these dimensions. By convention, the number would be stored as a plus or minus, that is, a debit or credit.

You could have one OLAP cube for your general ledger, another for headcounts, another for sales history, another for economic statistics, and so on. Then, in a few clicks, an Excel user could drop any of that data into her worksheet.

Excel-Friendly OLAP

Each brand of OLAP program has a different set of features and benefits. Each interacts with Excel in different ways. However, only a few of these programs are "Excel-friendly".

Excel-friendly OLAP programs have four general characteristics.

1. They use spreadsheet functions to populate Excel.

Unfortunately, most OLAP programs write numbers and text to Excel. But the most important feature of Excel-friendly OLAPs is that they use spreadsheet functions to return data from the OLAP database.

The difference between these two methods is the difference between push and pull. Most OLAPs force users to navigate through various dialogs to push data into Excel. But Excel-friendly OLAPs allow users to write formulas in their spreadsheets, formulas that specify the data they need. Then, to pull the data into Excel, users merely recalculate their spreadsheets.

The pull approach is much more powerful than the push approach, and much easier to learn.

2. They provide many spreadsheet functions.

Some OLAP programs offer only one or two Excel functions. But Excel-friendly OLAPs offer a dozen or more functions. By offering a wide variety of useful spreadsheet functions, Excel-friendly OLAPs give Excel users significant analytical power.

3. They reference many cubes in one worksheet.

In Excel, formulas in one spreadsheet can link to any number of cells in any number of workbooks. Similarly, in Excel-friendly OLAP databases, formulas in one Excel spreadsheet can link to any number of multi-dimensional cells in any number of OLAP cubes.

4. Users can control the OLAP database.

OLAP software doesn't do users much good if users can't control the database. Excel-friendly OLAP databases give qualified users the ability to create new cubes of data, to add new members to dimensions (like adding account numbers to the Chart of Account), and to update the cubes as necessary.

The bottom line is that Excel-friendly OLAP programs give Excel users significant power and flexibility, while eliminating many problems associated with writing values to cells.