You can configure TRIRIGA to use Tivoli Directory Integrator as its ETL runtime engine to run ETLJobItems from within TRIRIGA.
Before you begin
Install Tivoli Directory Integrator, if not already installed, on all the TRIRIGA systems that could run a TDI ETL Job Item. During the TDI install:
- Make note of the installation directory you enter on the Destination panel. You will enter this value later in TRIRIGAWEB.properties.
- Select either installation type. TRIRIGA requires only the TDI Server component.
- When prompted for the location of the Solution Directory, you can select any option. TRIRIGA specifies its own solution directory at runtime. However selecting the option “Use Install Directory” may simplify troubleshooting.
- Make note of the value you enter in the Server Port field on the Server Port Values Panel. You will enter this value later in TRIRIGAWEB.properties.
- Clear the “Start the Configuration Editor” check box on the Install Complete panel.
- Note: This step is very important for TDI/TRIRIGA integration to work. After you have installed Tivoli Directory Integrator, update it with the recommended fix packs (per TRIRIGA support matrix). TDI must be at least at FP04 (18.104.22.168) or it will not automatically start the TririgiaETLDispatch.xml assembly line which will result in ETL job items failing to run successfully.
- Edit TRIRIGAWEB.properties file to enable TRIRIGA to manage TDI server. Set the following properties…
- Install a JDBC driver library so that Tivoli Directory Integrator can use it to access TRIRIGA database…
- Edit TDI global.properties file to allow TRIRIGA to check and stop the TDI server from localhost without requiring authentication and authorization certificates. Set the api.remote.ssl.on property to false to tell TDI to trust requests from localhost…
- Start Tivoli Directory Integrator Agent from TRIRIGA Admin Console and verify that it starts successfully…
[Admin: This post is related to the 08.03.16 post about installing, upgrading, or uninstalling TRIRIGA TDI, and the 05.01.16 post about documentation on developing TDI with TRIRIGA. To see other related posts, use the TDI tag.]
I would like to see an example of running an ETL job item, because simply running the process fails. When users see the activated record, they assume they can click “Run Process” and get results. But you need to enter a set of restraining data to get results.
Every ETL job item is different. You must fill in the record information to get the results necessary for the transformation or processing. More information can be found at the following link: IBM TRIRIGA – Running ETL transforms. This blog entry will describe a sample run of a Survey Fact ETL job item.
- First, from the menu, click on Tools > Job Scheduling > ETL Job Item.
- Then search for “Survey Fact” and open the record. It’s recommended that you click on More > Copy to create a copy that you can modify, test, and play with.
- After that, change the name to an friendly name and enter at least these mandatory fields: Date, Start Date, Fiscal Period, and End Date.
- For the Date field, take notice of the “Quarter” and “Month” to enter the other subsequent Start Date, Fiscal Period, and End Date.
- See the example for “Q2/2017 – June”. After all of the fields are entered, click Save. Then you can click Run Process.
- The record window will close. Then you can see “Processing” for the status of the record.
- After that, you can open it again and it should show “Completed” as the status. You can also click on the Workflow Instance tab to see the completed workflows.
[Admin: To see other related posts, use the ETL tag.]
In my current project, there was a suggestion to extract (updated) data from TRIRIGA, with a high frequency, and import it into some kind of data warehouse (DW) or business intelligence (BI) solution. Then, from there, perform more advanced reporting and analytics. Have other TRIRIGA solutions implemented something similar? Are there any TRIRIGA best practices or recommendations for staging area, extract-transform-load (ETL), DW, or BI reporting solutions?
[Admin: This post is related to the 12.15.16 post about the IBM TRIRIGA Connector for Watson Analytics. To see other related posts, use the ETL tag or Analytics tag.]
IBM TRIRIGA platform version 3.4.1 and later provides integration with Tivoli Directory Integrator (TDI). TRIRIGA TDI integration offers support for running TDI-based ETL Job Items from within TRIRIGA. This wiki describes how to install the TDI feature of TRIRIGA. It also provides information about upgrading TRIRIGA when the TDI feature is installed and about uninstalling TRIRIGA TDI.
Installing TRIRIGA TDI
TDI is installed and configured by the TRIRIGA installer. The version of TDI installed is as follows…
Check the following install logs to verify that TDI installed successfully and to troubleshoot problems with TDI installation…
Upgrading TRIRIGA with TDI Installed
Before beginning a TRIRIGA upgrade: 1. Uninstall TDI (see Uninstalling TDI below)…
During a TRIRIGA upgrade, uninstall TDI before you rename the directory in which TRIRIGA is installed…
[Admin: This post is related to the 05.01.16 post about documentation on developing TDI with TRIRIGA. To see other related posts, use the TDI tag.]
After redeploying the TRIRIGA application by using ./ant install-was, our customer can no longer process an ETL job item with Tivoli Directory Integrator (TDI). In the log, we have found the following warning:
2016-06-29 16:30:16,035 WARN [com.tririga.platform.workflow.runtime.taskhandler.WFTaskHandlerBase](WFA:127701296 - 127598391 triProcessManual:309624523 IE=309624523) Workflow task handler error while Calling custom task class: com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL. TaskStep: Custom Task(39) WFTID=19192848.7 TSID=204231 Label='Call Kettle Transformation API' FormulaRecalc='Recalculate as Needed' EventAction='' StepInstance: WFIID=375605519425576 SID=204231 UserEvent=' ' SO=127598391 Results=1 Sum=0 Status='ACTIVE'. Cause: com.tririga.platform.util.locator.LocatorException: Could not find bean of type: class com.tririga.platform.workflow.runtime.taskhandler.ETL.TDIRequest[MID-3376369984]...
The bean “class com.tririga.platform.workflow.runtime.taskhandler.ETL.TDIRequest” (which is used by the workflow custom task) has not been loaded correctly. I’m thinking maybe some classpath settings are missing after “/ant install-was”. Have you seen this kind of issue?
As of TRIRIGA Application Platform version 3.5.1, the Java Excel API (JExcelApi) jxl.jar is no longer included as part of the TRIRIGA package. If you have Kettle ETLs that use Microsoft Excel input or output steps you will need to download jxl.jar and add it to your application server’s classpath.
- 1. If you have an ETL that ran prior to upgrading to 3.5.1, check to see if the server.log contains an entry similar to the following entry. If the entry exists you will need to obtain jxl.jar and add it to your application server startup.
2016-02-11 12:32:16,490 WARN [com.tririga.platform.workflow.runtime.taskhandler.WFTaskHandlerBase](WFA:221931 - 127581834 triProcessManual:309907888 IE=309907888) Workflow task handler error while Loading class com.tririga.platform.workflow.runtime.taskhandler.ETL.RunETL. Class definition not found. TaskStep: Custom Task(39) WFTID=19192848.7 TSID=204231 Label='Call Kettle Transformation API' FormulaRecalc='Recalculate as Needed' EventAction=''. Cause: java.lang.NoClassDefFoundError: jxl/format/CellFormat[MID-1509809430]
- 2. Get the jxl-2.6.12.jar from either of these two sources:
- If you already have the Kettle Spoon application which allows you to modify/create ETLs, download the application version that TRIRIGA integrates with here. Extract the jxl-22.214.171.124.jar file from the lib directory of the unzipped package.
- You can download the jar directly from SourceForge here.
- 3. Add jxl-126.96.36.199.jar to your TRIRIGA application server as follows…
IBM TRIRIGA uses either the Tivoli Directory Integrator (TDI) ETL development environment Configuration Editor, or the Pentaho ETL development environment Spoon to generate transform XML files. These transforms, when run through the API, move data from source to destination tables. In IBM TRIRIGA, ETL scripts are used to periodically take snapshots of the transactional data. These snapshots are time stamped and stored in metric fact tables. Metric queries use the information in the metric fact tables to visually display the information in metric charts.