The SCD Type 2 Loader enables you to
generate surrogate key values when you load a dimension table. The
generated surrogate key values replace the business key as the primary
key. This is because the business key from the source table identifies
the member, not the unique row in the dimension table.
You can configure a
simple surrogate key in the
Generated Keys tab
of the SCD Type 2 Loader. This surrogate key increments the highest
existing value in a specified column for each new row. You can also
use an expression to generate key values in other increments. To specify
a unique starting point for the keys that are generated in each load,
you can specify a lookup column. The initial key value is the highest
value in the lookup column.
Note: When loading a fact table
instead of a dimension table, you can generate simple surrogate keys
using the Lookup transformation.
In addition to surrogate
keys, you can also generate retained keys. Retained keys provide a
primary key value that consists of two columns, the begin datetime
change tracking column and a numeric column that receives generated
values. The combination of the two columns uniquely identifies each
row in the table.
The generated value
is retained because a single generated value is applied to all of
the rows that apply to a given member. When a new row is added to
an existing member, it receives the same generated value as the other
rows that apply to that member.
As with surrogate keys,
you can generate retained key values using expressions and lookup
columns.
In order to generate
unique retained keys, begin and end datetime change tracking is required.
To enhance performance,
you should create an index for your generated key column. If you identify
your generated key column as the primary key of the table, then the
index is created automatically. Surrogate keys should receive a unique
or simple index that consists of one column. Retained keys should
receive a complex index that includes the generated key column and
the beginning datetime column.
To create an index,
open the
Properties dialog box for the table
and use the
Index and
Keys tabs.