Home >  OLAP and Excel > 

The Partnership of OLAP
And Excel Brings A Cure

For Spreadsheet Hell

Late nights? Conflicting spreadsheet databases? Broken links?
Manual data entry? Embarrassing errors? Hand-carved reports?
Broken macros? Monster spreadsheets? Organized confusion?
Last-minute panics? Welcome to Spreadsheet Hell!

Let's talk about Spreadsheet Hell...and how to cure it.

Typically, you enter Spreadsheet Hell when you start manual spreadsheet reporting.  

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 Excel.

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 June, 2004:

=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 different names.)

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 Expense", "Jun-2004")

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 then recalculating.

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 print.

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 Department.

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 report.

The bottom line: A spreadsheet-friendly OLAP database offers the most effective cure we know for spreadsheet hell.