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
1. OLAP offers one version of the truth.
Most Excel users are accustomed to working with many versions of
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
Soon, you can create sophisticated spreadsheet reports, with very
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
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.