For business users of Microsoft Excel Free guides and templates
Home >  Ideas > 

Excel Data Strategies

What the Heck Is an Excel-Friendly OLAP Database?

Excel-friendly OLAP databases can give Excel formulas real-time access to Big Data...while reducing users' workload and errors.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

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. And a cube is like a multi-dimensional worksheet.

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, your Excel formulas could return any of that data into your 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 five general characteristics.

1. They use spreadsheet functions to populate Excel.

Unfortunately, most other programs write numbers and text to Excel...which spreads raw data throughout the company. But instead, Excel-friendly OLAPs use worksheet functions to return data from the OLAP database.

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

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

2. They provide many spreadsheet functions.

Modern Excel-friendly OLAPs offer dozens of worksheet functions. By offering a wide variety of useful worksheet functions, Excel-friendly OLAPs give Excel users significant analytical power.

3. They reference many cubes in one worksheet.

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

This means that formulas in any worksheet logged into the database can have direct access to hundreds of gigabytes of data, of nearly any kind.

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 or new products and regions to a sales database.

5. Users can collaborate in real time.

Users with the proper credentials can write data from their workbooks to OLAP two ways.

First, they can write the data by entering it manually. This allows users and managers to enter plans and other data to the database by using a worksheet as a data-entry form.

 Second, they can set up formulas that write back the data each time the workbook recalculates. This allows formulas in worksheets to write forecasts and other calculations directly to the database.

And because cubes can translate foreign currencies and consolidate the data in real time, users throughout the company can have real-time access to all data they have permission to view.

In short, Excel-friendly OLAP programs are like spreadsheets on steroids.


Charley's SwipeFile charts

Free Excel Dashboards