BI Scorecard: OLAP - InformationWeek

InformationWeek is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Software // Information Management

BI Scorecard: OLAP

A spreadsheet can take analysis just so far; for more robust analysis you need online analytic processing (OLAP).

If you've been following this series of reviews of BI products, you may have noticed that I've been discussing analysis capabilities in increasing order of complexity. In the Reporting segment, I examined interactive reports, which allow drilling into detail. In the next segment of the series, I looked at spreadsheet integration, which provides more flexibility and is one of the most popular tools for analysis. A spreadsheet can take analysis just so far, however; for more robust analysis you need online analytic processing (OLAP).

Who Needs OLAP?

Some analysts say that OLAP is appropriate for only a small percentage of users, primarily power users. I disagree.

On the BI user spectrum, "information consumer" is the least analytic and is the polar opposite of power user. When it comes to my energy bill, for example, I qualify as an information consumer.

After moving back to northern New Jersey during the coldest winter in 30 years, I positively flipped when I got one gas bill. My first question was, "What was my bill last month?" My gas provider had intuitively already provided a comparison chart including the prior month. Time period comparisons like this are characteristic of OLAP. So my next question was, "What was it last year?" For this, I had to dig through paper files, when OLAP could have given me the answer in a single click.

There is, actually, an alternative to OLAP for this type of user. As I discussed in the "Reporting" segment of this series, the ability to link multiple reports is one way of delivering drill down, a key OLAP capability. However, providing linked reports that can drill into one another assumes the report designer can predict a user's thought process. While this prediction is sometimes possible, it's clearly not always possible. Full OLAP functionality lets the user choose drill paths, and it encompasses more than just drilling.

Bottom line: I think all users — from intermittent information consumers to power users — would benefit from various aspects of OLAP functionality. Unfortunately, OLAP architectures and cost often prohibit broad access.

OLAP vs. Reporting

In the early 1990s, Essbase (then owned by Arbor but now property of Hyperion) was a bit of an anomaly. So Arbor hired the father of relational databases, E. F. Codd, to clarify this new thing called OLAP. Codd defined 12 rules but the following four most differentiate reporting from OLAP:

1. Multidimensional: Users analyze numerical values from different dimensions, such as product, time, and geography. A report, on the other hand, may be one dimensional, such as list of product prices at one point in time.

2. Consistently fast: As users navigate different dimensions and levels within a dimension, OLAP means fast — the speed of thought. If a user double-clicks to drill down from Year to Quarter, waiting 24 minutes or 24 hours for an answer isn't acceptable. Report users, of course, don't want slow reports either, but indeed, some reports take this long to run and must be scheduled.

3. Varying levels of aggregation: To ensure predictable query times, OLAP vendors preaggregate data in different ways. Reporting, to the contrary, can be at the lowest level of detail: Rather than sales by product, you might have individual line items for a particular order number.

4. Cross-dimensional calculations: With multiple dimensions come more complex calculations. In OLAP, you might want to analyze percentage contribution or market share. These analyses require subtotaling sales for a particular state then calculating percentage contribution for the total region, country, or world. Users may analyze this percentage market share by a number of other dimensions, such as actual vs. budget, this year vs. last year, or for a particular group of products. These calculations often must be performed in a particular order and involve input numbers that users might never see. Detailed reports, however, often rely on simple subtotals or calculations of values that are displayed on the report itself.

TABLE 1 The contrasting characteristics between reporting and OLAP.

We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
1 of 4
Comment  | 
Print  | 
More Insights
InformationWeek Is Getting an Upgrade!

Find out more about our plans to improve the look, functionality, and performance of the InformationWeek site in the coming months.

Pandemic Responses Make Room for More Data Opportunities
Jessica Davis, Senior Editor, Enterprise Apps,  5/4/2021
10 Things Your Artificial Intelligence Initiative Needs to Succeed
Lisa Morgan, Freelance Writer,  4/20/2021
Transformation, Disruption, and Gender Diversity in Tech
Joao-Pierre S. Ruth, Senior Writer,  5/6/2021
White Papers
Register for InformationWeek Newsletters
Current Issue
Planning Your Digital Transformation Roadmap
Download this report to learn about the latest technologies and best practices or ensuring a successful transition from outdated business transformation tactics.
Flash Poll