Gleaning the Cube

Large-scale OLAP products can take two distinct approaches to multidimensional data analysis. One, called relational OLAP (ROLAP), serves as a kind of middleware between client facilities and data stored in traditional relational databases. These products use SQL-based methods to access data and then convert the results into a multidimensional format at run time.

ROLAP proponents argue that this approach eliminates the need to store large amounts of data redundantly. They add that relational databases are the only way to store very large amo unts of data (i.e., hundreds of gigabytes) and maintain acceptable retrieval performance.

The other approach is to store the data in a multidimensional structure, sometimes referred to as a physical cube. Richard Finkelstein, an authority on SQL databases and performance benchmarks, argues that while relational databases are well suited for OLAP, data analysis is a different process that's better served by a completely different data structure.

Some products (including Oracle Express Server) take a hybrid approach, combining storage in a multidimensional database with access to relational data. Usually this entails storing summary data in the multidimensional database, with the ability to drill down to the detailed data stored in the relational database. Storing summary data in multidimensional form reduces the need to perform run-time calculations every time an analysis is done.

Dimensions, Measures, and Hierarchies

Picture a spreadsheet whose rows represent products and columns represent months; these constitute two dimensions. You can stack additional sheets (one for each sales office, s ay) that also have product and month rows. "Sales offices" thus becomes the third dimension.

While it's hard to visualize more than three dimensions, it's simple for a multidimensional database to have many more, representing, for example, different scenarios (e.g., budget versus actual) or customer categories. The data item to be measured across these dimensions is called a measure. In the spreadsheet example , sales is a measure. A multidimensional database can have multiple measures. For example, you might track both monetary income and units sold across the various dimensions.

If a dimension consists of units that can be broken down into smaller units or grouped into larger ones, the relationship between unit levels is known as a hierarchy. Typical hierarchies are years broken down into quarters and months, or sales offices grouped into regions.

Many OLAP applications assign a single unit at the top hierarchical level of each dimension to represent the total for the entire dimension. Analysis tools then allow you to drill down into greater detail -- for example, beginning with sales for the entire company, then by region, then by sales offices, and, finally, by individual salespersons.

---

Crossing Dimensions

illustration_link (20 Kbytes)

Data cubes let you "slice and dice" your data from many different perspectives.