The default behavior of TX is to always assign surrogate key values to the DW_Id field using the IDENTITY (autonumber) function of SQL Server. This means that there are separate surrogate keys assigned in staging and data warehouse, but also between the raw and valid table instances.
The surrogate key that is persisted and used in Slowly Changing Dimensions Type 1 and 2 scenarios, is the one assigned to the valid table instance in the data warehouse database. This is also the one used in fact tables, where the surrogate key is looked up using the natural key join.
Step by step
If the objective is to reuse a surrogate key either from a source table or as it was assigned in the staging area, thus eliminating the need to persist natural keys on fact tables in the data warehouse please follow the steps below.
- Drag a integer value field from the staging table.
- Drop it on the DW_Id field on the data warehouse table.
If you want to use the surrogate field from staging, use the DW_Id field from the staging table. If you want to use a source or custom field, simply use that field.
When a surrogate key is mapped according to the above, please note the following constraints and warnings:
- Identity inserts is done using the SET IDENTITY_INSERT ON/OFF option.
- You are responsible for uniqueness constraints on the surrogate key. Any violations will raise an error during processing.
- If multiple sources is mapped to a single data warehouse table, the surrogate key mapping applies to all tables.
- It cannot be combined with SCD Type 1 and 2 surrogate key assignement on the data warehouse table.
This article applies to: timeXtender 4.5, tX2012, TX2014.