Dimensional Modeling for Data Warehouse Projects

Course:   DWMODEL
Duration:   5 Days
Level:   II
On our website at:   http://www.verhoef-training.com/courses/DWMODEL.html
 
Course Summary

This course provides participants with the skills necessary to analyze and design a successful data warehouse using multi-dimensional data modeling techniques and proven industry best practices. The design concepts for the course will include works by Bill Inmon, Stefano Rizzi, and Ralph Kimballs. Financial examples and workshops addressing the students projects are included in the course. The course also discusses additional industry-wide best practices concerning Dimensional Modeling and Star Schemas. The instructor will include material from previous consulting engagements. The class can optionally use data modeling software such as CA Erwin.

Topics Covered In This Course

Introduction

  • Project Steps
  • Requirements Gathering
  • Requirements Analysis
  • From Analysis to Design

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

Starting the Design

  • Financial Case Study
  • Industry Design Process practices
  • 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 Bank) Dimension
  • Specialty Dimensions
  • 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
  • Conceptual and Logical Modeling

Fact table considerations

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

Dimension Considerations

  • Transaction-Grained Star Schema
  • Multiple versus Single Procurement Transaction-Grained Fact Tables
  • One Fact Table per Major System
  • Changing Dimensions
  • Predictable Changes with Multiple Version Overlay Strategy
  • Unpredictable Changes with Single Version Overlay Strategy

Other Dimension Considerations

  • Bus Matrix in detail
  • Transaction Fact variations
  • Role-Playing Dimensions (Using Views on Dimensions)
  • Common Dimensions
  • Master to Dimension Mapping
  • Customer Dimension variations
  • Outrigger Address Dimensions
  • Deal Dimension
  • Degenerate Dimensions
  • Junk Dimensions
  • Designing for Multiple Currencies
  • Allocating Facts to Lower Granularities
  • Accumulating Snapshot Fact
  • Designing for Multiple Units of Measure
  • Types of Fact Table Comparisons
  • 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 Hierarchies
  • Variable-Depth Hierarchies
  • Subsidiary Hierarchies
  • Subsidiary Bridge Table

Accounting

  • Finance and Accounting Data Warehouses
  • Periodic Snapshot
  • Periodic Snapshot Schema
  • Annual Budget Schema
  • Budget Variance Schema
  • OLAP Analytic Solutions

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
  • How to Perform a Star Schema Design Review
  • Multiple Fact Granularities
  • Combining Small Dimensions into One
  • Designing for Multiple Time Zones

Web Considerations

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

Other Data Warehouse Project Considerations

  • Data Warehouse Life Cycle Road Map
  • Planning and Management
  • Scoping
  • Justification
  • Business Staffing
  • IT Staffing
  • Effective Interviewing Techniques
  • Interview Wrap-up
  • Post Interview Documentation
  • Technical Architecture in 8 Steps
  • Star Schema Physical Design Considerations
  • Star Schema Aggregation Strategies
  • Star Schema Indexing Strategies
  • Data Staging Physical Design Considerations
  • Dimension Table Staging Considerations
  • Master Dimension Cross-Referencing Strategies
  • Fact Table Staging
  • Analytics Specification

Workshops

  • Design workshops will be conducted using financial examples provide by the Instructor and any students projects provided three weeks in advance of the class.
What You Can Expect

At the end of this course, delegates will be able to:

Who Should Take This Course

This course is designed for individuals who are technical staff, team leaders and project managers who need to understand how to design a data warehouse using multi-dimensional data modeling techniques.

Recommended Prerequisites

Participants should have at least some experience with any relational database management system.

Training Style

Instructor led with 50% lecture and 50% lab.

Related Courses
Code Course Title Duration Level
OWPDP
Oracle Physical Data Warehouse Design and Implementation
4 Days
I
Details
OWHDWD
ORACLE DATA WAREHOUSE DESIGN
5 Days
I
Details
DWHFUN
Data Warehousing Fundamentals
3 Days
I
Details
OBIBC
Oracle BI Enterprise Edition (OBIEE) 11g: Boot Camp
5 Days
I
Details
OBIBR
Oracle BI Enterprise Edition (OBIEE) 11g: Building Repositories
5 Days
II
Details
DWTEST
Fundamentals of Data Warehouse Testing
3 Days
II
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.