This documentation supports the 20.02 version of BMC Helix Platform.To view the documentation for the current version, select 20.08 from the Product version menu.

Creating a Pentaho job to load custom Foundation data


If you want to load custom Foundation data from Remedy IT Service Management (Remedy ITSM) Suite, 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.

Note

You can load Foundation data from Remedy ITSM Suite version 9.1 SP3 or later. For a complete list of compatible Remedy IT Service Management 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

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.

  1. Log in to BMC Remedy IT Service Management console as an ITSM administrator.
  2. Navigate to the CTM:People form.
  3. 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: 
    CTMPeople form_custom fields added.PNG

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.

  1. Log in to BMC Remedy AR System server as an AR administrator.
  2. In the Atrium Integrator Spoon client, select File > New > Job.
  3. 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

  4. 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:
    New job_Parameters tab.png


  5. 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:
    New job_job log entry table.png

    The following image is an example of Job log entry table fields that you need to copy to the new Pentaho job:
    New job_job log entry table.png

  6. 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.

  1. In the Atrium Integrator Spoon client, select File > New > Transformation.
  2. 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

  3. 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.

  4. 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.

    1. Select Input > Generate Rows.
    2. Right-click the step > Edit > specify the following information:
      1. Field name
      2. Field type
        The following image shows an example of Generate Rows step EMPL Header 1.

    Header transformation_1.png

    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.

    Header transformation_3.png

    Excel Writer

    This step appends the custom Foundation data to an Microsoft Excel spreadsheet.

    1. Select Output > Microsoft Excel Writer.
    2. 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.

    Header transformation_2.png

    Block this step until steps finish

    This step generates blocks the steps till all the earlier steps are completed successfully.

    1. Select Flow > Block this step until steps finish.
    2. 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:

    Header transformation_4.png

    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:

    Pentaho transformation_header.png

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.

Note

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.

  1. In the Atrium Integrator Spoon client, select File > New > Transformation.
  2. 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

  3. 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.

  4. 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.

    1. Select Input > AR Input.
    2. Right-click the step > Edit > specify the following information:
      1. Field name
      2. Field type

    The following image shows an example of Generate Rows step Get Employees.

    Process data_transformation_1.png

    Add constants

    This step adds one or more constants to the input rows.

    1. Select Transform > Add constants.
    2. Right-click the step > Edit > specify the following constants:
      1. varEmpty
      2. varOperation

    The following image shows an example of Add constants step.

    Process data_transformation_2.png

    Excel Writer

    This step appends the custom Foundation data to an Microsoft Excel spreadsheet.

    1. Select Output > Microsoft Excel Writer.
    2. 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:

    Process data_transformation_3.png

    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:

    Pentaho transformation_additional details.png

    Qualification

    Double-click the newly-created transformation Person Additional Details, and perform the following tasks:

    1. For pdireport-servername, select Qualification > Configure Qualification.
    2. 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%"))))
    3. Save the changes.
    4. 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.

Where to go from here

Synchronizing-custom-Foundation-data-changes-to-BMC-Helix-Platform

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*