Tel. 02-570-8449 Mobile 088-807-9770 Payment
Mon–Fri 9:00–17:00 @itgenius
IT Genius Institute
IT Genius Institute
CoursesWorkshopSchedule
For business
In-house trainingAI Transformation (TH)Government training (TH)Development servicesTraining room rental
Articles
About us
About IT GeniusInstructorsPortfolioOur clientsPayment methods
Contact usOnline
Theme
Language
THENLA
Ask about a coursePick whichever channel suits you
LINE @itgeniusFastest reply · Mon–Fri 9:00–17:00
Call us
02-570-8449Office088-807-9770Mobile
Email & social
contact@itgenius.co.thFor quotations and documentsFacebookitgeniusonlineYouTubeitgeniusengPlan training for your team
Home / Courses / Databases / Detail
Databases · DBC-55

Power BI Data Modeling

Power BI Data Modeling is a hands-on course in designing semantic models in Power BI, from star schemas, relationships, date tables and bridge tables to calculation groups, storage modes, model size optimisation and row-level security. It suits data analysts and Power BI report builders who want correct numbers and faster reports.

Updated
Power BI Data Modeling
From 5,850 THB / person 6,500 −10% excl. VAT 7% · group rates available
Corporate training quote Join the waiting list
PDFDownload the course outline
  • Duration12 hours · 2 days
  • FormatOnsite / live online
  • Next roundOn request
  • CertificateIncluded

Course overview

Many Power BI reports start quickly, but as data grows the totals stop adding up, slicers fail to filter, dozens of near-identical measures pile up and files become so large that refreshes crawl. Most of these problems are not caused by DAX formulas. They come from a data model that was built without thinking about grain, relationships and table structure from the start.

This course teaches learners to design Power BI data models systematically. It starts with analysing the business question and the grain, turning data into a star schema, setting relationship cardinality and filter direction correctly, using inactive relationships with USERELATIONSHIP, building an organisation-wide date table and handling role-playing dimensions and bridge tables. It then covers calculation groups, Import, DirectQuery and composite storage modes, aggregations, shrinking the model with DAX Studio and VertiPaq Analyzer, and row-level security, and closes with a workshop that designs a complete semantic model from scratch. (2 days, 6 hours per day, 12 hours in total, Intermediate level.)

What you’ll gain

  • Analyse a business question, define the grain and design a star schema from real data
  • Configure relationship cardinality, filter direction and inactive relationships correctly
  • Build a date table that supports the organisation's fiscal year and holidays
  • Handle role-playing dimensions, many-to-many relationships and facts at different grains
  • Use calculation groups and field parameters to remove duplicated measures
  • Choose storage modes and use aggregations to suit the data volume
  • Measure and reduce model size with DAX Studio and VertiPaq Analyzer
  • Apply static and dynamic row-level security

Who this course is for

  • Data analysts and Power BI report builders whose numbers do not add up or whose reports are slow
  • Learners who have finished a Power BI Desktop basics course and want to model data properly
  • BI teams who build shared semantic models used by many reports
  • Database developers who prepare data for Power BI reporting teams
  • Anyone who wants a solid modelling foundation before studying advanced DAX

Prerequisites

  • Able to import data, build visuals and publish reports in Power BI Desktop
  • Basic Power Query transformations and simple DAX measures such as SUM or CALCULATE
  • A basic understanding of tables and keys between tables
  • A Windows laptop that can install Power BI Desktop and add-on tools, plus your own or your organisation's Power BI account

Curriculum

Course Details

This course builds on Power BI Desktop for Business Analytics and focuses entirely on data model design. It also makes Basic DAX and Advanced DAX in Power BI easier to learn. It runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with labs on a single fictional company's data throughout. Intermediate level. Learners use the free Power BI

Desktop on their own Windows laptop with DAX Studio and Tabular Editor 2, and their own or their organisation's Power BI account for topics that need the Power BI service. The institute does not provide accounts or licences. The course does not go deep into DAX or visual design, which other courses cover. Power BI updates often, so the content follows the latest version on the training day.

Day 1 Getting the Model Structure Right

Section 1: Lab: A Good Data Model Starts with the Business Question

  • Why a good model matters more than complex DAX: correctness, speed and maintainability
  • Identify the business process, the grain and the numbers to report before loading data
  • Work with Model view, the Properties pane and Model explorer in Power BI Desktop
  • The problems that follow from loading a single flat table into Power BI
  • Lab: analyse a slow report with wrong numbers and trace the causes back to the model

Section 2: Lab: Star Schema, Facts and Dimensions

  • Separate fact tables from dimension tables and define a clear fact grain
  • Surrogate keys, business keys and handling rows with no matching reference
  • Flatten a snowflake into a star with Power Query, and where denormalising is worth it
  • Several fact tables sharing conformed dimensions
  • Lab: turn a flat sales table into a star schema

