Unit 5 Module 1

Creating Relationship

1. Look at the Create Relationships window. Manage Relationships window / New 2. From the first drop-down menus the user selects the tables that they wish to create a realationship between. Those tables will be loaded in the window, at which point the user selects the columns from each table that require the relationship. 3. Once the related columns are selected, Power Bi will automatically detect the cardinality. If the user attempts to set up relationship with the wrong cardinality, an error message will appear and the relationship will not be created.

Limitations

1. Only one column can be used on each side of the relationship. If a combination of two or more columns is needed, a custom column can be added in the query or a calculated column that concatenates the columns can be created. 2. Relationships cannot be created that from a closed loop. Note: Power BI will still let you create such a relationship, but it will mark the relationship as inactive. 3. Power BI does not support multiple role-playing relationships, which come from role-playing lookup tables. A role-playing lookup table is a table that joins the same fact table multiple times (and thus plays multiple roles) Power BI can only support one active role-playing relationship at a time, where an active relationship is a relationship that Power BI follows to automatically aggregate the data between the two linked tables. In the Power BI Relationships View, active relationships are represented by solid lines, and inactive relationships are represented by dotted lines. When the relationships are imported into Power BI, it defaults the first one to active and marks the remaining as inactive. In order to manage relationships, open the Manage Relationships window, which can be accessed through Home tab or Modeling tab / Manage Relationships button

Relationship View

Another way to view and manage relationships is to use the Relationships View. Unlike the Manage Relationships window, one of the strengths of Relationship View is much easier to quickly visualize and understand the model schema and relationships. A user can: 1. Visualize the model schema 2. Create and manage relationships 3. Make other limited schema changes. right-click on an object, a context menu will open that shows the supported operations for that specific option.

Managing Relationship Window

Any relationship created, whether automatically or manually, in a model can be viewed and managed by using the Manage Relationships window. In addition, any active relationships will be checked. Autodetect searches for missing relationships between datasets. If a suitable relationship is detected, a new relationship will be created. Edit opens the Edit Relationship, which is the same as the Create Relationship window, except with all the fields pre-populated. Delete removes the selected relationship.

Auto-detect Relationship

Auto-detect to try and find some relationships 1. Open the file 2. In Data or Reports View, click Manage Relationships 3. In the Manage Relationships window, click the Autodetect button. If there is no relationship between 2 tables, the auto-detection will find one new relationship.

Auto-Detecting Relationships

When a report is created using unrelated tables, Power BI can auto-detect and create any potential missing relationships by default. However, relationship detection within Power BI can be reconfigured to meet personal needs or desires. Within the Power BI, there are 3 options that control how relationships are detected. File / Options & Settings / Options / Data Load Update relationships when refreshing queries will attempt to discover missing relationships when refreshing imported data. Since this may result in dropping manually created relationships after data is loaded will attempt to auto-detect missing relationships after the data is loaded. If there is no relationship between two tables and you attempt to analyze the data in the report, repeating values will be generated. In general, the easiest way to handle missing relationships is to click Autodetect button in Managing Relationships window.

Relationship View: Managing Relationships

Within the Relationship View, a relationship is visualized as a connector between two tables, with the symbols at the ends of those connectors representing the cardinality. The number (1) denotes that the table on the One side of the relationship, whereas the (*) is shown next to the table on the Many side of the relationship. When a relationship is selected, Relationship View hightlights it. When you use the mouse to hover over the relationship, Relationship view highlights columns in the joined tables to visually which columns are used in the relationship. Another feature of Relationship View is that relationships can be created by dragging a foreign key column and dropping it onto the primary key column in the lookup table.

Rules

when data is imported from different sources, or no table joins are defined in the data source, Power BI might be unable to detect relationships upon import. Two circumstances: 1. The two tables have matching columns. Note: the column names don't have to be the same, but they must have matching values. 2. The key column in the lookup table must have unique values, similar to a primary key in a relationship database. However, this does not mean that all other tables must join the lookup table on the same primary key. power BI doesn't require the two columns to have matching data types. This is because Power BI resolves the join by converting the values in the latter column to the Text data type.