OLAP's cube is crumbling around the edges

Business intelligence is essentially a set of best practices for building models to answer business questions. However, today's BI best practices may be suboptimal for many enterprises' decision-support requirements.

For most users, BI is a journey that's been modeled and mapped out in advance by others, following a well-marked path through vast data sets. Data models, which must often be pre-built by specialists, generate or shape the design of such key BI artifacts as queries, reports and dashboards. Essentially, every BI application is some data modeler's prediction of the types of questions that users will want to ask of the underlying data marts. Sometimes, those predictions are little more than an educated guess -- and are not always on the mark.

BI's most ubiquitous data-modeling approach is the online analytical processing (OLAP) data structure known as a "cube." The OLAP cube -- essentially a denormalized relational database -- sits at the heart of most BI data marts. OLAP cubes, usually implemented as multidimensional "star" or "snowflake" schemas, allow large recordsets to be quickly and efficiently summarized, sorted, queried and analyzed.

To continue reading, register here to become an Insider. You'll get free access to premium content from CIO, Computerworld, CSO, InfoWorld, and Network World. See more Insider content or sign in.

Business intelligence is essentially a set of best practices for building models to answer business questions. However, today's BI best practices may be suboptimal for many enterprises' decision-support requirements.

For most users, BI is a journey that's been modeled and mapped out in advance by others, following a well-marked path through vast data sets. Data models, which must often be pre-built by specialists, generate or shape the design of such key BI artifacts as queries, reports and dashboards. Essentially, every BI application is some data modeler's prediction of the types of questions that users will want to ask of the underlying data marts. Sometimes, those predictions are little more than an educated guess -- and are not always on the mark.

BI's most ubiquitous data-modeling approach is the online analytical processing (OLAP) data structure known as a "cube." The OLAP cube -- essentially a denormalized relational database -- sits at the heart of most BI data marts. OLAP cubes, usually implemented as multidimensional "star" or "snowflake" schemas, allow large recordsets to be quickly and efficiently summarized, sorted, queried and analyzed.

However, no matter how well designed the dimensional data models within any particular cube, users eventually outgrow these constraints and demand the ability to drill down, up and across tabular record sets in ways not built into the underlying data structures.

The chief disadvantage of multidimensional OLAP cubes is their inflexibility. Cubes are built by pre-joining relational data tables into fixed, subject-specific structures. One way of getting around these constraints is the approach known as relational OLAP, which retains the underlying normalized relational storage approach while speeding multidimensional query access through "projections." However, relational OLAP also suffers from the need for explicit, upfront modeling of relationships within and among the underlying tabular data structures.

From the average user's point of view, all of this is mere plumbing -- invisible and boring -- until it prevents them from obtaining the new query tools, structured reports and dashboards needed to do their jobs. One unfortunate consequence of OLAP cubes' inflexibility is that requests for new BI applications inevitably wind up in a backlog of IT projects that can take weeks or months to deliver.

What might seem a trivial thing to the user -- such as adding a new field or new calculation to an existing report -- might represent a time-consuming technical exercise for the data modeling professional. Behind the scenes, this simple decision-support request might, beyond the front-end BI tweaks, also require remodeling of the data mart's OLAP star schema, re-indexing of the data warehouse, revision of extract transform load (ETL) scripts, and retrieval of data from different transactional applications.

No one expects the OLAP cube to vanish completely from the BI landscape, but its role in many decision-support environments has been declining over the past several years. Increasingly, vendors are emphasizing new approaches that, when examined in a broader context, appear to be loosening OLAP's hold on mainstream BI and data warehousing. The emerging paradigm for ad-hoc, flexible, multi-dimensional, user-driven decision support includes the following important approaches: