Tracking Fields

A tracking field is a field in a Type 2 slowly changing dimension that indicates which record is the most recent, or active, record. Type 2 dimensions can use several types of tracking fields.

Type 1 dimensions simply replace the existing record, so no earlier versions are retained. However, a Type 1 dimension can use a timestamp tracking field, such as a last-updated field, to indicate when the record was updated.

When you configure the Slowly Changing Dimension processor, you specify the tracking field name and type. You can specify as many tracking fields as appropriate.

The Slowly Changing Dimension processor supports the following types of tracking fields:

Version Increment: The processor increments each version of the record and places the latest version number in a user-defined field.; For example, if you specify a version field, each time a tracked change occurs, the processor writes the new version of the record with the value in the version field increased by one.
Active Flag: The processor uses a boolean active field. The most recent version is flagged as true and older versions are flagged as false.; Each time a tracked change occurs, the processor writes a new record with the active field set to true. The processor also updates the previous version of the record, setting the active field to false.
As Of / Start Timestamp: The processor places the datetime in a user-defined field each time it writes each new version. The record with the most recent datetime is the most recent record.; The processor sets the datetime based on the pipeline start time on the Transformer machine, or the time in a user-defined timestamp field in the change record, depending on the selected timestamp basis.
End Timestamp: The processor uses a user-defined field to indicate when a record version is no longer used. The record with no end timestamp is the most recent record.; When the processor writes a new version of a record, it leaves the user-defined field empty, and adds the timestamp to the previous version of the record.; The processor sets the datetime based on the pipeline start time on the Transformer machine, or the time in a user-defined timestamp field in the change record, depending on the selected timestamp basis.