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. Note: The end column identifies active records. The surrogate ID value gets reset when the value of the end column changes. To avoid duplicated surrogate IDs, always make sure that the value of the end column remains unchanged in the target table.
- 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.