Advanced Dimensional Modeling | ShaDey Inc. Data Platform

1

Grain-First Discipline

Declare grain before adding a single attribute. Grain is the atomic level of measurement: the finest granularity at which a fact record exists. Without grain discipline, your fact table becomes a shapeless collection of aggregations that cannot be reliably summed, drilled, or compared.

Grain Rule

Every fact table must have exactly one declared grain. The grain specifies "one row per [X]" where [X] is a unique combination of dimension keys. Write it down. Make it non-negotiable.

Star Schema with Grain Hierarchy: Store-Product-Date Fact Table

ShaDey Example: FACT_DAILY_SALES

ShaDey's point-of-sale grain is declared as: one row per store + product + calendar day. This means the combination of (store_key, product_key, date_key) is unique. Every day, for every product in every store, exactly one row exists capturing that day's sales.

Grain Discipline at ShaDey

FACT_DAILY_SALES is sourced from VBRP (SAP billing detail). VBRP has grain of one row per invoice line item, which is finer than daily store-product. To reach daily grain, aggregate VBRP by store_key, product_key, and date_key. The aggregation is the dimensional modeling, not the source system definition.

-- FACT_DAILY_SALES: grain = store + product + date CREATE TABLE FACT_DAILY_SALES ( store_key INT, product_key INT, date_key INT, quantity_sold DECIMAL(10,2), sales_amount DECIMAL(15,2), cost_amount DECIMAL(15,2), unit_price DECIMAL(10,4), PRIMARY KEY (store_key, product_key, date_key) );

Sample Data: FACT_DAILY_SALES

store_key	product_key	date_key	quantity_sold	sales_amount	cost_amount	unit_price
1001	5001	20260315	24	576.00	288.00	24.00
1001	5002	20260315	18	450.00	180.00	25.00
1002	5001	20260315	31	744.00	372.00	24.00
1001	5001	20260316	22	528.00	264.00	24.00
1003	5003	20260315	15	390.00	150.00	26.00

The Mixed-Grain Anti-Pattern

A common mistake: mixing order header and order-line grain in one fact table. An order header has attributes like order date and customer. An order-line has product and quantity. When you put both in FACT_SALES, the order-date attribute replicates on every line, and the unit-price becomes non-additive. Do not do this.

Mixed Grain Alert

If you SUM a measure in a fact table and the result does not reconcile to the source system, you likely have mixed grains. The table contains multiple atomic levels masquerading as one.

SQL Grain Verification

To verify that your fact table maintains grain discipline, run a grain-check query: the primary key count must equal the total row count.

-- Grain verification: primary key should equal row count SELECT COUNT(*) AS total_rows, COUNT(DISTINCT store_key, product_key, date_key) AS unique_keys FROM FACT_DAILY_SALES; -- Result: both should equal each other. If not, grain is violated.

Exercise 1: Identify the grain of these ShaDey tables:

VBRK (SAP billing header) - has one row per invoice
VBRP (SAP billing line item) - has one row per line item in an invoice
LIPS (SAP delivery line) - one row per delivery line
KNA1 (SAP customer master) - one row per customer
MARA (SAP material master) - one row per material (product)

Answer: For a retail sales fact table sourced from VBRP, the grain is one row per invoice-line-item. To get to daily store-product, you must aggregate VBRP with date grouping.

2

Detecting Hidden Mixed Grains

Mixed grains hide in plain sight. Your fact table passes schema validation, but when analysts query it, aggregation totals don't match the source system. This section teaches you how to detect and diagnose hidden mixed-grain problems.

The Promotion Fact Symptom

ShaDey's promotion team sources promo facts from two systems: SAP (store-level allocation) and the POS system (item-level redemption). A well-meaning engineer merges these into FACT_PROMOTION without declaring grain. The result: store + product + date + promotion_id appears unique, but store-level allocation rows co-exist with item-level redemption rows at different granularities.

-- BAD: Mixed grain - store-level promo allocation mixed with item-level redemption CREATE TABLE FACT_PROMOTION_BAD ( store_key INT, product_key INT, date_key INT, promotion_id VARCHAR(20), allocated_budget DECIMAL(12,2), redeemed_amount DECIMAL(12,2), unit_discount DECIMAL(8,4) );

Sample Data Showing the Problem

store_key	product_key	date_key	promo_id	allocated_budget	redeemed_amount
1001	NULL	20260315	PROMO_001	1000.00	0.00
1001	5001	20260315	PROMO_001	0.00	250.50
1001	5002	20260315	PROMO_001	0.00	175.25

Notice: store 1001 on 2026-03-15 has allocated_budget=1000 (one row), but redeemed_amount spreads across multiple product rows. Summing allocated_budget across products gives 1000, but that's not additive per product. This is mixed grain.

Hidden Grain Detection

Mixed grain reveals itself through reconciliation failures. If your FACT_PROMOTION.allocated_budget doesn't match SAP budget totals when grouped by store-promotion-date, you have mixed grain. Always test: "Does SUM(fact_measure) GROUP BY [declared grain keys] match the source system total?" If not, grain is violated.

The Fix: Grain Separation

Create two separate fact tables: one for allocation (grain: store-date-promotion), one for redemption (grain: store-product-date-promotion).

-- GOOD: Separate allocation and redemption at their natural grains
CREATE TABLE FACT_PROMO_ALLOCATION (
  store_key INT,
  date_key INT,
  promotion_id VARCHAR(20),
  allocated_budget DECIMAL(12,2),
  PRIMARY KEY (store_key, date_key, promotion_id)
);

CREATE TABLE FACT_PROMO_REDEMPTION (
  store_key INT,
  product_key INT,
  date_key INT,
  promotion_id VARCHAR(20),
  redeemed_amount DECIMAL(12,2),
  unit_discount DECIMAL(8,4),
  PRIMARY KEY (store_key, product_key, date_key, promotion_id)
);
      

