The attached document describes how a user can configure a template in the VaultSpeed Studio to generate an SCD Type 2 dimension based on an insert-only RDV by using a bridge table with multiple HUB’s. In the setup, there is the possibility to limit the fields which should show up in the dimension. The generated query will also remove unnecessary records (no changes in the combination of selected fields).
In the setup there is the possibility to limit the fields which should show up in the dimension. The generated query will also remove unnecessary records (no changes in the combination of selected fields).
DIMENSION creation based on a BRIDGE
The BRIDGE represents 1 hierarchy in a dimension with 2 levels (eg product; product type)
Slowly Changing Type 2 Dimension
Versions in the dimension will be compressed
PIT tables must exist on EACH entry table on the BRIDGE
MAIN_HUB must be assigned
Dimension_hkey will be calculated based on the BK’s of the MAIN_HUB in combination with the date
Only the change date of the main hub will be taken into account for the SCD2 time slices
Before you explore and use this example template, ensure that you understand the example SQL attached to understand what the template does and that it covers your needs.
The example that is used in this document is based on a customer.
In the Raw Data Vault model this is a HUB_CUSTOMER.
Various tasks that are needed:
PIT table on the HUB of choice
Signature objects
Assign Signature objects to the correct tables
Create Signature attribute type and assigned in the requested fields
Create the Template
Create Target definition
Fill in the Dependency
The complete implementation plan, the template itself and an example of the generated code of this template can be found in the attached PDF
Other article in the same series: