OLAP and Excel >
The Partnership of OLAP
For Spreadsheet Hell
Late nights? Conflicting spreadsheet databases?
Manual data entry? Embarrassing errors? Hand-carved reports?
Broken macros? Monster spreadsheets? Organized confusion?
Last-minute panics? Welcome to Spreadsheet Hell!
by Charley Kyd
Let's talk about Spreadsheet Hell...and how to cure it.
Typically, you enter Spreadsheet Hell when you start manual
To get raw data into your spreadsheet, you might copy and paste it to
Excel, or import it as a file. Or perhaps
you use an Excel-surly OLAP. Or worse, perhaps you key it into
Unless you're very lucky, the data isn't at the right level of
summary. Or it's for the wrong month. Or it's incomplete. Or it was
generated using an incorrect query. Or whatever.
Once you fix the data, you often need to add and correct
data from other sources. Then you typically need to sort the data, summarize it, and
format it. You've got to add new rows and columns, write new
formulas, and format the results. Then, you really ought to check for errors.
Finally, just when your report is done, someone mentions that the
source data has changed, and you've got to start all over again.
And all the while, you've got to answer concerned questions like, "When will my
report be ready?" and "What do you do all day?"
The Excel-OLAP Connection
This figure illustrates a better approach.
Here, your company stores different types of data in different OLAP
cubes. Excel users rely on INDEX-like formulas to return any of this
data from any number of cubes to any spreadsheet, a spreadsheet that
works much like a template.
Unlike Excel's INDEX function, OLAP spreadsheet functions typically use
labels rather than numbers to reference data.
To illustrate, suppose you have a simple
cube of General Ledger data in the FinDat database in the directory
C:\Finance. Let's say that the cube has three
dimensions: Department, Account, and Month. The following formula would
return year-to-date data for the Sales department for Account 5678 for
=OLAPRead("C:\Finance\FinDat.olp", "GL", "Sales", "5678", "Jun-2004 YTD")
(At ExcelUser we use Paris Technologies' PowerOLAP to illustrate spreadsheet friendly
OLAPs. OLAPRead is a PowerOLAP function. Other
Excel-friendly OLAP products use similar functions with
Because each dimension typically contains consolidation hierarchies,
your formulas can return data from any level of consolidation for any
dimension. To illustrate, the following formula would return
company-wide labor expenses for the month of June, 2004:
=OLAPRead("C:\Finance\FinDat.olp", "GL", "Total Departments", "Total Labor
In practice, the labels typically will be in cells. Therefore, your
actual formula might be in cell D9 and look something like this:
=OLAPRead(DBPath, "GL", D$3, $A9, Month)
Here, your database path would be entered in a cell named "DBPath".
And you might have other department names in row 3 and other
expense categories in column A. You could report performance for any
month merely by entering a month-year label in the cell named Month, and
Why Excel-Friendly OLAP Cures Spreadsheet Hell
Using Excel formulas to populate spreadsheet data is a simple idea,
but a powerful one. It offers Excel users escape from Spreadsheet Hell.
Here are three reasons this is so:
1. Every report acts as a template.
Suppose you create a "one-time" report for August, and then your boss
asks for an update for September. Or suppose she asks you to create a
similar report for other departments and other divisions.
If you were trapped in spreadsheet hell, you would need to create
each version of the report from scratch each month. But with OLAP, there's no problem. You merely enter the new labels for department,
division, and month, into the cells you've set up to contain this
information. And then you recalculate.
Similarly, if there's a last-minute change to your data, there's no
problem. You merely open your spreadsheet report, recalculate, and
2. Reports are easy to reconcile.
Suppose your company relies on two Gross Margin reports. One analyst
prepares a report of Gross Margin by Division by Month, and another
analyst prepares a report of Gross Margin by Product Line by Month. And
suppose that the Grand Totals of the two reports don't match.
If you were trapped in spreadsheet hell, you might need to compare
two SQL queries or two spreadsheet databases that don't quite agree. And
the differences might never be resolved.
But these reports would be easy to reconcile if both get their data from an
Excel-friendly OLAP database. Because both sets of formulas get their
data from exactly the same source, it's easy to compare a representative
formula from each report to learn where one report is picking up data
that the other report is missing.
3. Reports can include data from any source.
Many managers would like to see reports that include data from
external sources, or from internal sources not available to the IT
Perhaps managers want to track the financial performance of major
customers, vendors, and acquisition candidates. Perhaps they need to
track the prices of key commodities, monitor currency exchange rates, or
follow economic performance in key geographic regions.
Or perhaps they want to see consolidated financials that include the
performance of a new acquisition, even though the IT Department can't merge the acquired company's data for
at least two years.
If you were trapped in spreadsheet hell, non-IT data would be a
particular problem. This is because the only place to store external
data would be in a spreadsheet on some analyst's hard drive.
But with an Excel-friendly OLAP, these types of reports are no problem.
The external data is merely imported into an OLAP cube. And then any
Excel user with access to the OLAP system can use that data in any
The bottom line: A spreadsheet-friendly OLAP database offers the
most effective cure we know for spreadsheet hell.