OLAP (On-Line Analytical Processing) reporting systems provide what is commonly known as “slice-and-dice” functionality to non-technical end users. Users are able to see ad-hoc reports and charts to answer ad-hoc questions they may want answered. Another commonly used name is “drill-down reporting” on “OLAP cubes”. In essence this is similar to the Excel based Pivot reports, only that OLAP systems can do the same thing on massive amounts of data.

The OLAP Reporting revolves around two simple concepts: Dimensions, and Measures.

A Measure is any business metric that you want to calculate and report on. It can range from simple stuff like Dollar Sales (total sales revenue), Unit Sales (no of units for a particular product sold), Profits, Current Inventory Level, etc. to more complicated computed fields like Average Cost, Average Profit per Unit, % Margin, etc.

It should be noted that a Measure can have different values depending upon the context - whether you are looking at All Locations or a particular City, or whether you are looking at annual figures or weekly figures, or daily figures, whether you are looking at a particular demographic segment, etc. A measure’s value depends upon the specific subset of the entire database that you are looking at (hence the term “slice-and-dice” commonly used with “cubes”). The specific context for the calculation of Measures is provided by Dimensions.

Dimensions help subset the database to get to the specific information you are interested in. You may want to subset by Geography, Time, Product/Service Offering, Customer Demographics, etc. For example, I may want to analyze Unit Sales (Measure) for a particular product (specified on Product Dimension) in New York (specified on Geography Dimension) for the month of Jan 2006 (Time Dimension). I could then drill-down on Time Dimension to get the daily sales for the product.

OLAP tools provide this functionality through a user friendly GUI. The commercial OLAP systems are extremely expensive, but that would change with the advent of open-source OLAP tools.

An excellent open-source OLAP server is Mondrian that allows you to do OLAP cube reporting through a JSP web-based interface called JPivot.

The two together provide an excellent reporting system that can be quickly deployed on an existing datawarehouse to deliver impressive reports to end users.

In the next post we will set up a quick reporting system using Mondrian, JPivot, Tomcat and MySQL/MS-Access.