The CPA Journal

THE CPA AND THE COMPUTER

March 2002

Using Online Analytical Processing Tools

By Tom Achor, Achor Kristjanson Consulting, Bellevue, Wash.

Online analytical processing tools (OLAP), combined with data warehouses and similar repositories, are a subset of enterprise applications that go beyond relational databases and financial reports in their flexibility and timeliness.

The term OLAP was coined in 1993 by E.F. Codd. Codd listed 12 basic characteristics of OLAP:

Although OLAP data and relational data structures have similarities, their differences are significant. A relational structure minimizes storage requirements and data redundancy and maximizes flexibility. OLAP data is structured to optimize performance and data accessibility, and once created, its structure may be less flexible. From the user perspective, OLAP tools are simple, graphical, and focused— friendlier than queries done on a relational database. Relational databases are efficiency-driven and OLAP databases are effectiveness-driven.

The term OLAP is sometimes used interchangeably, but incorrectly, with business intelligence (BI), decision support systems (DSS), and executive information systems (EIS). More than a particular tool or even a technology, OLAP is simply computer-enhanced multidimensional analysis. Business managers would have been doing this kind of analysis long ago were it not for the overwhelming volume of calculations.

Exhibit 1 presents a sales table in a simple business database for an outdoor store. In OLAP terms, the figure is a cube. This table is a measure of units sold, dimensioned, in OLAP terms, by product and time. The dimensions are separated into categories or ranges of values (e.g., one year). The values in a dimension are called members or positions. As in a spreadsheet, the intersections of dimension members are called cells.

To consider another dimension, region, the cube would then have three dimensions, as in Exhibit 2. The cube can no longer be effectively portrayed in a two-dimensional table. This is one advantage to doing this type of analysis online: Rather than trying to see all dimensions at once, the user can navigate around the data.

Excel users can sometimes use the pivot table function to do a limited kind of multidimensional analysis on data in a spreadsheet or a linked database. Most leading OLAP products, including Hyperion, Essbase, and Cognos PowerPlay, include or offer an Excel-based interface that works like the pivot table.

For example, assume the aforementioned outdoor supply company has four regions with five stores each. Each store has six departments, and each department carries 20 product lines consisting of 100 products each. This simple sales analysis would require 240,000 references.

The analysis above, based on year, could be used in a shareholders’ report. For further detail, sales figures would probably need to be broken down further by quarter, month, week, and day, bringing the potential number of cells in the cube to 262,800,000.

Codd’s OLAP rules require multiple dimensions and levels—not only a mass of dimensioned data, but a hierarchy and a way to aggregate the data. For example, departments get aggregated in stores, stores in regions, and products in product lines. Even more important, days get aggregated in weeks or months, months in quarters, and quarters in years. OLAP engines provide graphical means to define these hierarchies for all the dimensions, and usually include calendars that have predefined hierarchies of time periods.

Probably the most significant of Codd’s 12 rules is “intuitive manipulation of data.” Modern OLAP tools deliver deceptively simple interfaces that allow for high-level analysis with drag-and-drop refreshing of the output. Most implementations also include extensive drill-down capabilities, often all the way down to an original transaction record.