The Slowly Changing Dimension processor evaluates
changes differently based on the type of slowly changing dimension being processed:
- Type 1
- A Type 1 slowly changing dimension keeps only a single version of a record.
- When key fields in a change record do not match an existing master dimension
record, the processor flags the change record for insert. When written to
the dimension, the change record becomes a new master record.
- When key fields in a change record match those in a master record, the
processor flags the change record for update. When written to the dimension,
the change record replaces the master record.
- Type 2
- A Type 2 slowly changing dimension has an active record - the most recent
version of a record - and keeps previous versions of the record for
historical reference.
-
When key fields in a change record do not match those in an existing
master dimension record, the processor flags the change record for
insert. When written to the dimension, the change record is added as a
new active record.
When key fields in a change record match those of an existing master
record, the active record can be updated or a new active record can be
written, depending on how you configure the stage:
- Type 2 for all changes - Each record in the change
data is written as a new active record.
- Type 2 for specified fields - A change
in one or more listed fields results in writing the record as a new
active record. A change in other fields results in updating the
existing active record with the changed data, like a Type 1 update.
Use this option when you only want changes in certain fields
to generate a new active record.
For example, say store
dimension data includes the store ID, address, and name of the
manager, and the store ID is the key field. If you specify the
address fields, then address changes result in new active
records. Changes to the manager name results in updating the
existing active record with the changed values.
- Type 2 exceptions - A change in any field
that is not listed results in writing the record as a new active
record. A change in a listed field results in updating the existing
active record with the changed data, like a Type 1 update. This is
the opposite behavior of Type 2 for Specified Fields.
Use this
option when you only want changes in certain fields to generate
a new active record.
In the previous example, if you want
a new active record for any change except the manager name, you
can use this option and specify the manager field. Then, changes
in any other field triggers writing a new active record.