TDWI Toronto Seminar

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

July 11, 2018

Duration: One Day Course

Prerequisite: None

Chris Adamson


Founder and BI Specialist

Oakton Software LLC

This course is DAY TWO of a two day course, TDWI highly recommends all attendees take DAY ONE prior to attending this course.

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 Five
Advanced Conformance

  • Conformance revisited
    • Spanning subject areas
    • Conformance and non-identical dimensions
    • Revised definition of conformance
  • Conformance and hierarchies
    • Conformed rollups
    • Using conformed rollups
    • Snowflakes and Conformance
  • Conformance and overlapping dimensions
    • Overlapping dimensions
    • Using overlapping dimensions
  • An advanced conformance matrix

Module Six
Advanced Dimension Design

  • Understanding hierarchies
    • Drilling with and without hierarchies
    • Multiple hierarchies in one dimension
    • Impact of hierarchies on BI, ETL and DBA perspectives
    • Why to model hierarchies
    • Snowflake schemas
  • Nulls
    • Problems introduced by NULL values
    • Avoiding NULL dimension attributes
    • Avoiding NULLs with optional relationships
    • NULL facts

Module Seven
Advanced Slow Change Processing

  • Time-stamped Dimensions
    • Why type 2 is not good enough
    • Time-stamped dimensions (transaction dimensions)
    • Using with a fact table
    • Hybrid attributes that behave like facts and dimensions
  • Mini-dimensions (Types 4 & 5)
    • Large and expanding dimension tables
    • The use of a mini-dimension to stem growth
    • Impact on schema capability
    • Type 4 and Type 5 slow changes
    • Loading the mini-dimension
  • Current and Previous (Type 3)
    • Limited access to unchanged value
  • Hybrid Responses
    • Tracking both Type 1 and Type 2 responses
    • For dimension Attributes (Type 6)
    • For entire dimension tables (Type 7)

Module Eight
Bridge Table Design

  • The dimension bridge
    • When a single fact needs to reference more than one dimension row
    • The “flattening” option
    • The bridge table
    • Use of the bridge
    • Avoiding double counting with an allocation factor
    • Avoiding double counting by hiding the bridge (sandbox)
  • The attribute bridge
    • A dimension attribute repeats for a single dimension row
    • The attribute bridge
    • Use of the bridge and avoidance of double-counting
    • Bridge table vs. factless fact table
  • The hierarchy bridge
    • Recursive relationships (ragged hierarchies, unbalanced hierarchies, variable depth hierarchies and instance hierarchies)
    • Flattening and backfilling
    • The hierarchy bridge and its structure
    • Using the bridge to “roll up” a recursive hierarchy
    • Using the bridge to “roll down” the hierarchy
    • ETL implications of hierarchy change and slow changes

Module Nine
Design Tasks and Deliverables

  • Agile Principles and Dimensional Models
    • Agile principles
    • Collaboration and iteration
    • Business Dimensional Model (BDM) techniques)
    • Short term benefits and long term needs
  • Scoping and Dimensional Design
    • Dimensional design as architecture task
    • Architecture and incremental implementation
    • Implementation without up-front architecture activity
  • Design activities
    • Key design activities
    • Resources required for design success
    • Estimating duration of design activities
    • Requirements, top level design, detailed design and roadmap
  • Documenting requirements (Business view)
    • Subject area definition
    • Metric groups and metric definition
    • Business hierarchies
    • Conformance
  • Top level design
    • Key elements of dimension table design
    • Key elements of fact table design
    • Documenting what is not visible
    • The conformance matrix
    • The design review
  • Detailed design
    • Attribute level design of fact and dimension tables
    • Technical design elements (DBA perspective)
    • Business definitions (business perspective)
    • Source to target mappings (integration perspective)
  • Roadmap
    • Project definitions
    • Managing scope of implementations

Module Ten:
Big Data and the Dimensional Model

Non-Relational Data

  • Embracing external and non-relational technologies
  • Understanding NoSQL technologies
  • Changing nature of data modeling

Tapping into non-relational data

  • Intake and exploration
  • Deployment
  • Augmenting dimensional data marts
  • Extending dimensional data marts

Architecture and Governance

  • Multi-directional data flows
  • Segmentation in data architecture
  • Logical vs physical models and solutions

Geared To

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