📚 Chapter 5 – Physical Implementation of the Data Warehouse

1️⃣ Environment & Administrative Architecture

Implementing the physical data warehouse requires an understanding of SQL Server Management Studio (SSMS) administrative configuration and connectivity paradigms:

• Administrative Privileges: Running SSMS under elevated administrative permissions prevents permission-denied errors when creating physical database files, modifying transaction logging states, or attaching system diagrams.

• Connection Addressing & Protocols:

  • localhost / 127.0.0.1: Relies on the modern TCP/IP stack.

  • (local): Resolves via the legacy NetBIOS protocol (requires parentheses).

  • Named Instances: Must follow the explicit syntax <ServerName>\<InstanceName> (e.g., localhost\SQL2025).

• SSMS vs. Solution Environments: Database management interfaces maintain independent database engine sessions for Object Explorer treeviews versus individual active query script tabs.

---

2️⃣ Storage Allocation, Sizing & Recovery Models

Data and Log File Proportions

A relational data warehouse physically separates table records (.mdf / .ndf) from transaction entries (.ldf):

• Transactional (OLTP) Proportions: OLTP workloads require transaction log allocations of roughly 25% of the data file size to sustain constant inserts, updates, and deletes.

• Analytical (OLAP) Proportions: Data warehouses primarily process heavy batch reads; steady-state reports generate zero transaction logging. Consequently, log space can be budgeted at approximately 10% of total data storage.

Transaction Log Recovery Models

Selecting the correct database recovery model directly impacts ETL throughput and disk volume consumption:

Recovery Model	Logging Mechanics	Truncation Behavior	Warehouse Suitability
Full	Every row insert, update, and delete is written to the log.	Log entries persist until a formal transaction log backup occurs.	Overkill for basic OLAP marts; rapid batch loads can quickly exhaust hard drive capacity.
Simple	Minimally logs operations and automatically clears space at checkpoints.	Automatically truncates inactive log portions without manual intervention.	Best for development or instances where warehouses can be rebuilt entirely from OLTP backups.
Bulk-Logged	Minimizes transaction log footprint during high-volume batch imports.	Requires scheduled log backups to keep the log file from expanding.	Optimal for enterprise data warehouse batch cycles, balancing maximum ETL speed with point-in-time recovery.

Backup Strategy Notice: Because Slowly Changing Dimensions (Type II) track historical records that may no longer exist in updated OLTP systems, production data warehouses must maintain independent backup schedules rather than relying on source reloading.

How to Set the Log Recovery Models

• In SQL Server, you can set the database recovery model using either the SQL Server Management Studio (SSMS) graphical interface or a Transact-SQL (T-SQL) command.

Method 1: Using SSMS GUI:

When Creating a New Database

• In SSMS Object Explorer, right-click the Databases folder and choose New Database…

• In the New Database dialog, select the Options page from the left-hand pane.

• Locate the Recovery model: drop-down menu and select Full, Simple, or Bulk-Logged. Click OK to create the database.

On an Existing Database:

• In SSMS Object Explorer, expand Databases, right-click your database (e.g., DWPubsSales), and select Properties.

• In the Database Properties window, click the Options page on the left.

• In the right-hand panel, select the desired option from the Recovery model: drop-down list (Simple, Full, or Bulk-Logged). Click OK to apply the change immediately.

Method 2: Using Transact-SQL (T-SQL)

• You can alter the recovery model instantly via a query window using the ALTER DATABASE statement:

USE [master];
GO

-- 1. Set to BULK_LOGGED (common during high-volume ETL batch runs)
ALTER DATABASE [DWPubsSales] SET RECOVERY BULK_LOGGED;
GO

-- 2. Set to SIMPLE (clears log automatically at checkpoints; best for dev/lab)
ALTER DATABASE [DWPubsSales] SET RECOVERY SIMPLE;
GO

-- 3. Set to FULL (standard for OLTP; retains log chain until log backups occur)
ALTER DATABASE [DWPubsSales] SET RECOVERY FULL;
GO

How to Check the Current Recovery Model with SQL

• You can check the current recovery model of a database using the following query:

SELECT 
    name AS [DatabaseName],
    recovery_model_desc AS [RecoveryModel]
FROM sys.databases
WHERE name = 'DWPubsSales';

---

3️⃣ Physical Database Creation Script (DWPubsSales)

• Execute the following DDL script in SSMS to instantiate the warehouse database container, assign database ownership, and set bulk-logged processing:

USE [master];
GO

-- 1. Close active connections and drop existing instances
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DWPubsSales')
BEGIN
    ALTER DATABASE [DWPubsSales] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [DWPubsSales];
