About N-Way Joins - trillium_discovery - trillium_quality - Latest

Trillium Control Center

Product type
Software
Portfolio
Verify
Product family
Trillium
Product
Trillium > Trillium Discovery
Version
Latest
Language
English
Product name
Trillium Quality and Discovery
Title
Trillium Control Center
Copyright
2024
First publish date
2008
Last updated
2024-10-18
Published on
2024-10-18T15:02:04.502478

N-way joins allows you to create a join project where multiple tables are joined together for data analysis. Unlike a simple join, a join project allows you to join two real entities at a time to create one or more output entities based on one or more result segments. These output entities can be instantly joined again with other entities to create multi-way joins. Sometimes the joining rows are of interest, but you are more likely to isolate and analyze non-joining rows in preparation for a data cleansing or a migration project.

Note: You can configure Entity Business Rules for join entities just as you would for an entity created using a loader connection or from the client.

Here is a typical n-way join:

 

Just like a simple join, the n-way joins allow you to examine relationships between entities, no matter what the original data source. Venn diagrams and Entity Relationship Diagrams (ERD) are available to help you to better visualize joins.

Note: If you do not have access to an entity in a repository, you will not see the joins or ERDs associated with that entity.

However, when creating n-way joins, you can specify a join expression for the left entity that may or may not match an expression over the right entity.

In a simple join, for example, the row will join only when the value of customer_id in the customer table (LHS) is the exact same as customer_id in the order table (RHS):

customer.customer_id = order.customer_id

However, join criteria in real life are far more complex and the join project provides you the capability to create n-way joins with:

  • Multiple join criteria:

customer.customer_id = order.customer_id and customer.postcode = order.delivery_postcode

  • Expression based join criteria -

trim(customer.customer_id) = substring(order.customer_id,1 6) and concat(customer.postcodeleft, customer.postcoderight) = order.delivery_postcode