Category:OLAP and Excel

From OLAP

The Power of Excel-Friendly OLAP

Should Excel be a key component of your company’s BPM system?

There’s no doubt how most IT managers would answer this question. Name IT’s top ten requirements for a successful BPM system, and they’ll quickly explain how Excel violates dozens of them. Even the user community is concerned. Companies are larger and more complex now than in the past; they seem too complex for Excel. Managers need information more quickly now; they can’t wait for another Excel report.

Excel spreadsheets don’t scale well. They can’t be used by many different users. Excel reports have many errors. Excel security is a joke. Excel output is ugly. Excel consolidation occupies a large corner of Spreadsheet Hell. And Sarbanes Oxley has changed everything.

Or so we’re told.

For these reasons, and many more, a growing number of companies of all sizes have concluded that it’s time to replace Excel.

But before your company takes that leap of hope or faith, perhaps you should take another look at Excel…particularly when Excel can be enhanced by an Excel-friendly OLAP database.

Excel-friendly OLAP could force your company to take another look at Excel. That technology helps to eliminate many of the classic objections to using Excel for business performance management.

Introducing OLAP

Excel-friendly OLAP products cure many of the problems that both users and IT managers have with Excel. But before I explain why this is so, I should explain what OLAP is, and how it can be Excel-friendly.

Although OLAP technology has been available for years, it’s still quite obscure. One reason is that “OLAP” is an acronym for four words that are remarkably devoid of meaning: On-Line Analytical Processing.

OLAP databases are more easily understood when they’re compared with relational databases. Both “OLAP” and “relational” are names for a type of database technology. Oversimplified, relational databases contain lists of stuff; OLAP databases contain cubes of stuff.

For example, you could keep your accounting general ledger data in a simple cube with three dimensions: Account, Division, and Month. At the intersection of any particular account, division, and month you would find one number. By convention, a positive number would be a debit and a negative number would be a credit.

Most cubes have more than three dimensions. And they typically contain a wide variety of business data, not merely General Ledger data. OLAP cubes also could contain monthly headcounts, currency exchange rates, daily sales detail, budgets, forecasts, hourly production data, the quarterly financials of your publicly traded competitors, and so on.

You can define any consolidation hierarchy for any of a cube’s dimensions. For example, in the Month dimension every month could roll up into quarters, which could roll up into years. Months also could roll up into year-to-date categories. Users treat both the “leaf” members and the consolidated members as equivalent sources of data. To illustrate, users could choose data from a leaf member like Aug-2006 just as easily as they could choose from a consolidated member like Aug-2006-YTD.

Other dimensions typically have their own roll-up structures. An Account dimension could roll up accounts into traditional financial statement hierarchies. A Division dimension could roll up divisions into the corporate reporting hierarchy. And a Product dimension could roll up products into one or more product structures.

Excel-Friendly OLAP

You probably could find at least 50 OLAP products on the market. But most of them lack a key characteristic: spreadsheet functions.

Excel-friendly OLAP products offer a wide variety of spreadsheet functions that read data from cubes into Excel. Most such products also offer spreadsheet functions that can write to the OLAP database from Excel…with full security, of course.

Read-write security typically can be defined down to the cell level by user. Therefore, only certain analysts can write to a forecast cube. A department manager can read only the salaries of people who report to him. And the OLAP administrator must use a special password to update the General Ledger cube.

Other OLAP products push data into Excel; Excel-friendly OLAPs pull data into Excel. To an Excel user, the difference between push and pull is significant.

Using the push technology, users typically must interact with their OLAP product’s user interface to choose data and then write it as a block of numbers to Excel. If a report relies on five different views of data, users must do this five times. Worse, the data typically isn’t written where it’s needed within the body of the report. Instead, the data merely is parked in the spreadsheet for use somewhere else.

Using the pull technology, spreadsheet users can write formulas that pull the data from any number of cells in any number of cubes in the database. Even a single spreadsheet cell can contain a formula that pulls data from several cubes.

To illustrate, suppose that an Excel dashboard presents information for a particular division and month. Excel users typically would designate a Month and a Division cell, which all the formulas would reference. With this design, you could change the Month cell from “Jun-2006” to “Jul-2006”, and the Division cell from “Northeast” to “Southwest”. Then, by simply recalculating your workbook, you would update the report to reflect the new settings. Under automation, you could print a report for every division for a given month.

At first reading, it’s easy to overlook the significant difference between this method of serving data to Excel and most others. Spreadsheets linked to Excel-friendly OLAP databases don’t contain data; they contain only formulas linked to data on the server. In contrast, most other technologies write blocks of data to Excel. It really doesn’t matter whether the data is imported as a text file, copied and pasted, generated by a PivotTable, or pushed to a spreadsheet by some other OLAP. The other technologies turn Excel into a data store. But Excel-friendly OLAP avoids that problem.

How Much Truth?

It’s common these days for database vendors to talk about having “one version of the truth.” (Recently, for example, Google listed 48,000 hits for that expression.) What’s less common is for anyone to ask these vendors how much relevant truth their systems can provide. This is a critical question for managers looking for BPM information, and for their staff—usually Excel users—who must provide the information. As most Excel users are sadly aware, the IT Department’s data warehouse never will provide all the data needed for business performance management.

It’s true that corporate data warehouses typically contain massive numbers of transactions. But this exhaustive detail largely is irrelevant to BPM, which typically relies on detailed summaries of data. At the extreme, data warehouses are a yard wide and a mile deep. But BPM requires data that is a mile wide and a yard deep.

Data Warehouse vs. OLAP Database

Here are some examples of data that OLAP databases can contain, but which data warehouses typically don’t:

Data Silos

Many information systems—both old and new—rely on databases that never will be added to the data warehouse. But these systems contain data that managers often need for managing business performance.

Most of those systems provide some way to export their data. Often, they support ODBC. Most can export their data as text files. Some companies even print reports from their legacy systems to files, and then use Monarch software to convert that text into rows and columns of data that can be imported into their OLAP database.

Mergers and Acquisitions

When two companies merge, the one company now has two data warehouses, not one. Each organization has one version of its own truth, but neither has one version of the whole truth. This is not an easy problem for IT to solve.

I know of one company, for example, that has five ERPs on four continents. For nearly ten years, IT’s goal has been to create a single data warehouse within two years.

Unfortunately, users and their managers need summary data to be fully available immediately, certainly by the end of the month in which a merger or acquisition closes.

One company closed the purchase of a billion-dollar subsidiary on the 26th of the month. By the Board meeting two weeks later, the Finance staff had printed more than 200 spreadsheets that reported both consolidated and consolidating reports for the new company, down to low-level summaries. All financial data was expressed in terms of the parent company’s Chart of Accounts. The staff could integrate the disparate systems so quickly because the parent already was using an Excel-friendly OLAP. They mapped the subsidiary’s meta data (general ledger codes, department codes, and so on) to the parent’s meta data. They imported the subsidiary’s financials to a new “slice” in the parent’s General Ledger cube, translating the meta data on the fly. Then they printed their standard spreadsheet analyses, all 200 pages of them, while adding a few new Excel analyses specific to the new subsidiary.

System Conversions

When a company purchases a new Enterprise Resource Planning (ERP) system, it creates at least two problems for BPM reporting.

First, the company typically converts the fewest months of historical data it can. For financial systems, companies often convert only one year of history prior to the current fiscal year. But for many BPM purposes, data about past performance is very useful, even critical:

•Monthly time-series forecasting requires at least 30 months of historical data, preferably more. •New products and sales offices often follow a consistent pattern for both revenue growth and startup expenses; but those patterns only can be discovered by analyzing data for startups during the past several years. •The analysis of trends in cost-volume-profit relationships during past downturns can serve as a guide to cost-reduction efforts during current downturns.

With an Excel-friendly OLAP in place before the conversion, all historical data continues to be available. Better yet, managers continue to receive their standard Excel reports, which can display data from both ERPs.

Second, transactions can be classified differently between the old and new systems, and this problem can be very difficult to solve.

To illustrate, I know of two large companies whose system conversions were significantly over budget. The accountants for both companies had specified that all account-department combinations that were not explicitly allowed were to be rejected by the new systems. But to reduce expenses, both systems were set to allow all such combinations that weren’t specifically prohibited. As a consequence, many transactions each month were automatically booked to incorrect account-department combinations.

Under normal circumstances the accountants in each company would have had to manually inspect more than one-hundred million account balances to find GL accounts whose transaction patterns had changed when the accounting systems changed. This would have been an impossible task, of course. However, both companies had been using Excel-friendly OLAP systems before their conversions began. Therefore, each created a simple spreadsheet that returned the monthly transactions for any specific account, department, and division, for the twelve months prior to the conversion and for all months after. Then using standard Excel statistics functions, and simple spreadsheet automation, the spreadsheets looped through every combination of account, department, and division, and listed all questionable combinations. The staff quickly corrected the obvious mistakes and researched the others.

External Data

