Articles

OLAP in a Nutshell

Introduction
The purpose of this brief article is to introduce the reader to OLAP, and how it fits in the MultiValue world. While much of what is described is common to all OLAP tools, there is a bias here towards fully-integrated, MultiValue-native OLAP solutions.

What is OLAP?
OLAP, an acronym for "On-Line Analytical Processing", is a technology that provides for evaluating any business activity, from the top down, at interactive speeds. Any observation, starting at the top, or anywhere within, may be drilled down to the next level of detail, and as far down as the original transactions.

In contrast with conventional reporting, OLAP is fast, flexible and thorough and may obsolete the need for any custom analysis programming. OLAP, being interactive, does away with nearly all hardcopy reports. Also with OLAP, comparing activity from one timeframe to another is straight-forward.

At the root of OLAP's speed and flexibility, is an underlying hypercube. For each kind of business analysis (sales, general ledger, inventory, etc.), a hypercube is created with as many dimensions as there are drill-down identifiers. As an example, a simple sales analysis might have five identifiers: BRANCH, SALES.REP, CUSTOMER, PRODUCT and PRODUCT.LINE. This would involve a five-dimension cube, where at each intersection, the activity metrics, for example, SALES and COST, are accumulated by the timeframe that the activity occurred.

By having five identifiers, the hypercube provides 32 summaries of activity - one for every combination of identifiers including the origin. The hypercube also retains the relationship between all of the summaries, in this case providing for drilling down through any of the 120 possible paths through the five identifiers. See Hypercube Arithmetic sidebar.

OLAP applications typically have between 5 and 12 drill-down identifiers. As this can lead to extremely large and overly detailed cubes that are slow to update, the cube can be trimmed by limiting the depth of exploration or by explicitly specifying the exploration paths.

Another purpose of the hypercube is to keep track of the original transactions that support each of the summaries. As transaction counts may be overwhelming for higher level summaries, transaction access is often configured to become available only on more detailed views.

Once the hypercube is configured for an application, it is loaded from the organization's historical transactions and then updated daily to keep current. This process of transforming the raw data into the hypercube can be resource intensive, and some experimentation may be required to get the right balance of results versus cube size and transformation time.

The bottom line objective of the OLAP hypercube is to have the answers prepared before the questions are asked.

Presentation
OLAP views are presented in a grid fashion, similar to a spreadsheet. The first column of a view is reserved for the identifiers that are displayed in an outline format representing their relationship.

The columns that follow derive their results from dictionary-like items that specify headings, formatting and which utilize a free-form expression in the syntax of the underlying BASIC language. Columns for each accumulator and timeframe combination are generated automatically by the transformation process. Adding new columns - such as the trend between this year's and last year's sales - is a simple point and click affair.

Also, in the case of native OLAP tools, data outside the cube, such as customer contact information or budget values may be freely intermixed with other column data.

Columns may also be collected into logical groups that can be switched in and out so that different kinds of analysis can be performed against the same subject matter with ease.

What to expect from a comprehensive OLAP tool:

	Drilling down - drilling down from one level to another should be intuitive and rapid. The transaction detail behind any of the presented values should be available with a mouse click.
	Speed - As you navigate within an OLAP system, response time when presenting the results of OLAP requests should be measurable within seconds. Even when presenting the information from millions of source records.
	Saving Views - Any time a useful view is developed, it should be easily saved as a view to be recalled later or shared with others. Developing a new view from scratch or modifying an existing view should be straight-forward.
	Plotting - Two and three-dimensional plots of selected data ought to be available.
	Pivoting - Allows for exploring one subject directly across another subject, such as product lines across sales reps or customer classes across the branches.
	Presentation Manipulation - Clipping presentations to show only the top or bottom performers; full sorting and filtering capabilities; leaving off subjects that had no activity in time period being studied; easily selecting individual subject or groups of subjects by their description; unlimited undo etc.
	Exporting - As OLAP presentations are already in a grid format, exporting to Excel or to an ASCII file should be simple.
	Security - Flexible security is crucial as a hypercube contains the essence of a entity's secrets. Look for being able to restrict any user to only those areas where they have a need-to-know without having to create separate sub-cubes.
	Integration - OLAP architectures very widely. Consider an OLAP tool that is fully integrated within the operating environment rather than one that is external. Being integrated, you will be able to freely mix cube data with data from your existing files such as customer contact information, budget values, etc. Integration also eliminates the need for additional hardware, networking, installation headaches and so on.
	Training Requirements - A well-designed OLAP tool will be intuitive enough as to require a minimum of training. A user that is familiar with the business should be productive with OLAP with no more than an hour or less of orientation. As OLAP is often intended for sales reps, middle managers, VPs, even the president, ease-of-use is critical.

Top of Page