ShaDey Promotion Separation

ShaDey runs promotions across stores via SAP VBAK/VBAP. Allocation budgets come from SAP allocation tables (store-promotion-date grain), while redemptions come from POS (store-product-date-promotion grain). These are separated into FACT_PROMO_ALLOCATION and FACT_PROMO_REDEMPTION. Never merge them.

3

Three Fact Table Types

There are three archetypes for fact tables in dimensional modeling. Each has its own grain, use case, and aggregation semantics. Understanding which to use prevents fundamental design errors.

Three Fact Table Archetypes: Transaction, Periodic Snapshot, Accumulating Snapshot

Type 1: Transaction Facts

One row per atomic transaction event. Lowest grain, immutable after creation, additive at transaction level. Most common for POS and operational analytics.

-- Type 1: Transaction Fact - one row per invoice line item (VBRP source) CREATE TABLE FACT_POS_SALES ( sales_key BIGINT PRIMARY KEY, store_key INT, product_key INT, date_key INT, transaction_time_key INT, customer_key INT, invoice_number VARCHAR(30), line_item_number INT, quantity_sold DECIMAL(10,2), sales_amount DECIMAL(15,2), cost_amount DECIMAL(15,2), discount_amount DECIMAL(12,2) );

Type 2: Periodic Snapshot Facts

One row per dimension combination per time period. Grain declared explicitly (e.g., store-product-week). Semi-additive measures (inventory_on_hand). Used for inventory, aging analysis.

-- Type 2: Periodic Snapshot - one row per product per store per week CREATE TABLE FACT_WEEKLY_INVENTORY ( store_key INT, product_key INT, week_key INT, inventory_on_hand DECIMAL(12,2), reorder_point DECIMAL(12,2), units_received DECIMAL(12,2), units_sold DECIMAL(12,2), PRIMARY KEY (store_key, product_key, week_key) );

Type 3: Accumulating Snapshot Facts

One row per order that persists and updates through fulfillment lifecycle. Grain: one row per order. Multiple date keys mark each milestone. Used for order tracking and SLA monitoring.

-- Type 3: Accumulating Snapshot - one row per order, updated at each milestone CREATE TABLE FACT_ORDER_FULFILLMENT ( order_key INT PRIMARY KEY, customer_key INT, order_date_key INT, shipped_date_key INT, delivered_date_key INT, return_date_key INT, order_amount DECIMAL(15,2), days_to_ship INT, days_to_deliver INT, return_flag BOOLEAN );

Comparison Table: When to Use Each Type

Fact Type	Grain	Immutable?	Best For	Example
Transaction	Event	Yes	Point-of-sale, payments, clicks	FACT_POS_SALES
Periodic Snapshot	Dimension combo + period	No	Inventory, account balance, aging	FACT_WEEKLY_INVENTORY
Accumulating Snapshot	Process instance	No	Order fulfillment, claim workflow	FACT_ORDER_FULFILLMENT

Fact Table Type Selection

At ShaDey, choose your fact table type based on the business process: FACT_SALES is transactional (one row per invoice line from VBRP), FACT_WEEKLY_INVENTORY is periodic snapshot (one row per sku-store-week), FACT_ORDER_FULFILLMENT is accumulating snapshot (one row per order tracking order-date, ship-date, delivery-date columns). Each type has different querying patterns and aggregation rules.

4

Additivity Traps

Not all measures in a fact table can be summed across all dimensions. Additivity is a contract between the fact table designer and the analyst. Violate it, and your BI tool will produce nonsensical results that pass all syntax checks.

Additivity Types

Fully Additive Measures

Safe to SUM across all dimensions. Examples: quantity_sold, sales_amount, discount_amount. These are counts or amounts that accumulate naturally.

Semi-Additive Measures

