List of Examples

Chapter 1: Basic Components

Example 1.1: Creating dwid user id

Example 1.2: Creating dw and source databases

Example 1.3: Creating data warehouse tables

Example 1.4: Generating customer surrogate key values

Example 1.5: Inserting more customers

Chapter 2: Dimension History

Example 2.1: Applying SCD1 to the customer names in customer_dim

Example 2.2: Creating and loading the customer_stg table

Listing 2.3: Applying SCD2 to product_name and product_category in the product_dim table

Example 2.4: Creating the product_stg table

Example 2.5: Loading products to its staging table

Chapter 3: Measure Additivity

Example 3.1: Inserting data to demonstrate fully-additive measures

Example 3.2: Querying across all dimensions

Example 3.3: Querying across the date, product, and order

Example 3.4: Querying across the date, customer, and order

Example 3.5: Querying across the date and order

Chapter 4: Dimensional Queries

Example 4.1: Script for that adds data for testing dimensional queries

Example 4.2: Daily Aggregation

Example 4.3: Annual aggregation

Example 4.4: Specific query (monthly storage product sales)

Example 4.5: Specific query (quarterly sales in Mechanicsburg)

Example 4.6: Inside-out - Monthly Product Performer

Example 4.7: Inside-out (loyal customer)

Chapter 5: Source Extraction

Example 5.1: Push CDC sales orders

Example 5.2: Creating the sales_order table

Example 5.3: Data for testing Push mode

Chapter 6: Populating the Date Dimension

Example 6.1: Stored procedure to pre-populate the date dimension

Example 6.2: Daily date population

Example 6.3: Loading dates from the source

Example 6.4: Adding more dates from additional sales orders

Chapter 7: Initial Population

Example 7.1: DW initial population

Example 7.2: Script for truncating the tables

Example 7.3: Sales orders for testing initial population

Example 7.4: Query to confirm the sales orders are loaded correctly

Chapter 8: Regular Population

Example 8.1: Daily dw regular population

Example 8.2: Adding sales orders

Chapter 9: Regular Population Scheduling

Example 9.1: DW regular population batch

Chapter 10: Adding Columns

Example 10.1: Adding new columns to the customer dimension

Example 10.2: Adding the order_quantity column

Example 10.3: Revised daily DW regular population

Example 10.4: Adding the order_quantity column to the sales_order table

Example 10.5: Adding nine sales orders with order quantities

Chapter 11: On-Demand Population

Example 11.1: Promotion indicator

Example 11.2: Populating the promotion indicator

Example 11.3: Creating the promotion staging table

Chapter 12: Subset Dimensions

Example 12.1: Implementing the month roll-up dimension

Example 12.2: The revised date pre-population script

Example 12.3: PA customers

Example 12.4: Non-PA customers

Example 12.5: The revised daily DW regular population

Example 12.6: Adding two customers

Chapter 13: Dimension Role Playing

Example 13.1: Adding the request_delivery_date_sk column

Example 13.2: The revised daily DW regular population

Example 13.3: Adding the request_delivery_date column to the sales_order table

Example 13.4: Adding three sales orders with request delivery dates

Example 13.5: Daily sales summary

Example 13.6: Creating date views

Example 13.7: Database view role playing

Chapter 14: Snapshots

Example 14.1: Creating monthly_sales_order_fact

Example 14.2: Populating month_end_sales_order_fact

Example 14.3: Modifying the sales order table

Example 14.4: Adding four date surrogate keys

Example 14.5: Creating four date views

Example 14.6: The revised daily DW regular population

Example 14.7: Adding two sales orders

Example 14.8: Adding three sales orders with Allocate and/or Packing dates

Example 14.9: Sales orders with Allocate and/or Packing dates

Chapter 15: Dimension Hierarchies

Example 15.1: A grouping query

Example 15.2: A drilling query

Chapter 16: Multi-Path and Ragged Hierarchies

Example 16.1: Adding the campaign_session column

Example 16.2: Creating the campaign_session_stg table

Example 16.3: Campaign session population

Example 16.4: Quarter path drilling query

Example 16.5: Drilling the campaign session path

Example 16.6: Ragged campaign session

Example 16.7: Nullifying the campaign_session column

Chapter 17: Degenerate Dimensions

Example 17.1: Degenerating order dimension

Example 17.2: Revised daily DW regular population

Example 17.3: Sales orders for testing degeneration

Chapter 18: Junk Dimensions

Example 18.1: Pre-populating the sales_order_attribute_dim table

Example 18.2: Adding sales_order_attribute_sk

Example 18.3: Revised daily DW regular population

Example 18.4: Adding Sales Order Attributes in the sales_order table

Example 18.5: Adding eight junk sales orders

Example 18.6: An example attributes analysis

Chapter 19: Multi-Star Schemas

Example 19.1: Creating the third star’s tables

Example 19.2: Creating the factory_master table

Example 19.3: Factory_dim initial population

Example 19.4: Creating the factory staging table

Example 19.5: Creating the daily production table

Example 19.6: Production regular population

Example 19.7: Factories in the factory_master source table

Example 19.8: Daily production data

Chapter 20: Non-straight Sources

Example 20.1: Creating a new staging table for the non-straight campaign population

Example 20.2: Revised campaign population script

Example 20.3: Removing campaign

Chapter 21: Factless Facts

Example 21.1: Creating the product_count_fact table

Example 21.2: Creating the product_launch_date view

Example 21.4: The script for initially populating the product count

Example 21.5: The revised daily DW regular population script

Chapter 22: Late Arrival Facts

Example 22.1: Adding the entry_date column

Example 22.2: The revised daily DW regular population

Example 22.3: Revised Month End Sales Order population

Example 22.4: Adding two sales orders

Chapter 23: Dimension Consolidation

Example 23.1: Creating the zip_code_dim table

Example 23.2: Creating the zip_code_stg table

Example 23.3: Zip_code_dim population

Example 23.4: The revised sales_order_fact script

Example 23.5: production_fact with zip_code_dim intial loading

Example 23.6: Revised daily population

Example 23.7: Adding two sales orders

Example 23.9: The revised production regular population script

Example 23.10: Adding three daily production records

Chapter 24: Accumulated Measures

Example 24.1: Creating the month_end_balance_fact table

Example 24.2: Month end balance initial population

Example 24.3: January month end balance regular population script

Example 24.4: Month end balance regular population for all months except January

Example 24.6: Across products

Example 24.7: Across months

Chapter 25: Band Dimensions

Example 25.1: Creating the band dimension

Example 25.2: Creating the annual tables

Example 25.3: The initial population script

Example 25.4: The revised regular population script