- When is an INTEGRATION KEY not a PRIMARY KEY?
This happens when the target table uses a surrogate key instead of the actual value. This will primarily happen when designing a Data Mart, specifically with a Dimension.
You can view this in any metadata sample that has a Data Mart in it, but we will use '05 - MSSQL DataVault ELT SSIS Solution' as an example.
Specifically [dim].[Address] in the [LOAD_BFX_DM] Project.
The PRIMARY KEY is set to the SK column. In this case [AddressKey].
The INTEGRATION KEY is set to the column that determines the generation of the SK. In this case [Address_BK]. This is the column that will determine a new row.
It is very important to note that the above is on the target artifact and is not the configuration of the integration view.
When designing both the loading of the RDV and DM, the BK is flagged as both the PRIMARY KEY and INTEGRATION KEY.
- What does SOURCE KEY mean?
When load/modeling a table for Data Vault, the SOURCE KEY should be set to the primary key in the source system. If the source table does not have a primary key then this should be set to the functional equivalent of what would be used for the primary key.
SOURCE KEY is used to determine the grain of a 'record' or 'row' in the table.
SOURCE KEY will be used to create the physical primary key on the PSA table, along with [FlexRowEffectiveFromDate].
SOURCE KEY can be flagged on as many columns as needed.
Hope this helps.
