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
|
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 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.
