Enriching is an operation which does not translate directly to any SQL query. The basic principle of enrichment relies on look-up, where a selected table (enriched table) is being updated by writing values to one of its columns (enriched column), based on values found in other tables.
Source is by default optional, meaning that the table does not have to be existing. If the table is missing, the step will not be executed and its target table will not be written. There is a way to define that the source is required, in which case in case of a missing source table the step will fail and report an error. More information about setting up the required steps can be found in the ETL Implementation Guide.
Configure Enrich in RayVentory Data Hub
•SOURCE TABLE: Enter the table that is used as source for the data. It supports auto-completion and will offer all available tables matching the current input string for selection.
•COLUMN: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
LOOK-UP
•SOURCES: Add a list of resources that should be used as sources. This field supports auto-completion and will offer all available entries matching the current input string for selection.
•KEY TYPE: There are two options to choose from for the type of key that will be used.
oColumn key: If this option is selected, the COLUMN KEY field will be available. Select the column that will be used as key. This field supports auto-completion and will offer all available entries matching the current input string for selection.
oPrimary key: If this option is selected, two field will be available. The PRIMARY KEY field and the FOREIGN KEY field. Select the respective columns for both fields. These fields support auto-completion and will offer all available entries matching the current input string for selection.
•FOREIGN KEY: Select a column that will be used as foreign key. This field supports auto-completion and will offer all available columns matching the current input string for selection.
•SEPARATOR: A separator can be entered into this field. This field is optional.
•Add: The Add button can be used to add an additional look-up.
Automatic value
•TYPE: Select Automatic value to define that an automatic value will be used to determine the value. If Automatic value is selected as type, the following fields will be available.
•VALUE: Use the dropdown menu to select the automatic value that should be used. The following values are available:
oCurrent datetime
oGUID
oAuto incremented number
oTable name
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Constant value
•TYPE: Select Constant value to define a constant value that will be used to determine the value. If Constant value is selected as type, the following fields will be available.
•VALUE: Enter the value that should be used. By clicking on the button located on the right hand side of the field it is possible to switch the input type between text input, number input, and checkbox input.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Regular expression match
•TYPE: Select Regular expression match to define a regular expression that will be used to determine the value. If Regular expression match is selected as type, the following fields will be available.
•SOURCE COLUMN NAME: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
•REGULAR EXPRESSION: Enter the regular expression that is to be used for the transformation.
Detailed information on how to use regular expressions in ETL can be found in the Programmability chapter in the ETL Implementation Guide.
•THIS COLUMN IS OPTIONAL: If this option is not checked, the ETL engine will check if the source column exists before starting the execution. If the option is checked, the engine will start the execution even if the column does not exist.
Column
•TYPE: Select Column to define which column will be used to determine the value. If Column is selected as type, the following fields will be available.
•SOURCE COLUMN NAME: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
•THIS COLUMN IS OPTIONAL: If this option is not checked, the ETL engine will check if the source column exists before starting the execution. If the option is checked, the engine will start the execution even if the column does not exist.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
SQL
•TYPE: Select SQL to use an SQL macro to determine the value. If SQL is selected as type, the following fields will be available.
•SQL: Enter the SQL macro that will be used to determine the value into this field.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Switch
•TYPE: Select Switch to use a switch to determine the value. If Switch is selected as type, the following fields will be available.
•SOURCE COLUMN NAME: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
•CASES: It is possible to add one or more cases. If a case is added the following fields will be available for configuration:
oCASE: Enter a value that should be used for the case. By clicking on the button located on the right hand side of the field it is possible to switch the input type between text input, number input, and checkbox input.
oTHEN: Enter a value that should be used as then. By clicking on the button located on the right hand side of the field it is possible to switch the input type between text input, number input, and checkbox input.
•DEFAULT: It is possible to add one default value by choosing default. Then a new field will be available where the value can be defined.
oDEFAULT VALUE: Enter a value that should be used default. By clicking on the button located on the right hand side of the field it is possible to switch the input type between text input, number input, and checkbox input.
•THIS COLUMN IS OPTIONAL: If this option is not checked, the ETL engine will check if the source column exists before starting the execution. If the option is checked, the engine will start the execution even if the column does not exist.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Transformed column
•TYPE: Select Transformed column to define a transformation type that will be used on the source column. If Transformed column is selected as type, the following fields will be available.
•SOURCE COLUMN NAME: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
•TRANSFORMATION TYPE: Choose one transformation type from the dropdown menu. The following transformation types are available:
oIs not null or empty
oIs null or empty
oLength
oLowercase
oUppercase
•THIS COLUMN IS OPTIONAL: If this option is not checked, the ETL engine will check if the source column exists before starting the execution. If the option is checked, the engine will start the execution even if the column does not exist.
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
FALLBACK
Column
•TYPE: Select Column to define the column that will be used to determine the value. If Column is selected as type, the following fields will be available.
oSOURCE COLUMN NAME: Enter the column from which the value will be taken. It supports auto-completion and will offer all available columns matching the current input string for selection.
oDETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Automatic value
•TYPE: Select Automatic value to define that an automatic value will be used to determine the value. If Automatic value is selected as type, the following fields will be available.
•VALUE: Use the dropdown menu to select the automatic value that should be used. The following values are available:
oCurrent datetime
oGUID
oAuto incremented number
oTable name
•DETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
Constant value
•TYPE: Select Constant value to define a value that will be used. If Constant value is selected as type, the following fields will be available.
oVALUE: Enter the value that should be used. By clicking on the button located on the right hand side of the field it is possible to switch the input type between text input, number input, and checkbox input.
oDETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.
SQL
•TYPE: Select SQL to use an SQL macro. If SQL is selected as type, the following fields will be available.
oSQL: Enter the SQL macro that will be used to determine the value into this field.
Detailed information on how to use scripts in ETL can be found in the Programmability chapter in the ETL Implementation Guide.
oDETERMINE COLUMN TYPE FROM CONTEXT: Enable or disable the automatic determination of the column type. When disabled, the column type must be entered manually into the COLUMN TYPE field.