Download a pdf version of the Course Description

Overview

Advanced Dimensional Modeling is an in-depth, technical course that teaches the advanced concepts for designing a dimensional Business Intelligence (BI) model for high-performance reporting and analytics solutions such as Business Performance Management (BPM), Customer Analytics, Supply Chain Analytics, etc. This course will review data warehouse architecture and basic dimensional modeling concepts but the primary focus will be on advanced dimensional modeling techniques and helping participants learn when to use which techniques. With hands-on workshops, attendees will transform end users' reporting and analytics requirements into Dimensional Models (using a phased modeling approach) which are understandable to the business and will perform well in the face of unpredictable query patterns.

Benefits To Your Company

By learning the best way to design dimensional models for Business Intelligence or Business Perform systems, your designers and analysts will better capture business requirements and relate them to the supporting data structures. They will:

• Understand how to building multi-dimensional models (star and snowflake).
• Understand how to navigate a dimensional model
• Understand sophisticated techniques for more complicated data warehousing solutions
• Construct data models that are flexible to changing decision support requirements

Who Should Attend

• Data Architects
• Data Modelers
• Business Intelligence designers
• Data Warehouse Architects
• Enterprise Architects (Data, Technical)
• Database designers
• Database administrators (DBA)

Learning Objectives

Architects, Modelers, BI designers, and database designers will learn to...

• Build dimensional models to address BI reporting and analytics requirements
• Demonstrate how dimensional models address business requirements
• Plan for capacity, security, and performance in your data warehouse

What Makes This Certified Course Unique

This EWSolutions-certified course provides participants with practical, in-depth understanding of how to create accurate data models for complex Business Intelligence solutions. Hands-on workshops throughout the course will reinforce the learning experience and provide the attendees with concrete results that can be utilized in their organizations.

Course Outline

1. Data Warehouse Systems Architecture
2. Building Dimensional models
   • When to build Dimensional models … and when not to
   • Phased modeling approach (Conceptual, Logical, Dimensional, Physical)
   • Using requirements for informative communication throughout the project’s lifecycle
3. Advanced Dimensional Modeling
   • Dimensional Model Navigation
     • Case for Surrogate Keys
     • Additive, Semi-additive, and Non-additive Facts
     • Relationships between Fact Tables
     • Factless Fact Tables
   • DW Architectures (Inmon, Kimball, CIF, Hybrids)
     • Conformed Dimensions
     • Dimension Hierarchy Approaches
   • Temporal Perspectives in Dimensional Models
     • Fact Tables (Current vs. Historical View Approaches)
     • Slowly Changing Dimensions - Types 1,2, 3 & 6
     • Date and Time Dimensions
   • Capacity Planning
   • Meta Data Tags
   • Designing Security into the Dimensional Model (role & row)
   • Fact Qualifier Matrices
   • Inmon 8 Steps for Dimensional Models
   • When to Snowflake ..and not to
   • Managing Data Granularity in the Dimensional Model
   • Dimensional Challenges
     • Rapidly Changing Dimensions
     • Large Dimensions
     • Accommodating multiple views of data (Enterprise, Business Unit)
     • Ragged Hierarchies
     • Degenerate Dimensions
     • Junk Dimensions
     • Bridge Tables
     • Role Playing Dimensions
     • Audit Dimensions
     • Demographic Dimensions
     • Data Validity Dimensions
     • Value Band Reporting
4. Performance Tuning Approaches
   • Aggregation Methods
   • Indexing (b-tree, bitmap, join indexes, etc)
   • Partitioning
   • RDBMS Tuning (e.g. buffer size, sort heap size)
   • Massively Parallel Processing (MPP) platforms vs. Symmetrical Multiprocessing (SMP) platforms
   • Query Optimization (e.g. Star Transformation, Star Join)
5. Workshop conclusion
   • Summary, additional exercises, sources for further reading, etc.

Standard Duration

• 3 days

About The Instructor:

Pete Stiglich
Senior Consultant
EWSolutions

Pete Stiglich is a Senior Consultant with EWSolutions with over 20 years of IT experience in the fields of Data Modeling, Data Warehousing/Business Intelligence, Customer Relationship Management (CRM), Customer Data Integration (CDI), OLAP, Database Design and Administration, Data Quality, and Transaction Processing for industries such as Medical, Retail, Credit Card, Banking, Manufacturing, Telecom and Government.

Pete has developed and taught courses on Conceptual Data Modeling, Dimensional Data Modeling, and SQL. Pete’s articles on Data Modeling have been published in Real World Decision Support and InfoAdvisors.

He can be reached at PStiglich@EWSolutions.com