Dimensional Data Modeling Steps – Northwind Data Warehouse
1. Understand Business Requirements
Identify business users (Sales, Finance, Operations).
Define key business questions (sales performance, customer behavior, product demand).
Finalize reporting needs and KPIs.
2. Identify Business Processes
Typical Northwind business processes:
Sales Orders
Product Sales
Shipping & Logistics
Customer Transactions
Supplier Procurement
3. Declare the Grain (Most Important Step)
Decide exactly what a single row in each fact table represents
Example:
One row per Order Line per Product per Day
This decision controls:
Level of detail
Storage size
Reporting flexibility
4. Identify Dimensions
Common dimensions from Northwind:
Date
Customer
Product
Employee
Supplier
Shipper
Category
Geography (Country, City, Region)
5. Identify Facts (Measures)
Numeric, additive values:
Sales Amount
Quantity Sold
Discount Amount
Freight Cost
Order Count
6. Design Star Schema
Fact table in the center
Dimension tables around it
Avoid snowflaking (keep dimensions denormalized)
7. Define Surrogate Keys
Create surrogate keys for all dimensions
Maintain Slowly Changing Dimensions (SCD Type 1 / Type 2 where required)
8. Design ETL Process
Extract data from Northwind OLTP
Clean, transform, and conform data
Load dimensions first, then facts
9. Validate with Sample Reports
Ensure the model supports all required reports
Check aggregations, filters, and drill-downs
10. Deploy & Optimize
Index fact tables
Partition by Date
Enable BI tools (Power BI / Tableau / QuickSight)
B. Core Star Schema (Northwind)
Fact Table
Fact_Sales
| Column | Description |
|---|---|
| Date_Key | |
| Product_Key | |
| Customer_Key | |
| Employee_Key | |
| Shipper_Key | |
| Quantity | |
| Unit_Price | |
| Discount | |
| Sales_Amount | |
| Freight |
Dimension Tables
Dim_Date
Dim_Product
Dim_Customer
Dim_Employee
Dim_Shipper
Dim_Supplier
Dim_Category
C. Five Business Reports with Grain & Granularity
1. Sales Performance Report
Business Question:
How much revenue is generated over time?
Fact Table: Fact_Sales
Dimensions: Date, Product, Category
Grain (Granule):
One row per Product per Day
Granularity:
Daily → Monthly → Yearly
Measures:
Total Sales Amount
Total Quantity Sold
2. Customer Purchase Behavior Report
Business Question:
Which customers generate the most revenue?
Fact Table: Fact_Sales
Dimensions: Customer, Date, Geography
Grain: One row per Customer per Order per Day
Granularity:
Order level → Monthly → Yearly
Measures:
Sales Amount
Order Count
3. Product Performance Report
Business Question:
Which products are best and worst sellers?
Fact Table: Fact_Sales
Dimensions: Product, Category, Supplier
Grain: One row per Product per Order Line
Granularity:
Order Line → Product → Category
Measures:
Quantity Sold
Revenue
Discount Amount
4. Employee Sales Contribution Report
Business Question:
How well are employees performing in sales?
Fact Table: Fact_Sales
Dimensions: Employee, Date, Region
Grain: One row per Employee per Order
Granularity:
Order → Month → Year
Measures:
Total Sales
Orders Handled
5. Shipping & Logistics Cost Report
Business Question:
What is the shipping cost trend by shipper?
Fact Table: Fact_Sales
Dimensions: Shipper, Date, Geography
Grain: One row per Order per Shipper per Day
Granularity:
Daily → Monthly → Yearly
Measures:
Freight Cost
Number of Shipments
D. Summary Table
| Report | Grain | Granularity |
|---|---|---|
| Sales Performance | Product per Day | Day → Month → Year |
| Customer Behavior | Customer per Order | Order → Month |
| Product Performance | Product per Order Line | Line → Product |
| Employee Performance | Employee per Order | Order → Year |
| Shipping Cost | Order per Shipper | Day → Year |
No comments:
Post a Comment