Star Schema: The Foundation of Dimensional Modeling

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- Dimension table with surrogate key and natural key
CREATE TABLE customer_dim (
    -- Surrogate key: Used in all fact table relationships
    customer_key        INT IDENTITY(1,1) PRIMARY KEY,
    
    -- Natural key(s): Business identifiers from source systems
    customer_id         VARCHAR(20) NOT NULL,       -- CRM system ID
    erp_account_number  VARCHAR(15),                -- ERP system ID
    
    -- SCD tracking columns
    effective_date      DATE NOT NULL,
    expiration_date     DATE,                       -- NULL = current
    is_current          BIT DEFAULT 1,
    
    -- Descriptive attributes
    customer_name       VARCHAR(100) NOT NULL,
    email               VARCHAR(100),
    phone               VARCHAR(20),
    customer_type       VARCHAR(20),                -- B2B, B2C
    credit_rating       VARCHAR(10),
    
    -- Denormalized geographic hierarchy
    address_line1       VARCHAR(100),
    city                VARCHAR(50),
    state_province      VARCHAR(50),
    postal_code         VARCHAR(20),
    country             VARCHAR(50),
    region              VARCHAR(20),                -- Derived: Northeast, West, etc.
    
    -- Analytics segments
    acquisition_channel VARCHAR(30),
    loyalty_tier        VARCHAR(20),
    lifetime_value_band VARCHAR(20),
    
    -- ETL metadata
    source_system       VARCHAR(20),
    etl_load_timestamp  DATETIME DEFAULT GETDATE()
);
 
-- Unique constraint on natural key + version
CREATE UNIQUE INDEX ix_customer_natural 
    ON customer_dim(customer_id, effective_date);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- SCD Type 1: Overwrite
-- Customer changes email address: just update the row
 
-- Before update:
-- customer_key | customer_id | email
-- 1042         | CUST-A457   | john@oldmail.com
 
UPDATE customer_dim
SET email = 'john@newmail.com',
    etl_load_timestamp = GETDATE()
WHERE customer_key = 1042;
 
-- After update:
-- customer_key | customer_id | email
-- 1042         | CUST-A457   | john@newmail.com
 
-- All historical facts linked to customer_key 1042 now 
-- show the new email when joined.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- SCD Type 2: Add New Row
-- Customer moves from Boston to Seattle
 
-- BEFORE: Single row
-- customer_key | customer_id | city    | effective_date | expiration_date | is_current
-- 1042         | CUST-A457   | Boston  | 2020-01-15     | NULL            | 1
 
-- Step 1: Expire the current row
UPDATE customer_dim
SET expiration_date = '2024-06-30',
    is_current = 0
WHERE customer_key = 1042;
 
-- Step 2: Insert new row with new surrogate key
INSERT INTO customer_dim (
    customer_id, customer_name, email, city, state_province,
    effective_date, expiration_date, is_current
)
VALUES (
    'CUST-A457', 'John Smith', 'john@email.com', 'Seattle', 'WA',
    '2024-07-01', NULL, 1
);
 
-- AFTER: Two rows
-- customer_key | customer_id | city    | effective_date | expiration_date | is_current
-- 1042         | CUST-A457   | Boston  | 2020-01-15     | 2024-06-30      | 0
-- 5831         | CUST-A457   | Seattle | 2024-07-01     | NULL            | 1
 
-- Historical facts joined on customer_key = 1042 show Boston
-- New facts joined on customer_key = 5831 show Seattle
-- Both are the same logical customer (CUST-A457)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- SCD Type 3: Add Column for Previous Value
-- Track current and previous sales territory
 
CREATE TABLE salesperson_dim (
    salesperson_key     INT PRIMARY KEY,
    salesperson_id      VARCHAR(20),
    salesperson_name    VARCHAR(100),
    
    -- Current value
    current_territory   VARCHAR(50),
    territory_change_date DATE,
    
    -- Previous value (Type 3)
    prior_territory     VARCHAR(50)
);
 
-- When territory changes: Seattle → Portland
UPDATE salesperson_dim
SET prior_territory = current_territory,
    current_territory = 'Portland',
    territory_change_date = '2024-07-01'
WHERE salesperson_key = 287;
 
-- Enables queries like:
-- "Sales by current territory vs prior territory"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