END
GO

-- 2. Allocate storage files (Adjust directory path to target drive)
CREATE DATABASE [DWPubsSales] ON PRIMARY
(
    NAME = N'DWPubsSales_Data',
    FILENAME = N'C:\_BISolutions\PublicationsIndustries\DWPubsSales\DWPubsSales.mdf',
    SIZE = 10MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 10MB
)
LOG ON
(
    NAME = N'DWPubsSales_Log',
    FILENAME = N'C:\_BISolutions\PublicationsIndustries\DWPubsSales\DWPubsSales_log.ldf',
    SIZE = 1MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 10MB
);
GO

-- 3. Set standard administrative ownership and bulk loading recovery
EXEC [DWPubsSales].dbo.sp_changedbowner @loginame = N'sa', @map = false;
GO
ALTER DATABASE [DWPubsSales] SET RECOVERY BULK_LOGGED;
GO

4️⃣ Dimensional Schema & Bridge Implementation

• The dimensional model implements surrogate identity keys, standardized Unicode types (nvarchar), standard numeric definitions (decimal(18, 4)), and conformed relational constraints:

The Concept of Surrogate Keys

• A surrogate key is an artificial, system-generated identifier—typically an auto-incrementing integer (such as an IDENTITY column in SQL Server)—created during the ETL process specifically for the data warehouse. It has no business meaning outside the warehouse system.

• It contrasts directly with a natural key (also called a business key), which is the identifier that naturally exists in the source transactional (OLTP) database (such as a string/character code like:

  pub_id = 0736 or title_id = BU1032).

Why Surrogate Keys Are Used Instead of Natural Keys:

• Decoupling from Source Changes: If an operational source system changes its key formatting, renames an ID, or recycles IDs, the data warehouse remains unaffected.

• Handling Multiple Data Sources: When integrating data from different transactional databases, two completely different products or stores might share the exact same natural key (e.g., both use ID 101). Surrogate keys allow each entity to have a globally unique identifier within the warehouse.

• Tracking Historical Changes (Slowly Changing Dimensions - Type II): If an author or store attribute changes over time, a Type II SCD creates a new row with a new surrogate key while the natural business key remains the same. This allows historical transactions to stay linked to the old record while new transactions link to the current record.

• Storage and Join Performance: In relational database engines, joining on compact 4-byte integers (INT) is much faster and requires less memory/disk space than joining on varying strings (VARCHAR, NVARCHAR, or alphanumeric codes).

USE [DWPubsSales];
GO

-- Dimensions
CREATE TABLE dbo.DimStores (
    StoreKey INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    StoreId NCHAR(4) NOT NULL,
    StoreName NVARCHAR(50) NOT NULL
);
GO

CREATE TABLE dbo.DimPublishers (
    PublisherKey INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    PublisherId NCHAR(4) NOT NULL,
    PublisherName NVARCHAR(50) NOT NULL
);
GO

CREATE TABLE dbo.DimAuthors (
    AuthorKey INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    AuthorId NCHAR(11) NOT NULL,
    AuthorName NVARCHAR(100) NOT NULL,
    AuthorState NCHAR(2) NOT NULL
);
GO

CREATE TABLE dbo.DimTitles (
    TitleKey INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    TitleId NVARCHAR(6) NOT NULL,
    TitleName NVARCHAR(100) NOT NULL,
    TitleType NVARCHAR(50) NOT NULL,
    PublisherKey INT NOT NULL,
    TitlePrice DECIMAL(18,4) NOT NULL,
    PublishedDateKey INT NOT NULL
);
GO

CREATE TABLE dbo.DimDates (
    DateKey INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Date DATETIME NOT NULL,
    DateName NVARCHAR(50) NULL,
    Month INT NOT NULL,
    MonthName NVARCHAR(50) NOT NULL,
    Quarter INT NOT NULL,
    QuarterName NVARCHAR(50) NOT NULL,
    Year INT NOT NULL,
    YearName NVARCHAR(50) NOT NULL
);
GO

-- Many-to-Many Bridge Table
CREATE TABLE dbo.FactTitlesAuthors (
    TitleKey INT NOT NULL,
    AuthorKey INT NOT NULL,
    AuthorOrder INT NOT NULL,
    CONSTRAINT PK_FactTitlesAuthors PRIMARY KEY CLUSTERED (TitleKey ASC, AuthorKey ASC)
);
GO

