Dimensional Modeling for the Data Warehouse Course

Course Code: MV 105
Course Abstract: This course provides participants with the skills necessary to design a successful data warehouse using multi-dimensional data modeling techniques. It is based on Ralph Kimball’s book The Data Warehouse Toolkit, Second Edition published in April, 2002.
Audience: This course is targeted at technical staff, team leaders and project managers who need to understand how to design a data warehouse using multi-dimensional data modeling techniques
Duration: 4 days
Learning Outcomes:

Upon completion of this course, the participant will be able to design a database warehouse using multi-dimensional techniques

Course Topics: Dimensional Modeling Primer
Operational Systems (OLTP)
Analytical Processing (OLAP)
Data Warehousing Requirements
Data Warehousing Team Responsibilities
Data Warehousing Components
Operational Source Systems
Data Staging Area
Extraction
Transformation
Cleaning
Conforming
Loading
Data Presentation Area
Data Access Tooling
Data Warehouse Terminology
Star Schema
On-Line Analytical Processing (OLAP)
Cubes
What Is Metadata?
Staging Meta Data
DBMS Meta Data
Data Access Tooling Meta Data
What Is a Fact?
What Is a Dimension?
Dimensional Modeling Myths
Avoiding Common Pitfalls

Retail Sales
Retail Sales Case Study
The Kimball 4 Step Design Process
Select the Business Process
Declare the Granularity of the Fact Table
Choose the Dimensions
Identify the Facts
Non-Additive Facts
Date Dimension
Product Dimension
Sales Amount and Quantity by Department Report
What Is a Drill Down?
What Is a Roll Up?
Location (Geographical or Store) Dimension
Promotion Dimension
What Is a Factless Fact Table?
What Is a Degenerate Dimension?
Star Schema Extensibility
What Is Snow Flaking?
Too Few or Too Many Dimensions
Surrogate Keys versus Natural Keys

Inventory
Inventory Periodic Snapshot Fact Table
Semi-Additive Facts
Enhanced Inventory Facts
Inventory Transaction Fact Table
Inventory Accumulating Snapshot Fact Table
Shared Common Dimensions
The Data Warehouse Bus Matrix
What Are Conformed Dimensions?
What Are Conforming Roll-Up Dimensions?
What Are Conforming Dimension Subsets?
What Are Conformed Facts?

Procurement
Procurement Case Study
Procurement Transaction-Grained Star Schema
Multiple versus Single Procurement Transaction-Grained Fact Tables
One Fact Table per Major Feeder System
What Are Slowly Changing Dimensions?
Type 1 Slowly Changing Dimension Strategy
Type 2 Slowly Changing Dimension Strategy
Type 3 Slowly Changing Dimension Strategy
Type 4 Slowly Changing Dimension Strategy
Predictable Changes with Multiple Version Overlay Strategy
Unpredictable Changes with Single Version Overlay Strategy

Order Management
Order Management Bus Matrix
Order Product Transaction Fact
Role-Playing Dimensions (Using Views on Dimensions)
Common Product Dimensions
Product Master to Dimension Mapping
Customer Ship-To and Bill-To Dimension
An Outrigger Address Dimension
Deal Dimension
Order Number Degenerate Dimension
Junk Dimension
Designing for Multiple Currencies
Allocating Facts to Lower Granularities
Shipment Invoice Line Item Fact
Order Fulfillment Pipeline
Order Fulfillment Accumulating Snapshot Fact
Designing for Multiple Units of Measure
Three Types of Fact Table Comparisons
Introduction to Real-Time Data Warehouse Design

Customer Relationship Management
Customer Relationship Management Overview
Operational and Analytic CRM
Buy or Build Your CRM System
Customer Dimension
Name and Address Quality Issues
Customer Dimension Columns
Date Dimension Outrigger
Customer Segmentation Columns
Adding a County Outrigger Dimension
Designing Dimension Outriggers
Large and Rapidly Changing Customer Dimension
Tuning Rapidly Changing Dimensions
Mini-Dimension Characteristics
Customer Mini-Dimension ERD
Variable-Width Dimension Columns
Fixed-Depth Customer Hierarchies
Variable-Depth Customer Hierarchies
Parent Subsidiary Company Hierarchies
Parent Subsidiary Bridge Table
Parent Subsidiary ERD
Parent Subsidiary Revenue Select Statements

Accounting
Finance and Accounting Data Warehouses
General Ledger Periodic Snapshot
General Ledger Periodic Snapshot Schema
General Ledger Journal Entry Transaction Fact
Annual Budget Schema
Budget Variance Schema
OLAP Analytic Solutions

Human Resources Management
Employee Transaction Schema
Employee Transaction Dimension
Employee Periodic Snapshot Schema
Audit Dimension
Employee Skill Outrigger Dimension
Employee Skill Outrigger Select Statement
Another Employee Skill Outrigger Solution
Another Employee Skill Outrigger Select Statement
A Third Employee Skill Outrigger Solution
Analyzing Survey Data with Star Schemas

Financial Services
Banking Data Warehouse Requirements
Monthly Account Balance Periodic Snapshot
Dimension Table Check List
Account Household Periodic Snapshot
The Fact Table as an Associative Entity
Customer Account Associative Bridge
More on Mini-Dimension Outriggers
Arbitrary Banding Range Technique
How to Design a Band Definition Table
How to Track Point-In-Time Balances
Account Balance as of a Given Date Select Statement
Context-Dependent Outriggers or Dimension Super-Type and Sub-Types

