Creating a relationship between sheets in a multi-sheet file data source

A relationship joins logically related tables that the users want to combine in a single query.

1. Go to the dashboard with the multi-sheet file.
2. If necessary, enable edit mode by clicking the Edit icon until it is green with a checkmark .
3. From the dashboard, click the Sources icon .
4. Select the sheets for which you want to create a relationship.

   You can use one of the following methods to select sheets:

   • Select a sheet from the metadata tree:

     In the metadata tree, click a sheet for the relationship. Click the More icon , then click Create relationship.

   • Select two sheets from the metadata tree:

     In the metadata tree, press Ctrl and click the two sheets that you want to join in a relationship, then click Relationship.

   • Select a sheet from the Relationships tab in the data preview pane:

     Click the Show data icon to open the data preview pane, then click the Relationships tab. Right-click a sheet for the relationship, then select New > Relationship.

5. In the Create relationship dialog box, select the first sheet and the second sheet to include in the relationship.

   Depending on the method that you used to start the relationship, the first and second sheets might already be added. If both sheets are added, you do not need to select the sheets.

6. Find the matching columns in both tables, then select Match selected columns.

   The matching columns are highlighted in the data grid. You might need to click Refresh to retrieve the data.

7. Click Matched columns to specify the join operator for the match.

   In the Defined matches dialog box, select a join operator. By default, columns are matched based on similar values, by using the equal (=) operator. However, you can also match columns based on a range of values by choosing a different join operator. For more information, see Join operators in relationships.

8. Click the relationship settings icon .

   By default, the relationship settings are detected from the source.

   Review, and if needed, modify the following settings:

   Relationship Type

   Specify one of the following relationship types:

   Inner join

   Include matching rows only.

   Left outer join

   Include all rows from the first table and matching rows from the second table.

   Right outer join

   Include matching rows from the first table and all rows from the second table.

   Full outer join

   Include all rows from both the first table and the second table.

   Cardinality

   Specify one of the following cardinality types:

   1-to-1

   Each row in the first table is related to exactly one matching row in the second table.

   1-to-many

   Each row in the first table has one or more matching rows in the second table.

   Many-to-1

   One or more rows in the first table match to a single row in the second table.

   Optimization

   Use the optimization filters to reduce the number of rows of data that are retrieved when the join is run. For more information, see Join optimization for relationships.

9. Click OK.