Data Modeling in Power BI

ref: Pragmatic Works


A tutorial video by Pragmatic Works, related to data model in Power BI.

The .pdf presentation, .pbix file and data sample are accessible at here.


00:14:46: star schema; fact table surrounded by dimension tables; fact table is on the Many side of the 1-to-many relationship with other dimension table.


00:16:15: snowflake schema; similar to star schema with Fact table at the center, but dimension tables now have their own hierarchy/relation.


00:18:59: conceptual data model.

00:20:57: dimensional data model: organizes data to be retrieved for reporting purposes; This model consists of Fact table and Dimension table; Fact is an event that include (or may not) measures; Dimension is a category of information, that provides the "who what where when why how" context surrounding a business process event; Dimension table contains descriptive Attributes (column in a Dim table) that define how a fact should roll up.

01:30:30: create a surrogate key by inserting a new index column.

01:42:24: in Data Model pane, we can create a layout that consist only the required data model; this makes the model diagram becoming neat.


01:52:18: ytd sales = TOTALYTD function.

01:55:01: USERELATIONSHIP dax function does not create a new relationship,

01:57:02: Use Tabular Editor tool (3rd party tool for Power BI) to create a Calculation Group which helps to reduce the number of Measures in a data model.

02:11:10: create an aggregate fact table as a snapshot of a bigger fact table (imagine billions of rows); use Group By feature in Power Query.



  2. cat.tut