Data Modeling Essential
Understanding data relationships, hierarchies, data models and Fact-Dimension model
In this section of our Power BI course, we will explain the basic concepts of data relationships, hierarchies, and data models. These are essential concepts that help you make the most out of Power BI for creating meaningful reports.
Click the link below, in file section, click Download then Microsoft Excel to download the Sales dataset to practice data modeling. Sales-Data
Get data and Connect with Power BI
1. Open Power BI Desktop from your computer. On the home screen, click on “Get data” from the Home ribbon.
2. A dialog box will appear with multiple data source options, select a data source (Excel), and click Connect. Choose your file, and click Open.
3. A Navigator window will appear. Check the box “Customer, Product, sales” and click Clean the
1. What are Data Relationships?
A data relationship in Power BI connects two or more tables based on a common column, like “ID” or “Name.” Think of it like a link or a bridge between different tables that lets you combine data from those tables in your reports.
Types of Relationships:
1. One-to-One Relationship: One record in Table A matches exactly one record in Table B.
Example: One Employee has one unique Aadhar number.
Employee Table:
Aadhar Table:
- One-to-Many Relationship: One record in Table A can match many records in Table B.
Example: One customer can make many orders. So, “Customer ID” is in both the customer and orders tables. and in if a customer has 2 orders, then the customer id will repeat 2 times in order.
2. Many-to-Many Relationship: One record in Table A can match many records in Table B, and the same is true in the opposite direction.
Example: Many students can enroll in many courses, and many courses can have many student.
Student Table:
Course Table:
Student Enrolment Table:
• Why are relationships important? They allow you to pull in data from multiple tables and use it together in one report. After the relationship is created, you can make a report showing both the customer names and their orders, even though the names were not in the Sales Table.
Fact-Dimension Model
A Fact Table contains the numeric data or measurable values you want to analyze, like sales amounts, quantities, or
transaction totals. These values are the facts that you’re interested in measuring. Here the Sales table is Fact table.
What is a Dimension Table?
A Dimension Table provides descriptive information that gives context to the facts in the fact table. These tables help you describe, group, and filter the facts. Here Customer and Product table are Dimensions.
• Example: Imagine you have two tables:
• Customers Table: This table has the names and IDs of your customers.
• Sales Table: This table has a list of orders made by customers, but only has the Customer ID, not their names, you can make a report showing both the customer names and their orders, even though the names were not in the Sales Table.
2. What are Hierarchies?
A hierarchy is a way to organize data from general to specific levels. Think of it like a family tree that breaks down information step-by-step.
• Example of a hierarchy: A common example is a Date Hierarchy, where you have data organized from Year > Quarter
> Month > Day. This lets you drill down from a broad overview (Year) to more specific details (Day).
• Why use hierarchies? They make it easier to navigate through different levels of data in a report. For example, in a
sales report, you might start by looking at annual sales and then drill down to see sales by quarter or month.
• How to create a hierarchy? In the Fields pane, right-click on a column you want to start your hierarchy with (like
“Year”), then select New hierarchy and add additional levels (like “Month” and “Day”).
3. What is a Data Model?
A data model is a collection of all your tables, their data, and the relationships between them in Power BI. Think of it as a blueprint that shows how different pieces of data connect and interact with each other.
Why is a data model important? It helps Power BI understand how to organize, calculate, and display data correctly in your reports. A well-designed data model ensures accurate analysis and makes it easier to create complex reports.
Building a data model: To build a good data model, make sure you:
Create relationships between tables using common columns.
Avoid duplicates and unnecessary data to keep the model clean.
Use hierarchies to make navigation and analysis simpler.
Example: In the below example there is a sales data in which all the tables are joined with Sales table This setup allows
you to analyze various aspects of the sales data—such as who bought what, when it was shipped, and what products are
most popular—by connecting different pieces of data through common fields (like Customer ID or Product ID).
