OLAP is nothing but Online Analytical Processing. As we have discussed about OLAP and OLTP Detailed description is down below.
1.1. What is OLAP?
Online Analytical Processing Server basically works on the multidimensional data model. It performs multidimensional analysis of business data and sophisticated data modelling. The chief component of OLAP is the OLAP server which resides between a client and database management system. It allows higher authority to get an access of information through fast and consistent access to data.
1.2. Types of OLAP
There are four types of OLAP Servers:
Ø Relational OLAP
Ø Multidimensional OLAP
Ø Hybrid OLAP
Ø Specialized SQL servers
1.2.1. Relational OLAP
Relational OLAP is also known as ROLAP which is nothing but the OLAP server which maps multidimensional operations to standard relational operations. ROLAP servers are placed between relational back-end server and client front end tools.
ROLAP has following features:
ü Implementation of aggregation navigation logic
ü Optimization for each DBMS back end.
ü Additional tools and Services.
1.2.2. Multidimensional OLAP
Multidimensional OLAP is commonly known as MOLAP which utilizes the multidimensional operation. It uses array based multidimensional storage engines for multidimensional views on the data. Many MOLAP severs uses two levels of data storage representation to handle dense sparse data sets.
1.2.3. Hybrid OLAP
It is combination of both ROLAP and MOLAP.
It offers following features:
ü Higher Scalability of data of ROLAP
ü Faster computation of MOLAP.
It is commonly known as HOLAP.
1.2.4. Difference between ROLAP and MOLAP
· Data Retrieval is very slow.
· Uses relational table.
· Available space in data warehouse is enough for it.
· Data retrieval is fast.
· Uses sparse array to store data.
· Maintains a separate database for data cubes.
1.3. OLAP Operations
As discussed in above “what is OLAP“, it is based on multidimensional view of data.
Different types of OLAP operations are as follows:
Ø Roll – up
Ø Drill – Down
Ø Slice – dice
Ø Pivot (rotate)
1.3.1. Roll – Up
Roll – up performs aggregation on data cube in any of the following ways:
Ø Dimension Reduction: In this one dimension is reduced that is hierarchical order is reduced or rolled up.
Ø Or by increasing dimensions by hierarchical order.
The following diagram illustrates roll – up:
Rollup is always performed on hierarchy. For example here in above example locations are in “cities” and are rolled up to “countries”. Aggregations performed on cities that sum of the items of cities.
1.3.2. Drill – Down
Drill – Down is the reverse operation of roll – up because here we go from higher form to lower form.
The concept of Hierarchy was “Day > Month > Quarter > Year”, and here same is happening as quarter have been divided into months that is nothing but reverse hierarchy. So the aggregate function has been divided according to months.
1.3.3. Slice and Dice
Slice is an action performed when you want one slice of information. For example we have data cube where time is in quarters is a dimension and cities as one dimension and items as another dimension. So if want data cube consisting of items and locations in quarter 1.
Here Slicing has been done for time Q1 for all items and locations.
Dice is another sort of action performed when we want a particular block of data it can more than two dimensions. For example if we require small data cube which has condition that time dimensions to be Q1 and Q2, Locations as Toronto and Vancouver and moreover items to be Mobile and Modem
It is a kind of Sub Data Cube. Sub Data Cube derived from a data cube.
Pivot means rotation of the table. So data inside data cube will be rotated along the rotational axes just to provide alternate presentation of data.