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

Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App © 2008-2017.
If you may any questions please contact us: