OLAP Operations in Multidimensional Data Model

Why OLAP Operations Are Used

OLAP (Online Analytical Processing) operations are used to analyze multidimensional data stored in a data warehouse. The key reasons for using OLAP operations are:

• To view and analyze data from multiple perspectives (dimensions)
• To generate summaries and aggregations at different levels of detail
• To help managers and analysts make better business decisions
• To allow interactive exploration of data by drilling into details or rolling up to summaries
• To support fast query processing on large volumes of historical data
• To enable users to slice, filter, and pivot data without writing complex SQL queries

A multidimensional data model is organized around a central theme (e.g., Sales) represented by a fact table containing numerical measures (e.g., sales amount, units sold), and dimension tables (e.g., Time, Location, Product).

OLAP Operations

1. Roll-Up (Consolidation / Aggregation)

Definition: Roll-up generates a summary by moving from a lower level to a higher level in a concept hierarchy, or by reducing dimensions.

Two ways to perform Roll-up:

• Climbing up a concept hierarchy
• Reducing the number of dimensions

Concept Hierarchy Example (Time):

Daily --> Weekly --> Monthly --> Quarterly --> Yearly

Example:

Suppose we have sales data at the city level (Kathmandu, Pokhara, Butwal). A roll-up on the Location dimension climbs from cities to countries:

After Roll-up (City --> Country):

The data is aggregated (summed) at the country level, reducing detail.

2. Drill-Down (Reverse of Roll-Up)

Definition: Drill-down is the reverse of roll-up. It navigates from a higher level summary to a lower level detail, either by stepping down a concept hierarchy or by introducing additional dimensions.

Example:

Starting from yearly sales data, a drill-down on the Time dimension moves from year to quarter:

After Drill-down (Year --> Quarter):

This gives more detailed information for analysis.

3. Slice

Definition: Slice performs a selection on one dimension of the data cube, resulting in a sub-cube. It fixes one dimension to a single value and returns the remaining dimensions.

Example:

Consider a 3D cube with dimensions: Time, Location, Product.

Slicing on Time = "Q1 2023" gives a 2D sub-cube showing sales for all locations and all products only for Q1 2023:

One dimension (Time) is fixed; the rest are free.

4. Dice

Definition: Dice performs a selection on two or more dimensions of the data cube, resulting in a smaller sub-cube. It is like applying multiple conditions simultaneously.

Example:

From the same 3D cube (Time, Location, Product), dicing with:

• Time = "Q1 2023" OR "Q2 2023"
• Location = "Kathmandu" OR "Pokhara"
• Product = "Laptop" OR "Phone"

This produces a smaller sub-cube containing only the combinations that satisfy all three conditions, rather than a single slice.

5. Pivot (Rotate)

Definition: Pivot (also called rotate) rotates the data axes to provide an alternative presentation of the data. It changes the orientation of the data cube to view it from a different angle without changing the data itself.

Example:

Original view (rows = Product, columns = Location):

After Pivot (rows = Location, columns = Product):

The data is the same but the perspective has changed, making it easier to analyze from a different angle.

Summary Table

Hierarchical Clustering: Two Categories and Agglomerative Example

Step 1 - Extract (Given Data)

Data points to cluster:

• P1 = (2, 10)
• P2 = (2, 5)
• P3 = (8, 4)
• P4 = (5, 8)
• P5 = (7, 5)
• P6 = (6, 4)

Target: 2 clusters. Linkage method not specified; I will use single linkage (minimum distance) as is standard for this textbook problem.

Step 2 - Solve

Two Categories of Hierarchical Clustering

1. Agglomerative (Bottom-Up): Each object starts as its own cluster. At each step the two closest clusters are merged, continuing until all objects form one cluster (or the desired number is reached).

2. Divisive (Top-Down): All objects start in a single cluster, which is repeatedly split into smaller clusters until each object is alone (or the desired number is reached).

Distance Formula (Euclidean)

$$d(P_i, P_j) = \sqrt{(x_2 - x_1)^2 + (y_2 - y_1)^2}$$

Initial Distance Matrix

Sample checks:

• $d(P5,P6)=\sqrt{1^2+1^2}=1.41$ (minimum)
• $d(P3,P6)=\sqrt{2^2+0^2}=2.00$
• $d(P3,P5)=\sqrt{1^2+1^2}=2.24$

Step A: Merge closest pair

Minimum = 1.41 (P5, P6). Merge → C1 = {P5, P6}

Clusters: {P1}, {P2}, {P3}, {P4}, {P5,P6}

Step B: Recompute (single linkage)

• $d({P5,P6},P1)=\min(9.06,9.22)=9.06$
• $d({P5,P6},P2)=\min(5.00,4.12)=4.12$
• $d({P5,P6},P3)=\min(2.24,2.00)=2.00$ ← minimum overall
• $d({P5,P6},P4)=\min(3.61,5.00)=3.61$

Merge P3 → C1 = {P3, P5, P6}

Clusters: {P1}, {P2}, {P4}, {P3,P5,P6}

Step C: Recompute

• $d({P3,P5,P6},P1)=\min(9.22,9.06,9.22)=9.06$
• $d({P3,P5,P6},P2)=\min(6.08,5.00,4.12)=4.12$
• $d({P3,P5,P6},P4)=\min(5.83,3.61,5.00)=3.61$ ← minimum overall

(Others: $d(P1,P2)=5.00,\ d(P1,P4)=5.39,\ d(P2,P4)=4.24$)

Merge P4 → C1 = {P3, P4, P5, P6}

Clusters: {P1}, {P2}, {P3,P4,P5,P6}

Step D: Recompute

• $d({P3,P4,P5,P6},P1)=\min(9.22,5.39,9.06,9.22)=5.39$
• $d({P3,P4,P5,P6},P2)=\min(6.08,4.24,5.00,4.12)=4.12$
• $d(P1,P2)=5.00$

Minimum = 4.12 (between big cluster and P2). Merge P2 → C1 = {P2, P3, P4, P5, P6}

Clusters: {P1}, {P2,P3,P4,P5,P6}

Final Result (2 clusters)

We stop when exactly two clusters remain:

$$\boxed{\text{Cluster 1} = {P1} = {(2,10)}}$$ $$\boxed{\text{Cluster 2} = {P2,P3,P4,P5,P6} = {(2,5),(8,4),(5,8),(7,5),(6,4)}}$$

Point P1 = (2,10) is the outlier that separates last, forming its own cluster; all other points group together.