EXPERIMENT NO 4
AIM: Implementation of OLAP operations: i) Roll Up, ii) Drill Down, iii) Slice, iv) Dice, v) Pivot.
Theory:
OLAP:
OLAP (Online Analytical Processing) in data warehousing enables quick, multidimensional analysis of
large datasets. It allows users to perform complex queries, analyze trends, and extract insights by
viewing data across various dimensions, like time, geography, or product. OLAP tools support
operations such as slicing, dicing, and pivoting for detailed examination. In data mining, patterns,
correlations, or trends are identified from large datasets using techniques like clustering, classification,
or association. Both OLAP and data mining help businesses make data-driven decisions by revealing
insights from stored data.
OLAP Operations:
Roll-up Operation:
The Roll-up operation in OLAP aggregates data by climbing up a hierarchy or reducing
dimensions. It moves from detailed data to summarized data, such as going from daily sales to
monthly sales. Roll-up can be performed by either grouping data by higher-level dimensions
or removing one or more dimensions.
Syntax: SELECT dimension_1, dimension_2, SUM(measure) FROM table_name GROUP BY
dimension_1, dimension_2 WITH ROLLUP;
Drill-down Operation:
Drill-down in OLAP is an operation that allows users to navigate from a summary view to more
detailed data by moving down a data hierarchy. For example, from "Year" to "Month" to "Day."
It helps in gaining deeper insights by exploring finer data granularity.
Syntax: Select Dimension1, Dimension2, Sum(measure)/Count(*) OVER (PARTITION BY
dimension1, dimension2) FROM table_name JOIN table_name GROUP BY
dimension1,dimension2;
Slice Operation:
In OLAP, the slice operation selects a single dimension from a multidimensional cube, creating
a new sub-cube by fixing a value for that dimension. This reduces the cube's dimensionality
and allows focused analysis. For example, if a cube has dimensions for Time, Location, and
Product, applying a slice on Time (e.g., "Time = 2023") will display data for 2023 across all
other dimensions.
Syntax: SELECT measures FROM cube WHERE dimension = value;
Dice Operation:
The dice operation in OLAP allows users to focus on a specific subset of data by selecting
multiple dimensions and applying conditions on them. It refines the data cube to show only
the relevant slice based on multiple dimension values.
Syntax: SELECT [measure] FROM [cube] WHERE [dimension1 condition] AND [dimension2
condition] AND [dimension3 condition];
Pivot Operation:
The pivot operation in OLAP rotates data dimensions to view it from different perspectives,
swapping rows and columns. For example, if sales data is shown by region and year, pivoting
could switch the view to display year by product instead. This allows users to analyze
relationships and trends across various dimensions.
Syntax: Select [dimension1] ,SUM(CASE WHEN [condition1] THEN [measure] END), SUM(CASE
WHEN [condition2] THEN [measure] END),….. SUM(CASE WHEN [condition_n] THEN
[measure] END) FROM (Select dimension1, dimension2 FROM table_name) GROUP BY
[dimension];
OLAP OPERATIONS SYNTAX AND OUTPUT:
ROLL-UP OPERATION:
SYNTAX:
SELECT port.port_location, SUM(fact_port.revenue) AS total_revenue FROM fact_port JOIN
port ON fact_port.port_key = port.port_key GROUP BY port.port_location WITH rollup;
DRILL-DOWN OPERATION:
SYNTAX:
SELECT port_key,
Extract (year from
time_key) as year,
sum(revenue) as
total_revenue from
fact_port group by
port_key,
year order by port_key;
SLICE OPERATION:
SYNTAX:
SELECT port_key, date(time_key) as date, revenue FROM fact_port WHERE port_key in(102);
DICE OPERATION
SYNTAX:
SELECT date(time_key) as date, revenue FROM fact_port_key=1005 AND EXTRACT(YEAR
FROM time_key)=2022 AND EXTRACT(MONTH_KEY)=8 AND port_key=102;
PIVOT OPERATION:
SYNTAX:
SELECT container_key, sum(case when month=6 then revenue else 0 end) as June, sum(case
when month=9 then revenue else 0 end) as September, sum(case when month=10 then
revenue else 0 end) as October, sum(case when month=12 then revenue else 0 end) as
December from(select container_key from container, month from time_key) as month,
revenue from fact_port where container_key in(1,5));
Conclusion: Thus, we have successfully implemented all the OLAP operation.
