In Power BI, data modeling refers to the art of transforming raw data into a structured and relational model ready for analysis and reporting. It involved creating dimension tables (or schemas as they are called in Power BI) and using DAX (Data Analysis Expressions) to create calculated measures.
Components of data modeling
There are 4 key components of data modeling in Power BI:
1. Data Preparation (Power Query)
This involves cleaning the data in preparation for its analysis.
It involves removing duplicates, handling missing values, standardizing formats, splitting/merging columns, creating derived columns, renaming columns, and filtering our unnecessary rows, among others.
2. Relationships
This is the connection of tables based on how they relate to one another.
3. DAX Measures
These are the calculations done for business metrics.
They define business logic and KPIs that respond to filters.
Measures calculate totals, ratios, percentages, and react to filters and slicers.
4. Model view
This is the architecture diagram where you can arrange your tables into a schema, review and edit relationships, manage hierarchies, and validate filter directions.
At the heart of Power BI data modeling are schemas, fact and dimension tables, and relationships.
Fact and dimension tables
Fact tables store measurable events - things you want to analyze. For example, sales transactions, payments, orders, app events, etc.
Dimension tables provide context for analysis. For example, customer, product, geography, etc. They are descriptive rather than numeric.
Relationships
We have already created relations as a definition of how tables interact. There are two key concepts of relationships:
Cardinality:
One-to-many (dimension → fact) ✅
Many-to-many ⚠️ (use only when unavoidable)Filter direction
Single direction (recommended)
Both directions (advanced, risky)
Schemas
A schema refers to the structure and organization of data within a data model. There are two key types of schemas: star schema and snowflake schema.
Top comments (0)