OLAP

1. Intro

Online Analytical processing (OLAP) allows rapid ad hoc analysis of large datasets via a drag and drop programming. OLAP has two main components, a back end which stores multi-dimensional data and a front end which allows users to drill through the data. A pivot chart is an example of a front end.  The data can also be accessed via MDX, a multi-dimensional query language rooted in SQL. Microsoft, Cognos and Hyperion are the leaders in this market.  An OLAP cube can be considered a data warehouse.OLAP cubes can have two to many dimensions.  The maximum number of dimensions if determined software used but I seldom see a cube with more than 6 dimensions. The diagram below is of a 3 dimensional OLAP cube.

2. For Modeling

OLAP cubes allow users to quickly run complex queries using a graphical interface.  They do not find relationships in the data the user must drill through the data to find those relationships.  A good use for OLAP Cubes is verifying heuristic rules uncovered by other techniques.

3. Example

Table: Customer

Fields : Name, State, Age, Gender, Total Purchases, Total Expenditures, Total Fiction, Total Nonfiction
Dimensions

A dimension is any field of collection of fields you may want to group by. In the above example good candidates for dimensions are State, Age and Gender.

Facts

Fields you wish to report on are considered facts. In the above example good candidates for dimensions are Total Nonfiction, Total Fiction, Total Purchases and Total Expenditure.  Some field may be considered both facts and dimensions.  If a common question is, “what is the profile of are biggest customers?” then purchases and Expenditure should be added as Dimensions and State, Age and Gender added as Facts.   In practice most if not all Dimensions will also be included as Facts.  Notice Name is excluded as both a Dimension and Fact.  Name is the lowest level of the data so should not be included unless required.