How Joins Work in Incorta

[KailaT]

Understanding Incorta Joins: A Comprehensive Guide

In data analytics, effectively linking and analyzing data from diverse sources is crucial for deriving actionable insights. Incorta’s advanced join capabilities enable businesses to seamlessly integrate and analyze different datasets. Here’s a detailed look at how these joins work and their benefits for both business and technical purposes.

Types of Joins in Incorta

Incorta supports several join types to help you link data effectively:

• One-to-One (1:1): Connects each record in one table with a single corresponding record in another table.
• Many-to-One (M:1): Links multiple records in one table to a single record in another, useful for combining detailed and summary data.
• Many-to-Many (M): Utilizes Spark based Materialized Views to manage complex relationships where records in both tables can have multiple matches.

Example: Many-to-One (M:1) Join

Scenario: Sales Analysis

Tables:

• Sales Table: Contains multiple sales records for each customer.
• Customer Table: Contains a unique record for each customer.

Implementation:

1. Select Sales Table as the child table (left side in the Join screen).
2. Select Customer Table as the parent table (right side in the Join screen).
3. Join Definition: Sales.Customer_id = Customer.Customer_id

Technical Details:

• Primary Key: The join column in the Customer table (parent) must be a unique primary key to ensure accurate linking.
• Default Join Type: Incorta uses left outer joins by default. This means all records from the Sales table will appear in the result, even if there are no matching entries in the Customer table. Records with unmatched customer IDs will show up in the NULL bucket (e.g., CustomerName = NULL).

Business Benefits:

• Comprehensive Sales Insights: Aggregate sales data per customer to identify key trends.
• Enhanced Reporting: Automatically include all sales records, with missing customer information clearly indicated.

Adjusting Join Type:

• Inner Join: Apply a NOT NULL filter on the parent column to exclude records without matches (e.g., Customer.CustomerKey IS NOT NULL).
• Right Outer Join: To see customers without sales records, select the Customer dimension in Analyzer and check Show Empty Group.
• 

Example: Complex Many-to-One (M:1) Join

Scenario: Payroll and Employee Analysis

Tables:

• Employee Table: Includes Employee_id, start_effective_date, and end_effective_date.
• Payroll Table: Contains a foreign key Employee_id and transaction date (Txn_date).

Implementation:

1. Join Definition:
• Payroll.Employee_id = Employee.Employee_id
• Payroll.Txn_date >= Employee.start_effective_date
• Payroll.Txn_date < Employee.end_effective_date

Technical Details:

• Range Join: This join type allows linking records based on date ranges. Incorta requires an equi-join condition (e.g., Payroll.Employee_id = Employee.Employee_id) in every range join definition to ensure proper alignment of payroll data with employee records over time.

Business Benefits:

• Accurate Payroll Analysis: Match payroll records with the correct employee roles and time periods.
• Dynamic Insights: Adjust reports to reflect employee status changes and payroll periods accurately.

Practical Considerations

When using Incorta joins, consider these factors to ensure effective data integration:

• Outer Joins: Default left outer joins include all child records and show NULL for unmatched parent records. This approach helps identify gaps in data but might need adjustment for specific reporting needs.
• Filter Adjustments: Use NOT NULL filters to convert to inner joins or select Show Empty Group to perform right outer joins.

Creating Joins Across Schemas:

You can join tables from different schemas, which will appear in both the child and parent schemas. However, editing the join is restricted to the child schema.

Conclusion

Incorta’s versatile join capabilities empower businesses to connect and analyze data from various sources effectively. Whether you're conducting simple Many-to-One joins or more complex range joins, Incorta provides the tools needed to generate valuable insights and support data-driven decision-making.

For more detailed technical instructions and best practices on setting up joins, refer to our comprehensive technical guide here.