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.