This 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).
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.
This template is designed for:
DIMENSION creation based on a BRIDGE
The BRIDGE represents 1 hierarchy in a dimension with max 5 levels (eg product product type product segment). With the outer ends of the bridge being the lowest and highest level of that hierarchy
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 defined
Only the change date of the indicated LNK tables with the SO DIM_ON_BRIDGE_LNK_TIME_SELECTION will be used in defining the SCD2 time slices
Dimension_hkey will be calculated based on the BK’s of the MAIN_HUB in combination with the date
https://files-us-east-1.t-cdn.net/files/o1MYPpmTJdWe005JqxE27https://files-us-east-1.t-cdn.net/files/A5kMjpzIvoiF2VxzMqlGH
Other article in the same series: