How Joins Work in Incorta

[KailaT]

Introduction

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 simple M:1 join Incorta by:

1. Select Sales as the child table (left side in the Join screen).

2. 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:

1. Select the Customer dimension in Analyzer.

2. 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.