-- Central Fact Table
CREATE TABLE dbo.FactSales (
    OrderNumber NVARCHAR(50) NOT NULL,
    OrderDateKey INT NOT NULL,
    TitleKey INT NOT NULL,
    StoreKey INT NOT NULL,
    SalesQuantity INT NOT NULL,
    CONSTRAINT PK_FactSales PRIMARY KEY CLUSTERED (OrderNumber ASC, OrderDateKey ASC, TitleKey ASC, StoreKey ASC)
);
GO

-- Referential Constraints
ALTER TABLE dbo.DimTitles WITH CHECK ADD CONSTRAINT FK_DimTitles_DimPublishers 
FOREIGN KEY (PublisherKey) REFERENCES dbo.DimPublishers (PublisherKey);
GO
ALTER TABLE dbo.DimTitles WITH CHECK ADD CONSTRAINT FK_DimTitles_DimDates 
FOREIGN KEY (PublishedDateKey) REFERENCES dbo.DimDates (DateKey);
GO
ALTER TABLE dbo.FactTitlesAuthors WITH CHECK ADD CONSTRAINT FK_FactTitlesAuthors_DimAuthors 
FOREIGN KEY (AuthorKey) REFERENCES dbo.DimAuthors (AuthorKey);
GO
ALTER TABLE dbo.FactTitlesAuthors WITH CHECK ADD CONSTRAINT FK_FactTitlesAuthors_DimTitles 
FOREIGN KEY (TitleKey) REFERENCES dbo.DimTitles (TitleKey);
GO
ALTER TABLE dbo.FactSales WITH CHECK ADD CONSTRAINT FK_FactSales_DimStores 
FOREIGN KEY (StoreKey) REFERENCES dbo.DimStores (StoreKey);
GO
ALTER TABLE dbo.FactSales WITH CHECK ADD CONSTRAINT FK_FactSales_DimTitles 
FOREIGN KEY (TitleKey) REFERENCES dbo.DimTitles (TitleKey);
GO
ALTER TABLE dbo.FactSales WITH CHECK ADD CONSTRAINT FK_FactSales_DimDates 
FOREIGN KEY (OrderDateKey) REFERENCES dbo.DimDates (DateKey);
GO

5️⃣ How the Fact Table Is Constructed

• A fact table represents the numerical measurements (measures) and context of a specific business process or event (such as a customer buying books or taking inventory). It contains foreign keys that reference related dimension tables, which supply the descriptive attributes used for slicing, filtering, and analysis.

The construction of a fact table follows these core principles:

A. Determine the Grain

• Before creating columns, determine the grain—the exact atomic event represented by a single row in the fact table.

  • In FactSales, the grain is: one row per title per sales order line item.

B. Denormalize Operational Data

• In transactional OLTP databases, sales orders are split into two normalized tables: an order header (Sales) and line items (SalesLineItems or sales).

  • In a data warehouse, these entities are collapsed into a single fact table (FactSales).

  • While this repeats header values (like OrderNumber), it eliminates the need for expensive multi-table joins when running analytical aggregate queries.

C. Assemble the Two Column Types

A standard fact table contains only two fundamental types of columns:

• Foreign Key Columns (Dimensional Keys): Pointers containing the surrogate keys of all surrounding dimension tables that describe the context (e.g., OrderDateKey, TitleKey, StoreKey).

• Measure Columns (Numeric Metrics): Quantitative data that can be summarized or aggregated (e.g., SalesQuantity, actual sales price).

Degenerate (Fact) Dimensions: Transactional identifiers that do not join out to any dimension table (such as OrderNumber or invoice numbers) are kept directly inside the fact table as degenerate dimensions.

D. Define Composite Primary Keys and Clustered Indexes

• Because fact rows represent the intersection of several dimensional contexts for a particular transaction, uniqueness is enforced by combining the degenerate identifier and the foreign keys into a composite primary key:

CREATE TABLE dbo.FactSales (
    -- 1. Degenerate Dimension
    [OrderNumber]   NVARCHAR(50) NOT NULL, 
    
    -- 2. Foreign Keys (pointing to Dimension Surrogate Keys)
    [OrderDateKey]  INT NOT NULL,          
    [TitleKey]      INT NOT NULL,          
    [StoreKey]      INT NOT NULL,          
    
    -- 3. Measures (Additive Metrics)
    [SalesQuantity] INT NOT NULL,          

    -- Composite Primary Key & Clustered Index
    CONSTRAINT [PK_FactSales] PRIMARY KEY CLUSTERED 
    (
        [OrderNumber] ASC, 
        [OrderDateKey] ASC, 
        [TitleKey] ASC, 
        [StoreKey] ASC
    )
);

E. Handle Many-to-Many via Bridge (Factless Fact) Tables

