Multi-active satellites and related links

July 21st, 2020

Ba1a7016 1d6d 4296 a4bd fbdaa72780c9
Dirk Vermeiren
3154984 C F983 4 B8 B 8030 6 D8 FDEBEAA9 A 76c8593c82feac5adb0ec2bdb3ff683e 2000 1

Introduction

The power of VaultSpeed lies in the extended predefined templates that generate code based on a number of configuration parameters. If you work with a template-based solution, you'll have to solve these extended issues inside the templates you build. Additionally you'll have to test and maintain them.
In a series of blog articles, we want to address some complex challenges and how VaultSpeed provides a solution out of the box.

This article describes the challenges and the solution for the implementation of links between multi-active satellites with subsequence source attribute(s).

The challenge described in this article is twofold: first we need to define the level of detail for the link. Second, we also need to decide about what happens when a delete occurs on the source. Do we want the link record to be 'virtually' deleted or not?

What is a multi-active satellite ?

A multi-active satellite is a satellite that can have multiple active records for the same business key. There can be different types of multi-active satellites:

A multi-active satellite with subsequence source attribute(s):

In this case there is an attribute in the source object that will make the data in the satellite unique again. The combination of the business key(s), subsequence attribute(s) and load date will be the unique key of the satellite. A language code for example can be a subsequence attribute in a source system.

A multi-active satellite without a subsequence source attribute

This is a less common type. It means there is no unique source attribute. In this case we intodruce a generated sequence number per business key to ensure that the combination of the business key(s), the generated subsequence attribute and the load date is unique.

This article describes one of the scenarios possible when combining multi-active satellites with links. It explains how VaultSpeed’s logic solves these challenges as a standard functionality. Just by setting a few parameters.

The case uses data models describing a fictitious motor bike company, as commonly used in VaultSpeed demos or documentation.

The case

Sources

The challenges are explained using 2 multi-active objects within the VaultSpeed moto sales demo source. These objects are the product_features and product_features_category tables as shown in figure 1. You can see some example records in figure 2 for product features and figure 3 for product feature categories.

Multi Active Satellites and Related Links 6

Figure 1

The language codes in both of the objects are the source subsequence attributes and there is a foreign key from the product feature object to the product feature category object based on the product_feature_cat_id attribute.

Multi Active Satellites and Related Links 3

Figure 2 - Product Features

Multi Active Satellites and Related Links 4

Figure 3 - Product Feature Categories

Raw Data Vault

  • The raw Data Vault uses the INSERT ONLY LOGIC approach
    With INSERT ONLY LOGIC, there are NO load_end_dates and the only actions allowed are inserts. This means that a delete results in the insert of a new record with the same attribute values as the previous record and the DELETE_FLAG is set equal to Y.

Setup in VaultSpeed

The setup of the source and the raw Data Vault for the above implementation of source and Data Vault is very simple and requires changing 1 parameter, setting 1 switch per object and indicating 1 subsequence attribute per source object.

  • Build the raw Data Vault using insert only logic.
    VaultSpeed system parameter : INSERT_ONLY_LOGIC = Y
  • The product_features and product_features category source object are multi-active.
    Set VaultSpeed switch multi-active to 'Yes' using the graphical source editor. This indicates that the product feature source object is a multi-active satellite (figure 4).
    • By right clicking on the product feature and product feature class language attributes you can set their
      attribute types to 'subsequence attribute' to indicate that that they are a source based subsequence
      attribute (figure 5).
Multi Active Satellites and Related Links 2

Figure 4

Multi Active Satellites and Related Links 1

Figure 5

Multi Active Satellites and Related Links 5

Figure 6

What about the delete logic?

When no subsequence attribute is present in the satellite on link, some special management is required when handling deletes on the link satellite.

A delete flag for the satellite on link can be placed to ‘Y’ only if all the multi-active records have been deleted. So not when only some of the multi-active records are being deleted.

We need to build a join with the satellite that owns the data at the foreign key side of the relationship to ensure that all the records are really deleted. Only then can the delete record with delete_flag = Y be inserted in the satellite on link (This insert is because of insert only logic.)

The logic explained

,dist_del_stg as
(
SELECT
distinct product_features_hkey
FROM moto_stg_ms_tst_ref_scn1.stg_product_features stg1
WHERE operation = 'D'
)

dist_del_stg selects the delete records arriving from staging. They are used to limit the staging content and the satellite content to the new records that have deletes.

Conclusion

Through some simple parameterization and a few manipulations inside the graphical source editor in VaultSpeed you get the full support of this feature in the raw Data Vault layer of your integration system.

The choices made in this setup and their logical consequences were carefully analyzed and all necessary logic to support all possible cases were subsequently implemented inside the standard templates of VaultSpeed.

The complexity is hidden for customers and delivers high value as well as easy, out-of-the-box configuration and implementation.