Data Warehousing Architecture, Dimensional Modeling & ETL

Explanation

Dimensional modeling optimizes data representation for rapid analytical aggregation. In a Star Schema, a centralized Fact table (containing numeric additive measures like sales_revenue and quantity_sold) links via primary-foreign key relationships to denormalized Dimension tables (Date, Product, Store, Customer). In a Snowflake Schema, dimension tables are normalized to reduce redundancy, requiring more joins during querying. OLAP engines organize this data into multi-dimensional hypercubes enabling real-time drill-down from annual revenues to individual store sales.

Key points

• Architectural Methodologies: Inmon's Corporate Information Factory (top-down, normalized EDW) vs Kimball's Dimensional Data Warehouse (bottom-up, conformed data marts).
• Dimensional Modeling: Fact tables (metrics, foreign keys) surrounded by Dimension tables (descriptive attributes).
• Star Schema (denormalized dimensions, single join) vs Snowflake Schema (normalized dimensions, multiple joins).
• ETL Process: Extraction (capturing raw operational data), Transformation (cleansing, deduplication, schema conforming), Loading (inserting into warehouse tables).
• OLAP Cube Operations: Slice (single dimension slice), Dice (sub-cube selection), Roll-up (aggregating up hierarchy), Drill-down (navigating to lower granularities).

Example

Code examples

-- Star Schema Dimensional Model for Retail BI
CREATE TABLE Dim_Date (
    Date_Key INT PRIMARY KEY,
    Full_Date DATE,
    Month_Name VARCHAR(15),
    Quarter INT,
    Year INT
);

CREATE TABLE Dim_Product (
    Product_Key INT PRIMARY KEY,
    Product_Name VARCHAR(100),
    Category VARCHAR(50),
    Brand VARCHAR(50)
);

CREATE TABLE Fact_Sales (
    Sales_ID INT PRIMARY KEY,
    Date_Key INT REFERENCES Dim_Date(Date_Key),
    Product_Key INT REFERENCES Dim_Product(Product_Key),
    Units_Sold INT,
    Total_Revenue DECIMAL(12,2)
);

Exam questions

• [10 Marks] Define a Data Warehouse. Compare Star Schema and Snowflake Schema with neat architectural diagrams and SQL schemas.
• [8 Marks] Explain the stages of the ETL (Extract, Transform, Load) pipeline. Discuss the four fundamental OLAP operations (Roll-up, Drill-down, Slice, Dice).