OLAP IN DATAWAREHOUSING
OLAP (Online Analytical Processing) is the key concept in data warehousing. It refers to a set of
techniques used for retrieving, analyzing, and processing data in a multidimensional way.
A data warehouse would exact information from multiple data sources and formats like textiles, excel
sheet, multimedia files.
The extracted data is cleaned and transformed
Key points in OLAP in data warehousing
1. Multidimensional data
In data warehousing, data is typically organized into a multidimensional model. This
means data is stored in a way that allows for easy and efficient analysis across multiple
dimensions or attributes for example sales data can be analyzed by time, product, location etc.
2. Operations
Online analytical processing provides various operations for data analysis, including roll-up
(Aggregating data from a lower level of granularity to a higher level), drill-down (opposite of roll-
up), slice and slice (selecting a subset of the data), and pivot (changing the orientation of the
cube).
3. Cubes.
Online analytical processing systems often use data cubes, which are multi-dimensional
structures that store data in a format that’s optimized for analytical queries. Each cell in the
cube represents a data point at intersection of different dimensions.
ONLINE ANALYTICAL PROCESSING FUNCTIONS IN DATA WAREHOUSE
Has intuitive easy to use interface
Online analytical processing supports complex calculations
Online analytical processing provides data view in multidimensional manner
Online analytical processing has time intelligence
BASIC ANALYTICAL OPERATIONS OF OLAP
Since OLAP servers are based on multidimensional view of data ,
Roll-up
Drill-down
Slice and dice
Pivot (rotate)
ROLL-UP
This is known as consolidation or aggregation. The roll-up operation can be performed in two ways.
1. Reducing dimensions
2. Climbing up concept hierarchy. Concept hierarchy is a system of grouping things based on their
order or level
2) Drill-down
In drill-down data is fragmented into smaller parts.it is the opposite of the rollup process. It can be
done via
Moving down the concept hierarchy
Increasing a dimension
3) Slice
One dimension is selected, and a new sub cube is created.
Dimension time is sliced
A new cube is created altogether
4) Dice
This operation is similar to a slice .The difference in dice is you select 2or more3 dimensions that result
in the sub cube
Data engineers use dice operation to create similar sub cube from an OLAP cube. They determine the
required dimensions and build a smaller cube from the original hypercube
5) Pivot
This known as rotation. In pivot, you rotate the data axes to provide a substitute presentation of data.
Forex ample, a three dimensional OLAP cube has the following on the respective axes
X-axis product
Y-axis location
Z-axis time
Upon a pivot, the OLAP cube has the following
X-axis location
Y-axis time
Z-axis product
TYTPES OF ONLINE ANALYTICAL PROCESSING IN DATA WAREHOUSING
OLAP hierarchical Structure
There are following three major OLAP models in data warehouse:
1. Relational Online Analytical Processing (ROLAP)
The kind of system where users query data from a relational database or from their own local
tables’ .thus, the number of the potential questions is not limited.
It includes
Implementation of aggregation navigation logic
Optimization of each DBMS
Additional tools and services
2. Multidimensional Online Analytical Processing (MOLAP)
MOLAP involves creating a data cube that represents multidimensional data warehouse
It provides high speed of calculations
3. Hybrid Online Analytical Processing (HOLAP)
It combines MOLAP and ROLAP to provide the best of both architectures .pre computed aggregates
and cube structure stored in multidimensional database.
HOLAP allows data engineers to quickly retrieve analytical results from a data cube and extract
detailed information from relational databases.
Benefits of OLAP in data warehouse
Online Analytical Processing in data warehouse allows users to perform complex, ad-hoc queries for
business intelligence and reporting purposes
It enables faster query performance compared to traditional databases, making it well-suited for
analytical workloads.
It plays a vital role in helping businesses make informed decisions by providing a flexible and efficient
way to analyze their data from various perspectives
