Add columns to a base table with a relationship
1. Add columns to a base table with a relationship
Welcome back. In this video, we'll demonstrate how to add and manage relationships in a data model.2. Welcome back
Oakmark has a few problems with the usage of a join in one of our data models, where applications is joined to loans.3. Welcome back
They've noticed that 90% of the data model's users never even touch a column from the applications table. There's concern that this extra join is unnecessarily increasing costs, as it's performed every time someone uses the model, even when they don't need the data from the join.4. Welcome back
To address their concerns, let's replace the join with a relationship.5. Demo
Starting with a base table of just loan data, we'll add the application data back on with a relationship.6. Demo
Select the base table and click **Add relationship** to open the **Add a relationship** modal.7. Demo
Then, select the applications table as the **Target source** and use **Application Id** as the Join key.8. Demo
We can add a name and description so that other users can identify the relationship. Let's name the relationship "Loans to applications" and enter in the description "Applications for each loan".9. Demo
After we click save, we can see the new relationship appears under **Relationships** in the editor panel.10. Demo
If we click **Publish** and then view the published version of the model, we can see the relationship listed on the element overview for the base table.11. Demo
However, to actually use the relationship, we'll need to first create a new workbook and add the element from our data model.12. Demo
To use columns from the relationship, we'll click **Available columns** to make the **Source columns** popover appear.13. Demo
If we scroll down, we can see there is a collapsed section of related columns from the relationship.14. Demo
We can select the check box next to that table name to add all columns from the relationship, or we can expand the section and add individual columns.15. Demo
After we select our columns, they appear in the **Columns** section of the editor panel. Columns added via relationships have the name of their source table appended to the column name by default.16. Demo
Now, we're free to use these columns like any other column in Sigma.17. Demo
If we review Query history, we can see that the join was not performed until the columns were added. And, when we did add them, it was performed automatically.18. Let's practice!
Now, it's your turn to create some relationships and use them in a downstream workbook to add new columns!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.