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?
• 1.2 How does OLAP work? 

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	1º
		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 1^st and 2^nd 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.