Chapter 3 · Foundations

Dimensional Modelling

Before writing a single load, decide what one row of each table means. This chapter walks through the SBEcoLTDDW star schema: the course's tables, the columns the pipeline adds, and the modelling decisions behind each one.

Start with the grain

The grain is one sentence saying what a single fact row represents. Everything else follows from it.

Because the grain is the header and not the order line, product can't be a dimension of this table; you would need a line-level fact for that. And because OrderTotal is a plain amount, it is fully additive: you can sum it across days, customers and states and the result still means something.

Facts and dimensions

A quick bus matrix shows which dimensions each fact uses. It is the plan you extend when new facts arrive.

Keys: surrogate, alternate, smart and degenerate

• Surrogate key CustomerKey (IDENTITY): meaningless, owned by the warehouse. It has to be, because one source customer can have several versions, and each version needs its own key.
• Alternate (business) key CustomerAlternateKey: the source's CustomerID, used to match incoming rows to existing versions.
• Smart key DateKey = yyyymmdd (e.g. 20140423). For dates a readable key is the norm: it is stable, sortable and can be computed from the date without a lookup.
• Degenerate dimension SalesOrderNumber: an identifier with no attributes of its own, so it lives in the fact table. It doubles as the fact's business key for idempotent MERGEs, with a filtered unique index to enforce it.

The course DDL, and one fix

These are the course's Chapter 2 scripts as they run in the lab. One change was needed: Year was declared tinyint, which only holds 0–255, so any real year failed with an overflow.

-- Change: Dimension.Date.Year is smallint instead of tinyint (tinyint stops at 255, so real years fail).
USE SBEcoLTDDW;
GO

CREATE TABLE Dimension.Date (
    DateKey int NOT NULL,
    FullDate date NOT NULL,
    DayName nvarchar(9) NOT NULL,
    DayNumber tinyint NOT NULL,
    MonthName varchar(9) NOT NULL,
    MonthNumber tinyint NOT NULL,
    QuarterNumber tinyint NOT NULL,
    Year smallint NOT NULL      -- course: tinyint

    CONSTRAINT PK_Date PRIMARY KEY CLUSTERED (
        DateKey ASC
    )
);
GO

CREATE TABLE Dimension.Customer (
    CustomerKey int IDENTITY (1,1) NOT NULL,
    CustomerAlternateKey int NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FullName nvarchar(100) NOT NULL,
    City nvarchar(50) NOT NULL,
    State char(2) NOT NULL,
    Statename nvarchar(20) NOT NULL,
    Zip char(5) NOT NULL

    CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (
        CustomerKey ASC
    )
);
GO

USE SBEcoLTDDW;
GO

CREATE TABLE Fact.Orders (
    OrderID int IDENTITY (1,1) NOT NULL,
    DateKey int NOT NULL,
    CustomerKey int NOT NULL,
    OrderTotal money NOT NULL

    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (
        OrderID ASC
    )

);
GO


ALTER TABLE Fact.Orders WITH CHECK ADD CONSTRAINT FK_Fact_Orders_DateKey_Dimension_Date FOREIGN KEY(DateKey)
REFERENCES Dimension.Date (DateKey);
GO

ALTER TABLE Fact.Orders CHECK CONSTRAINT FK_Fact_Orders_DateKey_Dimension_Date;
GO

ALTER TABLE Fact.Orders WITH CHECK ADD CONSTRAINT FK_Fact_Orders_CustomerKey_Dimension_Customer FOREIGN KEY(CustomerKey)
REFERENCES Dimension.Customer (CustomerKey);
GO

ALTER TABLE Fact.Orders CHECK CONSTRAINT FK_Fact_Orders_CustomerKey_Dimension_Customer;
GO

What the pipeline adds, and why

The pipeline extends the course tables in place, in an idempotent deploy step that only adds what's missing:

/* deploy 05 - extend the course tables for the pipeline (idempotent)
   Dimension.Customer  + SCD Type 2 columns: ValidFrom, ValidTo, IsCurrent, RowHash, LoadBatchID
   Fact.Orders         + SalesOrderNumber (business key for idempotent MERGE), SourceModifiedDate, LoadBatchID
   Indexes that use the new columns are created with EXEC so this batch compiles before they exist. */
SET NOCOUNT ON;

IF COL_LENGTH(N'Dimension.Customer', N'ValidFrom') IS NULL
    ALTER TABLE Dimension.Customer ADD
        ValidFrom   datetime2(3) NOT NULL CONSTRAINT DF_Customer_ValidFrom DEFAULT ('1900-01-01'),
        ValidTo     datetime2(3) NULL,
        IsCurrent   bit NOT NULL CONSTRAINT DF_Customer_IsCurrent DEFAULT (1),
        RowHash     varbinary(32) NULL,
        LoadBatchID int NULL;

IF COL_LENGTH(N'Fact.Orders', N'SalesOrderNumber') IS NULL
    ALTER TABLE Fact.Orders ADD
        SalesOrderNumber   nvarchar(25) NULL,
        SourceModifiedDate datetime NULL,
        LoadBatchID        int NULL;

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Customer_AlternateKey')
    EXEC (N'CREATE NONCLUSTERED INDEX IX_Customer_AlternateKey
            ON Dimension.Customer (CustomerAlternateKey, ValidFrom)
            INCLUDE (ValidTo, IsCurrent, RowHash);');

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_Orders_SalesOrderNumber')
    EXEC (N'CREATE UNIQUE NONCLUSTERED INDEX UX_Orders_SalesOrderNumber
            ON Fact.Orders (SalesOrderNumber) WHERE SalesOrderNumber IS NOT NULL;');