- 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 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
2. Drill Down
- 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.
In drill down operation, the view is changed to a greater level of detail.
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.
3. Slice Operation
4. Dice 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.
Two or more dimensions are selected from the given cube to produce a sub cube as shown in the following diagram.
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.
Approaches to OLAP
The OLAP systems are categorized using the different techniques mentioned below:
In MOLAP, data is stored in a multidimensional cube(array storage) rather than the relational database.
- 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.
- 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.