How To Implement Same As Link - OptimalBI/optimal-data-engine-mssql GitHub Wiki

Same-as and hierarchical links represent recursive relationships. Same-as link let us know that two records in a Hub table both represent the same core concept. Hierarchical link let us know that there is a form of hierarchy  between two records in the same Hub table.

Quite often Same-as link (SAL) structure is required when we need to capture an integration between the business concept records loaded from different data sources. For example, staff details are loaded from two sources, Sales database and HR database. Source systems have different business keys for staff. Hub key could be a concatenation of source abbreviation and identifier used at the source, or it could be a multi-part hub key.

As we know that we get the same data from two sources, we can implement a record matching logic, so that sales data and HR data could be integrated. SAL captures that integration.

Hierarchical link (HAL) structure is required when we need to capture hierarchical relationship between the business concept records in same hub table. For example, neonatal hospital require to store the relationship between mother and newborn patients.

ODE supports both SALs and HALs. Use general helper script to configure these types of links. Create two link key columns with different names to distinguish the relationship parties. Associate hub key column (or columns in case of multi-part hub key) with both of the link key columns accordingly. Don't forget to give your link SAL or HAL prefix or suffix, so Data Vault model remains easily readable.

Use this article to create any type of link in ODE, including Same-As links and Hierarchical links.