Data Modelling, Relationships & Joins

Data Modelling in Power BI

Data modeling is the process of analyzing and defining all the different data types a business collects and produces, as well as the relationships between those bits of data. By using text, symbols, and diagrams, data modeling concepts create visual representations of data as it’s captured, stored, and used at the business.
As business determines how data is used and when, the data modeling process becomes an exercise in understanding and clarifying data requirements.

By modeling data, you can document what types of data you have, how you use it, and the data management requirements surrounding its usage, protection, and governance. The benefits of data modeling include:

• Creating a structure for collaboration between different teams.
• Revealing opportunities for improving business processes by defining data needs and uses.
• Saving time and money on IT and process investments through appropriate planning.
• Reducing errors (and error-prone redundant data entry) while improving data integrity.
• Increasing the speed and performance of data retrieval and analytics by planning for capacity and growth.
• Setting and tracking target key performance indicators tailored to your business objectives.

Types of Schemas

A snowflake schema is another way of organizing data. In a schema, dimension tables are split into smaller sub-dimensions to keep data more organized and detailed, just like snowflakes in a large lake.

Star Schema

Structure: A star schema resembles a star with a central fact table containing transactional data (e.g., sales data) surrounded by dimension tables (e.g., product details, customer information, time hierarchy ). These dimension tables are connected to the fact table through foreign keys.
Focus: Optimized for analytical workloads. The pre-aggregated data and clear relationships between tables enable efficient querying and analysis of trends and patterns.
Pros: Fast and efficient for complex analytical queries, reduces redundancy compared to fully flat tables, simplifies data modeling for data warehouses.
Cons: Might not be ideal for storing highly granular data or handling frequent schema changes, can be less efficient for operational tasks like real-time data updates.

Flat tables

Structure: Flat tables store all relevant data points for an entity (e.g., a product) within a single table, often with numerous columns to represent various attributes. This approach prioritizes ease of data access and flexibility.

Focus: Optimized for operational tasks and handling large volumes of detailed data. The denormalized structure allows for faster reads and writes of individual entities.
Pros: Simpler data access for specific entities, efficient for writes and reads of detailed data, can be flexible to accommodate schema changes.
Cons: Can lead to significant data redundancy, potentially slower for complex analytical queries that require joins across multiple tables, requires careful management to avoid data inconsistency.

Snowflake Schema

Structure Snowflake Schema is also a type of multidimensional model used for data warehouses. In the snowflake schema, the fact tables, dimension tables and sub-dimension tables are included. This schema forms a snowflake structure with fact tables, dimension tables and sub-dimension tables
Focus Best for Flexibility and Data Integrity: If we need to handle large datasets with multiple levels of hierarchy and a high degree of normalization, the Snowflake Schema offers greater flexibility. It’s perfect for maintaining data integrity across complex datasets.
Pros : Ideal for large organizations dealing with large, normalized datasets or those with frequent updates, like customer or inventory management systems. It minimizes redundancy and improves storage efficiency.
Storage Considerations: Snowflake is more storage-efficient due to its normalized structure, making it a great choice for scenarios with complex, high-volume data.

Fact Tables and Dimension Tables

In data warehousing, fact tables and dimension tables are key components of star or snowflake schemas. Fact tables store numeric data like sales or order amounts and include foreign keys linking to dimension tables. Dimension tables provide context with descriptive details like product names or customer demographics. The main difference between Fact or Reality table and the Dimension table is that dimension table contains attributes on that measures are taken actually table. Below are the main differences between the two:

Relationships in Power BI

A model relationship propagates filters applied on the column of one model table to a different model table. Filters will propagate so long as there's a relationship path to follow, which can involve propagation to multiple tables.

Relationship paths are deterministic, meaning that filters are always propagated in the same way and without random variation. Relationships can, however, be disabled, or have filter context modified by model calculations that use particular Data Analysis Expressions (DAX) functions.

Cardinality

Each model relationship is defined by a cardinality type. There are three cardinality type options, representing the data characteristics of the "from" and "to" related columns. The "one" side means the column contains unique values; the "many" side means the column can contain duplicate values.

The three) options, together with their shorthand notations, are described in the following list:

One-to-many (1:*)
One-to-one (1:1)
Many-to-many (:)

When you create a relationship in Power BI Desktop, the designer automatically detects and sets the cardinality type. Power BI Desktop queries the model to know which columns contain unique values. For import models, it uses internal storage statistics; for DirectQuery models it sends profiling queries to the data source. Sometimes, however, Power BI Desktop can get it wrong. It can get it wrong when tables are yet to be loaded with data, or because columns that you expect to contain duplicate values currently contain unique values. In either case, you can update the cardinality type as long as any "one" side columns contain unique values (or the table is yet to be loaded with rows of data).