• If an event has an indirect many-to-many relationship (such as one title having multiple authors), that relationship is not placed directly in the primary FactSales table. Doing so would duplicate the sales row for every author and cause double-counting of quantities in reports.

• Instead, a secondary bridge table (or factless fact table), such as FactTitlesAuthors, is constructed containing only the foreign keys to link the entities

CREATE TABLE dbo.FactTitlesAuthors (
    [TitleKey]    INT NOT NULL,
    [AuthorKey]   INT NOT NULL,
    [AuthorOrder] INT NOT NULL,
    CONSTRAINT [PK_FactTitlesAuthors] PRIMARY KEY CLUSTERED ([TitleKey] ASC, [AuthorKey] ASC)
);

• Example Walkthrough:

┌────────────────────────┐
                        │       DimStores        │
                        ├────────────────────────┤
                        │ PK  StoreKey           │
                        │     StoreId            │
                        │     StoreName          │
                        └───────────┬────────────┘
                                    │
                                    │ 1
                                    │
                                    │ ∞
┌────────────────────────┐      ┌───┴────────────────────┐
│        DimDates        │      │       FactSales        │
├────────────────────────┤      │     (Primary Fact)     │
│ PK  DateKey            ├──────┤────────────────────────┤
│     Date               │ 1  ∞ │ PK,FK  OrderNumber     │
│     DateName           │      │ PK,FK  OrderDateKey    │
│     ...                │      │ PK,FK  StoreKey        │
└────────────────────────┘      │ PK,FK  TitleKey        │
                                │        SalesQuantity   │ <── Measure (Atomic Grain)
                                └───┬────────────────────┘
                                    │ ∞
                                    │
               ┌────────────────────┘
               │ 1
               ▼
┌────────────────────────┐
│       DimTitles        │
├────────────────────────┤
│ PK  TitleKey           │
│     TitleId            │
│     TitleName          │
│     TitlePrice         │
│     ...                │
└──────────────┬─────────┘
               │ 1
               │
               │ ∞
┌──────────────┴─────────┐
│   FactTitlesAuthors    │
│     (Bridge Table)     │
├────────────────────────┤
│ PK,FK  TitleKey        │
│ PK,FK  AuthorKey       │
│        AuthorOrder     │
└──────────────┬─────────┘
               │ ∞
               │
               │ 1
┌──────────────┴─────────┐
│       DimAuthors       │
├────────────────────────┤
│ PK  AuthorKey          │
│     AuthorId           │
│     AuthorName         │
│     AuthorState        │
└────────────────────────┘

Concrete Data Walkthrough: Why the Bridge Prevents Double-Counting

• The Problem (Incorrect: Placing AuthorKey directly in FactSales)

Suppose Book T100 was co-authored by Author A and Author B. A customer purchases 1 copy in a single order line:

1. Incorrect Design (Direct Relationship Causing Double-Counting)

OrderNumber	TitleKey	AuthorKey	SalesQuantity
ORD-001	T100	A	1
ORD-001	T100	B	1

• Query by Author: Author A sold 1, Author B sold 1. (Looks fine).

• Query by Title / Overall Total: SUM(SalesQuantity) calculates 2 copies, which doubles the real transaction!

---

2. FactSales (Primary Fact Table – Correct Atomic Grain)

The primary fact records the event once at the atomic grain of the title sale:

FactSales (Grain: 1 row per title sold)

OrderNumber	TitleKey	StoreKey	OrderDateKey	SalesQuantity
ORD-001	T100	S1	20260929	1

3. FactTitlesAuthors (Bridge / Factless Fact Table)

Bridge linking titles to all contributing authors

TitleKey	AuthorKey	AuthorOrder
T100	A	1
T100	B	2

---

• 1. FactTitlesAuthors (Bridge / Factless Fact Table)

TitleKey	AuthorKey	AuthorOrder
T100	A	1
T100	B	2

• Total Sales Calculation: SELECT SUM(SalesQuantity) FROM FactSales evaluates accurately to 1.

• Author Allocation: Reporting tools and SSAS query FactSales joined through FactTitlesAuthors to distribute or filter credit across co-authors without distorting company-wide sales metrics.

6️⃣ Visual Studio Solution Organization

• To preserve database scripts alongside upcoming SSIS/SSAS components, archive structural DDL and recovery definitions within the solution explorer:

1. In Visual Studio, open PublicationsIndustries.sln.

2. Right-click the solution root > Add > New Solution Folder named DWPubsSales.

3. Right-click DWPubsSales > Add > Existing Item…

4. Import the physical schema creation script (Create DWPubsSales Script.sql), the administrative backup utility script, and initial baseline backup files.