OLAP Operations for Data Warehouses

Multidimensional Modeling and Data Warehouse

• It is Part of the Business Intelligence course

Chapter 4: Data Warehouse Manipulation (OLAP Operations Language)

• The notes cover data warehouse manipulation using OLAP operations

Course Plan

• Introduction to SQL for OLAP operations
• Syntax of OLAP operations
• Exercises on OLAP operations

Introduction: SQL Use

• SQL is utilized as a processing method when the cube is stored in a Relational Database Management System (RDBMS)
• produit(GENCOD, nomP, Marque, #Nature, PrixAchat, PrixReventeConseille) defines product attributes like product code, name, brand, etc.
• vente (GENCOD, NMAG, Date, Qte, PrixVente) describes sales information including product code, store code, date, quantity, and sales price
• magasin (NMAG, Enseigne, Adresse, Ville, #Dept) outlines store details such as store code, name, address, city, and department
• nat2Cat(Nature, #Categorie) links product nature to category
• cat2Ray (Categorie, Rayonnage) associates category with shelf location
• dep2Reg(Dept, Region) connects department to region

Introduction: SQL Queries Examples

• To find the total sales amount by city and product
  • select ville, nomp, sum(qte*prixvente) from vente, produit, magasin where produit.GENCOD = vente.GENCOD and vente.NMAG = magasin.NMAG group by ville, produit
• To determine the total sales amount by region and category
  • select region, categorie, sum(qte*prixvente) from vente, produit, magasin, dep2reg, nat2cat where produit.GENCOD = vente.GENCOD and vente.NMAG = magasin.NMAG and produit.nature = nat2cat.nature and magasin.dept = dep2reg.dept group by region, categorie
• To calculate the total sales amount by region and category in 2000
  • select region, categorie, sum(qte*prixvente) from vente, produit, magasin, dep2reg, nat2cat where produit.GENCOD = vente.GENCOD and vente.NMAG = magasin.NMAG and produit.nature = nat2cat.nature and magasin.dept = dep2reg.dept and year(date) = 2000 group by region, categorie
• To get the total sales amount by region, category, and year
  • select region, categorie, semestre(date), sum(qte*prixvente) from vente, produit, magasin, dep2reg, nat2cat where produit.GENCOD = vente.GENCOD and vente.NMAG = magasin.NMAG and produit.nature = nat2cat.nature and magasin.dept = dep2reg.dept group by region, categorie, year(date)

Introduction: Limitations of SQL

• SQL is not well-suited for defining multidimensional queries, which can reduce performance
• OLTP operations can be defined using relational algebra (or algebraic language)

Introduction: OLAP Operations

• OLAP operations can be defined similarly to relational algebra (OLAP algebra) and then translated into SQL or MDX language queries

Relational Language Reminder (Algebraic)

• Projection: R2 = π R1 (Ai, Aj, ..., Am)
  • Projects a relation R1 onto attributes Ai, Aj, ..., Am
• Restriction: R2 = σR1 (condition)
  • Restricts a relation R1 based on a specified condition
• Join: R3 = R1 X R2 (condition)
  • Joins relation R1 with R2 based on a condition

OLAP Operations Syntax

• Typical Operations include Roll-up, Drill-down, Slice and dice, Pivot (rotate), and Drill-across
• There are other operations beyond the typical ones

OLAP Operations Syntax - Cube and Dimensions

• Cube: Sales (idC, idt, IdP, Quantity) represents sales data with customer, time, and product IDs, plus quantity
• Dimensions:
  • Customer (idC, country, city)
  • Time (idt, year, quarter, month, day)
  • Product (IdP, category)
• Cube = fact table

OLAP Operations Syntax: Roll-up

• ROLLUP (Cube Name, (Dimension -> Level)*, AggFunction(Measure)*)
  • AggFunction(Measure)*) generalizes data from detailed levels to summary levels along dimensions
  • For example, ROLLUP (Sales, Customer -> Country, SUM(Quantity)) aggregates sales quantity by country
• ROLLUP* expands upon the ROLLUP function
  • ROLLUP*(CubeName, [(Dimension → Level)], AggFunction(Measure))
  • For instance, ROLLUP*(Sales, Time -> year, SUM(Quantity)) cumulates sales quantity from month to trimester to year across time

Roll-up Operations Specifics

• If dimensions are not specified, all dimensions of the cube will be aggregated to all levels, producing a single cell containing the global sum of the measure
  • Total Sum of Quantity: ROLLUP*(Sales, SUM(Quantity)) yields a single cell with the total quantity
• When a cube has multiple measures, an aggregation function for each measure must be specified
  • ROLLUP (Sales, Customer -> Country, (SUM(Quantity), COUNT(CA))
• The result of a ROLLUP operation can be assigned to a new cube
  • Cube2 ← ROLLUP (Sales, Customer -> Country, SUM(Quantity))

OLAP Operations Syntax: Drill-Down

• Drill-Down operation
  • DRILLDOWN performs the inverse of roll-up, moving from a general level to a more detailed level in a hierarchy
  • The syntax is DRILLDOWN (CubeName, (Dimension -> Level)*).
  • For example: DRILLDOWN(Sales, Time -> Month)

OLAP Operations Syntax: Sort

• An Operation to sort the data
  • SORT (CubeName, Dimension, (Expression [ {ASC | DESC } ])*)
  • Example: SalesByQuarter← SORT (Sales, Product, category) sorts members of the Product dimension in ascending order by category name
  • The default sort order: ASC (ascending)
  • SORT (SalesByQuarter, Time, Quantity DESC) sorts members of the Time dimension in descending order of Quantity within the SalesByQuarter cube

OLAP Operations Syntax: Pivot (Rotate)

• Pivoting rotates cube axes for an alternative data presentation
  • Syntax: PIVOT (CubeName, (Dimension -> Axis)*)
  • Example: PIVOT (Sales, Time -> X, Customer -> Y, Product -> Z)

OLAP Operations Syntax: Slice

• Slicing selects a subset of the cube based on dimension values
  • Syntax: SLICE (CubeName, Dimension, Level = Value)
  • Example: SLICE (Sales, Customer, City = 'Paris')

OLAP Operations Syntax: Dice

• Dicing selects a sub-cube based on specified conditions
  • Applies boolean conditions on dimension levels, attributes, or measures
  • DICE(CubeName, Φ) where Φ is a boolean condition
  • DICE (Sales, (Customer.City = 'Paris' OR Customer.City = 'Lyon') AND (Time.Quarter = 'Q1' OR Time.Quarter = 'Q2'))

OLAP Operations Syntax: Rename

• Renaming renames schema elements or members
  • Syntax: RENAME(CubeName, ({SchemaElement | Member } -> NewName)*)
  • Example: RENAME (Sales, Sales -> Sales2022, Quantity -> Quantity2022 )

OLAP Operations Syntax: Drill-across

• It groups 2 cubes
  • The function regroups 2 cubes

OLAP Operations Syntax: Add Measure

• Adds or subtracts new Measures to the cube ADDMEASURE(CubeName, (NewMeasure = Expression, [AggFct])*) where AggFct is the aggregation function. SUM being the default ADDMEASURE( Sales2021-2022, PercentageChange = (Quantity2022-Quantity2021) / Quantity2021)

OLAP Operations Syntax: Drop Measure

• Adds or subtracts new Measures to the cube: DROPMEASURE(CubeName, Measure* ) Example: DROPMEASURE(Sales2021-2022, Quantity2022)

OLAP Operations Syntax: Max Function

• Returns Max values from the Cube
  • MAX(CubeName, Measure) [BY Dimension*]
  • Example: MAX (Sales, Quantity) BY Time, Customer

OLAP Operations Syntax: Rank

• The Rank function
  • RANK (Sales, Time) BY Category, City ORDER BY Quantity DESC

OLAP Operations Syntax: Difference

• Shows the Difference between two parameters
  • DIFFERENCE (Sales, TopTwoSales)

OLAP Exercises

• ROLLUP*(Sales, Time -> Quarter, SUM(Quantity))
• SLICE (Sales, Customer, City = 'Paris')
• DICE(Sales, (Customer.City = 'Paris' OR Customer.City = 'Lyon') AND (Time.Quarter = 'Q1' or Time.Quarter = 'Q2'))
• MAX (Sales, Quantity) BY Time, Customer
• MAX (Sales, Quantity, 2) BY Time, Customer
• TOPPERCENT (Sales, Quantity, 70) BY City, Category ORDER BY Quantity ASC

Exercise Schemas

• Conceptual cube schema with keys not shown for clarity

Example question to test knowledge of these OLAP concepts

• Total amount of sales, customer, order year, and product category. ROLLUP*(Sales, Customer → Customer, OrderDate → Year, Product → Category, SUM(SalesAmount))