Creating a Pentaho job to load custom Foundation data

If you want to load custom Foundation data from Remedy IT Service Management (Remedy ITSM), you must create a Pentaho job and transformations in Remedy ITSM to identify, read, and load the custom Foundation data into BMC Helix Platform for processing. if you do not want to load custom Foundation data from Remedy ITSM, this task is not required.

Important

You can load Foundation data from Remedy ITSM version 9.1 SP3 or later. For a complete list of compatible Remedy ITSM versions, see Foundation data load compatibility information.

Example

To meet your business requirements, you want to provide additional details like Passport Number and SSN for all Employees in your organization. In such case, you must specify the custom Foundation data in Remedy ITSM so that the custom data is identified and loaded to BMC Helix Platform. For example, to define the custom Foundation data, you can create a Pentaho job Initial Sync - Person Additional Details and the following transformations:

Person Additional Details - Header to define the header of Excel spreadsheet.
Person Additional Details to define the sequence of data load.

When you run the Pentaho job Initial Sync - Person Additional Details, the Passport Number and SSN fields are loaded from Remedy ITSM.

Before you begin

Ensure that you have Identifying fields added to view overlaysthat you want to load.
Ensure that you have extended the Foundation definitions in BMC Helix Platform to include the custom Foundation fields.

Task 1: To modify CTM:People form to include custom fields

Identify the custom fields that you want to import in BMC Helix Platform from Remedy ITSM Foundation data. For example, custom fields for Sites, People, Company, Support group, and category Foundation data. Add the custom fields to CTM:People form, so that these fields are loaded from Remedy ITSM, and displayed as part of the out-of-the-box Foundation record.

Log in to BMC Remedy IT Service Management console as an ITSM administrator.
Navigate to the CTM:People form.
Add the custom fields to suit your business needs.
For example, Passport Number and SSN fields.
The following image shows an example of custom fields that are added to CTM: People form:

Task 2: To create a Pentaho job in Remedy ITSM

Create a custom Pentaho job in Remedy ITSM, so that your custom Foundation data is loaded to BMC Helix Platform.For example, create a Pentaho job Initial Sync - Person Additional Details for loading custom Foundation data for employee, such as Passport Information and SSN.

You can create multiple transformations in a single job for different Foundation data types, for example, a single Pentaho job that contains a transformation each for extended Person data, extended Organization data, and extended Site data.

Log in to BMC Remedy AR System server as an AR administrator.
In the Atrium Integrator Spoon client, select File > New > Job.

Right-click the new job, and in the Job tab, provide the following information:

UI field	Description
Job name	Provide a unique name for the custom job. For example, Initial Sync - Person Additional Details.
Directory	Specify the directory as Innovation Suite SYNC, so that the job can execute and load custom Foundation data to BMC Helix Platform , and save the changes.Important: If you do not select the Innovation Suite SYNC, the job is not executed and custom Foundation data is not loaded to BMC Helix Platform.

The following image shows an example of newly-created Pentaho job Initial Sync - Person Additional Details:
New job_settings.png

In the Parameters tab, copy the parameters from a predefined Pentaho job for Innovation Suite - Sync directory to the new job.
For example, copy the parameters from Initial Sync-Foundation data job to the newly-created Pentaho job Initial Sync - Person Additional Details.
The following image is an example of parameters that you need to copy to the new Pentaho job:
In the Log tab, copy the log fields from a predefined Pentaho job for Innovation Suite - Sync directory to the new job. You must copy the log fields for both Job log table properties and Jog entry log table properties.
For example, copy the log fields from Initial Sync-Foundation data job to the newly-created Pentaho job Initial Sync - Person Additional Details. This step ensures that the log settings used for new Pentaho job are same as that of other predefined Pentaho jobs in Innovation Suite - Sync directory.
The following image is an example of Job log table fields that you need to copy to the new Pentaho job:
The following image is an example of Job log entry table fields that you need to copy to the new Pentaho job:
Click OK.

Task 3: To create a custom Pentaho transformation for defining the header of Excel spreadsheets

Create a custom Pentaho transformation in Remedy ITSM, so that you define the header of the Excel spreadsheet. For example, for defining the header of Excel spreadsheet that loads Passport Information and SSN custom fields, create a Person Additional Details - Header transformation.

In the Atrium Integrator Spoon client, select File > New > Transformation.

Right-click the new job, and in the Transformation tab, provide the following information:

UI field	Description
Transformation name	Provide a unique name for the custom transformation. For example, Person Additional Details - Header.
Directory	Specify the directory as Innovation Suite SYNC, so that the transformation can execute and load Excel spreadsheets to BMC Helix Platform .Important: If you do not select the Innovation Suite SYNC, the job is not executed and custom Foundation data is not loaded to BMC Helix Platform.

The following image shows an example of new Pentaho transformation Person Additional Details - Header:
New transformation_settings.png

