-
Incorta supports the following types of joins:
-
One-to-one (1:1)
-
Many-to-one (M:1)
-
Many-to-many (M:M) joins using Materialized Views
Example: Simple Many-to-one (M:1) Join
Tables:
-
Sales table
-
Customer table
The Sales table has multiple sales records for one customer. The Customer table has only one record for every customer.
Create a 1:1 join Incorta by:
-
Select Sales as the child table (left side in the Join screen).
-
Select Customer as the parent table (right side in the Join screen).
The join definition for this example is: Sales.Customer_id = Customer.Customer_id.
The join column in the parent table must be a unique primary key.
All joins in Incorta are left outer joins by default. This means records in the child table (for example, Sales) are included in a query even if they don’t have a corresponding record in the parent table (for example, if customer_id is null, or Sales.customer_id does not exist in the Customer table). If the report groups records by a field from the parent table (for example, CustomerName), records without a corresponding record display under the NULL bucket (CustomerName = NULL).
To transform an outer join to an inner join, add a NOT NULL filter on the parent column in the Insight. For example, Customer.CustomerKey is NOT NULL.
To transform the default left outer join into a full outer join:
-
Select the Customer dimension in Analyzer.
-
Select Show Empty Group to see customers with no Sales records. You must select Show Empty Group for every dimension on which you want to see a right outer join.
You can create a join between two tables in different schemas. The join displays under both schemas (child and parent schema), but you can only edit it from the child schema.
Example: Complex Many-to-one (M:1) Join
Tables:
-
Employee table with a dimension of type 2. The key is Employee_id, start_effective_date, end_effective_date
-
Payroll table with a foreign key pointing to the Employee table
The join definition for this example is:
- Payroll.Employee_id = Employee.Employee_id
- Payroll.Txn_date >= Employee.start_effective_date
- Payroll.Txn_date < Employee.end_effective_date
This type of join is called a range join. Incorta requires an equi-join condition (for example, Payroll.Employee_id = Employee.Employee_id) in every range join definition.
-
-
Based on the example Hannah gave, but Incorta can also support a more complex scenario. If the parent table Employee only have the start_effective_date, and no end_effective_date column, the following join condition will also work but more expensive in join calculation during data loading time:
- Payroll.Employee_id = Employee.Employee_id
- Payroll.Txn_date >= Employee.start_effective_date
What Incorta will do is to find all records from Employee table with the Employee_id matched from the child table Payroll.Employee_id and sort the records from Employee table with the same Employee_id by start_effective_date and join to the record with the same or the Max value (latest) in the start_effective_date which is greater than (earlier) than the Txn_date.
This behavior is to avoid you to get a random record returned from Employee table with the same Employee_id.
This solution simplify the model so you don't need to write a ETL program or a MV to generate an end_effective_date column. -
FYI, I added the above content as a KB article here: https://community.incorta.com/t/x17l91/how-joins-work-in-incorta.