OLAP

  • OLAP (Online Analytical Processing) provides the support for the multidimensional view of the data.
  • It provides easy and efficient access to the various views of information to the users.
  • The complex queries are also processed by using OLAP. It is easy to analyze information by processing multidimensional views of the data.
  • The data warehouse is used to analyze the information, where the ample amount of historical data is stored.

OLAP Operations

OLAP techniques are applied to retrieve the information from the data warehouse in the form of OLAP multidimensional databases.

Multi-dimensional model has two types of tables:

1. Dimension tables: It contains the attributes of dimensions.
2. Fact tables: It consists of the facts or measures.
The following OLAP operations are performed to implement OLAP:

1. Roll up or consolidation
  • Multi-dimensional databases have hierarchies with respect to the dimensions.
  • Consolidation is rolling up or adding data relationship with respect to one or more dimensions.
  • This hierarchy can be as,  the total order street<city<State<country.
  • The roll up operation aggregates the data by city to country by location hierarchy as shown in the following diagram.
roll up

2. Drill Down
In drill down operation, the view is changed to a greater level of detail.
For example: In the diagram shown below, the drill operations are performed on the upper cube by stepping down a concept hierarchy for time. It can be defined as day<month<quarter<year.

drill down

3. Slice Operation
  • Slicing is referred to an ability to look at a database from various points of view.
  • Slice operation selects one dimension of the given cube and creates a sub-cube, as  illustrated in the following diagram.
slice operation

4. Dice operation
Two or more dimensions are selected from the given cube to produce a sub cube as shown in the following diagram.

dice operation

5. Pivot / Rotate
Pivot technique is used for visualization of data. It rotates the data axis to give  another presentation of the data, as shown in the following schematic.

pivot operation

Approaches to OLAP

The OLAP systems are categorized using the different techniques mentioned below:

1. MOLAP
In MOLAP, data is stored in a multidimensional cube(array storage) rather than the relational database.

2. ROLAP
  • ROLAP works with relational databases. The base data and the dimension table are stored as relational tables and new table are created to hold an aggregated information.
  • Each action of slicing and dicing operation is equivalent to adding a 'WHERE' clause in the SQL statement.
3. HOLAP
  • HOLAP has the combine advantages of MOLAP and ROLAP.
  • When detail information is required, the database is divided into relational database and specialized storage.
    • For example: The Microsoft SQL server 7.0 OLAP services support a hybrid OLAP server.
▲