In the Parameters tab, copy the Parameters and Logging channels from a predefined Pentaho transformation for Innovation Suite - Sync directory to the new transformation.
For example, copy the Parameters and Logging channels from a transformation in Initial Sync-Foundation data job to the newly-created Pentaho transformation Initial Sync - Person Additional Details - Header.

From the Design tab on the left side, drag the following steps to the canvas:

Step	Description
Generate Rows	This step generates the rows that you want to display in an Microsoft Excel spreadsheet. Select Input > Generate Rows. Right-click the step > Edit > specify the following information: Field name Field type The following image shows an example of Generate Rows step EMPL Header 1. You can add multiple Generate Rows steps in your transformation, according to the rows you want to include in the Excel spreadsheet. The following image shows an example of Generate Rows step EMPL - AGNT Row 2 for adding employee name, passport number, and SSN row headers in the in the Excel spreadsheet.
Excel Writer	This step appends the custom Foundation data to an Microsoft Excel spreadsheet. Select Output > Microsoft Excel Writer. Right-click the step > Edit > specify the following fields for the Excel Writer step: Filename Sheet name You can add multiple Excel Writer steps in your transformation, according to the Foundation data you want to include in the Excel spreadsheet. The following image shows an example of Excel Writer step EMPL Row 1.
Block this step until steps finish	This step generates blocks the steps till all the earlier steps are completed successfully. Select Flow > Block this step until steps finish. Right-click the step > Edit > specify the step name that you want to watch for. You can add multiple Block this step until steps finish steps to your transformation. The following image shows an example of Block this step until steps finish step Wait for Row 4:
Connection	Add the hop connection between the steps, so that you specify the sequence in which the steps must be processed. The following image shows an example of hop connections created for Pentaho transformation Initial Sync - Person Additional Details:

Task 4: To create a custom Pentaho transformation for processing the custom Foundation data

Create a custom Pentaho transformation in Remedy ITSM, so that you define the process for generating the Excel spreadsheets. For example, for loading Passport Information and SSN custom fields for Employee Foundation data, you create a Person Additional Details transformation.

Important

If you are loading different types of Foundation data, you must create a transformation for every Foundation data type in the same Pentaho job. For example, you must create a transformation each for extended Person data, extended Organization data, and extended Site data.

In the Atrium Integrator Spoon client, select File > New > Transformation.

Right-click the new job, and in the Transformation tab, provide the following information:

UI field	Description
Transformation name	Provide a unique name for the custom transformation. For example, Person Additional Details.
Directory	Specify the directory as Innovation Suite SYNC, so that the transformation can execute and load Excel spreadsheets to Innovation Suite.Important: If you do not select the Innovation Suite SYNC, the job is not executed and custom Foundation data is not loaded to Innovation Suite.

The following image shows an example of new Pentaho transformation Person Additional Details:
Personal additional details_transformation.png

In the Parameters tab, copy the Parameters and Logging channels from a predefined Pentaho transformation for Innovation Suite - Sync directory to the new transformation.
For example, copy the Parameters and Logging channels from a transformation in Initial Sync-Foundation data job to the newly-created Pentaho transformation Initial Sync - Person Additional Details.

From the Design tab on the left side, drag the following steps to the canvas:

Step	Description
AR Input	This step reads the custom Foundation data from AR System form. Select Input > AR Input. Right-click the step > Edit > specify the following information: Field name Field type The following image shows an example of Generate Rows step Get Employees.
Add constants	This step adds one or more constants to the input rows. Select Transform > Add constants. Right-click the step > Edit > specify the following constants: varEmpty varOperation The following image shows an example of Add constants step.
Excel Writer	This step appends the custom Foundation data to an Microsoft Excel spreadsheet. Select Output > Microsoft Excel Writer. Right-click the step > Edit > specify the following fields for the Excel Writer step: Filename Sheet name You can add multiple Excel Writer steps in your transformation, according to the Foundation data you want to include in the Excel spreadsheet. The following image shows an example of Excel Writer step Write Employee:
Connection	Add the hop connection between the steps, so that you specify the sequence in which the steps must be processed. The following image shows an example of hop connections created for Pentaho transformation Person - Additional Details:
Qualification	Double-click the newly-created transformation Person Additional Details, and perform the following tasks: For pdireport-servername, select Qualification > Configure Qualification. Specify the qualification string. For example, to ensure that adiitional data (other than the qualified People data) is not processed, specify the following string: (('DataTags' = $NULL$) OR (('DataTags' != $NULL$) AND NOT(('DataTags' LIKE "%config-fnd%") OR ('DataTags' LIKE "%seeddata%") OR ('DataTags' LIKE "%SOLUTIONDATA%")))) Save the changes. Change the server to AR Server. The qualifications are passed to specify that Instance ID of Person is the required field. The Instance ID fields stores the foreign key in the extension record definition.