One-to-many (and many-to-one) cardinality

The one-to-many and many-to-one cardinality options are essentially the same, and they're also the most common cardinality types.

When you configure a one-to-many or many-to-one relationship, choose the one that matches the order in which you related the columns. Consider how you would configure the relationship from the Product table to the Sales table by using the ProductID column found in each table. The cardinality type would be one-to-many, as the ProductID column in the Product table contains unique values. If you related the tables in the reverse direction, Sales to Product, then the cardinality would be many-to-one.

One-to-one cardinality

A one-to-one relationship means both columns contain unique values. This cardinality type isn't common, and it likely represents a suboptimal model design because of the storage of redundant data.

Many-to-many cardinality

A many-to-many relationship means both columns can contain duplicate values. This cardinality type is infrequently used. It's typically useful when designing complex model requirements. You can use it to relate many-to-many facts or to relate higher grain facts. For example, when sales target facts are stored at product category level and the product dimension table is stored at product level.

Filter Direction

Relationship Filters

In Power BI, when two tables are connected, filters can flow between them to control which data is visible in visuals. This flow is determined by the filter direction in the relationship settings.

There are two types:

Single-direction filter:

Allows filter context to flow from one table to another (most common).
Bi-directional filter: Allows filter context to flow in both directions between connected tables.
Single-Direction Filters
Best For:
Star schema models (fact and dimension tables)
Maintaining predictable filter behavior
Optimizing model performance
In a star schema, filter direction should typically flow from the dimension tables (e.g., Customers, Products) to the fact tables (e.g., Sales, Transactions). This setup reflects how users usually analyze data: e.g., sales by customer, not customers by sale.

Example:
You have a Sales table and a Products table. You want to filter sales by product category. The relationship should allow filters to flow from Products to Sales (one-to-many).

Bi-Directional Filters

Use with Caution:
Complex reporting scenarios
Many-to-many relationships
Role-based security (RLS) configurations
Bi-directional filtering lets two tables filter each other. While powerful, it can cause:

Ambiguous relationships
Unexpected results in visuals
Performance degradation, especially on large datasets
Example:
Imagine you have a Sales table and a Regions table, but not every sale is assigned to a region. A bi-directional filter might seem helpful, but it can lead to wrong totals or slow down the report.

Best Practices
Default to Single Direction
It’s simpler, faster, and more stable in most cases.

Use Bi-Directional Filters Sparingly
Only when absolutely necessary, and understand the impact on model performance.

Consider Using DAX Instead
Functions like USERELATIONSHIP() or CROSSFILTER() can give you more control without needing a full bi-directional relationship.

Watch Out for Ambiguity
If Power BI warns you about ambiguous paths — check your filter directions. Simplify relationships where possible.

Common Mistakes to Avoid

• Using bi-directional filters to fix visuals instead of fixing the data model
• Forgetting to evaluate performance after enabling both-direction filters
• Not testing RLS impact when filter directions change
• Creating circular filter paths, which Power BI won’t allow

Joins in Power Query

Allows you to combine data from different sources, offering comprehensive insights. Here’s an overview of the most common merge options in Power Query, explained with simple analogies and real-life examples.

Join Types

Now we will go into the different types of join functions Power Query has made available to us

1. Left Outer Join (All from first, matching from second)
   • Analogy: Imagine you’re planning a wedding. You have a guest list (Table 1), and then you collect RSVPs (Table 2). A left outer join ensures that everyone from your guest list (Table 1) will be included, even if they didn’t RSVP. For those who did RSVP, you’ll also have their response.
   • Practical Example: In a company, if you have a list of all employees (Table 1) and another list of their completed training sessions (Table 2), a left outer join shows all employees, whether or not they have completed any training (Table 2).

2. Right Outer Join (All from second, matching from first)
   • Analogy: Now, you’re only interested in the RSVPs (Table 2). You still want to know if they were on the original guest list (Table 1), but you also want to include people who RSVP’d without being invited. A right outer join shows you all RSVPs, whether or not they were on the guest list.
   • Practical Example: If you have a list of all customers who placed orders (Table 2) and another list of all potential customers (Table 1), a right outer join will show all customers who placed orders, even if they weren’t on the original prospect list.

3. Full Outer Join (All rows from both)
   • Analogy: Now you want to see both your guest list and RSVPs entirely. You want to know who was invited and who RSVP’d, and also see if anyone RSVP’d who wasn’t on your list. A full outer join shows all the data from both tables, matching where possible and leaving blanks where there are no matches.
   • Practical Example: If you have a list of products sold (Table 1) and a list of products in inventory (Table 2), a full outer join will give you all products, both sold and in inventory, even if some products were sold but are not in inventory and vice versa.

