TDWI Toronto Seminar

Advanced Dimensional Modeling: Techniques for Modern BI and Analytics Programs – Day One

July 10, 2018

Duration: One Day Course

Prerequisite: This course assumes a basic understanding of dimensional modeling concepts, techniques, and terminology.

Chris Adamson


Founder and BI Specialist

Oakton Software LLC

This course is DAY ONE of a two day course, TDWI recommends all attendees take DAY TWO to complete the learning experience.

In the age of big data, your entire team must be fluent in dimensional modeling—not just the data modelers! This course immerses students in the principles, processes, and deliverables of dimensional modeling, equipping you for successful BI and analytics projects.

You will master the complete set of best practices—from multiple fact table designs, to bridge tables, to advanced slow change processing.  You will learn to match these techniques to real-world business complexity, and explore their impacts on BI and integration tools.

You will learn the central role of the dimensional model in bringing analytic insights to a general audience, and how to forge next-generation data architectures that incorporate non-relational data and virtualization.

Students will also learn how dimensional modeling enables agile development, and will receive templates for capturing requirements and designs.

You Will Learn

Module One

  • Dimensional Modeling
    • Process measurement
    • The purpose of a dimensional model
  • BI and Analytic Services
    • OLAP, Performance Management, and Analytics
    • Governance, quality, and master data
  • Service interaction
    • Shared information assets
    • Solution integration
    • Unified planning
  • Data Architectures for Dimensional Data Marts
    • Inmon’s CIF Architecture
    • Kimball’s Dimensional Bus
    • Stand-alone Data Marts
  • Data Architecture for Expanded Services
    • Flexible intake solutions
    • Segmented solutions for reconciliation, quality and governance
    • Virtualization options

Module Two
Multiple Stars

  • Multiple star solutions
  • Designing multiple fact tables
    • Identifying multiple processes
    • Differences in dimensionality
    • The pitfalls of single fact table design
  • Using multiple stars
    • How not to query multiple fact tables
    • The concept of drilling across
    • What you need to know about your query and reporting tools
  • Conformance and business value
    • High impact business questions span processes
    • The concept of conformance
    • Ensuring subject areas work together
    • Enabling incremental implementation

Module Three
Advanced Fact Table Design

  • Transaction schemas
    • Transaction grain
    • Shortcomings of transaction designs
  • Periodic Snapshots
    • Snapshot grain and period
    • Semi-additivity, density, and impact on BI
    • Building both transaction and snapshot schemas
    • Snapshots and averages
  • Accumulating Snapshots
    • Studying process efficiency
    • Accumulating metrics in a single row
    • Lag analysis
    • Impacts on slow change processing and data integration
    • Building both transaction and accumulating snapshots
  • Factless Fact Tables
    • Processes that seem to lack metrics
    • Factless fact tables that track events
    • Pros and cons of adding constant-value fact
    • Factless fact tables that track conditions
    • Comparing conditions to actual events

Module Four
Analytics, Performance Management and the Dimensional Model

  • Shared Information Resources
    • Modern BI Services: OLAP, Performance Management, Analytics
    • Hidden Services: Governance and Quality Management
    • Semantic consistency across BI, PM and Analytics
    • Metric and KPI definitions
  • Performance Management
    • What is performance management
    • Performance management and other BI services
    • Integrating performance management and OLAP
    • Fact and KPI consistency
  • Business Analytics
    • What is Business Analytics
    • Analytics and Other BI Services
    • Analytics and Data
  • Analytics-Friendly Data Warehouse
    • Fact table grain and analytics
    • Dimensions and analytic variables
    • Tracking reference data changes for analytics
    • Capturing weak identifiers for analytics
    • Dealing with missing data
  • Analytics-Influenced Data Warehouse
    • Tracking new facts
    • Behavioral dimensions based on analytics
    • Multi-directional data integration


This course is intended for anyone who contributes to data mart development, including:

  • BI program & project managers
  • Business analysts
  • Data modelers and architects
  • BI reporting & ETL developers
  • DBA’s
  • “Power users” and business subject matter experts

Register Online

Rest easy—online registrations for this seminar are secure. Our secured server environment keeps your information private.

Subscribe to Receive seminar updates via email

TDWI Toronto Seminar

MicroTek Training Center
330 Bay St
Suite 610
Toronto, ON M5H 2S8, Canada
July 9–11


  • Download on the App Store
  • Get it on Google Play