Saturday, November 3, 2012

Building a Reporting Tool on Top of Analysis Services: Understanding OLAP


In order to effectively build a reporting tool, one first needs to understand On-Lyne Analytical Processing (OLAP).


1.1  What is OLAP?

OLAP can be viewed as a method of aggregating data in a way that facilitates the construction and interpretation of queries on multidimensional databases. OLAP tools give business analysts with reduced knowledge of query languages the ability to explore complex databases because it structures the data according to dimensions, hierarchies, levels and measures that are easily transposed to the nature of the business they are analyzing. Instead of dealing with complex notions like foreign keys, indexes and primary keys, the user intercepts dimensions like “Product” and “Time” and drills down through levels according to the desired degree of detail.

1.2  How does OLAP work?

OLAP relies on multidimensional cubes to aggregate data and the queries are defined considering the following principles:
·         Dimensions: Several defined areas that make sense to group the data. For example, it makes sense to aggregate all the months, weeks and days under the dimension TIME.
·         Hierarchies: How dimensions are structured. A dimension can have several hierarchies because the same data can be organized according to different rules.
·         Levels: Levels are like a zoom in and out tolls, they help the user to drill down and up in detail.
·         Measures: Measures, although a component of OLAP, are a specific dimension of the OLAP-Cubes that consists of the output the user wants to know, usually a numerical indicator.
Let’s consider the following example in order to understand each of the principles explained:
“A business analyst, working for a car dealer, wants to know the net sales and the gross sales in Germany during the first quarter of the current year.”
For this he needs to use the Sales OLAP cube that, for this purpose, has the following structure:

Dimensions
Hierarchies
Levels
Members (Examples)
Operational
Country
Country
Germany
Dealer
Make
Mazda
Stand
Major MAZDA, Inc
Time
Calendar/Standard Time
Year
2012
Quarter
Month
March
Day
3rd
Week Time
Year
2011
Week
25
Day
Monday
Measures
Measures
Measures Level
Net Sales; Gross Sales

 

 

In the example depicted the user could use the shaded blue parameters in order to query the OLAP cube. This is not the unique configuration to get this information, for example, to limit the sales to the first semester, instead of choosing the 1st and 2nd quarter, one could use the Month hierarchy, by drilling down from the Quarter hierarchy, and then select from January to June.
Organizations usually work with various cubes like stocks, prices, people, etc, but what is important to understand is how dimensions, hierarchies and levels work together to deliver the measures you are looking for on your reports.
OLAP is considered by Lloyd (2011) as one of the 4 components of Business Intelligence and this technology is usually present in the various BI solutions available in the market. One of them is the SQL Server from Microsoft that guarantees OLAP through the Analysis Services. There are various ways to build a reporting tool on top of this OLAP solution, especially through the use of Excel. This will be the theme of the next post where the reader will finally be able to know everything he needs to decide how he must build his reporting tool.