Telecommunications and Utilities
Telecommunications Data Warehouse Bus Matrix
How to Perform a Star Schema Design Review
Version 1 Customer Billing Star Schema
Problems with the Version 1 Customer Billing Star Schema
Version 2 Customer Billing Star Schema
Problems with the Version 2 Customer Billing Star Schema
Version 3 Customer Billing Star Schema
Geographic Location Dimension
Geographic Information Systems
Analyzing Geo-Spatial Data

Transportation
Airline Frequent-Flyer Case Study
Multiple Fact Granularities
Segment-Level Flight Activity
Segment and Trip-Level Granularity in One Fact Table
Cargo Shipper Schema
Travel Services Hotel Schema
Combining Small Dimensions into One
Country-Specific Calendar Outriggers
Time of Day as a Dimension or a Fact?
Designing for Multiple Time Zones

Education
Admissions Tracking Accumulating Snapshot
Accumulating Snapshot Design for Moving Forward Through a Series of Events
Accumulating Snapshot Design for Events that Happen Out of Sequence
Accumulating Snapshot Design for Tracking Events that Back Up
Horizontal versus Vertical Event Tracking
Factless Fact Table for Participant Registration
Factless Fact Table for Facilities Utilization
Factless Fact Table for Tracking Events that May Not Happen
Factless Fact Table for Tracking Weighted Events
Factless Promotion Coverage Fact Table
Promoted Products that Did Not Sell Select Statement

Health Care
What Is a Value Circle and Why Do Designers Need to Know About It?
Discovering Dimensions in the Value Circle
Health Care Billing Accumulating Snapshot
Designing the Date Dimension to Track Events that Have Not Happened Yet
Designing the Date Dimension for Events that Have Been Skipped
Dropping the Granularity of Accumulating Snapshots to Avoid the Multi-Valued Problem
Three Techniques for Eliminating Multiple Values within a Fact Table Column
Turning a Billing Fact into a Profitability Fact
Handling Complex Events with an Accumulating Snapshot
Mining Free-Form Comments into Dimension Constraints and Facts
Handling Facts with Sparse Data Values
Handling Late Arriving Historical Dimension Data

Electronic Commerce
What Is a Uniform Resource Locator (URL)?
Client Server Interactions on the Internet
Click Stream Characteristics
Identifying Web-Site Visitor Origin
Identifying the Session
Identifying the Visitor
Proxy Web Server Challenges
Click Stream Dimensions
Click Stream Session Tracking
Click Stream Web Page Tracking
Aggregated Click Stream Facts
Tracking E-Commerce Profitability

Insurance
Insurance Value Chain
Insurance Bus Matrix
Insurance Policy Transaction Schema
Automobile Transaction Schema
Policy Premium Periodic Snapshot Schema
Multi-Valued Policy Holder Insured Driver Bridge
Keeping Your Bus Matrix Up To Date
Tracking Policy Claims
Policy Claims Transactions
Policy Claims Transaction Schema
Policy Claims Periodic Snapshot
Policy Claims Accumulating Snapshot
Consolidating Policy Premium and Claim Amounts
Factless Fact Table Accident Events
Dimensional Modeling Design Mistakes

Building the Data Warehouse
Data Warehouse Life Cycle Road Map
DW Project Planning and Management
DW Project Scoping
DW Project Justification
DW Project Business Staffing
DW Project Business or IT Staffing
DW Project IT Staffing
DW Requirements Planning
Collecting DW Requirements
DW Effective Interviewing Techniques
DW Interview Wrap-up
DW Post Interview Documentation
DW Requirements Prioritization and Consensus
DW Business Impact and Feasibility Prioritization of Requirements in Quadrants
DW Technical Architecture in 8 Steps
DW Tool Suite Selection and Installation
DW Star Schema Physical Design Considerations
DW Star Schema Aggregation Strategies
DW Star Schema Indexing Strategies
DW Data Staging Physical Design Considerations
DW Dimension Table Staging Considerations
DW Master Dimension Cross-Referencing Strategies
DW Fact Table Staging
DW Analytics Specification
DW Deployment
DW Maintenance and Growth
Ten Common DW Design Mistakes to Avoid

Workshops
[The participants do various workshops depending on how many days the course is taught.]
Use the instructor supplied order and shipment star schema tables to do various drill downs and roll ups. [We use MS Access since it is readily available in most IT environments, not that you would ever implement a star schema with Access.]

Use Ralph Kimball’s 4 step methodology to begin to develop a star schema for a business process identified at each participant team. Each team identifies a fact table, defines the granularity of the fact table, chooses dimensions, and defines additive, semi-additive, or non-additive numeric facts. Each team may do the same or different star schemas as other teams. Each team writes up their star schema requirements with MS Word and implements it with the data modeling software chosen for the class. Each team presents their star schema to the class.
Each team modifies their star schemas based upon the comments that the class has given them.
The class is shown how to do a drill across using the instructor supplied order and shipment star schemas developed in Microsoft Access. The participants do their own drill across in Access.
If the participants bring real star schemas to class, the class may do a design review on the selected star schemas.
Prerequisites: Participants should have at least some experience with any relational database management system.
Note: All fields are required
At the present time we do not offer training for individuals or groups less then 6 individuals. We apologize for any inconvenience.


We Value Your Privacy!

Ready to get started or in need of more information? Contact us today.

Go To Blog Virtual Learning

Testimonials

“Super instructor. Really knows his stuff. Answers questions thoroughly and completely. I thought the course design was great and covered each topic in a well designed manner.”

Student – Java Programming