Chapter 4: Designing a Data Warehouse

📚 Chapter 4 – Designing a Data Warehouse

1️⃣ Relational OLAP: Warehouse vs. Data Mart

A Data Warehouse (DW) is a centralized database optimized for analytical querying and information extraction rather than daily transactional processing.

  • Data Warehouse: Encompasses multiple enterprise business processes across an organization.
  • Data Mart: A specialized subset of a data warehouse focused on a single business process (e.g., Sales, Inventory).
  • Process vs. Department: Data marts should be architected around business processes rather than specific corporate departments. Process-driven marts are reusable across the enterprise (e.g., both Accounting and Logistics query Sales), preventing siloed and conflicting data copies.

2️⃣ Core Architectural Components: Facts & Dimensions

Measures & Granularity

  • Measures: Numeric, quantitative data representing measurable events (e.g., SalesQty, ActualSalesPrice)

  • Granularity: The lowest atomic level of detail represented in a fact table. Storing data at the atomic grain provides maximum reporting flexibility and drill-down capability.

  • Non-Measure Values: Descriptive properties (such as StandardRetailPrice in a product catalog) describe an entity rather than an individual transaction event and belong in dimension tables.

Fact Tables

Fact tables store the numeric measurements and foreign keys referencing dimensional entities.

  • Denormalization: Unlike normalized OLTP systems, fact tables collapse related transactional entities (e.g., merging Sales and SalesLineItems into a single FactSales table) to simplify querying and avoid complex joins.

  • Data Type Simplification: Proprietary vendor types (like SQL Server’s money) are converted to standard, interoperable types such as decimal(18, 4).

  • Composite Primary Keys: Fact tables typically enforce row uniqueness through a composite primary key consisting of the date key and associated dimensional keys.

Dimension Tables

Dimension tables contain textual contexts, filters, and grouping labels that answer “who, what, where, and when”.

  • At a minimum, every dimension table contains a Key Column (surrogate identifier) and a Name Column (human-readable label).

  • Descriptive textual columns should use standard Unicode (nvarchar) to ensure consistent data representations across platforms.

3️⃣ Schema Topologies: Star vs. Snowflake

The structural relationship between fact tables and their supporting dimensions defines the schema pattern:

Architecture Structural Pattern Normalization & Redundancy Join Complexity Performance Profile
Star Schema Single-tier ring of dimensions directly attached to the fact table. Denormalized; contains repeated text values across rows. Low; requires fewer SQL joins to pull reports. Optimal for small to moderate dimensions; reduces query processing overhead.
Snowflake Schema Multi-tier ring where dimensions link out to secondary normalized sub-tables. Normalized; removes redundant values into separate lookup tables. High; requires cascading joins across dimension tiers. Minimizes disk storage; optimal for very large dimensions containing millions of rows.

Best Practice: Prefer the Star Schema for simplicity and report performance. Use Snowflake structures only when normalizing massive dimension tables provides measurable performance or operational benefits.

4️⃣ Dimensional Patterns & Structural Classifications

Standard Dimensions

Single- or multi-table dimensions attached directly to the central fact table containing surrogate keys and descriptive hierarchies (e.g., DimTitles, DimStores).

Fact (Degenerate) Dimensions

Dimensional attributes stored directly within the fact table rather than inside an external dimension entity.

  • Typical examples: Transaction IDs, invoice numbers, or purchase order numbers (SalesId, SalesLineItemId).

  • Creating a separate table would yield a 1:1 relationship with the fact table, introducing unnecessary join overhead.

Date and Time Dimensions

  • Granularity Control: Tracking date and time down to the second in a single dimension creates over 31 million rows per year, degrading query execution.

  • Separation of Concerns: Separate the calendar dates (DimDates, tracking holidays, fiscal periods, weekdays) from intraday clock times (DimTimes, tracking hour, minute, shifts).

  • Surrogate Date Keys: When foreign key constraints cannot compare mismatched datetime stamps, an integer surrogate key (YYYYMMDD) or explicit ETL date casting ensures referential integrity.

Role-Playing Dimensions

A single dimension table referenced multiple times by the same fact table under different functional contexts.

  • Example: DimDates linked to FactSales via OrderDateKey, DueDateKey, and ShipDateKey.

Junk Dimensions

Consolidates multiple low-cardinality flags, indicators, and miscellaneous codes into a single dimension table.

  • Instead of creating individual tables for boolean indicators (e.g., WasOnSaleFlag, EmployeeWasTempFlag), combine all unique combinations into a single table (e.g., DimMiscInformation) with a single surrogate key linking to the fact table.

Parent-Child Dimensions

Dimensions that model hierarchical, variable-depth relationships using a recursive self-referencing foreign key.

  • Example: DimEmployees mapping ManagerId back to EmployeeId to handle organizational reporting structures with uneven depths.

Many-to-Many Dimensions: Direct vs. Indirect

  • Direct Many-to-Many: Each fact row corresponds to exactly one pairing of dimensional entities for that specific event (e.g., a specific book sold in a specific store). Both surrogate keys reside directly in FactSales.

  • Indirect Many-to-Many: A single entity in one dimension relates simultaneously to multiple entities in another (e.g., a book written by multiple authors).

    • Placing AuthorId directly in FactSales forces the fact row to duplicate for each author, causing double counting during volume and revenue aggregations.

    • Solution: Use an intermediate Bridge Table (factless fact table such as FactTitleAuthors) connecting DimTitles and DimAuthors while leaving FactSales aggregated strictly at the title grain[cite: 10].

Conformed Dimensions

Shared dimension tables that have consistent data structures and definitions across multiple data marts.

  • Example: DimDates and DimTitles shared between FactSales and FactInventory.

  • Enables cross-functional reporting across different enterprise business processes using Ralph Kimball’s Bus Architecture.

5️⃣ Surrogate Keys vs. Natural Keys

  • Natural Key: The original identifier assigned by the transactional source system (e.g., varchar or char codes like stor_id or title_id).

  • Surrogate Key: An artificial, system-generated integer (commonly using an IDENTITY property) created during the ETL process.

  • Advantages:

    • Decouples the analytical system from operational schema migrations.
    • Optimizes join performance by using compact integer values.
    • Allows integration of data from disparate OLTP sources that share conflicting natural keys.
    • Provides the fundamental mechanism required to track historical changes.

6️⃣ Slowly Changing Dimensions (SCD)

Dimensions evolve over time as real-world attributes change (e.g., an author changes their legal surname).

SCD Type Change Mechanism Historical Tracking Implementation Details
Type I Overwrite existing attribute values in-place. No history retained. Historical reports reflect updated values retroactively. Simple updates; used when correcting errors or when historical states carry no business value.
Type II Insert a new row with a new surrogate key for each change. Complete historical trail. Accurate point-in-time reporting. Requires tracking columns: StartDate, EndDate, and an IsCurrent status flag.
Type III Add a separate column to store the previous attribute value. Limited history (usually current vs. immediately preceding value). Uses paired columns such as AuthorCurrentName and AuthorPreviousName.

7️⃣ Hands-On Exercise: Schema Mapping for Publication Industries

In this exercise, you review the schema transition mapping from the transactional database (pubs) to the analytical reporting schema (DWPubsSales).

Target Relational Architecture (DWPubsSales)

  • Fact Entity: FactSales (Grain: One row per title per sales order line item).

  • Standard Dimensions (Star):

    • DimTitles (TitleKey, TitleId, TitleName, TitleType, PublisherId, TitlePrice, PublishedDate).

    • DimStores (StoresKey, StoreId, StoreName)

    • DimDates (DateKey, DateName, DateMonth, DateYear)

  • Bridge Architecture (Indirect Many-to-Many):

    • DimAuthors (AuthorKey, AuthorId, AuthorName)

    • FactTitleAuthors (TitleKey, AuthorKey, AuthorOrder)

Exercise Procedure

  1. Open Publications Industries BISolution Worksheets.xlsx (from C:\_BISolutions\PublicationsIndustries\).

  2. Navigate to the Data Warehouse worksheet.

  3. Verify the mappings from the transactional pubs source types to the DWPubsSales destination types:

    • String lengths standardized to nvarchar(50).

    • Monetary measures cast from money to decimal(18, 4).

    • Natural identifiers retained as secondary lookup columns (StoreId, TitleId) alongside system-generated surrogate keys (StoresKey, TitleKey).

References & Further Reading

  • Primary Course Text:
    Root, R., & Mason, C. (2012). Chapter 4: Designing a Data Warehouse. In Pro SQL Server 2012 BI Solutions (pp. 131–159).
  • Dimensional Modeling Reference:
    Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3rd ed.). John Wiley & Sons.
  • Enterprise Warehouse Architecture:
    Inmon, W. H. (2005). Building the Data Warehouse (4th ed.). John Wiley & Sons.