August 15, 2023

SCD management methodology – Docs for ESB 6.x

SCD management methodology

Slowly Changing Dimensions (SCDs) are dimensions that change slowly over time.

The SCD editor offers the simplest method of building the data flow for the
SCD outputs. In the SCD editor, you can map columns, select surrogate key columns, and set
column change attributes through combining SCD types. The following figure illustrates an
example of the SCD editor.


SCD keys

You must choose one or more source keys columns from the incoming data
to ensure its unicity.

You must set one surrogate key column in the dimension table and map it
to an input column in the source table. The value of the surrogate key links a record in
the source to a record in the dimension table. The editor uses this mapping to locate
the record in the dimension table and to determine whether a record is new or changing.
The surrogate key is typically the primary key in the source, but it can be an alternate
key as long as it uniquely identifies a record and its value does not change.

Source keys: Drag one or more columns from the
Unused panel to the Source keys panel to be used as the key(s) that ensure the unicity of
the incoming data.

Surrogate keys: Set the column where the
generated surrogate key will be stored. A surrogate key can be generated based on a
method selected on the Creation list.

Creation: Select any of the below methods to be
used for the key generation.

  • Auto increment: auto-incremental key.

  • Input field: key is provided in an input

    When selected, you can drag the appropriate field from the
    Unused panel to the complement field.

  • Routine: from the complement field, you can press Ctrl+Space to display the autocompletion list and
    select the appropriate routine.

  • Table max +1: the maximum value from the
    SCD table is incremented to create a surrogate key.

  • DB Sequence: from the complement field, you can enter the name of the
    existing database sequence that will automatically increment the column indicated
    in the name field.

    Note that this option is only available through the SCD Editor of the tOracleSCD.

Slowly Changing Dimensions types

The Slowly Changing Dimensions support four types of changes: Type 0 through Type
. You can apply any of the SCD types to any column in a source table by
a simple drag-and-drop operation.

  • Type 0: is not used frequently. Some
    dimension data may be overwritten and other may stay unchanged over time. This is
    most appropriate when no effort has been made to deal with the changing dimension

  • Type 1: no history is kept in the database.
    New data overwrites old data. Use this type if tracking changes is not necessary.
    this is most appropriate when correcting certain typos, for example the spelling
    of a name.

  • Type 2: the whole history is stored in the
    database. This type tracks historical data by inserting a new record in the
    dimensional table with a separate key each time a change is made. This is most
    appropriate to track updates, for example.

  • Type 3: only the information about a
    previous value of a dimension is written into the database. This type tracks
    changes using separate columns. This is most appropriate to track only the
    previous value of a changing column.

SCD Type 2 principle lies in the
fact that a new record is added to the SCD table when changes are detected on the
columns defined. Note that although several changes may be made to the same record on
various columns defined as SCD Type 2, only one
additional line tracks these changes in the SCD table.

The SCD schema in this type should include SCD-specific extra columns
that hold standard log information such as:

  • start: adds a column to your SCD schema to
    hold the start date. You can select one of the input schema columns as a start
    date in the SCD table.

  • end: adds a column to your SCD schema to
    hold the end date value for a record. When the record is currently active, the end
    date is NULL or you can select Fixed Year Value and fill in a fictive year to
    avoid having a null value in the end date field.

  • version: adds a column to your SCD schema
    to hold the version number of the record.

  • active: adds a column to your SCD schema to
    hold the true or false status value. this column helps to easily spot the active

Document get from Talend
Thank you for watching.
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x