More advanced Link Object Staging Logic | VaultSpeed
ArticlesTutorials

More advanced link object staging logic

April 14th, 2019

Ba1a7016 1d6d 4296 a4bd fbdaa72780c9
Dirk Vermeiren
Ilustration More advanced Link Object Staging Logic

When the source relationship is not based on the business keys

Note: This is a fictitious example, created only to illustrate the more advanced logic that is necessary in the staging layer. It explains how to solve the relationship and load the link table when the foreign/primary key relationship between the source objects is not based on the business key.

The relational source model as shown in the image above shows the relationship between an invoice source table and a customer table.

The business keys are indicated by the 'U' (unique) before the column. If they do not exist, then the business key is indicated by the U key. In this model, this means:

  • For invoices, the business key is invoice_number
    For customers, the business key is a composite key that consists of these columns:
    • FIRST_NAME
    • LAST_NAME
    • BIRTH_DATE
    • GENDER

The relationship between the 2 tables in this model is based on the customer_id, which is the technical key (surrogate key). It is not based on the business key, meaning the combination of first_name, last_name, birth_date and gender.

As a result:

  • The link table can’t be loaded by using the invoices table only – where the relationship exists. A lookup is needed to find the business keys in the customers table.
  • Not all the records to solve the relationships will be found during the lookup because the data in the landing area holds incremental data. This means that a lookup in the customer hub is necessary to find the missing related records and their business keys.

The following image illustrates the lookups that are needed to resolve the link between invoices and customers. In the invoices landing table there are two incremental records which have 5 and 7 as the customer-id. When the lookup is done in the customers landing table it only holds the record with the customer_number = 5, meaning that the record with customer_number = 7 is not in the landing area during this incremental load.

The only way to solve this is to do the lookup to the satellite, on the condition that the satellite holds the primary key of the customers table = customer_number. Through the lookup to the hub using the hash key, the business keys can be found resulting in a fully solved link table.

The following image shows the SQL used to load the invoices staging table and used to resolve the link table between invoices and customers in an Oracle Database prior to 12C.

More advanced Link Object Staging Logic 2

Now what if this technical key was also put into the hub? This would result in one less lookup from the satellite to thehub. However, hubs should only contain business keys. It is good practice to put the technical key in the satellite but make it a special attribute that does not take part in the change of history. This means it will not be a part of the hash difference attribute calculation.

The target Data Vault model for this article is shown below.

More advanced Link Object Staging Logic 3



Spread the word

More like this

Thumbnail Training1 Demo
Demo

Hands-on demo

In this free hands-on demo, we’ll set up fully functional automation in less than 2 hours.

Thumbnail Training2 Demo
Demo

Hands-on demo

In this free hands-on demo, we’ll set up fully functional automation in less than 2 hours.

Thumbnail Training7 Demo
Demo

Hands-on demo

In this free hands-on demo, we’ll set up fully functional automation in less than 2 hours.