Introducing relationships

Now you’ve learned about summarising by group, you’re going to add to that by learning about the following data relationships:

Data model relationships
Relationship types
Autodetect on load

When you work with more than one query or data source within a pbix file, you’re likely to use relationships - even if they are not knowingly referenced or created as you work.

Think of it this way, you wouldn’t use an Excel workbook to store multiple worksheets that had nothing to do with each other. The same is true in Power BI Desktop. When you use multiple sources, they should be related.

How does Power BI Desktop create relationships?

Relationships are created either automatically by Power BI Desktop and the Query Editor, or they are created manually. They might include any of the following:

Formal and visible links
Hierarchies or natural layers in your data
Joins between tables
Less formal joins that are not visible but still present

Relationships and join types in database design and usage are huge, multi-layered topics, and are subject to large amounts of written material and debate.

In this step, you’ll examine the main principles required to successfully identify, create, edit and understand relationships as they apply to Power BI Desktop and its components.

Introducing data model relationships

Relationships let you use data from different sources in a useful and meaningful way. For example, this could be on a visual like a chart or, to answer a question more meaningfully. Your data will have natural relationships. Here are a few examples:

You have a list of customers in one table and products purchased in another
IT assets are in a table and where they have been deployed is in another table
HR information lists employees, and facilities information lists locations where the employees work
Manufacturing equipment is held in an asset register, whereas product lines produced on the equipment are held as separate data
Patient data is held in a data system and patient referrals is recorded in another system

What other examples of data model relationships can you think of?

Relationship types

Relationships are links between separate tables that have a common field or column. To use the data from each table in a meaningful way there only needs to be a single common field.

Here are the three main types of relationships:

One to many or many to one - Here, one table of data has many related records in another table. For instance, one customer places many orders. This is by far the most common relationship type.
One to one - In this relationship, one record in a table only has one related record in another. For instance, one IT asset is deployed to one person.
Many to many - Many records in one table can be related to many records on another. For example, one piece of manufacturing equipment could produce many products, or one product could be produced on many pieces of equipment.

Unique data

Furthermore, the type of relationship is known as the relationship’s cardinality. Wherever there is a reference to ‘One’ in the relationship type, it indicates that some data is unique in a table.

Autodetect on load

Power BI Desktop takes much of the hard work out of creating relationships between tables by using a process called Autodetect. This is an automatic process and occurs by default whenever a table is added to the data model.

Power BI Desktop will audit the new table and identify the columns, data types, and data. It also determines whether any of the columns contain unique data. This is important because Autodetect will try to establish one to many, or one to one relationships with tables already in the data model.

For Autodetect to work, the unique data must be found in another table’s column. The name of the column does not need to be the same, but the data type must match. It does not need to match every record.

A top tip graphic showing the following helpful advice to participants. If you are connecting to a data source which already has related tables, Power BI Desktop will try to recreate the relationships automatically.

Summary

You’ve now learned about data model relationships, relationship types and autodetect on load. This is the foundation on which you’ll build in the next step, and where you’ll learn about managing these relationships in Power BI Desktop’s Model View.

Model view and formatting data

SELF PACED PLATFORM

TRAINING CONTENT

JOB ROLE PATHS

CERTIFICATIONS