Can be summed across some dimensions, but NOT time. Example: inventory_on_hand. You can sum inventory across products for a store on a date, but you CANNOT sum inventory across dates (that makes no sense: you'd add Monday's inventory + Tuesday's inventory, which is double-counting). Use AVERAGE or MAX across time instead.

Non-Additive Measures

Never SUM. Examples: unit_price, margin_percentage, average_order_value. These are ratios or unit metrics. Always use AVERAGE or weighted aggregations.

Additivity Contract

Document every measure with its additivity type. BI tools cannot detect additivity violations. A wrong aggregation query returns a plausible-looking number that is completely wrong.

ShaDey Additivity Reference Table

Measure	Additivity	Safe Dimensions	Danger Dimension	Correct Aggregation
sales_amount	Fully Additive	All	None	SUM
quantity_sold	Fully Additive	All	None	SUM
unit_price	Non-Additive	None	All	AVERAGE or WEIGHTED_AVG
inventory_on_hand	Semi-Additive	Product, Store	Time	SUM by product/store, AVERAGE by date
margin_percentage	Non-Additive	None	All	(SUM profit) / (SUM cost)

SQL Example: Correct vs. Incorrect

-- WRONG: Summing unit_price across products
SELECT
  store_key,
  SUM(unit_price) AS total_price  -- BAD! Non-additive measure
FROM FACT_POS_SALES
GROUP BY store_key;

-- CORRECT: Average unit price
SELECT
  store_key,
  AVG(unit_price) AS avg_price
FROM FACT_POS_SALES
GROUP BY store_key;
      

Measure Documentation

Every measure in ShaDey fact tables must have documented additivity. FACT_SALES documents: quantity_sold (fully additive), sales_amount (fully additive), unit_price (non-additive - use AVG), discount_percent (non-additive - use AVG), gross_margin (non-additive - use weighted AVG). Place these rules in the data dictionary and ETL documentation.

5

Multi-Calendar Time Modeling

Retail operates in multiple calendar systems simultaneously: calendar year (Jan-Dec), fiscal year (varies by company), and NRF 4-5-4 retail calendar (weeks grouped into 4, 5, or 4-week periods). A single DIM_DATE must support all three without redundancy.

ShaDey Fiscal Calendar

ShaDey's fiscal year runs Feb-Jan (aligned with global retail conventions). Each fiscal year is divided into 13 four-week periods, plus the fiscal year adjustment week. DIM_DATE must map every calendar day to all three calendar systems.

-- DIM_DATE: One row per day, supports 3 calendar systems CREATE TABLE DIM_DATE ( date_key INT PRIMARY KEY, calendar_date DATE, calendar_year INT, calendar_quarter INT, calendar_month INT, calendar_day_of_month INT, day_of_week INT, fiscal_year INT, fiscal_quarter INT, fiscal_period INT, nrf_year INT, nrf_week INT, nrf_period INT, is_weekend BOOLEAN, is_holiday BOOLEAN, holiday_name VARCHAR(50) );

Sample Data: 2026-03-15 Across All Calendars

date_key	calendar_date	calendar_year	fiscal_year	fiscal_period	nrf_week	nrf_period
20260315	2026-03-15	2026	2026	2	11	2

Calendar Strategy

Store all calendar mappings in DIM_DATE. Do NOT compute fiscal_year in BI queries. Let DIM_DATE be the single source of truth. This prevents calendar disputes between Finance and Operations.

6

Late-Arriving Facts & Dimensions

The real world doesn't respect ETL schedules. A POS sale is recorded 3 days after transaction. A product is reclassified after being loaded. A store opens but the dimension hasn't been refreshed yet. This section covers patterns for handling asynchronous data arrival.

Late-Arriving Dimension Pattern: Inferred Members

When a fact references a dimension key that doesn't exist yet, create an inferred member with a placeholder description. When the dimension is loaded later, backfill the inferred member or update the fact key.

-- Inferred member pattern for late-arriving dimension CREATE TABLE DIM_PRODUCT ( product_key INT PRIMARY KEY, product_id VARCHAR(20), product_name VARCHAR(100), category VARCHAR(50), is_inferred BOOLEAN, inferred_date DATE ); -- Insert inferred member when late-arriving fact references unknown product INSERT INTO DIM_PRODUCT (product_key, product_id, product_name, is_inferred, inferred_date) VALUES ( -999, 'UNKNOWN_PRODUCT', 'Unknown Product (Late Arrival)', true, CURRENT_DATE );

Late-Arriving Patterns

At ShaDey, inferred members (product_key=-999, store_key=-999) are used to capture facts that arrive before their dimension records are loaded. These placeholders are later reconciled when the real dimension rows arrive. Use is_inferred flag to track which dimension members are awaiting backfill from SAP MARA, KNA1, or MARD master data.

7

SCD Type Tradeoffs in Multi-Source

Slowly Changing Dimension handling gets complex when facts come from multiple source systems with different governance. SAP demands Type 2 (history), CRM demands Type 1 (overwrite). This section resolves the tension.

SCD Type 1: Overwrite (Type 1)

Update dimension attributes in place. Fast, simple, loses history. Use for low-cardinality attributes (customer country) where history is never needed.

SCD Type 2: Versioned (Type 2)

Insert new row for every change. Preserves complete history. Add row_effective_date, row_expiration_date, and is_current_row columns. Requires carefully filtering in BI queries.

SCD Type 3: Previous Value

Keep only current and previous values. Store current_value and previous_value columns. Good for "before-after" analysis without full versioning overhead.

SCD Strategy for ShaDey

DIM_CUSTOMER uses Type 2 because customer address/segment history is essential for regional and cohort analysis. DIM_PRODUCT uses Type 2 because product category changes affect category-year comparisons. DIM_STORE uses Type 1 because store attributes rarely change and historical store changes are never analyzed.

Decision Matrix: Which SCD Type for ShaDey?

Entity	Change Frequency	History Needed?	Recommended SCD Type	Reason
Customer	Low	Yes	Type 2	Address changes matter for regional analysis
Product	Medium	Yes	Type 2	Category changes affect historical reporting
Store	Very Low	No	Type 1	Store attributes rarely change; ignore history

8

Filter Behavior vs SCD Behavior

BI tools (Tableau, Power BI, Looker) each handle SCD2 dimensions differently. Some filter to current row only, others apply as-of-date logic automatically. Misunderstanding this causes incorrect drill-down behavior and silent data errors.

BI Tool Difference

Tableau uses as-of-date filtering (rows active on fact date). Power BI requires explicit relationship filters. Looker uses LEFT JOIN semantics. Design your DIM_CUSTOMER.is_current_row flag to handle all three.

Safe vs Unsafe Drill Paths with SCD2

Drill Path	Safe?	Issue	Solution
Fiscal Quarter -> Day	Yes	None	Drill naturally within timeline
Customer Region -> Customer Name	No	If customer moves regions, drill breaks	Use is_current_row flag; filter drill explicitly
Product Category -> Product SKU	No	Historical sales show under new category	Use as-of-date in drill query

9

Conformed Dimensions

Conformed dimensions are shared dimension tables used identically across multiple fact tables. They enable cross-process analytics by ensuring that when you join FACT_SALES and FACT_INVENTORY through DIM_PRODUCT, you're analyzing the same product definition. Kimball's bus matrix documents conformance.

Definition & Purpose

A dimension is "conformed" when it is used unchanged across multiple fact tables. For example, DIM_DATE used by FACT_SALES, FACT_INVENTORY_SNAPSHOT, FACT_ORDER, and FACT_RETURN must have identical calendar logic, grain, and surrogate keys. Conformance prevents "same metric, different answer" problems that plague disconnected data warehouses.

Kimball Concept

The bus matrix is a Kimball technique that documents which conformed dimensions apply to which fact tables. For ShaDey, DIM_DATE, DIM_PRODUCT, and DIM_STORE are fully conformed across all four major fact tables: FACT_SALES, FACT_INVENTORY_SNAPSHOT, FACT_ORDER, and FACT_RETURN. Non-conformed dimensions (e.g., FACT_SALES has DIM_PROMOTION but FACT_ORDER does not) break the bus.

ShaDey Inc. Conformed Dimension Examples

ShaDey operates three fully conformed dimensions that align across the retail 8 business domains (Ordering, Fulfillment, Returns, Inventory, Promotions, Customers, Products, Stores):

DIM_DATE: Fiscal calendar with year, quarter, month, week, day, weekday, holiday_flag. Used by FACT_SALES (transaction date), FACT_INVENTORY_SNAPSHOT (snapshot date), FACT_ORDER (order date), FACT_RETURN (return date). SAP source: VBAK-ERDAT, VBRK-BLDAT.
DIM_PRODUCT: Material master from SAP MARA (MATNR, MTART, SPART, BISMT, MEINS). Conformed across FACT_SALES (product sold), FACT_INVENTORY_SNAPSHOT (sku held), FACT_ORDER (product ordered), FACT_RETURN (returned sku).
DIM_STORE: Plant/warehouse master from SAP T001W (plant table, keyed by WERKS). Includes location, region, warehouse code. Conformed across FACT_SALES (selling location), FACT_INVENTORY_SNAPSHOT (holding location), FACT_RETURN (return location).

Bus Matrix: ShaDey Fact & Dimension Conformance

Fact Table	Source SAP	Grain	DIM_DATE	DIM_PRODUCT	DIM_STORE	DIM_CUSTOMER	Other Dims
FACT_SALES	VBRK/VBRP (Invoice)	One row per invoice line	Yes (invoice_date)	Yes (material)	Yes (shipping location)	Yes (bill-to customer)	DIM_PROMOTION, DIM_SALESPERSON
FACT_INVENTORY_SNAPSHOT	MARD (Stock)	One row per SKU per warehouse per day	Yes (snapshot_date)	Yes (material)	Yes (warehouse WERKS)	No	DIM_BATCH_STATUS
FACT_ORDER	VBAK/VBAP (Sales Order)	One row per order line	Yes (order_date)	Yes (material ordered)	Yes (ship-to location)	Yes (sold-to customer)	DIM_DELIVERY_STATUS
FACT_RETURN	VBRK/VBRP (Credit Memo)	One row per return line	Yes (return_date)	Yes (material returned)	Yes (return location)	Yes (returning customer)	DIM_RETURN_REASON

Cross-Process Analytics

Because DIM_DATE, DIM_PRODUCT, and DIM_STORE are conformed, you can write a single drill path: "Sales by Product -> Inventory on Hand -> Orders Pending -> Returns This Year" without worrying about conflicting date definitions or product hierarchies. Non-conformed dimensions break this trust.

Conformance Validation: ShaDey PDM Tables

In ShaDey's PDM (Persistent Data Model), conformance is enforced through shared reference tables. All fact tables reference the same DIM_DATE.date_key, same DIM_PRODUCT.product_key, and same DIM_STORE.store_key. This is not a recommendation; it is a non-negotiable requirement for dimensional design at ShaDey.

-- Conformed DIM_DATE used across all ShaDey fact tables
CREATE TABLE DIM_DATE (
  date_key INT PRIMARY KEY,
  calendar_date DATE UNIQUE,
  fiscal_year INT,
  fiscal_quarter INT,
  fiscal_month INT,
  day_of_week INT,
  is_holiday BOOLEAN,
  is_weekend BOOLEAN
);

-- FACT_SALES references DIM_DATE (conformed)
CREATE TABLE FACT_SALES (
  sales_key INT PRIMARY KEY,
  invoice_date_key INT,
  product_key INT,
  store_key INT,
  customer_key INT,
  quantity DECIMAL(10,2),
  FOREIGN KEY (invoice_date_key) REFERENCES DIM_DATE(date_key)
);

-- FACT_INVENTORY_SNAPSHOT references same DIM_DATE (conformed)
CREATE TABLE FACT_INVENTORY_SNAPSHOT (
  inventory_key INT PRIMARY KEY,
  snapshot_date_key INT,
  product_key INT,
  store_key INT,
  quantity_on_hand DECIMAL(10,2),
  FOREIGN KEY (snapshot_date_key) REFERENCES DIM_DATE(date_key)
);
      

Why Conformance Matters

Without conformed dimensions, combining FACT_SALES and FACT_INVENTORY requires reconciliation logic between different date definitions. With conformed dimensions, the join is trivial and auditable. Every analyst gets the same "Product X sold in Region Y" number because they all use the same DIM_PRODUCT and DIM_STORE.

10

Junk & Mini-Dimensions

Junk dimensions combine low-cardinality flags into a single dimension table to reduce fact table width. Mini-dimensions extract frequently-changing attributes from slowly-changing dimensions to avoid SCD2 bloat and improve query performance.

Junk Dimensions: Combining Flags

Every fact table accumulates boolean and low-cardinality flags: is_gift_wrapped, is_express_shipping, is_loyalty_member, is_returns_eligible, is_same_day_delivery. Adding these as separate columns wastes storage and query performance. Instead, create a junk dimension that combines them into a single surrogate key.

Junk Dimension Pattern

A junk dimension stores all combinations of low-cardinality flags in a single table. For example, DIM_ORDER_FLAGS combines 6 boolean columns (gift_wrapped, express, loyalty, refundable, same_day, corporate). This creates at most 2^6 = 64 rows. Instead of 6 BOOLEAN columns in FACT_SALES, you add one order_flags_key INT. This reduces fact table width by 5 bytes per row (40 bytes at scale).

ShaDey Example: DIM_ORDER_FLAGS

FACT_ORDER currently stores six separate boolean columns. These flags come from SAP VBAK (sales order header) and VBAP (sales order item). Consolidating them into DIM_ORDER_FLAGS:

-- Junk Dimension: Combines 6 low-cardinality order flags
CREATE TABLE DIM_ORDER_FLAGS (
  order_flags_key INT PRIMARY KEY,
  is_gift_wrapped BOOLEAN,
  is_express_shipping BOOLEAN,
  is_loyalty_member BOOLEAN,
  is_refundable BOOLEAN,
  is_same_day_delivery BOOLEAN,
  is_corporate_account BOOLEAN,
  flag_combination_hash VARCHAR(64)
);

-- Example rows: only actually-occurring combinations
-- order_flags_key=1: all FALSE
-- order_flags_key=2: is_loyalty_member=TRUE, rest FALSE
-- order_flags_key=3: is_express_shipping=TRUE, is_loyalty_member=TRUE, rest FALSE

-- FACT_ORDER now references junk dimension
CREATE TABLE FACT_ORDER (
  order_key INT PRIMARY KEY,
  order_date_key INT,
  product_key INT,
  store_key INT,
  customer_key INT,
  order_flags_key INT REFERENCES DIM_ORDER_FLAGS,
  quantity DECIMAL(10,2),
  order_amount DECIMAL(12,2)
);

-- Query benefit: Simple, performant filtering
-- SELECT SUM(order_amount)
-- FROM FACT_ORDER f
-- JOIN DIM_ORDER_FLAGS flg ON f.order_flags_key = flg.order_flags_key
-- WHERE flg.is_express_shipping = TRUE
-- AND flg.is_loyalty_member = TRUE;
      

Junk Dimension Benefits

Reduces fact table width (saves storage and I/O). Makes filtering cleaner (one join vs. WHERE clause on 6 columns). Enables centralized management of flag semantics. At ShaDey scale (billions of orders), this saves terabytes of storage.

Mini-Dimensions: Extracting Volatile Attributes

Some dimension attributes change frequently, causing SCD2 to create excessive versions. For example, DIM_CUSTOMER stores both stable attributes (customer_id, legal_name, country) and volatile attributes (annual_credit_limit, customer_segment, loyalty_points). A customer's credit limit might change 10 times per year, creating 10 new DIM_CUSTOMER rows (SCD2 bloat). Instead, extract the volatile attributes into a separate mini-dimension.

Mini-Dimension Pattern

A mini-dimension is a separate table that stores frequently-changing attributes with its own SCD2 history. DIM_CUSTOMER_DEMOGRAPHICS stores credit_limit, customer_segment, loyalty_tier, and has its own row_effective_date and row_expiration_date. DIM_CUSTOMER stays lean (stable attributes only) and joins to DIM_CUSTOMER_DEMOGRAPHICS via customer_key + effective_date logic.

ShaDey Example: Separating DIM_CUSTOMER from DIM_CUSTOMER_DEMOGRAPHICS

ShaDey's customer master (SAP KNA1) includes both stable fields (KUNNR, NAME1, LAND1) and volatile fields (EIKTO credit limit, KTOKD account group). For reporting, volatile attributes cause DIM_CUSTOMER SCD2 bloat. Solution: create a mini-dimension for demographics.

-- Stable DIM_CUSTOMER (SCD1 or SCD2 for stable attributes only)
CREATE TABLE DIM_CUSTOMER (
  customer_key INT PRIMARY KEY,
  customer_id VARCHAR(20),
  legal_name VARCHAR(100),
  country_code CHAR(2),
  industry_code VARCHAR(10),
  is_current_row BOOLEAN
);

-- Mini-dimension: Volatile attributes with SCD2
CREATE TABLE DIM_CUSTOMER_DEMOGRAPHICS (
  demographics_key INT PRIMARY KEY,
  customer_key INT REFERENCES DIM_CUSTOMER,
  effective_date DATE,
  expiration_date DATE,
  is_current_row BOOLEAN,
  credit_limit DECIMAL(12,2),
  customer_segment VARCHAR(30),
  loyalty_tier VARCHAR(20),
  annual_revenue_bucket VARCHAR(20)
);

-- FACT_SALES joins both dimensions
CREATE TABLE FACT_SALES (
  sales_key INT PRIMARY KEY,
  sale_date_key INT,
  product_key INT,
  customer_key INT,
  demographics_key INT,
  quantity DECIMAL(10,2),
  FOREIGN KEY (customer_key) REFERENCES DIM_CUSTOMER(customer_key),
  FOREIGN KEY (demographics_key) REFERENCES DIM_CUSTOMER_DEMOGRAPHICS(demographics_key)
);
      

Mini-Dimension Benefits

Prevents SCD2 bloat on DIM_CUSTOMER (which could have millions of versions). Allows different SCD strategies (DIM_CUSTOMER is SCD1, DIM_CUSTOMER_DEMOGRAPHICS is SCD2). Improves query performance (DIM_CUSTOMER is smaller, more cacheable). Makes reporting logic clearer (segment is explicitly demographic, not core customer identity).

11

Business Keys vs Technical Keys

Every entity has a business key (SAP material number, customer account number) and a technical key (surrogate integer, hash). They serve different purposes. Confusing them breaks reconciliation.

Business Key: The Stable Identifier

A business key is how humans and source systems refer to an entity. For ShaDey: MATNR (SAP material), KUNNR (SAP customer), WERKS (SAP plant/store). Business keys must be stable across time and systems. They should never be reused for a different entity.

Technical Key: The Database Identifier

A surrogate integer (or hash) used for storage efficiency and join performance. Examples: product_key=5001, customer_key=10234. Technical keys are internal; never expose them to business users or source systems.

Key Stability Audit: ShaDey Entities

Entity	Business Key (SAP)	Stable?	Reused?	Audit Status
Product	MATNR	Yes	No	Pass
Customer	KUNNR	Yes	Occasionally (inactive reused)	Flag for validation
Store	WERKS	Yes	No	Pass

Key Selection at ShaDey

All ShaDey dimensions use MATNR (product), KUNNR (customer), and WERKS (store) as business keys sourced from SAP. Technical keys (product_key INT, customer_key INT, store_key INT) are auto-generated surrogates. Facts always join on technical keys. Reporting and audits always validate against business keys to catch reuse or stability issues.

12

Canonical Entity Design

A canonical entity is a minimal, stable definition of a business concept. It does NOT include every possible attribute. It defines the core identity that other systems agree on. Extensions live in separate satellite tables.

Why Canonical Design Matters

A canonical entity defines the narrowest useful core: the minimal set of attributes that ALL consumers need. By separating core from extended, you achieve:

Data stability: The core table rarely changes; only new attributes trigger extended table updates
Multi-consumer alignment: Finance, supply chain, and merchandising all join to the same DIM_PRODUCT_CORE and know they're seeing the "official" product identity
Graceful extension: New domain-specific attributes (e.g., planogram position for retail, lead time for supply chain) join via satellite tables without impacting existing queries
Performance: Smaller core tables scan faster for daily operational lookups; extended attributes are only hydrated when needed

Canonical Core vs Extended Attributes: DIM_PRODUCT Example

At ShaDey Inc., the product dimension is consumed by sales, supply chain, and merchandising teams. The canonical core is what they all agree on; extensions vary by domain:

Attribute	Category	Value (Example: Denim Jacket XL Blue)	SAP Source	Consumers
product_key	CORE	4521928	MARA.MATNR	All
product_id	CORE	MAT-10847392	MARA.MATNR	All
product_name	CORE	Denim Jacket XL Blue	MARA.MAKTX	All
category	CORE	Outerwear / Jackets	MARA.MATKL / T179 hierarchy	All
brand	CORE	ShaDey Premium	MARA.BRAND (custom field)	All
planogram_position	EXT	Shelf A, Tier 2	SCML external table	Merchandising
shelf_life_days	EXT	365	MARC.LMZFL	Merchandising, QA
lead_time_days	EXT	45	MARC.BESCHAFFUNGSZEIT	Supply Chain, Procurement
reorder_point_qty	EXT	150 units	MARC.EISBE	Supply Chain, Procurement
minimum_order_qty	EXT	12 units	MARC.BSTME	Supply Chain, Procurement

Reference Data Harmonization Across Multi-System Sources

SAP, Salesforce, and Shopify each define reference codes (e.g., country, currency, unit of measure) differently. The DWH must harmonize these into a single canonical representation:

-- DIM_COUNTRY: Canonical reference, used by all sources
CREATE TABLE DIM_COUNTRY (
  country_key INT PRIMARY KEY,
  iso_code CHAR(2) UNIQUE,  -- All systems align to ISO 3166-1
  country_name VARCHAR(50),
  sap_country_code VARCHAR(10),  -- SAP uses T005/T005U: "DE", "US"
  salesforce_country VARCHAR(50),  -- Salesforce uses full names: "Germany", "United States"
  shopify_country_code VARCHAR(50),  -- Shopify uses ISO sometimes, sometimes full name
  region VARCHAR(30),
  CONSTRAINT uc_harmonized UNIQUE (sap_country_code, salesforce_country, shopify_country_code)
);

-- Example rows
INSERT INTO DIM_COUNTRY VALUES
  (1, 'US', 'United States', 'US', 'United States', 'US', 'North America'),
  (2, 'DE', 'Germany', 'DE', 'Germany', 'DE', 'Europe'),
  (3, 'GB', 'United Kingdom', 'GB', 'United Kingdom', 'UK', 'Europe');
      

Canonical Entity Design Rules at ShaDey

Rule 1: Minimal Core: Include only attributes that appear in 80% or more of use cases. If an attribute is domain-specific, it belongs in an extension table.
Rule 2: No Overwriting: Once a core attribute is defined, never remove it or change its data type. Extensions can be added; the core is append-only.
Rule 3: Single Source per Attribute: Each core attribute maps to exactly one source system or derivation rule. Avoid blending SAP + Salesforce in the same column; create separate columns and document precedence.
Rule 4: Reference Harmonization: All code tables (country, currency, UoM, status) must have a canonical definition in the DWH with mappings to source systems. No ad-hoc lookups.

Narrowest Useful Core

The canonical core is not the union of all attributes across all consumers. It is the intersection of what they all need, plus what rarely changes. Everything else belongs in satellites. This design principle prevents the DWH dimension from becoming a sprawling, fragile, slow-to-query monster table.

Reference Data Survivorship: When Systems Disagree on Definitions

When SAP uses country code "US", Salesforce uses "United States", and Shopify uses "USA", the data warehouse must harmonize these into a single canonical definition. Reference data survivorship requires identifying the master source for each reference domain and mapping all others to it.

Reference data is the invisible glue holding multi-system analytics together. When it's wrong, every join is wrong. A mismatched country code in one lookup table can silently invalidate millions of rows downstream.

ShaDey Example: Order #4500001234 is placed from "United States" (Shopify). When it lands in SAP, it arrives as LAND1="US". The DIM_COUNTRY lookup must translate both Shopify "United States" and SAP "US" to a canonical ISO code "US" (ISO 3166-1) so the order joins correctly to dimensional queries.

-- DIM_COUNTRY: Canonical reference definition with source mappings
CREATE TABLE DIM_COUNTRY (
  country_key INT PRIMARY KEY,
  country_iso_code CHAR(2) UNIQUE NOT NULL,  -- Master: ISO 3166-1 alpha-2 (US, GB, DE, JP)
  country_name VARCHAR(100),  -- Display name

  -- Source system mappings
  sap_land1_code VARCHAR(3),  -- SAP uses: "US", "GB", "DE" (sometimes matches ISO, sometimes not)
  salesforce_code VARCHAR(50),  -- Salesforce uses: "United States", "United Kingdom", "Germany" (full names)
  shopify_code VARCHAR(50),  -- Shopify: "United States", "USA", sometimes ISO ("US")
  ecommerce_platform_code VARCHAR(50),  -- Magento legacy codes

  -- Governance
  master_source VARCHAR(30),  -- Which system is authoritative? "ISO_STANDARD"
  is_active BOOLEAN,
  created_date DATE,
  updated_date DATE
);

-- Example data: Three sources, one canonical definition
INSERT INTO DIM_COUNTRY VALUES
  (1, 'US', 'United States', 'US', 'United States', 'USA', 'United States', 'ISO_STANDARD', TRUE, '2024-01-01', '2026-04-01'),
  (2, 'GB', 'United Kingdom', 'GB', 'United Kingdom', 'UK', 'United Kingdom', 'ISO_STANDARD', TRUE, '2024-01-01', '2026-04-01'),
  (3, 'DE', 'Germany', 'DE', 'Germany', 'DE', 'Germany', 'ISO_STANDARD', TRUE, '2024-01-01', '2026-04-01'),
  (4, 'JP', 'Japan', 'JP', 'Japan', 'Japan', 'JP', 'ISO_STANDARD', TRUE, '2024-01-01', '2026-04-01');
      

Reference Data Governance: Who Owns the Mapping?

Master Source Selection: ISO country codes are international standards. All source systems map to ISO. If SAP adds a new country before Shopify, the ETL still maps it correctly via DIM_COUNTRY.
Mapping Maintenance: When Shopify changes how it represents "United Kingdom" (UK → GB), the mapping is updated once in DIM_COUNTRY. All downstream queries automatically use the new canonical value.
New Code Handling: If a source system introduces an unmapped code (e.g., Shopify adds "Palestine" as "PS"), ETL should flag it as "UNMAPPED_CODE" and require manual resolution before loading. Never silently null out unmapped codes.
Validation Query: Run a periodic audit to find source values with no DIM_COUNTRY mapping. This catches upstream schema changes or new enum values.

Rule 1: Canonical Definition First: Choose one master representation (ISO codes for countries, 3-letter airport codes for locations, standardized UoM for units). All sources map to it.
Rule 2: Document Precedence: If Salesforce and SAP disagree on which customer is in which region, document the tie-breaker rule. Example: "SAP LAND1 is authoritative for tax/billing; Salesforce is advisory for marketing."
Rule 3: Versioned Mappings: When a mapping changes (Shopify swaps UK to GB), timestamp the change. Queries can ask "Show 2024 orders by country using 2024 mappings" vs. "Show 2024 orders using today's mappings."

ShaDey Reference Data: Standardized Across All Systems

-- Example: Find all orders placed in the United States, regardless of source system representation
SELECT
  o.order_id,
  o.order_source,  -- 'SAP' | 'SHOPIFY' | 'MAGENTO'
  o.order_date,
  CASE
    WHEN o.order_source = 'SAP' THEN CONCAT('SAP.LAND1=', o.source_country_code)
    WHEN o.order_source = 'SHOPIFY' THEN CONCAT('Shopify.country=', o.source_country_code)
    ELSE o.source_country_code
  END AS source_representation,
  dc.country_iso_code,
  dc.country_name
FROM FACT_ORDERS o
JOIN DIM_COUNTRY dc ON TRUE
  AND (
    (o.order_source = 'SAP' AND o.source_country_code = dc.sap_land1_code)
    OR (o.order_source = 'SHOPIFY' AND o.source_country_code = dc.shopify_code)
  )
WHERE dc.country_iso_code = 'US'
ORDER BY o.order_date;
      

DIM_COUNTRY Reference Data Canonical Definition

Reference data is the invisible glue. When SAP says "US", Salesforce says "United States", and Shopify says "USA", a poorly designed lookup forces analysts to write three different queries for the same logical question. A well-designed DIM_COUNTRY with explicit source mappings lets every query use "WHERE country_iso_code = 'US'" regardless of where the order came from. Reference data survivorship rules prevent silent join failures that corrupt downstream metrics.

Single-system traditional DWH; simple ETL Hash (MD5/SHA-256) 16-32 bytes No Yes Data Vault, ELT, parallel loads; deterministic matching UUID/GUID 16 bytes No Yes Distributed systems, no coordination needed Range Allocation 8 bytes Yes Yes Multi-source ETL; per-system key ranges

ShaDey Inc. Technical Key Strategy: Hash (MD5)

ShaDey uses MD5 hashing of the business key for all dimensions. This enables load-order-independent reconciliation: if two ETL runs load the same SAP MATNR, they generate the same product_key hash. The approach supports parallel source ingestion (SAP, CRM, POS) without coordination.

-- Hash-based surrogate key generation
CREATE TABLE DIM_PRODUCT (
  product_key VARCHAR(32) PRIMARY KEY,  -- MD5 hash of business key
  product_id VARCHAR(10) UNIQUE NOT NULL,  -- MATNR
  product_name VARCHAR(100),
  -- ... attributes ...
);

-- Function to generate hash key
-- Hash the business key deterministically
-- SQL example: MD5(CONCAT('PRODUCT_', product_id))
-- PySpark example:
--   import hashlib
--   df = df.withColumn('product_key', F.md5(F.concat(F.lit('PRODUCT_'), F.col('product_id'))))

INSERT INTO DIM_PRODUCT (product_key, product_id, product_name)
VALUES (
  LOWER(MD5(CONCAT('PRODUCT_', '654321'))),
  '654321',
  'Organic Green Tea'
);

-- Query to verify hash consistency across loads
SELECT
  product_id,
  LOWER(MD5(CONCAT('PRODUCT_', product_id))) AS computed_key,
  product_key AS stored_key,
  CASE
    WHEN LOWER(MD5(CONCAT('PRODUCT_', product_id))) = product_key THEN 'MATCH'
    ELSE 'MISMATCH'
  END AS validation
FROM DIM_PRODUCT
ORDER BY product_id;

      

When to Use Each Strategy

Sequence (Auto-increment): Use when ETL is single-threaded, centralized, and load order is predictable. Common in traditional data warehouses with nightly batch loads from one source. Trade-off: Not reproducible across reloads.

Hash: Use for deterministic, reproducible keys in modern ELT/cloud environments. Data Vault methodology mandates hash keys. Enables parallel independent loads (SAP loads to table A, CRM loads to table B, both generating same hash keys). Trade-off: Hash collisions extremely rare but require explicit handling; keys are larger (16-32 bytes vs. 8).

UUID/GUID: Use in highly distributed systems (microservices, federated warehouses) where no central coordination exists. Trade-off: Large (16 bytes), unordered, not sortable for pagination or range queries.

Range Allocation: Use when multiple source systems feed the warehouse and you want to avoid hash computation overhead. Pre-allocate key ranges: SAP gets 1-1,000,000; CRM gets 2,000,000-3,000,000. Each source team owns their range. Trade-off: Requires centralized governance; source changes require reallocation.

Key Strategy Selection

ShaDey chose hash keys because: (1) reproducibility across partial reloads, (2) parallel safe for multi-source ingestion, (3) easy reconciliation across environments (dev/staging/prod generate identical keys). If ShaDey's ETL were strictly sequential and never reloaded, sequences would be simpler. If ShaDey were globally distributed with 100 independent data teams, UUIDs would be mandatory.

13

BI Consumption Safety Patterns

A well-designed star schema can still cause BI mishaps. This section covers patterns that make fact tables safe for analyst self-service without requiring a data engineer to validate every query.

Semantic Clarity: Naming Conventions

Use consistent, unambiguous column naming. Prefix dimensions with _key, measures with _amount or _quantity, flags with is_ or has_.

Pattern	Meaning	Example	Anti-Pattern
*_key	Foreign key to dimension	store_key, product_key	store_id, product
*_amount	Monetary measure	sales_amount, discount_amount	sales, discount
*_quantity	Unit count	quantity_sold, units_received	qty, count
is_*	Boolean flag	is_weekend, is_holiday	weekend_flag, holiday

Aggregate Awareness Pattern

Create pre-aggregated fact tables for common queries (daily, monthly). Tag dimensions as "denormalized for query speed" vs. "core reference." Use conformed dimensions across all aggregations.

Drill-Path Safety

Avoid fan-out at fine grains. If analyst drills from quarterly summary to daily detail on a many-to-many relationship (product category to transaction), results become unreliable. Always drill along conformed dimension hierarchies.

BI Safety at ShaDey

All ShaDey BI models enforce strict naming conventions (_key, _amount, _quantity, is_*), use conformed dimensions (DIM_DATE, DIM_PRODUCT, DIM_STORE), and document drill paths that are safe. Analysts cannot drill from FACT_SALES (daily transaction grain) to a product-store-category hierarchy and sum; they must use pre-aggregated FACT_MONTHLY_SALES or explicitly apply grain-aware filtering.

14

Neo4j Graph for Dimensional Model Lineage

Model your entire star schema as a graph in Neo4j to track lineage: which sources feed which facts, which dimensions are shared, which transformations apply. This section provides complete Cypher code.

Graph Model: Nodes and Relationships

Nodes: Fact tables, Dimension tables, Source tables, Attributes, Transformations. Relationships: FEEDS_INTO, SHARED_BY, ATTRIBUTES, TRANSFORMS.

-- Cypher: Create lineage graph -- Node: Fact table CREATE (fact_sales:FactTable { name: 'FACT_DAILY_SALES', grain: 'store + product + date', row_count: 14000000 }); -- Node: Dimension table CREATE (dim_store:DimensionTable { name: 'DIM_STORE', role: 'conformed' }); -- Relationship: FEEDS_INTO MATCH (src:SourceTable {name: 'LIPS'}), (fact:FactTable {name: 'FACT_DAILY_SALES'}) CREATE (src)-[:FEEDS_INTO {transform: 'aggregate by date'}]->(fact); -- Query: Which sources feed FACT_DAILY_SALES? MATCH (src)-[:FEEDS_INTO]->(fact:FactTable {name: 'FACT_DAILY_SALES'}) RETURN src.name;

Query: Shared Dimensions

-- Which dimensions are shared across multiple facts? MATCH (dim:DimensionTable)-[:SHARED_BY]->(fact:FactTable) RETURN dim.name, COUNT(fact) AS fact_count ORDER BY fact_count DESC;

Lineage Benefits

A Neo4j lineage graph enables impact analysis: "If I change DIM_PRODUCT category hierarchy, which facts are affected?" This prevents silent data quality issues from cascading across reports.