Multi-Active Satellites and Related Links

by Dirk Vermeiren, CTO – VaultSpeed

Introduction

The power of VaultSpeed lies in the extended pre-defined templates that generate code based on a number of configuration parameters. In case you work with a template based solution, you will have to solve these extended issues inside the templates you build. Additionally you will have to test and maintain them.
In a number of blogs we want to address some complex challenges and how a solution is provided out of the box by VaultSpeed.

This blog 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 post 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 choose for 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 but it means there is no unique source attribute. In this case we intodruce a generated sequence number per Business Key to ensure the combination of the Business Key(s), the generated subsequence attribute and load date is unique.

This blog describes one of the scenario’s 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 fictive motor bike company. An example that is commonly used in VaultSpeed demo’s 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 on Figure 1. You can see some example records on Figure 2 for Product Features and Figure 3 for Product Feature Categories.

 

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.

 

Figure 2
Product Features
Figure 3
Product Feature Categories

 

The source case has the following characteristics :

  • The Subsequence Attribute is a source attribute
    • In both tables the Business Key Attribute (indicated in Red) is not the same as the Primary Key attribute (indicated in Blue).
      It means that the relationship is not based on the Business Key but on other attributes. A lookup is needed for building the Link HashKey as described in a previous blog on our website.
  • The Primary key is at a lower level of detail than the business Key in both the source objects, product_feature and product_feature_cat
    • The rows indicated in Blue show the primary key, the rows indicated in Red, contain the Business Key.
      As you can see, for the same Business Key value you have multiple primary Key Values.
      It means that for a lookup via the Business key, multiple primary keys will be returned. The logic must be able to cope with it.
  • Both Objects in the relationship are Multi-Active Objects
    • The product_feature object as well as the product_feature_cat object are multi-active objects. A lookup of a business key for the link can result in multiple records being returned.

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).

 

Figure 4
Figure 5

Links between Multi-Active Objects

We encounter a number of specific choices and challenges that need to be covered by the loading logic for the Links between Multi-Active Objects:

Subsequence in Link or Satellite on Link ?

Link

No: the Subsequence Attribute is a part of the Key of the Satellite, but it is not a part of the Business key. So when building the Link you just load the link object with the unique combinations of the hash on the business keys of the relationship.

Satellite On Link

Here you could make the choice to add the SubSequence number to the Satellite on Link. In our example, we created a link between 2 Hubs with Multi-Active Satellites.

In this case, if you take the Subsequence Attribute in account in the Satellite on Hub you would get the cartesian product of all the combinations of all the subsequence attributes values of a Link key as shown on figure 8. The example case would have been even more compelling if the Multi-Active data had different Subsequence Attributes.

Considering this, VaultSpeed has chosen not to include the Subsequence Attributes in the Satellite on Link.

 

Figure 8

What about the Delete logic ?

When no subsequence attribute is present in the Satellite on Link there is some special management 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 part 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 all the records are really deleted. Only then the delete record with delete_flag = Y can 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.



,find_del_and_related_stg_rec as 
(
SELECT 
stg1.product_features_hkey,
stg1.pro_fea_lan_code_seq,
stg1.load_date,
stg1.load_cycle_id,
stg1.trans_timestamp,
stg1.operation,
stg1.error_code_fkprfe_pfca 
FROM moto_stg_ms_tst_ref_scn1.stg_product_features stg1
     JOIN dist_del_stg dds 
          ON dds.product_features_hkey = stg1.product_features_hkey
)

FIND_DEL_AND_RELATED_STG_REC selects the Delete record data from staging.



,find_del_related_sat_rec as 
(
SELECT 
sat.product_features_hkey,
sat.pro_fea_lan_code_seq, 
sat.load_date, 
sat.load_cycle_id,
sat.trans_timestamp,
sat.delete_flag,
sat.load_end_date,
max(load_end_date) 
          over (partition by sat.product_features_hkey,
                             sat.pro_fea_lan_code_seq
                order by     sat.load_date) 
       as max_load_end_date 
FROM moto_fl_tst_ref_scn1.sat_ms_product_features sat 
     JOIN dist_del_stg dds 
          ON dds.product_features_hkey = sat.product_features_hkey 
     LEFT OUTER JOIN moto_stg_ms_tst_ref_scn1.stg_product_features stg1 
          ON stg1.product_features_hkey = sat.product_features_hkey 
             AND stg1.pro_fea_lan_code_seq = sat.pro_fea_lan_code_seq 
             AND stg1.load_date = sat.load_date 
WHERE stg1.product_features_hkey is NULL 
      AND stg1.pro_fea_lan_code_seq IS NULL 
      AND stg1.load_date IS NULL
      AND sat.delete_flag = ‘N’
) 

FIND_DEL_RELATED_SAT_REC selects the records from the foreign key related satellite which have deletes in the incoming data. We need the MAX(Load_date) to find the active record in an INSERT ONLY Satellite. Also notice that the PARTITION BY is on the Hash Key AND the Subsequence Attribute as this is the unique key for a record in combination with the load_date.



,find_active_del_related_sat_rec as 
(
SELECT
sat.product_features_hkey,
sat.pro_fea_lan_code_seq,
sat.load_date,
sat.load_cycle_id,
sat.trans_timestamp,
sat.delete_flag,
sat.load_end_date
FROM find_del_related_sat_rec sat 
WHERE sat.load_end_date = sat.max_load_end_date
) 

FIND_ACTIVE_DEL_RELATED_SAT_REC does the selection of only the active records based on the MAX_LOAD_DATE calculated by the analytical function in FIND_DEL_RELATED_SAT_REC.



,not_deleted_records_lks AS 
(
SELECT 
COALESCE(fdsat.product_features_hkey, fdstg.product_features_hkey) product_features_hkey,
COUNT(1) count_existing_active 
FROM find_active_del_related_sat_rec fdsat
     FULL OUTER JOIN find_del_and_related_stg_rec fdstg 
          ON fdstg.product_features_hkey = fdsat.product_features_hkey 
             AND fdstg.pro_fea_lan_code_seq = fdsat.pro_fea_lan_code_seq 
WHERE (fdstg.product_features_hkey is null 
       OR fdsat.product_features_hkey is null)
GROUP BY COALESCE(fdsat.product_features_hkey, fdstg.product_features_hkey)
)


NOT_DELETED_RECORD then calculates how many active records there still are in the Satellite based on the incoming data with delete records.

The FULL OUTER JOIN is very important as there could be INSERT records after the DELETE in staging that need to be taken taken into account.

The logic of the Delete Flag that needs to be used in further logic is than shown below.

CASE WHEN COALESCE (NOT_DELETED_RECORDS_LKS.count_existing_active,0) = 0 
AND stg.operation = 'D' THEN 'Y'::text ELSE 'N'::text END delete_flag

If all the incoming records match with the still active records in the Satellite than the count = 0 and the Delete flag of the Satellite on Link can be set equal to Y otherwise the count > 0 and then the delete_flag will be set to “N”.

Conclusion

Through some simple parameterisation 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 analysed 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 to with easy, out of the box configuration and implementation.