Share This:


In ITSM 8.1, UDM allows for loading certain CIs via spreadsheets and the DMT Data Load mechanism. Those OOB CIs are Business Service, Computer System, Product, Processor, and Operating System. This blog will discuss a basic outline for extending this to include other CI types as well as customer CMDB attributes and classes (provided that the custom class is in BMC hierarchy). This blog is written as a convenience and reference. The audience for this blog is a consultant, implementer, or developer who is well familiar with the end-to-end UDM infrastructure, including the Carte server, Atrium Integrator, the Pentaho client for working with transformations (known as Spoon), and managing DMT jobs, templates, and steps.

Extending the OOB spreadsheet:

If you are using the BMC provided spreadsheets to load data into the Transactional CI spreadsheet, the first step is to add create a copy of the existing Sheet from Transactional_CI workbook.

Note (1): Steps to create a copy of Sheet from workbook

  1. Right click on the Sheet which you want to copy
  2. Select Move OR Copy Option
  3. Create the copy at appropriate sequence/position in the same workbook as shown in figure(1)
  4. You will need to rename the sheet appropriately


Figure (1)


I suggest you using BMC naming convention when possible, such as “AST_Equipment”. Most CI classes have many fields (columns) in common and only some are “required”, however it is incumbent on you to make sure that the class you’re extending includes the data needed to instantiate a CI of that class. You can use spreadsheet management to give access to your updated spreadsheet to your end-users. Remember that CI Name is used as a unique identifier here and should be unique for all your CI data loads.

Updating the transformation:

From the Atrium Integrator console, find the CI-CMDB job, and open the transformation in the Pentaho client as shown in Figure (2-A).

Figure (2-A)


Figure (2-B)


Note (2): Steps to open transformation

  1. To open transformation click on File menu Select Open
  2. You need to double click on CI-CMDB transformation [as shown in figure(2-B) in the repository to open it

This is a good opportunity to save a backup of the OOB transformation if you haven’t already.

Note (3): Backup CI-CMDB transformation as shown in Figure (3)


Figure (3)

Figure 3.png

You may find it easier to copy one of the existing set of steps in the transformation and then modifying it to suit the new class.

Note (4): Steps to create a copy

  1. To copy click on top left of step in the transformation then press left click mouse button and drag it to the bottom right of transformation without releasing the left click mouse button. You will see the blocks in the workflow diagram highlighted.
  2. You can then click on Edit Menu button and Copy the selection.
  3. Click on the vacant area at the bottom of the transformation that you are editing.
  4. Right click and select ‘Paste from Clipboard’ option to replicate it. It will look like as shown in figure (4)

Figure (4)


After you’ve copied the set of steps, you can begin editing the transformation. Keep saving the transformation so that you don’t lose the changes accidently.

First, update the name of the step to reflect the new CI type and on the “Sheets” tab, set the Sheet name to what you added in the Excel spreadsheet:

Note (5): Steps to edit the building blocks

  1. Double click on the First block [input step] to open it
  2. Change the step name field on the top appropriately [E.g. CI-Equipment-Input] as shown in figure (5-A)
  3. Select the Tab named sheet as shown in Figure (5-A)
  4. Change the sheet name with the one you created [mentioned in Note (1)]
  5. Click OK

Figure (5-A)

     Figure 5a.png

Next, under the Fields tab, click on the button “Get fields from header row..”, which is at the bottom right corner of the window and it will fetch all the fields from corresponding tab of a sheet. Once you click on this button, it will prompt whether to clear existing list. Click ok and you will get new list of fields as per the spreadsheet tab.

Figure (5-B)

figure 5b.png

If you’ve added columns, removed columns, or made changes to their “position”, this should be reflected here.

Next, edit the CMDBOutput step

Note (6): Modify CMDB Output step

Figure (6-A)


To fetch the Class names from AR System database you need to provide the Server Name, AR User Name, AR User Password, and AR TCP Port Number beforehand as shown in Figure (6-A)

  1. Click on Edit menu
  2. Select ‘Set Environment Variables’ option
  3. The Parameter Window will appear when you can set the required variables as shown in Figure (6-A)
  4. Click on Ok button to make the variable available for next step
  5. Double click on CI CMDB Output step under construction
  6. Change the step name appropriately [E.g.CI-Equipment-CMDBOutput] as shown in Figure (6-B)
  7. Click on Browse Button and Select the required Class Name from the CMDB database as shown in Figure (6-B) [E.g BMC_Equipment]
  8. Click on Edit Mapping button to check the correctness of field mapping as shown in Figure (6-B)
  9. Add OR remove fields from this list depending on the Attributes in the class and columns available in the spreadsheet.
  10. Click OK

Figure (6-B)


Note (7): Error Handling for CMDB Output block

  1. Right click on this CMDB Output step [to enable the error handling part]
  2. Select ‘Define Error Handling’ option
  3. Select the Target Step as shown in Figure (7)
  4. Check the ‘Error Handling’ Checkbox as shown in Figure (7)
  5. Enter value in ‘Error Description’ Field Name as shown in Figure (7)
  6. Enter the value in ‘Error Codes’ Field Name as shown in Figure (7)
  7. Click OK

Figure (7)


Next, make any changes needed to the steps that “normalize” data in the “Value Mapper” steps as shown in Figure (8)

Figure (8)


Note (8): You may need to add additional value mapper steps or remove them, depending on the needed data for that class.

Now, edit the AROutput block

Note (9): Remove the fields not present in the Class Selected. Also Add new fields if as required.

  1. Double click on the Appropriate AR Output Step under contruction
  2. Click on ‘Field Mapping’ tab as shown in Figure (9-A)
  3. Check for the mapping
  4. Delete the fields which are not present in the class
  5. Click OK.



As with the CMDBOutput mapping the changes here should match the requirements for the class and the changes you’ve made to the columns on the worksheet. This all goes into AST:LoadAttributes, which should match up with the CI that was added during the CMDBOutput step.

You should re-name the error steps, however it should not be necessary to change them, since the data coming back reporting the error is not class specific.

Note (10): Error handling for AR Output

  1. Right click on the AR Output Step under constriction
  2. Select Define Error Handling
  3. Select the ‘Target Step’
  4. Check the ‘Error Handling’ checkbox as shown in Figure (10)
  5. Enter value in ‘Error Description’ Field Name  as shown in Figure (10)
  6. Enter the value in ‘Error Codes’ Field Name as shown in Figure (10)
  7. Click OK


Figure (10)



Save the transformation. After this is complete, you should have a new transformation that will pick up information in the added worksheets.

You can now you can create and run the UDM Job from Data Management Job console.