Spoon example transform

You can download a copy of any of the existing .ktr scripts that are contained in an existing ETL job item to follow along in the step descriptions. The following shows an example of a Spoon transform.

Most of the as-delivered ETLs have the same flow as the example but the specifics are different, for example, the database tables from which data is extracted and how the data is transformed.

The example transform includes the following items:
  • Pulls input rows and fields from T_TRIORGANIZATIONALLOCATION org, and T_TRISPACE space where org.TRILOCATIONLOOKUPTXOBJID = space.SPEC_ID.
  • Uses IBS_SPEC.UPDATED_DATE to limit the rows that are selected, by using the date range that is passed in from the transform business object.
  • Use Value Mapper to make sure that there is a value in all rows for space.TRIHEADCOUNTNU, space.TRIHEADCOUNTOTHERNU, and org.TRIALLOCPERCENTNU, if not set it to 0.
  • Uses the Calculator to set TRIFACTTOTALWORKERSASS to (space.TRIHEADCOUNTNU + space.TRIHEADCOUNTOTHERNU) * org.TRIALLOCPERCENTNU.
  • Gets TRICREATEDBYTX and TRIRUNDA, passed in from the Transform BO through Get Variables step.
  • Uses Add Constant to set the sequence name and increment so that it is available in the input stream for sequencing step.
  • Uses the DB Procedure NEXTVAL to set the SPEC_ID, set this step to use five threads for enhanced performance.
  • Uses a JavaScript scripting step to determine whether the project was on time or not, and to calculate the duration of the project. Set this step to use three threads for better performance.
  • Maps the fields to T_TRISPACEALLOCFACTOID.
Key things to consider as you build a transform include the following items:
  • Test as you add each step to make sure that your transform is doing what you want.
  • Transforms need to be developed in a defensive manner. For example, if you are making calculations that are based on specific fields, all rows must have a value in these fields, no empties. If not, the transform crashes. Use Value Mapper to make sure that all fields used in a calculation have a value.
  • Dates are difficult to handle as the databases TRIRIGA® supports keep DATE and TIME in the date field. Date solutions show how to handle date ranges in SQL.
  • Make sure to use JNDI settings and that your transform database is independent, especially if your solution needs to run multiple database platforms (DB2, Oracle, and Microsoft SQL Server).
  • Any attributes on the Transform business object are sent to the Transform as a variable. There are a couple exceptions. Attributes of type Time or System Variable are ignored. You can use the variables in your SQL or pull them into the input stream by using Get Variables with the following syntax: ${VariableName}, where VariableName is the attribute name.
  • Make sure to completely test and set up the transform before you use variables in the Table Input. It is challenging to test JavaScript, Table Input Preview, and Table Mapping. You can set variables in the transform with Edit > Set Environment Variables or in the Execute page Variable section. By using variables more of the test functions within Spoon are made available.
  • Test your connection before you use JNDI, before you run a search, or before you run a Spoon transform. The JNDI connection must be tested to avoid Spoon having any potential performance issues.
  • Consider adding an index. It can be key to performance as the ETLs pull data from the T tables in a manner that is different from the regular application.

The preceding items detail the transform as you configure the Spoon steps used. The items concentrate on the main steps that are used by the transforms that are delivered with TRIRIGA. Spoon provides other step types that you can use to manipulate your data; use the steps as necessary, depending on your transform needs.