If a query includes multiple sources, create relationships in Builder to define how to combine their records based on a shared value, such as an entity ID, account number, or department.
Note: In SQL Editor, use a JOIN clause to define relationships between a query's sources.
Overview
For example, say your query includes an Orders fact table and a Customers dimension table as sources. On the Relationships tab, you can define how to relate the orders:
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 111222 | 001 | 01/02/2021 |
| 121212 | 010 | 02/01/2021 |
| 212121 | 100 | 01/12/2021 |
to customers:
| CustomerID | CustomerName | Region |
|---|---|---|
| 001 | AAA Dry Cleaners | North |
| 010 | Wayne Technology | East |
| 011 | Señor Taqueria | West |
Tip: To identify a column with unique values—such as CustomerID—in a dimension table, mark it as a Key column.
Since both tables include customer IDs, you can create a relationship based on their respective CustomerID columns and select how to combine—or join—their records.
A Left join relationship returns all records from the first source—on the left—and any also matched in the other.
Tip: To avoid duplicates in a Left join, ensure the second source column—on the right in the relationship—contains unique values, such as a dimension table's key column.
For example, in the relationship between our Orders and Customers tables, Left join returns all orders, with additional details about those with customer IDs matched in the Customers table. Orders with customer IDs not matched in the Customers table include null values for the additional customer data.
| OrderID | CustomerID | CustomerName | OrderDate | Region |
|---|---|---|---|---|
| 111222 | 001 | AAA Dry Cleaners | 01/02/2021 | North |
| 121212 | 010 | Wayne Technology | 02/01/2021 | East |
| 212121 | 100 | -------------------- | 01/12/2021 | ------- |
A Right join relationship returns all records from the second source—on the right—and any also matched in the other.
Tip: To avoid duplicates in a Right join, ensure the first source column—on the left in the relationship—contains unique values, such as a dimension table's key column.
In the case of our Orders and Customers tables, Right join returns all customers, with additional details for those with IDs matched in the Orders table. Customers with IDs not matched in the Orders table include null values for the additional order data.
| OrderID | CustomerID | CustomerName | OrderDate | Region |
|---|---|---|---|---|
| 111222 | 001 | AAA Dry Cleaners | 01/02/2021 | North |
| 121212 | 010 | Wayne Technology | 02/01/2021 | East |
| -------- | 011 | Señor Taqueria | ------------- | West |
An Inner join relationship returns only records that appear in both sources.
In our Orders and Customers tables' relationship, Inner join returns only orders from customers with IDs matched in both sources.
| OrderID | CustomerID | CustomerName | OrderDate | Region |
|---|---|---|---|---|
| 111222 | 001 | AAA Dry Cleaners | 01/02/2021 | North |
| 121212 | 010 | Wayne Technology | 02/01/2021 | East |
A Full join relationship returns all records from either source.
In the example of our Orders and Customers tables, Full join returns all orders and customers. Records with customer IDs matched in only one source include null values for missing data.
| OrderID | CustomerID | CustomerName | OrderDate | Region |
|---|---|---|---|---|
| 111222 | 001 | AAA Dry Cleaners | 01/02/2021 | North |
| 121212 | 010 | Wayne Technology | 02/01/2021 | East |
| -------- | 011 | Señor Taqueria | ------------- | West |
| 212121 | 100 | -------------------- | 01/12/2021 | ------- |
On the Relationships tab, you define how the query's multiple sources relate to one another. To define this relationship, you match data shared between the sources, and select how to join their records in the query results.
Note: Query relationships are cumulative; they build on each other. The sources in one join determine the columns available to join to other sources.
Step 1. Specify the sources to join
To define a source's relationship with other sources in the query, drag it from Sources to the Relationships tab.
- To define the first relationship between the query's sources, drag two sources from Sources to Join 1.
- To define a source's relationship with a previous join's sources, click Add another join, and drag the source from Sources to the new join.
Note: If a query has multiple joins, arrange them in the order they should occur, as each join depends on the previous join's sources. To reposition a join, select Move up or Move down from its menu. To delete a join, such as if the query no longer includes its source, select Remove from its menu.
Step 2. Specify how the sources relate
To define the relationship between sources, you can map shared values based on:
- Columns with corresponding data
- A calculated field, to adjust a source's values as needed to accurately match the other source
Tip: You can only join columns of the same data type. To match data of different data types, such as if account IDs are integers in one source and text strings in the other, edit a column's type from the Field properties panel.
In the join, select the corresponding columns from each source, and select how to match records based on their values.
| Operator | Description |
|---|---|
| Equal to (=) | Matches records with identical values in both sources |
| Not equal to (!=) | Matches records with different values in both sources, such as to map account IDs to account names |
| Greater than (>) | Matches records when the left source's numerical or date/time value is larger or later than the right source's value |
| Greater than or equal to (>=) | Matches records when the left source's numerical or date/time value is identical to, larger than, or later than the right source's value |
| Less than (<) | Matches records when the left source's numerical or date/time value is smaller or earlier than the right source's value |
| Less than or equal to (<=) | Matches records when the left source's numerical or date/time value is identical to, smaller than, or earlier than the right source's value |
To accurately map values between sources, you can apply a calculation to source columns. For example, to map to account numbers in an Account Map dimension table, you may need to:
- Adjust the values within a single source column, such as to extract the first six digits from account numbers in a Trial Balance fact table—
SS_LEFT(tb.account, 6) - Use values from multiple sources' columns, such as combine—or concatenate—account numbers and IDs from the previously joined Trial Balance fact table and Entity Map dimension table, respectively—
CONCAT(tb.account,entity.id) - Define a common pattern within a column's values, such as to isolate the account number from prefixes or suffixes in a Trial Balance fact table—
tb.account LIKE __1005%
Note: To define a pattern, you can use wildcards to represent characters to ignore—an underscore (
_) for a single character, or a percent sign (%) for no or multiple characters.
To apply a calculation to a source's values:
- In the join, select Calculated field under the source with the values to adjust.
- On the Field properties panel, drag the columns to include in the calculation from Sources to Included columns.
Note: To ensure proper SQL, include only columns from the selected source.
- Under Calculation, compose the Structured Query Language (SQL) operation to apply to the columns' values.
- To refer to column in the calculation, click it or select Include in calculation from its menu under Included columns, or enter its number in brackets such as
{1}+{2}. - To quickly add a SQL operator or function to the calculation, select it from the Calculation menu.
- To refer to column in the calculation, click it or select Include in calculation from its menu under Included columns, or enter its number in brackets such as
- In the join, select how to match records based on the joined columns' values:
Operator Description Equal to (=) Matches records with identical values in both sources Not equal to (!=) Matches records with different values in both sources, such as to map account IDs to account names Like If the calculated field defines a pattern with wildcards, matches records with values that match the pattern Not like If the calculated field defines a pattern with wildcards, matches records with values that don't match the pattern Greater than (>) Matches records when the left source's numerical or date/time value is larger or later than the right source's value Greater than or equal to (>=) Matches records when the left source's numerical or date/time value is identical to, larger than, or later than the right source's value Less than (<) Matches records when the left source's numerical or date/time value is smaller or earlier than the right source's value Less than or equal to (<=) Matches records when the left source's numerical or date/time value is identical to, smaller than, or earlier than the right source's value
Step 3. Define any compound joins
A single join can map multiple values between its sources. To map additional pairs of values within a join:
- Click Add field.
- Specify the additional columns or calculated fields to map between the sources.
- Select whether to join multiple mapped values (And) or only specific values based on the data (Or).
If multiple values in a source each correspond to different values in the other source, select And to match records based on multiple mapped values.
For example, if a Trial Balance fact table has columns that map to the combination of the Account number and Entity key columns in an Account Mapping dimension table, click And and join the tables' corresponding columns.
Note: In SQL Editor, the relationship appears as JOIN ON (TB.entity = AccountMap.entity) AND (TB.account = AccountMap.account)
If a source's values can correspond to multiple values in the other source depending on the data, select Or to match records based on the specific mapped values.
For example, if the Account key column in an Account Mapping dimension table maps to either the Account or Sub-account column in the Trial Balance fact table, click Or and join the key column to both of the fact table's columns.
Note: In SQL Editor, the relationship appears as JOIN ON (AccountMap.account = TB.account) OR (AccountMap.account = TB.subaccount)
Note: If your compound join includes both And and Or operators, it matches records that meet all criteria separated by an And, plus any that meet criteria separated by an Or.
Step 4. Select how to join the sources' records
In the join, select how to combine the sources' records in the query results:
| Join | Returns |
|---|---|
| Left join |
All records from the left source in the join and any matched in the right source |
| Right join |
All records from the right source in the join and any matched in the left source |
| Inner join |
Only records matched in both sources |
| Full join |
All records from either source |
After you define the relationship between all the query's sources, click Save .