This is an excerpt from Pierre Leclerc (


OLAP (On Line Analytical Processing):
An OLAP program allows a user to access, extract, organize and analyze data on line. The great new thing in OLAP is the Data Cube. What is a DATA CUBE? 

Data Cubes

A database holds records (rows of data) as they were entered by users (ex: cash register,  manual entry of data from a sheet of paper) or by machines (electronic counter on a conveyer) or by a user with a machine (bar code reader). Very few people look at the raw data except to make corrections. Most data consumers want to look at sub-totals. A DATA CUBE is a database of sub-totals. To do so, there are members of dimensions that are hierarchized.

A dimension is a
collection of data of the same type, allowing the construction of a DATA CUBE. Time, Location, Product, Quantities and Amounts are classic dimensions. I am not showing you what it looks like in a data cube because a data cube is an entity that transcends the human intellect. In a regular database, it would look like this:








Dress 9087




New York

Pants 6888





Blouse 3543




Los Angeles

Shirt 67654





Tie 45355



Imagine that you have millions of such records, the analysts need sub-totals (quantities and/or amounts) by date, by location, by product. That is what is done in a data cube. You end up with a database of sub-totals.

An OLAP application is not a database in which you enter data, it is an application that reads one or more databases, combines the data and generates sub-totals

After "dimension" and to finish this presentation, I need to impose on you two more terms: members and hierarchy.

A member is an element of a dimension. ex.: the "dress 9087" is a member of the dimension "Product", "Chicago" is a member of the dimension "Location", "01/06/2003" is a member of the dimension "Time", etc.

In each dimension, members can be organized based on a hierarchy. For example, a subtotal for "Ladies Garments" can be created including figures for "Dress 9087", "Blouse 3543", . In this case, the member "Dress 9087" becomes a child of "Ladies Garments" and hence "Ladies Garments" would be a parent of "Dress 9087". "Chicago" could be a child of a sub-total "USA". You can create as many levels of hierarchy that you like to analyse the data by country, by year, by types of garments, etc. These new big members do not show in the original database, you create them in the data cube. This possibility or grouping the members allow new analysis of the data and can lead to very valuable business decisions.

OLAP applications (EssBase, Cognos, Business Object, OLAP on SQL Server, etc.) organize the data for the analysts and the report designers. Most of the sub-totaling work is done on the main server making the individual analysis and reports less complex and less computer time hungry on the desktop stations.

The data sits in a data warehouse (centralized database like Oracle, Sybase, SQL server, Access) or in many different databases. The OLAP application can either extract the data from the database or can read text files downloaded from the database. It then creates the data cubes overnight on a server accessible to analysts and report designers. The data cubes can be immense or small, can serve corporate or departmental needs.

Reporting, Analyzing and Charting

Once the data is organized, one has to design analysis and reports. To this end, Excel is the best tool. With Excel and the add-in created by Essbase, you will be creating a reporting framework that is convivial to users. I have worked downstream form Essbase and I consider this "team" (Excel/Essbase) to be the best tool for data analysts, report designers and users who need to issue reports daily, weekly and yearly.

Essbase is not cheap and remember that you might have to hire a team of experts to maintain the data cubes. If you already have a single database you might not need to resort to a data warehouse or an OLAP application.


left arrow Back home