tMysqlSCD – Docs for ESB 5.x

tMysqlSCD

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.

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.

Type2: 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.

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.

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.

1. Drop the following components from the Palette onto the design workspace: a tMysqlConnection, a tFileInputDelimited, a tMysqlSCD, a tMysqlCommit,
   and two tLogRow components.

2. Connect the tFileInputDelimited, the
   first tLogRow, and the tMysqlSCD using the Row
   Main link. This is the main flow of your Job.

3. Connect the tMysqlConnection to the
   tFileInputDelimited and tMysqlSCD to tMysqlCommit using the OnComponntOk trigger.

4. Connect the tMysqlSCD to the second
   tLogRow using the Row
   Rejects link. Two columns,
   errorCode and errorMessage,
   are added to the schema. This connection collects error information.

   

1. In the design workspace, double-click tMysqlConnection to display its Basic
   settings view and set the database connection details. The
   tMysqlConnection component should be
   used to avoid setting several times the same DB connection when multiple DB
   components are used.

   Note

   If you have already stored the connection details locally in the
   Repository, drop the needed
   metadata item to the design workspace and the database connection detail
   will automatically display in the relevant fields. For more information
   about Metadata, see Talend Studio User
   Guide.

   In this scenario, we want to connect to the SCD table where changes in the
   source delimited file will be tracked down.

   

2. In the design workspace, double-click tFileInputDelimited to display its Basic settings view.

   

3. Click the three-dot button next to the File Name
   field to select the path to the source delimited file,
   dataset.csv in this scenario, that contains the
   personal details.

4. Define the row and field separators used in the source file.

   Note

   The File Name, Row separator, and Field
   separators are mandatory.

5. If needed, set Header, Footer, and Limit.

   In this scenario, set Header to
   1. Footer and limit for the number of processed
   rows are not set.

6. Click Edit schema to describe the data
   structure of the source delimited file.

   In this scenario, the source schema is made of eight columns:
   id, firstName,
   lastName, address,
   city, company,
   age, and status.

   

7. Define the basic settings for the first tLogRow in order to view the content of the source file with
   varying attributes in cells of a table on the console before being processed
   through the SCD component.

1. In the design workspace, click the tMysqlSCD and select the Component tab to define its basic settings.

   

2. In the Basic settings view, select the
   Use an existing connection check box to
   reuse the connection details defined on the tMysqlConnection properties.

3. In the Table field, enter the table name
   to be used to track changes.

4. If needed, click Sync columns to retrieve
   the output data structure from the tFileInputDelimited.

5. In the design workspace, double-click tMysqlCommit to define its basic settings.

6. Select the relevant connection on the Component
   list if more than one connection exists.

7. Define the basic settings of the second tLogRow in order to view reject information in cells of a
   table.

1. Double-click the tMysqlSCD component in
   the design workspace or click the three-dot button next to the SCD Editor
   in the component’s Basic settings view to
   open the SCD editor and build the data flow
   for the SCD outputs.

   

   All the columns from the preceding component are displayed in the
   Unused panel of the SCD editor. All the other panels in the SCD editor are empty.

2. From the Unused list, drop the
   id column to the Source
   keys panel to use it as the key to ensure the unicity of the
   incoming data.

3. In the Surrogate keys panel, enter a name
   for the surrogate key in the Name field,
   SK1 in this scenario.

4. From the Creation list, select the method
   to be used for the surrogate key generation, Auto-increment in this scenario.

5. From the Unused list, drop the
   firstname and lastname columns
   to the Type 0 panel, changes in these two
   columns do not interest us.

6. Drop the status column to the
   Type 1 panel. The new value will
   overwrite the old value.

7. Drop the company column to the
   Type 2 panel. Each time a person
   changes his/her company, a new record will be inserted in the dimensional
   table with a separate key.

   In the Versioning area:

   – Define the start and end columns of your SCD table that will hold the
   start and end date values. The end date is null for current records until a
   change is detected. Then the end date gets filled in and a new record is
   added with no end date.

   In this scenario, we select Fixed Year
   Value for the end column and
   fill in a fictive year to avoid having a null value in the end date
   field.

   – Select the version check box to hold
   the version number of the record.

   – Select the active check box to spot
   the column that will hold the True or
   False status. True for the current active record and False for the modified record.

8. Drop the address and city
   columns to the Type 3 panel to
   track only the information about the previous value of the address and
   city.

   For more information about SCD types, see SCD management methodologies.

   

9. Click OK to validate your configuration
   and close the SCD editor.

1. Click Edit schema to view the input and
   output data structures.

   The SCD output schema should include the SCD-specific columns defined in
   the SCD editor to hold standard log
   information.

   

   Note

   If you adjust any of the input schema definitions, you need to check,
   and reconfigure if necessary, the output flow definitions in the
   SCD editor to ensure that the
   output data structure is properly updated.

2. In the Basic settings view of the
   tMysqlSCD component, select Create table if not exists from the Action on table list to avoid creating and
   defining the SCD table manually.