4. Inner Join (Only matching rows)
   • Analogy: You’re only interested in the people who were on your guest list and RSVP’d. If someone was invited but didn’t RSVP, or RSVP’d without being on the guest list, you don’t want to see them. An inner join only shows rows that exist in both tables.
   • Practical Example: If you have a list of current employees (Table 1) and a list of people who completed mandatory training (Table 2), an inner join will show only those who are both current employees and have completed the training.

5. Left Anti Join (Rows only in first)
   • Analogy: You want to see who was on your guest list but didn’t RSVP. A left anti join shows only the people who are in Table 1 (your guest list) but have no matching record in Table 2 (RSVPs).
   • Practical Example: If you have a list of all employees (Table 1) and another list of those who completed training (Table 2), a left anti join will show you the employees who haven’t completed the training.

6. Right Anti Join (Rows only in second)
   • Analogy: Now you want to see who RSVP’d but wasn’t on the original guest list. A right anti join shows only the people who are in Table 2 (RSVPs) but have no match in Table 1 (the guest list).
   • Practical Example: If you have a list of employees who signed up for a training session (Table 2) but weren’t originally required to take the training (Table 1), a right anti join will show you the unexpected attendees.

Key Insights:
• Use left outer join to retain all the data from the first table and match as much as you can from the second.
• Use right outer join to retain all the data from the second table and match as much as you can from the first.
• Use full outer join to combine everything from both tables, regardless of matching.
• Use inner join when you only care about data that exists in both tables.
• Use left anti join and right anti join to find rows that exist only in one of the tables but not both.

Power Query Joins vs Power BI Relationships

Performance
One of the distinguishing features of a reporting system is the performance of it. If you are building a reporting system, and the loading the report takes five minutes, then what is the benefit of this report over doing it in the operational system? because it would have taken that much in the operational system anyway!

When you build a reporting system, you should target creating reports that load fast. Having extra relationships, doesn’t help with the performance. The DAX queries behind the scene would be longer.

Another big deterministic of a good reporting model is the ability to make it easy-to-understand for data visualizers. If a data visualizer have 500 tables in a model all inter-related, anyone would have a hard time to understand the model.

Relationships come with a cost of complexity. You might need a both-directional relationship sometimes, or you might get into the problem of having an inactive relationship and trying to resolve that. Less relationship means fewer problems of this type when it is unnecessary.

Recommended Power BI Model

You should not have tons of relationships, and you should not create one big table with everything in it. The recommended thing is to design a totally different schema. A new layout of tables and relationships. Something that is different from the operating system’s database. Something that helps to get the best of both worlds.

This model, has many names; dimensional model, star schema, data warehouse etc. Through the process of creating this model, you combine some of the tables (dimensions), and keep some of them to be connected through the relationships (dimension to fact table relationships).

What if you don’t have a reference table that you can use as a dimension? what if there are only mapping tables in your data source? Many, have faced the same challenges. If you don’t have a reference table, you can create them from your mapping tables.
Just because you can load all the tables into the model and then create relationship, it doesn’t mean it is the best thing to do. Just because you can merge all the tables using Power Query, it doesn’t mean you should do it.

Power BI modeling starts with a pen and paper. This is very important that you start your modeling by a pen and paper. Yes, no tools or services needed. Just a pen and paper. You need to draw the dimensional model, the layout of the tables. You need to list fields in your dimension and fact tables. All of these are much better to be done using a pen and paper. You can then take the next step of implementing it using Power BI.

A good model should consider many things. It should first start with the requirement. How are you going to build a database diagram of a reporting system, when you don’t know what values should be shown in the report?

A good model also requires an understanding of the existing data, and how that data can be transformed into the star-schema model.

Don’t build your model on this rule that; this is the format that I get data, so can’t really do much about it. This is wrong! You need to transform that format.

My report works just fine, why bother?
Why bothering if your report is running just fine? or if it doesn’t have any performance problems? Do you really need to think about the model if you got only a few tables and everything works as it should? Why spending time on something that the users won’t see?

Yes, you need to work on your model. If you didn’t do that from the beginning of your implementation, do it now. Your model gets bigger and bigger every time. You will have more tables, more relationships, bigger challenges.

Building a proper data model is like building a base for a 100 stories building. would you first build the 100 stories and then come and fortify the base?! of course not. You should do it as soon as possible, or your whole building might collapse at some point.

Your Power BI data model is your world. It is up to you to decide in which world you want to live in. Would you prefer to have a model that is too complicated that every time someone asks for a change it ends up with hours and often days of work because it is not designed in the way it should? Or do you prefer to build a model that is a high performance, simple, easy to change, and simple to integrate? That is your choice; your model, your world.