Advanced Business Intelligence Data Modelling Training Course

Course overview

Business intelligence teams lose trust when reports show conflicting revenue totals, duplicated customer counts, slow refreshes, or measures that change meaning between dashboards. These problems are rarely solved by better visuals alone. They stem from weak semantic models: unclear business definitions, poorly designed fact and dimension tables, uncontrolled relationships, and DAX calculations that do not behave correctly across filters. This advanced course equips participants to design governed, performant models that provide a reliable analytical layer for Power BI and enterprise reporting.

Participants work through dimensional modelling patterns for operational and analytical data, including star schemas, grain definition, conformed dimensions, slowly changing dimensions, role-playing dates, bridge tables, snapshots, and many-to-many relationships. They build robust DAX measures using filter context, CALCULATE, iterator functions, time intelligence, and calculation groups. The course also addresses model performance through storage-mode selection, aggregations, incremental refresh, query reduction, VertiPaq optimisation, and diagnostic analysis with DAX Studio and Tabular Editor.

Instruction combines expert-led modelling decisions with practical labs based on a multi-source sales, finance, and customer analytics case. Participants critique a flawed model, redesign its schema, create a governed measure layer, test business rules, and tune report performance. They leave with a documented semantic-model design pack containing a star-schema diagram, data-grain statements, relationship rules, a business metric catalogue, DAX measures, refresh strategy, and model-performance recommendations that can be adapted for workplace use.

The course is designed for experienced Power BI developers, BI analysts, data modellers, analytics engineers, and technical leads who already create reports or datasets and now need to own the quality, scalability, and governance of enterprise BI models.

Course objectives

By the end of this course, participants will be able to:

• Define fact-table grain and design star schemas for multi-process business reporting
• Create conformed, role-playing, slowly changing, and bridge dimensions for consistent analysis
• Configure relationship cardinality, cross-filter direction, and many-to-many patterns without ambiguous results
• Develop DAX measures using CALCULATE, filter context, iterators, and reusable time-intelligence patterns
• Implement calculation groups and measure conventions to standardise enterprise metric logic
• Optimise VertiPaq models through cardinality reduction, storage-mode choices, aggregations, and incremental refresh
• Diagnose slow DAX queries and model bottlenecks using DAX Studio and Tabular Editor
• Produce a governed semantic-model design pack with metric definitions, lineage rules, and deployment recommendations

Benefits of attending

Target competencies

Who should attend

• Senior Power BI Developers — who need to build scalable semantic models rather than report-specific datasets
• Business Intelligence Developers — who must standardise metrics across dashboards, teams, and subject areas
• Data Modellers — who translate operational data structures into analytical star schemas
• Analytics Engineers — who prepare curated data products for self-service BI consumption
• BI Technical Leads — who set modelling, performance, and governance standards for reporting teams
• Data Analysts — who maintain complex Power BI datasets and need reliable DAX and relationship design

Requirements and prerequisites

Participants should already be comfortable building Power BI reports and datasets, loading and shaping data with Power Query, creating basic relationships, and writing straightforward DAX measures such as SUM, COUNTROWS, and DIVIDE. Familiarity with relational database concepts, SQL SELECT statements, primary and foreign keys, and basic star-schema terminology is expected. Experience publishing to the Power BI Service is useful but not essential. This is not a beginner Power BI course and does not teach visual design or introductory DAX from first principles. Prior use of DAX Studio, Tabular Editor, or SQL Server Analysis Services is not required.

Training methodology

The five-day programme alternates instructor-led model reviews with guided build sessions in Power BI Desktop. Participants work from a deliberately flawed operational reporting model, inspect its data grain and relationship behaviour, then rebuild it as an enterprise semantic model. Labs use DAX Studio and Tabular Editor to test query plans, refine measures, and apply calculation groups. Small-group design reviews compare alternative modelling patterns for finance and sales scenarios. The final session converts each participant’s work into a practical model-design and improvement plan for a current workplace dataset.

Course outline

Day 1: Dimensional Modelling for Enterprise BI

• Business-process modelling and analytical data-grain statements
• Fact tables, transaction facts, periodic snapshots, and accumulating snapshots
• Dimension design, surrogate keys, and conformed dimensions
• Star schemas versus snowflake schemas in Power BI semantic models
• Slowly changing dimension handling for historical reporting
• Role-playing dimensions and canonical date-table architecture
• Source-to-semantic-model mapping and data lineage documentation

Workshop: Participants assess a flawed sales and finance dataset, define the grain of each process, and produce a target star-schema diagram with source mappings.

Day 2: Relationships, Security, and Model Behaviour

• Relationship cardinality and active versus inactive relationship design
• Cross-filter direction and ambiguity prevention
• Many-to-many relationships and bridge-table patterns
• Handling ragged hierarchies and parent-child organisational structures
• Composite keys and relationship alternatives in Power BI
• Row-level security filter propagation and security-table design
• Model validation through reconciliation totals and relationship test cases

Workshop: Participants rebuild an ambiguous customer-account model using bridge tables and relationship test cases, then validate secured totals for different user roles.

Day 3: Advanced DAX Measure Engineering

• Row context, filter context, and context transition
• CALCULATE filter modifiers including ALL, REMOVEFILTERS, KEEPFILTERS, and USERELATIONSHIP
• Iterator patterns with SUMX, AVERAGEX, FILTER, and VALUES
• Virtual tables and variable-driven measure construction
• Robust time intelligence using marked date tables and custom comparison periods
• Semi-additive measures for balances, inventory, and headcount
• Calculation groups and dynamic format strings in Tabular Editor

Workshop: Participants create a governed measure set for revenue, margin, year-to-date performance, prior-period comparison, and month-end balance reporting.

Day 4: Performance and Semantic Model Optimisation

• VertiPaq storage principles, cardinality, and dictionary compression
• Column reduction, data-type selection, and encoding-aware model design
• Import, DirectQuery, Dual, and composite-model storage modes
• Aggregation tables and aggregation-awareness configuration
• Incremental refresh policies and partition management concepts
• DAX query analysis using DAX Studio Server Timings and Query Plan
• Model metadata management and best-practice checks in Tabular Editor

Workshop: Participants benchmark a slow dataset, identify costly columns and measures, and document a quantified optimisation plan using DAX Studio findings.

Day 5: Governed Deployment and Model Design Review

• Business metric catalogues, measure descriptions, and naming conventions
• Semantic model documentation for tables, relationships, calculations, and assumptions
• Dataset ownership, endorsement, certification, and release-control practices
• Deployment pipelines and environment-specific parameter management
• Testing strategy for data reconciliation, DAX logic, security, and performance
• Enterprise modelling anti-patterns and remediation decisions
• Stakeholder design reviews and semantic-model roadmap planning

Workshop: Participants complete and present a semantic-model design pack containing schema, measure catalogue, validation tests, performance actions, and deployment recommendations.

Tools & standards covered

Microsoft Power BI Desktop, DAX Studio, Tabular Editor, SQL Server Analysis Services Tabular

A typical training day

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions