Data Modelling in Power BI
Assume you are running a business that requires you to order and supply different products from different suppliers and sell them to different customers every day. To keep records of your business, you are likely to use tools such as Excel to store data for your suppliers, products, customers, and sales made on a daily basis in a table.
Having so much data in a table soon becomes problematic as the transactions grow into tens and hundreds of thousands. If this data is summarized in different tables, each storing data relating to one entity, then there's a need to establish the relationships between the different tables. This is where data modelling comes in.
The purpose of data modeling in Power BI is to create a structured, optimized, and meaningful representation of data to support effective analysis, reporting, and visualization. Data modeling is a critical step in the Power BI workflow that transforms raw data into actionable insights for decision-making and business intelligence.
A data model in Power BI is a logical representation of how data is structured and connected within the tool. It consists of:
Tables: Represent the data from one or more sources like Excel files, CSVs, or cloud services.
Relationships: Define how these tables are connected to each other.
DAX (Data Analysis Expressions): The formula language used to create custom calculations, i.e., measures and calculated columns
A well-designed data model is the foundation for creating reports and visualizations fast and should have the following features.
Scalability: Well-designed data models should handle more complex analysis as business needs grow without major changes.
Reduced Maintenance: Once the model is set up, it should be easier to maintain if it’s well organized and streamlined.
Proper Documentation: Its design should ensure that anyone working with it can easily understand how it works.
Why is Data Modeling Important in Power BI?
- Efficient Data Exploration
Data Modeling helps create hierarchies and drill-down paths, allowing users to easily explore data, uncover insights and identify trends.
For example, hierarchy looks like this;
In a Sales Data set:
Country →County →City →Store →Product
or
In a Time Data set:
Year →Quarter →Month →Day.
- Improved Performance
A well-designed data model makes data retrieval fast and efficient, whereas a poorly structured data model with redundant data or complex relationships can slow down queries and delay reports.
- Accurate Reporting
Having a good data model ensures accuracy, consistency, and reliability. This helps in generating reliable reports, provides better insights, and leads to better decision-making. Proper data modeling helps eliminate errors and ensures that the data used for analysis is correct and dependable.
Types of Data Modeling
Relational Model: It prioritizes writing data efficiently without duplication. For example, a customer's address is only stored in one table, and linked to orders via IDs. This makes it great for applications but complex for analysts to run a simple report.
Dimensional Model: Uses Star or Snowflake schemas. It trades storage efficiency for query speed by duplicating and centralizing context (denormalization). For example, instead of querying multiple interconnected tables, an analyst looks at one central Sales Fact table linked to a Customer Dimension table to get sales by customer in seconds
The table below shows comparison between the relational and dimensional data models.
Fact Tables and Dimension Tables
What is a Fact Table?
A Fact table stores quantitative and numerical data which can be aggregated and analyzed. Fact table is a centerpiece of a data model and has information and has information about Transactions, Numerical measures like Sales amount, Quantity sold, Product cost and Foreign keys that links to dimension tables.
In a Fact table, each row represents a single transaction or event, and each column represents a measurable attribute. For example, a sales fact table may have columns for the product sold, quantity and the total sales amount.
Key Characteristics:
Measures or Metrics: Fact tables contain facts (quantitative data) such as sales amount, quantity sold, etc.
Foreign Keys: They have foreign keys that link to dimension tables, providing context for the facts.
Granularity: The level of detail or granularity of the fact table is defined during the design process (e.g., sales per day, per transaction).
Example:
Consider a retail business analyzing its sales data. The fact table might look like this:
ProductID, and StoreID are foreign keys linking to dimension tables.
What is a Dimension Table?
A dimension table in Power BI contains descriptive data. It provides context and additional details to the numerical data in a fact table. For Example: A product dimension table may have columns such as product name, category, description and supplier name. Dimension tables usually contain fewer rows, whereas Fact tables can hold large amounts of data that grows over time.
In a data model, a dimension table is connected to a fact table through a relationship. This allows Power BI users to analyze the fact table data by filtering, grouping and aggregating it based on the attributes in the dimension table.
Key Characteristics:
- Descriptive Data: Contains descriptive data such as product names, customer names, dates, etc.
- Primary Keys: Each dimension table has a primary key that corresponds to a foreign key in the fact table.
- Attributes: Dimension tables include various attributes that describe the dimension.
Example:
Continuing with the retail business, dimension tables might look like this:
Product Dimension Table
Store Dimension Table
In these examples:
The Product Dimension Table provides details about each product.
The Store Dimension Table provides details about each store.
By linking these dimension tables to the fact table, you can generate insightful reports and analyses that provide a complete view of your business data.
Schemas
What is a Schema?
A schema is a collection of database objects, including tables, views, and indexes. It defines the structure of the data and the relationships between the different objects. In Power BI, a schema represents a logical grouping of tables that are related to each other.
Types of Schemas
There are two types of schemas: Star schema and Snowflake schema. Let's explore each of these in detail.
Star schema
Star Schema is a database design that separates data into fact tables(for summarizations) and dimension tables(for filtering and grouping).Its highly effective for Power BI as it optimizes the performance and usability by structuring data for easy reporting and visualization.
Explicit Measures are custom calculations created with DAX Formulas, whereas Implicit Measures are automatically generated by Power BI from columns that can be summarized such as sum or average without needing custom formulas.
A star schema may be preferable in some instances because it offers the following;
Simplified Querying: Star Schemas are easy to understand and implement. Their denormalized structure reduces the number of joins and required to retrieve data. This simplifies and leads to faster data aggregation and reporting.
Denormalization: It is the process of combining tables to reduce complexity and improve performance often by adding redundant data.
Flexibility: Star schemas are adaptable and easy to update when new data sources are added to a Power BI report.
Intuitive Analysis: Star schemas provide more intuitive and user-friendly experience for end-users of a Power BI report. The data is organized in a logical and easy to understand manner, making it easier for end-users to identify trends and patterns in the data.
Easier Maintenance: Having fewer tables and simple relationships between them, star schemas are easier to maintain and update over time.
An example of a model with a star schema design is shown below.
Snowflake schema
The Snowflake Schema is like the Star schema in terms of having a central fact table that everything passes through, but the main difference is that the Snowflake schema has normalized dimension tables as opposed to denormalized dimension tables for the Star schema.
The Star schema typically goes down two levels of data hierarchy while the Snowflake schema goes to a third level. The purpose of a Snowflake schema is to normalize the denormalized data in a Star schema.
In a snowflake schema, the relationships between tables form a pattern that resembles a snowflake, with the central fact table connected to multiple dimension tables and each dimension table potentially connected to additional sub-dimension tables.
An example of a model with a snowflake schema design is shown below.
Relationships in Power BI
In Power BI, relationships between tables are created to enable data to be combined across them. This is done using the following:
Primary and Foreign Keys: Ensures unique identifiers in fact tables match corresponding keys in dimension tables.
Cardinality: Defines the type of relationship between two tables, defining how data and filters flow between them.
Relationships Concepts
Primary keys
Primary keys are unique columns in a table that helps creates a relationship between two tables by sharing common values. The Primary key uniquely identifies each record.
Foreign keys
Foreign key is a column in one table whose values match the values of the primary key in another table.
In the above image, In the product dimension table, primary key is the product Id column, which is a unique identifier of a product or row in the products table. The product dimension table is connected to the fact Orders Fact table based on Product ID, so here the product Id column within the orders table acts as a foreign key.
Cardinality
Each relationship in a model is defined by a cardinality type. It refers to the number of unique values in one table related to the number of unique values in another table.
- One to One (1:1)
This relationship occurs when one record in the first table is related to one and only one record in the second table. This is relatively uncommon in Power BI.
One to Many(1:*)
This occurs when a single record in one table relates to multiple records in another table, commonly used to link dimension tables(one side)to fact tables(many side).
Many to One(*:1)
This occurs when multiple records in a fact table relate to a single record in a dimensional table, which is commonly used in Power BI because it simplifies data aggregation such as sum or average and enhances filtering.
Many to Many (* : *)
This occurs when many records in the first table can be related to many records in the second table. This type of relationship is not directly supported in Power BI because it creates ambiguity in data aggregation and filtering, leading to performance issues and invalid results. Instead, such relationships require a bridge table to resolve the Many to Many connections.
Ambiguity: Unclear data aggregation due to multiple relationship paths.
Bridge Table: A table that links Many-to-Many relationships.
Cross Filter Direction
In Power BI, the cross-filter direction controls how filters propagate between visuals. Each relationship in a model defined with a cross-filter direction. It can be a single (Single directional) or both (Bi-directional).
Single
A single arrowhead represents a single-direction filter in the direction of the arrowhead. Here, only one of the tables in the relationship can filter data across to the other table. This means filtering data in one table affects the data shown in the other table, but filtering in the other table does not affect the data shown in the first table.
Bi-Directional
Bi-Directional cross-filtering enables them to apply filters on both sides of a table relationship(data can be filtered in either direction).This means filtering data in one table affects the data shown in the other table and vice versa. This type of filtering is less common and can degrade performance while potentially creating ambiguous filter paths. Cross-Filter options are dependent on the cardinality.
Active and Inactive Relationships
Active Relationships
Active Relationships are created in Power Bi Desktop’s Model View. To create one, drag and drop a common filed from one table to another table. Active relationships are visible in solid lines. These are default relationships used for filtering and calculations between tables ensuring data is linked for analysis.
Inactive Relationships
In Power Bi, inactive relationships are used when multiple relationships exist between tables, letting us specify which one should be used for certain calculations. Marking a relationship as inactive prevents it from being used by default, resolving issues like ambiguous paths and conflicting filters.
Joins
What are Joins?
Table joins can be used to compare or combine rows between tables. They compare tables based on one or more related columns. When the compared row values between two tables are identical, they are considered a match. The behavior that follows depends on the join type. Mastering joins is a valuable skill for the data cleaning process.
How to Merge Queries
So, how do you perform a join in Power Query? The easiest way to join tables in Power Query is by using merge queries. To start, go to the Home tab in the ribbon and navigate to the Combine section. There you will find the Merge Queries button. You have two options there:
Merge Queries performs a join on your current table and adds the result of this merge in the same query. This means you can easily see the impact of the merge in a single place.
Merge Queries as New performs the same join on your table but puts the result of the merge in a separate query. The benefit of this approach is that your starting query remains the same. This means that you can still reference that starting query for other purposes.
After choosing your desired way to merge queries, the below pop-up appears. This menu allows you to choose the Merge type you want to perform.
Types of Joins
- Left Outer Join
One of the most common joins is the Left Outer Join or simply the Left Join. The left outer join returns all rows from the Left Table (table1). Then, by matching one or more column values with rows from table1 with similar column(s) from rows in table2, the join returns the matching values of the Right Table (table2).
- Right Outer Join
The Right Outer Join returns all values from the Right Table (Table2) while only returning the matching values from the Left Table (Table1).
Its behavior is very similar to the Left Outer join, except that its base table is the Right table instead of the Left.
- Full Outer Join
There are cases where you want to return all rows from both Table1 and Table2. In those cases, the Full Outer Join is your friend.
The Full Outer Join tries to match column values from Table1 with Table2 and if successful, puts them next to each other. The values of Table1 that don’t exist in Table2 get their own row. Any rows without a match in Table 2 return null values for these rows.
Similarly, the values of Table2 that don’t exist in Table1 also get their own row. For these rows, the missing columns from Table1 get null values.
- Left Anti Join
The Left Anti Join only returns rows from the Left Table (Table1). Based on the key columns, the join searches for which values in Table1 do not exist in Table2. It then only returns the unique values from Table1.
- Right Anti Join
The Right Anti-Join is the twin sister of the Left Anti-Join. It returns the rows from the Right Table (Table2) which are not present in the Left Table (Table1).
- Inner Join
The Inner Join is a useful join that returns only those rows that have matching values in both the Left Table and the Right Table. This can be useful when creating tables for your data model. Imagine filtering down a transaction table to contain only those transactions you want to analyze, but your product table still contains all products from your database.
- Self Join
A self-join is a join like all the others, but the table is joined with itself. In the Merge Queries interface, you can select the table to merge. When you want to do a self-join, you can simply fill in the same table twice.
The following images show the common types of joins in Power BI.
Conclusion
This article has provided an overview of data modelling in Power BI by exploring the types of modelling, different types of schemas, the relationships that exist within a schema and how to join tables in Power Query, covering standard joins like Inner, Left, Right, and Full Outer. The article also explores special joins such as the Self Join, along with ways to union tables.
It’s important to remember that each join type has its use case and that you should choose the one that best fits your needs. With these tools, you can now efficiently model data in Power BI.
Enjoy Power BI!
Top comments (0)