Understanding schemas and data modelling on Power BI
Source: Dev.to
Power BI Overview
Power BI is a vital tool for connecting data sets from different sources, transforming that data, and creating interactive visual reports and dashboards.
Many beginners wonder about the difference between schemas and data models.
- Schemas are specific designs that conceptualize how to structure data for analysis.
- Data models are the implementations of the selected schemas.
Thus, data modelling in Power BI depends on the schema you create.
Common Terminologies
Fact table
Stores key business data (e.g., sales or transactional data) that changes regularly. A fact table usually contains:
- Dimension key columns that relate to dimension tables.
- Numeric measure columns that allow summarisation.
Dimension table
Stores additional information related to the transactional data (lookup/descriptive data) and is mostly constant. A dimension table contains:
- A key column (or columns) that acts as a unique identifier.
- Other columns that support filtering and grouping.
Normalized data
Data stored in a way that reduces repetitive information.
Denormalized data
Data stored with repetitive information in rows.
1. Schema Design (The Planning Stage)
Before bringing data into Power BI, decide how to structure it for analysis. A star schema is the most preferred approach: a central fact table surrounded by dimension tables.
A well‑planned schema ensures:
- Better performance
- Easier DAX development
- More accurate reports
Actions to execute: Decide which tables are facts and which are dimensions, and identify relationships between them (Microsoft Docs – Create and manage relationships).
Types of Schemas
1. Star Schema
The fact table sits in the middle, with multiple dimension tables surrounding it. Power BI’s engine works best with this layout.
2. Snowflake Schema
Similar to the star schema, but one or more dimension tables are further normalised into sub‑dimension tables.
2. Data Modeling (The Implementation Stage)
Data modeling is the hands‑on process where you apply the schema design in Power BI. It involves:
- Importing data.
- Cleansing data in Power Query.
- Creating tables.
- Establishing relationships (e.g., one‑to‑many) in the Modeling view.
You are essentially implementing the star schema using Power Query and the Relationship view.
Semantic Models
A semantic model consists of all connected data, transformations, relationships, and calculations. The typical Power BI flow is:
- Connect to data.
- Transform data.
- Create relationships and calculations → semantic model.
Learn more about semantic models
Relationships in Power BI
Relationships are connections between tables based on common columns. They enable data from multiple sources to be used in a single, accurate report and ensure that slicers and visualisations interact correctly.
Microsoft Docs – Create and manage relationships
Common Types of Relationships
-
One‑to‑Many (1:M) / Many‑to‑One (M:1) – The most common relationship; one dimension table links to multiple rows in a fact table (e.g., Product → Sales).
-
One‑to‑One (1:1) – Each record in Table A matches exactly one record in Table B; rare and often suggests the tables could be merged.
-
Many‑to‑Many (M:M) – Multiple rows in one table match multiple rows in another; typically requires a bridge (junction) table.
Creating Relationships in Power BI
Auto‑detect feature – When you load multiple tables, Power BI automatically attempts to find and create relationships for you. These auto‑detected relationships can be reviewed and edited in the Model view.
Power BI Relationships
Automatic detection
Power BI can automatically detect relationships based on the names of the columns within your data tables.
To use this feature:
- Go to the Modeling tab.
- Select Manage relationships ► Autodetect.
Manually creating a relationship
Use this method when Power BI cannot automatically detect a relationship (e.g., the columns have different names).
- On the Modeling tab, choose Manage relationships ► New.
- In the Create relationship dialog:
- Select the first table and the column you want to use.
- Select the second table and the column you want to use.
- Click OK.
Key Aspects of Power BI Relationships
-
Cardinality – Defines how many rows are related between tables. Options are:
- One‑to‑many (most common, e.g., one customer to many orders)
- One‑to‑one
- Many‑to‑many
-
Cross‑filter direction – Determines how filters flow between tables:
- Single (default – filters flow from the “one” side to the “many” side)
- Both (bidirectional – filters flow both ways; use with caution)
-
Active vs. Inactive – Only one active relationship can exist between two tables for direct filtering. Additional inactive relationships can be defined and used in DAX calculations with functions such as
USERELATIONSHIP. -
Autodetect – Power BI can automatically find and create relationships based on matching column names during data load, but manual configuration is often required for optimal modeling.