Enterprise Data Model (EDM) Steps
Data Warehouse Design for Northwind
(With Granule & Granularity)
1. Define Business Objective
Goal
Provide consistent enterprise-wide reporting for:
Sales
Products
Customers
Employees
Logistics
EDM Focus
Single version of truth across all reports.
2. Identify Business Processes
(Kimball + EDM aligned)
| Process | Description |
|---|---|
| Order Processing | Customer orders and order lines |
| Sales Analysis | Revenue & quantity |
| Product Analysis | Product & category trends |
| Customer Analysis | Buying behavior |
| Shipping Analysis | Delivery performance |
3. Define Enterprise Data Domains (EDM)
| Domain | Entities |
|---|---|
| Sales | Orders, Order Details |
| Customer | Customers |
| Product | Products, Categories, Suppliers |
| Employee | Employees |
| Time | Date |
| Logistics | Shippers |
4. Conceptual Enterprise Data Model (High-Level)
Core Entities
Customer
Order
Order Line
Product
Employee
Shipper
Date
Key Relationships
Customer → Order (1:M)
Order → Order Line (1:M)
Product → Order Line (1:M)
At EDM level, no granularity yet—only business meaning.
5. Identify the Fact & Define the Granule (MOST IMPORTANT STEP)
Fact Table: FactSales
Granule (Grain)
One record per product per order per day
This means:
Each row = 1 product line item in an order
Lowest level of detail available from source
Granularity
Daily
Order Line level
Transactional
Supports all summary and detailed reports Prevents double counting
6. Logical EDM → Dimensional Model
Fact Table
FactSales
| Attribute | Description |
|---|---|
| OrderID | Degenerate dimension |
| DateKey | Order date |
| CustomerKey | FK |
| ProductKey | FK |
| EmployeeKey | FK |
| ShipperKey | FK |
| Quantity | Units sold |
| UnitPrice | Price |
| Discount | Discount |
| SalesAmount | Calculated |
Dimension Tables (Granularity Alignment)
| Dimension | Grain |
|---|---|
| DimCustomer | One row per customer |
| DimProduct | One row per product |
| DimEmployee | One row per employee |
| DimDate | One row per day |
| DimShipper | One row per shipper |
7. Granule vs Granularity (Clear Distinction)
| Term | Meaning | Example |
|---|---|---|
| Granule | What one row represents | One product in one order |
| Granularity | Level of detail | Daily, transactional |
8. Reports with Granule & Granularity Mapping
Report 1: Executive Sales Summary
Required Granularity
Monthly / Yearly
Derived From Granule
Aggregated from order-line daily grain
KPIs
Total Revenue
Total Orders
Average Order Value
Safe aggregation because base grain is atomic
Report 2: Product Performance Report
Granularity
Product × Month
KPIs
Revenue per Product
Units Sold
Category Contribution %
Uses
DimProduct
FactSales
Report 3: Customer Analysis Report
Granularity
Customer × Quarter
KPIs
Customer Lifetime Value
Avg Purchase Frequency
Repeat Purchase Rate
Grain Dependency
Needs order-line detail to avoid duplication
Report 4: Employee Sales Performance
Granularity
Employee × Month
KPIs
Revenue per Employee
Orders Handled
Avg Discount %
Grain Rule
Employee linked at order level, rolled up from line items.
Report 5: Shipping & Logistics Report
Granularity
Shipper × Month
KPIs
Avg Shipping Days
On-Time Delivery %
Orders Shipped
Grain
Calculated using order-line facts + order dates
9. Why Lowest Granularity Wins (EDM Rule)
| Benefit | Explanation |
|---|---|
| Flexibility | Supports future reports |
| Accuracy | No aggregation loss |
| Performance | Can pre-aggregate later |
| Governance | Single business definition |
10. EDM Traceability Matrix
| EDM Entity | DW Object | Grain |
|---|---|---|
| Order | FactSales | Order Line |
| Product | DimProduct | Product |
| Customer | DimCustomer | Customer |
| Employee | DimEmployee | Employee |
| Shipper | DimShipper | Shipper |
11. Final EDM-Driven Architecture
Northwind OLTP
↓
Staging (Raw Data)
↓
Enterprise Data Warehouse
↓
Star Schema (FactSales)
↓
Aggregates / Data Marts
↓
Reports & KPIs
Key Exam / Interview Takeaway
Always define the grain before building the fact table.
Granule = what one row means
Granularity = level of detail
No comments:
Post a Comment