Home >  BI for Excel >

Five Reasons OLAP Belongs
On Excel Users' Desktops

What's the most valuable technology for business users of Excel?
It may well be the multi-cube database technology, called OLAP. 

As Excel users, we're excited about Excel-friendly OLAP programs. Here are a few of the reasons for our excitement:

1. OLAP offers one version of the truth.

Most Excel users are accustomed to working with many versions of the truth.

  Joe's reports use a PivotTable query written two years ago by a programmer who's long gone. Sally, on the other hand, writes similar reports using her own query against similar data. For some reason, her numbers never quite agree with Joe's.

  When Sally first ran the July report, she got one number for Total Sales. Several months later she ran the same report and got a slightly different number. She now maintains a spreadsheet database with each month's data.

  Because Joe had the same problem, he also saves his data in Excel. Unfortunately, the two spreadsheet databases never quite agree. Some co-workers link their spreadsheets to Sally's data; others link to Joe's data.

  Neither person can reconcile their numbers to similar numbers produced by their General Ledger software. They do try to keep their results close, of course. And rounding helps.

On the other hand, with OLAP, there's one version of the truth. Each Excel formula that returns August sales for the Northeast division will return the same number. This is because each Excel formula gets that number from exactly the same cell in the OLAP database.

2. Fast report development.

Most reports start with data. Excel-friendly OLAPs provide an interface that allows users to explore that data quickly and easily--and then use it in Excel.

For example, you could look at sales by product by month for a particular region and customer class. Or sales by region by customer class for a particular month and product. Or whatever.

Then, when you have a rough version of what you want your report to contain, you click a button to drop the report into Excel. Many OLAPs can take this step. But most of them write raw data to your spreadsheet. Only the Excel-friendly ones give you Excel formulas that return your data from the server.

With the sample formulas in your spreadsheet, you can add your own touches. You can insert rows and columns. Add calculations. Add or delete formulas. Whatever.

Suppose you need data from a second cube, from the Headcount cube, for example. You would follow the same approach to create the initial view of your data. Then you merely copy the formulas you need into your first spreadsheet. Or you can create your formulas from scratch.

Soon, you can create sophisticated spreadsheet reports, with very little work.

3. Immediate spreadsheet updating.

Suppose you create a report for the Southwest Division for April. You show it to your boss, who says, "That's great! I'd like to see an update every month! Oh, and while you're at it, would you give me a similar report for each of the other divisions, and for the company as a whole?"

In the past, this request was really bad news. It meant that you would have to work long hours to create each report. For companies that rely on PivotTables, the problem isn't quite as bad. But for OLAP users, this request is no problem at all.

Users of Excel-friendly OLAPs have it easy. To change divisions, they change the cell with the division label from "Southwest" to, say, "Central". Then they recalculate and print. Or, they enter, say, July in the cell they've set up for the month label. Then they recalculate and print.

4. Independence from IT

Excel users tend to have an uneasy relationship with programmers. Users understand what the data means, but they don't have easy access to the data. Programmers have access, but they don't understand what the data means.

Both groups would benefit if users could have easier access to data. Users benefit because they can create reports and analyses more quickly and easily. Programmers benefit because they can reduce the number of user queries that they must write.

With OLAP, programmers work with users to define and error-check the simple query that populates each cube. Then, period after period, IT runs the same query to update the cube.

Excel users do the rest.

5. Reduced errors

Typical Excel reports are filled with errors. But reports linked to OLAP cubes have much less possibility of error. Here are some reasons:

  Typical Excel reports contain numbers -- values -- in many cells. Any of those numbers could have been accidentally changed in the worksheet. But because Excel-friendly OLAPs use formulas to return data from cubes, there's much less chance of error.

  Typical Excel reports have no practical way to reconcile their results to an ultimate truth. But most OLAP-based Excel reports have that power. To illustrate, a report of Sales by Division by Month could include a simple reconciliation formula outside the print area. The formula could return "Error!" if the spreadsheet total of the sales for all divisions doesn't match the equivalent value returned from the OLAP cube.

  Typical Excel reports often use data from spreadsheet databases, many of which contain errors. But with OLAP, there's no need for these private databases. Because everyone uses the same data, it's much more accurate and certainly more consistent.

  Typical Excel reports, which rely on numbers in cells, have no practical way to adapt after their source data has been changed. But users can refresh OLAP-based Excel reports merely by recalculating their workbooks.