CREATE TABLE date_dim (
    -- Surrogate key (often formatted as YYYYMMDD for convenience)
    date_key                INT PRIMARY KEY,            -- 20240115
    
    -- Full date
    full_date               DATE NOT NULL UNIQUE,       -- 2024-01-15
    
    -- Calendar attributes
    calendar_year           INT NOT NULL,               -- 2024
    calendar_quarter        INT NOT NULL,               -- 1
    calendar_month          INT NOT NULL,               -- 1
    calendar_week           INT NOT NULL,               -- 3
    day_of_year             INT NOT NULL,               -- 15
    day_of_quarter          INT NOT NULL,               -- 15
    day_of_month            INT NOT NULL,               -- 15
    day_of_week             INT NOT NULL,               -- 2 (Monday)
    
    -- Display names
    day_name                VARCHAR(10) NOT NULL,       -- 'Monday'
    month_name              VARCHAR(10) NOT NULL,       -- 'January'
    quarter_name            VARCHAR(10) NOT NULL,       -- 'Q1'
    year_month              VARCHAR(7) NOT NULL,        -- '2024-01'
    year_quarter            VARCHAR(7) NOT NULL,        -- '2024-Q1'
    
    -- Fiscal calendar (company-specific)
    fiscal_year             INT NOT NULL,               -- 2024 (or offset)
    fiscal_quarter          INT NOT NULL,
    fiscal_month            INT NOT NULL,
    fiscal_week             INT NOT NULL,
    fiscal_period           VARCHAR(20),                -- 'FY24-P07'
    
    -- Flags
    is_weekend              BIT NOT NULL,               -- 1 for Sat/Sun
    is_holiday              BIT DEFAULT 0,              -- Company holidays
    is_business_day         BIT NOT NULL,               -- Workdays
    is_month_end            BIT NOT NULL,
    is_quarter_end          BIT NOT NULL,
    is_year_end             BIT NOT NULL,
    
    -- Holiday attributes
    holiday_name            VARCHAR(50),                -- 'Independence Day'
    
    -- Prior period references (for easy comparisons)
    prior_year_date_key     INT,                        -- Same date last year
    prior_month_date_key    INT,                        -- Same date last month
    prior_week_date_key     INT                         -- Same date last week
);
 
-- Pre-populate 20 years of dates
-- The date dimension is generated, not loaded from source systems

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Junk dimension consolidating transaction flags
CREATE TABLE transaction_flags_dim (
    transaction_flags_key   INT PRIMARY KEY,
    is_returned             BIT NOT NULL,
    is_gift                 BIT NOT NULL,
    is_rush_order           BIT NOT NULL,
    is_taxable              BIT NOT NULL,
    is_discounted           BIT NOT NULL
);
 
-- Pre-populate all combinations: 2^5 = 32 rows
-- transaction_flags_key | is_returned | is_gift | is_rush | is_taxable | is_discounted
-- 1                     | 0           | 0       | 0       | 0          | 0
-- 2                     | 0           | 0       | 0       | 0          | 1
-- 3                     | 0           | 0       | 0       | 1          | 0
-- ... (32 combinations total)
 
