BPM Admin Guide

Install the ETL

This section describes the steps you need to follow to install the ETL.

The ETL (Extract-Transform-Load) is the mechanism which reads (extracts) data from Maconomy or People Planner, transforms it, and stores it (loads it) in the data warehouse database.

The steps below take origin in a data warehouse on Oracle. If the data warehouse is on MS SQL Server, change the connection type from Oracle to MS SQL Server accordingly.

Furthermore, the steps assume the setup of a data warehouse for BPM Analysis (Maconomy). If a data warehouse is set up for BPM People Planner, import the DataIntegrationRepository.xml from the peopleplanner folder in the RPU.

To install the ETL, you must:

  • Connect to a Pentaho Server
  • Import the BPM ETL. The file name is DataIntegrationRepository.xml.
    • Standard ETL is under etl\reporting
    • PeoplePlanner ETL is under etl\peopleplanner
Note: When running ETL jobs that load data to the Data Warehouse, one of the Number fields on Maconomy may fail while being converted to string (data type) before loading. We recommend temporarily disabling the cache on steps that fail until this issue gets resolved.

Connecting to a Pentaho Server

Note: When running ETL on a server that has its time zone set to Singapore, an issue occurs with the Gregorian Calendar when running Create Tables and Views. This affects Pentaho 8.2 and 9.3. The workaround is to change the time zone of the Pentaho server to a different UTC+08:00 time zone, for example, Beijing.

The issue is caused by the time zone itself and affects other applications worldwide.

To connect to a Pentaho Server, follow these steps:

  1. Go to Start » Pentaho Enterprise Edition » Data Integration
  2. Click the Connect button on the upper-right corner of the window.
    • For the first connection, it will open the Repository Manager automatically.
    • For subsequent connections, select Repository Manager in the drop-down list, then click Add.
  3. Click Get Started on the Repository Manager window.
  4. Fill in the details:
    • Display Name - name to identify the connection.
    • URL - URL to open Pentaho User console. Make sure to enter the correct port no.
    • Description - additional information to help identify the connection.
  5. Click Finish.
  6. A test will be automatically performed, if successful click Connect Now. Otherwise, review the connection details.
  7. Log in with username admin and password password.

Import the BPM ETL

To import the BPM ETL, follow these steps:

  1. Select Tools » Repository » Import Repository…
  2. Browse to the location of the BPM Analysis or BPM People Planner ETL XML file. The file is named DataIntegrationRepository.xml.
  3. At the prompt (Do you want to apply a set of rules to the import?) select No.
  4. Select the root as directory (default). Click OK.
  5. Click Yes to create the suggested folders. The transformations and jobs are now imported.
  6. Click Close.