Advanced Data Modelling, Star Schemas & Composite Models

Instructor: Alaa Hady · Language: English or Arabic · Suggested duration: 2 days · Level: Advanced (Level 300)

Objectives

• Architect enterprise-grade dimensional models spanning multiple business processes and granularities.
• Design Slowly Changing Dimensions (SCD Types 1, 2, and 3) and Snapshot Fact tables.
• Implement Composite Models combining Import, DirectQuery, Dual storage modes, and Direct Lake.
• Balance model flexibility, query accuracy, and memory footprint at scale.

Outline and contents

1. Enterprise dimensional bus matrix and conformed dimensions across multiple fact tables (Chasm & Fan traps)
2. Granularity management: allocating header/budget facts down to line-item dimensions
3. Handling Slowly Changing Dimensions (SCD Type 2) with historical validity dates in Power BI
4. Periodic Snapshot vs. Accumulating Snapshot vs. Temporal/Events-in-Progress fact patterns (e.g., open tickets, inventory balances)
5. Semi-additive measures (LASTNONBLANKVALUE, CLOSINGBALANCEMONTH) and currency conversion architectures
6. Parent-child ragged hierarchies (PATH, PATHITEM, ISINSCOPE) for organizational charts and Chart of Accounts
7. Composite Models: mixing Import, DirectQuery, Dual storage modes, and chaining semantic models (DirectQuery for PBI datasets)
8. User-defined Aggregations and Automatic Aggregations over massive fact tables
9. Security architectures: Dynamic RLS with bridge tables, hierarchical manager RLS, and Object-Level Security (OLS)
10. Architecture Workshop: Designing a multi-fact enterprise semantic model from complex ERP/CRM schemas

Target audience

Senior analysts, BI developers, data modellers, and analytics engineers.

Expected outcomes

Participants can architect resilient multi-fact models, handle historical dimension tracking and currency conversion, and configure hybrid storage modes without introducing ambiguous relationship paths.