Managers often need to see their performance reported within the context of their business environment. That environment can be described by the financial data of publicly held customers and competitors, by local and regional economic data, by population trends, and by other measures. IT doesn’t control such data. Nor do IT managers typically understand it. That’s not their job; it’s the user’s job. In most companies, if users don’t create and maintain cubes of external data, no one ever will. It’s not unusual for a knowledgeable user to create an OLAP cube on her local computer, populate it with public data, and then test its use with various spreadsheet reports. Once the cube is tested, she can work with the database administrator to move the cube to the OLAP server.

Forecasts

Most data warehouses provide empty buckets for budget data. But they typically don’t capture the wide variety of forecasts that companies generate. Nor do they help to generate those forecasts. But Excel-friendly OLAP offers both solutions.

To illustrate, Excel users easily can generate both top-down and bottom-up sales forecasts, compare the forecasts to find large conflicts, and then revise the forecasts after researching the differences.

To prepare the top-down forecasts, users can send a forecasting spreadsheet to the sales people. Unlike most spreadsheets, this one would include formulas that write the new forecast data to the appropriate area of an OLAP cube on the server. Full security would be maintained, of course. To prepare the bottom-up forecasts, users first create a spreadsheet that uses statistical methods to extend past sales performance for any product and region into the future. This spreadsheet also writes the forecast to an area of the OLAP cube, again, with full security. Then, using automation, they apply this spreadsheet forecast to every product and every region.

To compare the forecasts, they set up a spreadsheet to compare the top-down and the bottom-up forecasts for any product and region. Again, using automation, they calculate the workbook for every combination and automatically note where the two versions vary by an unusual degree.

Statistical Corrections

Forecasts, analyses, and management reporting all can be seriously flawed if analysts rely on historical data that reflects errors and oversights. But for a variety of reasons, managers, investors, and auditors all take a dim view of prior-period adjustments to the General Ledger. One way to handle this problem, particularly for forecasting and analysis, is for users to maintain an error-correction entity that can be consolidated or ignored, depending on the circumstance. Of course, these corrections must be managed carefully. It would be very easy, after all, for indications of real problems to be “corrected” out of existence. But when statistical corrections are tightly controlled, they provide the only practical way that past performance can be analyzed as it actually happened, not as it was mistakenly booked at the time.

Excel Dashboard Reporting

Excel has not been an obvious choice for BPM reporting. One reason for this is obvious: Typical Excel reports are ugly and difficult to read. But they don’t need to be.

Figure 1 illustrates an Excel dashboard report of public data for Starbucks Corporation. I created this report completely in Excel, with no assistance from third-party tools. This particular report uses data from two public web sites, downloaded into Excel. The report could display equivalent data for any public company whose financial information is covered by the two web sites.

Figure 1

In a business environment, a report like this could report performance for a department, division, product line, or for an entire company. The data would come from an Excel-friendly OLAP, not from the Web.

One significant advantage to using Excel for this type of reporting is that Excel users can change the report quickly and easily, without involving the IT Department. In fact, assuming that the necessary data already resides in the OLAP database, an Excel user typically could replace one measure with another in less than ten minutes.

Another significant advantage is that the report – even a single figure in the report -- can display data from many original sources. To illustrate, a figure could show the trend in labor costs (from the General Ledger cube) per full-time-equivalent employee (from the Headcount cube). Another figure could show the ratio of total company sales (from the General Ledger cube) to the sales of its largest publicly traded competitor (from a Competitor cube).

There is virtually no limit to the appearance that an Excel dashboard can take. Figure 2 illustrates a mockup dashboard based on a standard display that Business Week used about ten years ago. In fact, I often “steal” ideas for dashboard designs from the pages of business magazines. Excel dashboards also can compare the same measures for many different products, divisions, departments, and other entities.

Figure 2

Limitations of Excel BPM Reporting

As a general rule, Excel output tends to be on paper rather than on screen. Although many managers prefer it that way, paper reporting can seem primitive to certain managers.

However, Excel-friendly OLAP vendors are making progress towards web-based reporting.

PARIS Technologies allows Excel reports to connect to an OLAP database over the Internet. This gives Excel users read-write access to their cubes from virtually anywhere in the world. Several vendors offer interactive Web implementations of Excel reports linked to Applix TM1 cubes. And Excel 2007 spreadsheets will offer ways for users to interact with Analysis Services over the Web. On the other hand, the high-tech solution might not always be the best solution. One large company decided to take a low-tech approach to online management reporting. Each month the company automatically captures nearly 10,000 bitmaps of Excel reports of their OLAP data, and then displays those images on their Intranet.

But whatever the display limitations, Excel-friendly OLAP databases should cause you to take another look at Excel for business performance management.

Examples of OLAP integration in Excel

See also Example 6 - OLAP and Excel. Note that the previous examples should be reviewed in order for this one to make sense.

Information for OLAP and Excel provided by Charley Kyd of [www.exceluser.com]