Section 3: Relationships: Cardinality and Filter Direction

  • How one-to-many, one-to-one and many-to-many relationships differ
  • Single versus both directions, and the risk of ambiguous filter paths
  • Inactive relationships and activating them with USERELATIONSHIP in a measure
  • CROSSFILTER and TREATAS for cases with no direct relationship
  • Lab: fix wrong numbers caused by badly configured relationships

Section 4: Lab: An Organisation-wide Date Table

  • Why to turn off Auto date/time and build your own date table
  • Build a date table in Power Query or DAX with fiscal years and weeks
  • Mark as date table, Sort by column and a year, quarter and month hierarchy
  • Helper columns for working days, holidays and the Thai Buddhist year
  • Lab: a date table that supports the company's fiscal year and holidays

Section 5: Lab: Role-playing Dimensions and Bridge Tables

  • Order date, ship date and payment date: inactive relationships or separate dimensions
  • Slowly changing dimensions Type 1 and Type 2 from a reporting point of view
  • Bridge tables for many-to-many cases such as customers in several groups, compared with many-to-many relationships
  • Facts at different grains, such as a monthly budget against daily sales
  • Lab: compare budget with actual sales, and sales by order date with ship date
Day 2 Performance, Security and Model Maintenance

Section 6: Lab: Calculation Groups and Field Parameters

  • Explicit versus implicit measures, and hiding columns that should not be summarised
  • Organise measures in a dedicated measure table and display folders
  • Create a calculation group for time intelligence such as YTD and YoY in Model view
  • Field parameters that let users switch measures and dimensions themselves
  • Lab: remove duplicated measures with a calculation group

Section 7: Storage Modes, Composite Models and Aggregations

  • Import, DirectQuery and Dual: strengths, limits and the effect on data freshness
  • Composite models: combine several sources and storage modes in one report
  • User-defined aggregations that answer summary queries over large facts faster
  • An overview of Direct Lake in Microsoft Fabric and when it fits
  • Lab: a composite model with a monthly aggregation over data in SQL Server

Section 8: Lab: Shrinking the Model with VertiPaq Analyzer

  • How VertiPaq stores data in columns and compresses it
  • Use DAX Studio and VertiPaq Analyzer to inspect table size, column size and cardinality
  • Remove unused columns and rows, split date from time and reduce numeric precision
  • Calculated columns versus Power Query, and the effect on file size
  • Use Performance Analyzer to find slow visuals
  • Lab: shrink a sample file and measure before and after

Section 9: Lab: Row-Level Security

  • Static RLS with roles and DAX filters
  • Dynamic RLS with USERPRINCIPALNAME and a permissions table
  • Make RLS filters flow correctly through relationships and bridge tables
  • Test with View as and assign role members in the Power BI service
  • An overview of object-level security to hide tables or columns
  • Lab: dynamic RLS so each manager sees only their own branch

Section 10: Workshop: Designing a Complete Semantic Model

  • Naming, descriptions and checking the model with Best Practice Analyzer in Tabular Editor
  • An overview of saving as a Power BI Project (PBIP) for Git
  • Workshop: design a model for sales, stock and budget data from scratch
  • Review against a checklist covering grain, relationships, file size and RLS
  • Present and review the models together

Schedule & training options

For individuals — public rounds

No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770

Join the waiting list Ask about rounds

For organisations — in-house / private

  • Tailor the content to your team’s tools and projects
  • Your dates, at your office or live online
  • Quotation with tax ID for procurement
Corporate training quote

Instructors

Frequently asked questions

How does this course differ from Power BI Desktop for Business Analytics and the DAX courses?

This course builds on Power BI Desktop for Business Analytics, which teaches report building from data import to visuals. This course focuses only on data model design: star schemas, relationships, date tables, storage modes, aggregations and RLS. Basic DAX and Advanced DAX in Power BI focus on writing formulas. With a correct model, DAX becomes much shorter and easier to follow, so the courses complement each other.

Do I need to be good at DAX before joining?

No. You only need to write basic measures such as SUM and CALCULATE. The course uses DAX only where modelling needs it, such as USERELATIONSHIP, calculation groups and RLS, and explains every formula used in the labs.

Do I need a Power BI licence, and does the institute provide one?

The institute does not provide accounts or licences. Most labs use Power BI Desktop, which is free. Testing RLS in the Power BI service and publishing to a workspace shared with others needs your own or your organisation's Power BI account, usually Power BI Pro or higher. If you do not have one, you can test RLS with View as in Desktop instead.

Can I use a MacBook?

Power BI Desktop, DAX Studio and Tabular Editor run only on Windows. Mac users need a Windows machine or a Windows virtual machine that can install the software, and should test it before the training day.

Can I bring my organisation's model to discuss in class?

Yes, during the workshop and Q&A, but remove confidential and personal data first or use sample data with the same structure. All main labs use a fictional company's data so everyone can follow along together.

Does the course cover Microsoft Fabric?

The course focuses on Power BI semantic models, which follow the same principles in Power BI and Fabric. Direct Lake is covered as an overview so you know when it fits, but building lakehouses or warehouses in Fabric is not taught. For that, continue with Microsoft Fabric Essentials.