SCD management methodology
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
field.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
3. 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
issues. -
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
record.