-- Fact table references single junk dimension key
CREATE TABLE sales_fact (
    sale_key                BIGINT,
    date_key                INT,
    product_key             INT,
    customer_key            INT,
    transaction_flags_key   INT,    -- Single FK replaces 5 columns
    sales_amount            DECIMAL(12,2)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- Single date dimension used in three roles
SELECT 
    od.calendar_month AS order_month,
    sd.calendar_month AS ship_month,
    dd.calendar_month AS delivery_month,
    AVG(DATEDIFF(day, od.full_date, sd.full_date)) AS avg_order_to_ship,
    AVG(DATEDIFF(day, sd.full_date, dd.full_date)) AS avg_ship_to_delivery
FROM shipment_fact f
JOIN date_dim od ON f.order_date_key = od.date_key      -- Role: Order Date
JOIN date_dim sd ON f.ship_date_key = sd.date_key       -- Role: Ship Date  
JOIN date_dim dd ON f.delivery_date_key = dd.date_key   -- Role: Delivery Date
GROUP BY od.calendar_month, sd.calendar_month, dd.calendar_month;
 
-- Optionally create views for role clarity:
CREATE VIEW order_date AS SELECT * FROM date_dim;
CREATE VIEW ship_date AS SELECT * FROM date_dim;
CREATE VIEW delivery_date AS SELECT * FROM date_dim;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

-- Denormalized hierarchy in product dimension
-- All hierarchy levels stored as attributes (the dimensional modeling way)
 
CREATE TABLE product_dim (
    product_key             INT PRIMARY KEY,
    product_code            VARCHAR(20),
    product_name            VARCHAR(100),
    
    -- Hierarchy Level 1: Department
    department_code         VARCHAR(10),
    department_name         VARCHAR(50),
    
    -- Hierarchy Level 2: Category
    category_code           VARCHAR(10),
    category_name           VARCHAR(50),
    
    -- Hierarchy Level 3: Subcategory
    subcategory_code        VARCHAR(10),
    subcategory_name        VARCHAR(50),
    
    -- Hierarchy Level 4: Brand
    brand_code              VARCHAR(10),
    brand_name              VARCHAR(50),
    
    -- Product is Level 5 (most granular)
    -- ...other product attributes
);
 
-- Query at any hierarchy level with simple GROUP BY
-- Sales by Department
SELECT department_name, SUM(sales_amount)
FROM sales_fact f JOIN product_dim p ON f.product_key = p.product_key
GROUP BY department_name;
 
-- Sales by Category within Department
SELECT department_name, category_name, SUM(sales_amount)
FROM sales_fact f JOIN product_dim p ON f.product_key = p.product_key
GROUP BY department_name, category_name;
 
-- Drill-down to product
SELECT department_name, category_name, subcategory_name, 
       brand_name, product_name, SUM(sales_amount)
FROM sales_fact f JOIN product_dim p ON f.product_key = p.product_key
WHERE department_name = 'Electronics'
GROUP BY department_name, category_name, subcategory_name, 
         brand_name, product_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Conformed customer dimension used across multiple fact tables
-- Same dimension, same meaning, everywhere
 
-- Sales Fact
SELECT c.customer_segment, SUM(sf.sales_amount) AS sales
FROM sales_fact sf
JOIN customer_dim c ON sf.customer_key = c.customer_key
GROUP BY c.customer_segment;
 
-- Service Calls Fact (same customer dimension)
SELECT c.customer_segment, COUNT(*) AS support_calls
FROM service_call_fact scf
JOIN customer_dim c ON scf.customer_key = c.customer_key
GROUP BY c.customer_segment;
 
-- Combined cross-fact analysis
SELECT 
    c.customer_segment,
    SUM(sf.sales_amount) AS sales,
    COUNT(DISTINCT scf.call_key) AS support_calls,
    SUM(sf.sales_amount) / NULLIF(COUNT(DISTINCT scf.call_key), 0) AS sales_per_call
FROM customer_dim c
LEFT JOIN sales_fact sf ON c.customer_key = sf.customer_key
LEFT JOIN service_call_fact scf ON c.customer_key = scf.customer_key
GROUP BY c.customer_segment;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- Bridge table pattern for many-to-many relationship
-- Patient visits can have multiple diagnoses
 
-- Diagnosis Bridge Table
CREATE TABLE patient_diagnosis_bridge (
    patient_diagnosis_group_key INT,    -- Group identifier
    diagnosis_key               INT,     -- FK to diagnosis_dim
    diagnosis_priority          INT,     -- Primary=1, Secondary=2, etc.
    weighting_factor            DECIMAL(5,4)  -- For weighted aggregation
);
 
-- Patient Visit Fact references the group, not individual diagnoses
CREATE TABLE patient_visit_fact (
    visit_key                   BIGINT PRIMARY KEY,
    patient_key                 INT,
    date_key                    INT,
    provider_key                INT,
    patient_diagnosis_group_key INT,     -- Links to bridge
    visit_charge                DECIMAL(10,2)
);
 
-- Query: Visits by diagnosis (through bridge)
-- Note: aggregates will double-count if patient has multiple diagnoses
SELECT 
    d.diagnosis_category,
    COUNT(*) * b.weighting_factor AS weighted_visit_count,
    SUM(f.visit_charge * b.weighting_factor) AS weighted_charges
FROM patient_visit_fact f
JOIN patient_diagnosis_bridge b 
    ON f.patient_diagnosis_group_key = b.patient_diagnosis_group_key
JOIN diagnosis_dim d 
    ON b.diagnosis_key = d.diagnosis_key
GROUP BY d.diagnosis_category;