Creating or modifying ETL job items
ETL job items define the scripts that capture information from the TRIRIGA® database, transform it, and load it into fact tables or flattened hierarchy tables.
Procedure
- Click Tools > System Setup > Job Scheduling > ETL Job Item.
- Select an existing job item or click Add.
- In the General section, enter an ID for the ETL job item.
Include the fact table name in the ID field. The status is supplied by the TRIRIGA system when the ETL job item is created.
- Enter a
name and description for the ETL job item. In the Details section, the Job Item Class field is set to ETL by the TRIRIGA system.
- Select the Job Item Type.
- If the job item type is Rebuild Hierarchy,
complete the following steps.
- Enter the
hierarchy module name. If specified, it must be a module with a hierarchy defined in TRIRIGA, such as Location. When this ETL job item runs, all flat hierarchies for this module are rebuilt.
- Enter the
hierarchy name. When this ETL job item runs, the flat hierarchy for this business object is rebuilt. If specified, the hierarchy name takes precedence over the hierarchy module name.
- The TRIRIGA system ignores information that is entered in the calendar period, fiscal period, and other dates in the Details section.
- To rebuild all flat hierarchies of a specific module, specify the hierarchy module name and leave hierarchy name blank.
- To rebuild a single flat hierarchy, specify both the hierarchy module name and hierarchy name.
- If both the hierarchy module name and hierarchy name are blank, or either contains All, all flat hierarchies are rebuilt.
- Enter the
hierarchy module name.
-
If the job item type is Kettle Transformation, complete the following
steps.
-
Specify the transform file by browsing for and selecting the file.
The TRIRIGA system expects the Kettle transform file to be in .ktl or .xml format.
- Optional: After you upload the transform file, it can be viewed by clicking View Content.
-
Specify the transform file by browsing for and selecting the file.
-
If the job item type is Kettle Transformation, complete the following
steps.
-
Enter the module names. If more than one name is specified, they must be delimited with a
comma.
- Each module name is converted into a variable for the transform file in the format ${Module.<moduleName>.ViewName} where <moduleName> is the module name.
- Each variable’s value that is passed into the ETL is the name of the View for that module. This variable’s value can be used if the ETL must know the name of a specific Module's view.
-
Enter the business object names. If you specify more than one business object name, the names
must be delimited with a comma.
- Each business object name is converted into a variable for the transform file in the format ${BO.<boName>.TableName} where <boName> is the business object name.
- A business object is not guaranteed to be unique across the database unless the module name is included. If you use a business object that is not uniquely named, include the module name in the comma-separated list. Use the following syntax: <moduleName>::<boName>, where <moduleName> is the module name and <boName> is the business object name.
- A variable is provided to the transform file as ${BO.<moduleName>::<boName>.TableName}. Each variable’s value is the name of the table for that business object. This variable’s value can be used if the ETL must know the name of a specific Business Object's table.
-
Enter the module names. If more than one name is specified, they must be delimited with a
comma.
-
If the job item type is Kettle Transformation, complete the following
steps.
- Typically, ETL job items are run under the control of one or more job schedules.
- To unit test the ETL job item, set the date parameters in the Details section.
-
The following date parameters depend on the ETL, some ETLs use the information, some ETLs do
not. The date parameters are overwritten when an ETL job item is run by the Job Scheduler.
- Select the Calendar Period to pass a variable that contains the calendar period of the job.
- Select the Fiscal Period to pass a variable that contains the fiscal period of the job. The fiscal period is used by the Capture Period field in the TRIRIGA Workplace Performance Management fact tables.
- Select the Date to pass a variable that contains the date record of the job. The date record is used to stamp the Date Dimension field in the TRIRIGA Workplace Performance Management fact tables.
- Select the Date to pass a variable that contains the date of the job. Enter a date or click the Calendar icon and select a date.
- Select the Start Date to specify the date of the first data capture and to pass a variable that contains the start date of the job. Enter a date or click the Calendar icon and select a date.
- Select the End Date to specify the date of the last data capture and to pass a variable that contains the end date of the job. Enter a date or click the Calendar icon and select a date.
- The Metrics section summarizes logging data for this ETL job item. The Average Duration is calculated based on the Total Duration and the # of Runs (Total Duration / # of Runs).
- The Logs section shows the time and status from each time the ETL job item is run. This data is summarized in the Metrics section.
- Click Create Draft.
- Click Activate.