Creating Multi Company Support in DMT

Version 4
    Share:|

    We've installed Remedy 8.1.02 and it no longer supports loading multiple company data in a single workbook.  We are single tenant so we don't have the added complexity of limiting People records to only have access to specific companies or have Unrestricted Access selected/not selected.  At least with these instructions you can load multiple company data in single files.  Hopefully someone else in the community can figure out how to implement the multi-tenant portion for People records.

     

    Here are instructions on how to configure everything related to adding multiple company support in DMT.

    Note: Our company only required the Location, People, and People Organizations to have multiple company support.  The same process can be used for the other spreadsheets.

    The attached Word document contains everything in this post.  The attached workbooks are configured with multiple company support.

     

    DMT

    If you haven’t already done so you’ll need to configure DMT and download the spreadsheets.

     

    Setup the DMT Load path

    • Login to AR Server.  Ex: devremedyars
    • Create folder. Ex: E:\DataManagementLoad
    • Update permissions on folder (User Name: "Users" - Update to allow Write permissions)
    • From the Remedy mid-tier, open the Application Administration Console | Custom Configuration | Foundation | Advanced Options | System Configuration Settings - System Settings
    • Add the Data Management Load File Path: Ex: \\devremedyars\e$\DataManagementLoad\
    • Select Test and verify the message states "The test has been executed and appears successful..."
      • I think the common issues are not including the backslash at the end of the URL and not updating the permissions to the folder
    • Open the AR Server, DataManagementLoad folder and verify a new folder has been created with the name of the logged in user
    • If everything looks good select Save

     

    Download the spreadsheets

    • From the Remedy mid-tier, Select Applications | Data Management | Spreadsheet Management
    • Spreadsheet Type: BMC Template
    • Spreadsheet Category: Foundation
    • Save the appropriate spreadsheets by single clicking the spreadsheet row and selecting the Download button.  Our company only needs the below spreadsheets updated, the same process is used for every spreadsheet you want to add multi-company support.
      • Location
      • People
      • PeopleOrg

     

    Spreadsheets

    Location

    • Rename Location.xlsm to Multi_Company_Location.xlsm
    • Open Multi_Company_Location.xlsm
    • Perform the below actions in each of these sheets:
      • SIT_LoadSiteGroup
      • SIT_LoadSiteCompanyAssoc
      • CTM_LoadRegion
    1. Select the entire column for Column A.  Right-click and select Copy
    2. Right-click on Column A and select Insert Copied Cells
    3. Change the cell contents to:
      1. A1: This required field contains the Company name.
      2. A2: 254
      3. A3: Company*
    4. Save the file

     

    People

    • Rename People.xlsm to Multi_Company_People.xlsm
    • Open Multi_Company_People.xlsm
    • Perform the below actions in these sheets (The other sheets already have a Company column):
      • FIN_LoadCostCenterUDAAssoc
      • CTM_LoadPeople
    1. Select the entire column for Column A.  Right-click and select Copy
    2. Right-click on Column A and select Insert Copied Cells
    3. Change the cell contents to:
      1. A1: This required field contains the Company name.
      2. A2: 254
      3. A3: Company*
    4. Save the file

     

    • Optional: CTM_LoadSGPFunctionalRole doesn't list the Work Order Functional Roles, if you want to include them for easy reference update cell E1 to include:

    30000   for Work Order Assignee

    31000   for Work Order Manager

    • You can find more Functional Roles in the SYS:Menu+Items form. Advanced Search:

    'Menu Type' = "Functional Role" AND 'Status' = "Enabled" AND 'Menu Label 2' != $NULL$

     

    PeopleOrg

    • Rename PeopleOrg.xlsm to Multi_Company_PeopleOrg.xlsm
    • Open Multi_Company_PeopleOrg.xlsm
    • Perform the below actions in this sheet:
      • CTM_LoadPeopleOrganization
    1. Select the entire column for Column A.  Right-click and select Copy
    2. Right-click on Column A and select Insert Copied Cells
    3. Change the cell contents to:
      1. A1: This required field contains the Company name.
      2. A2: 254
      3. A3: Company*
    4. Save the file

     

    BMC Atrium Integrator Spoon

    If you don’t already have Atrium Integrator Spoon:

    https://docs.bmc.com/docs/display/public/ars201401/Atrium+Integrator+Spoon+client

    If you don’t already have Atrium Integrator Client:

    https://docs.bmc.com/docs/display/public/ac81/Installing+Atrium+Integrator+client+on+a+single+computer

    The Atrium Integrator client installation file wasn’t easy for me to find.  The installation file is available on the BMC support website in the Product Downloads section.

    • I have favorites setup but to find this file you’ll need to open view the Licensed Products View
      • There’s a search button but searching for Atrium Integrator  doesn’t display any results
    • Scroll to the BMC Remedy IT Service Management Suite and expand it
    • Select the appropriate version and expand it.  We’re using 8.1.02
    • The BMC Atrium Core - CMDB folder is available, expand it
    • Select the BMC Atrium Integrator Client to download it
    • Extract the file and install the program

     

    Location Transformation

    • Open BMC Atrium Integrator Spoon and login to your Remedy server
    • Select the Open icon
    • Find Name: Location | Type: transformation
    • Double-click to open it
    • Select File | Save As
    • Transformation Name: Multi_Company_Location
    • Each flow corresponds to a sheet in the workbook
      • The first step and second to last step needs to be updated to support the new Company* column
    • Open step SIT_LoadSiteGroup
      • Select the Fields tab
      • Right-click on the first row
      • Select Insert before this row (On my computer the row is randomly inserted somewhere in the list or it doesn’t add it anywhere in which case you’ll have to add it as the last row)
      • Input the following information in the new row:
        • Name: Company*
        • Type: String
        • Length: -1
        • Precision: -1
        • Trim type: none
        • Repeat: N
      • If it isn’t already the first row, move it to the top of the list.  The order has to match exactly with the spreadsheet.  You can select the row number and <ctrl> <up arrow> to move the field faster
      • Selecting an empty row at the end can cause an empty row to be created.  If you do that, make sure to delete it.
      • Select OK
    • Open step SIT:LoadSiteGroup
      • Select the Field Mapping tab
      • Find Company in the Form Field row
      • Change JobCompany to Company*
        • The order of field mappings doesn’t matter in this step like it did in the previous step.
      • Select OK
    • Follow the same process for the remaining steps:
      • CTM_LoadRegion, CTM:LoadRegion
      • SIT_LoadSiteCompanyAssoc, SIT:LoadSiteCompanyAssoc
    • Save the transformation and close it

     

    Location Job

    • Select the Open icon
    • Find Name: Location | Type: job
    • Double-click to open it
    • Select File | Save As
    • Transformation Name: Multi_Company_Location
    • Open the Location job entry
    • The Specify by name and directory is Location.  Select the icon with the green arrows to view all transformations
    • Select Multi_Company_Location
    • Select OK
    • Select OK
    • Save the job and close it

     

    People Transformation

    • Open BMC Atrium Integrator Spoon and login to your Remedy server
    • Select the Open icon
    • Find Name: People | Type: transformation
    • Double-click to open it
    • Select File | Save As
    • Transformation Name: Multi_Company_People
    • Each flow corresponds to a sheet in the workbook
      • The first step and second to last step needs to be updated to support the new Company* column
    • Most sheets already have a Company column so there aren’t many updates to make
    • Open step FIN_LoadCostCenterUDAAssoc
      • Select the Fields tab
      • Right-click on the first row
      • Select Insert before this row (On my computer the row is randomly inserted somewhere in the list or it doesn’t add it anywhere in which case you’ll have to add it as the last row)
      • Input the following information in the new row:
        • Name: Company*
        • Type: String
        • Length: -1
        • Precision: -1
        • Trim type: none
        • Repeat: N
      • If it isn’t already the first row, move it to the top of the list.  The order has to match exactly with the spreadsheet.  You can select the row number and <ctrl> <up arrow> to move the field faster
      • Selecting an empty row at the end can cause an empty row to be created.  If you do that, make sure to delete it.
      • Select OK
    • Open step FIN:LoadCostCenterUDAAssoc
      • Select the Field Mapping tab
      • Find Company in the Form Field row
      • Change JobCompany to Company*
        • The order of field mappings doesn’t matter in this step like it did in the previous step.
      • Select OK
    • Follow the same process for the remaining steps:
      • CTM_LoadPeople, CTM:LoadPeople
    • Save the transformation and close it

     

    People Job

    • Select the Open icon
    • Find Name: People | Type: job
    • Double-click to open it
    • Select File | Save As
    • Transformation Name: Multi_Company_People
    • Open the People job entry
    • The Specify by name and directory is Location.  Select the icon with the green arrows to view all transformations
    • Select Multi_Company_People
    • Select OK
    • Select OK
    • Save the job and close it

     

    PeopleOrg Transformation

    • Open BMC Atrium Integrator Spoon and login to your Remedy server
    • Select the Open icon
    • Find Name: PeopleOrg | Type: transformation
    • Double-click to open it
    • Select File | Save As
    • Transformation Name: Multi_Company_PeopleOrg
    • Each flow corresponds to a sheet in the workbook
      • The first step and second to last step needs to be updated to support the new Company* column
    • Open step CTM_LoadPeopleOrganization
      • Select the Fields tab
      • Right-click on the first row
      • Select Insert before this row (On my computer the row is randomly inserted somewhere in the list or it doesn’t add it anywhere in which case you’ll have to add it as the last row)
      • Input the following information in the new row:
        • Name: Company*
        • Type: String
        • Length: -1
        • Precision: -1
        • Trim type: none
        • Repeat: N
      • If it isn’t already the first row, move it to the top of the list.  The order has to match exactly with the spreadsheet.  You can select the row number and <ctrl> <up arrow> to move the field faster
      • Selecting an empty row at the end can cause an empty row to be created.  If you do that, make sure to delete it.
      • Select OK
    • Open step CTM:LoadPeopleOrganization
      • Select the Field Mapping tab
      • Find Company in the Form Field row
      • Change JobCompany to Company*
        • The order of field mappings doesn’t matter in this step like it did in the previous step.
      • Select OK
    • Save the transformation and close it

     

    PeopleOrg Job

    • Select the Open icon
    • Find Name: PeopleOrg | Type: job
    • Double-click to open it
    • Select File | Save As
    • Transformation Name: Multi_Company_PeopleOrg
    • Open the PeopleOrg job entry
    • The Specify by name and directory is Location.  Select the icon with the green arrows to view all transformations
    • Select Multi_Company_PeopleOrg
    • Select OK
    • Select OK
    • Save the job and close it

     

     

    Data Management

    Configure the Data Management tool to use the new Atrium Integrator jobs

    • From the Remedy mid-tier, Open Data Management | Job Console
    • Expand Other Functions
    • Open Atrium Integrator Jobs
      • Select Create
        • Atrium Integrator Job: Multi_Company_Location
        • Type: Job
        • Directory: /
        • Log Level: Minimal
        • Attachment Required: Checked
        • Data Tag: config-dmt
      • Select Save
      • A success message should appear, select OK
      • Perform the above steps for the Multi_Company_People and Multi_Company_PeopleOrg jobs

     

    Configure the Data Management tool to use new templates

    • From the Remedy mid-tier, Open Data Management | Job Console
    • Expand Other Functions
    • Open Search Template in the Other Functions section
      • Select Category: Foundation
      • If search results already appear then select New Search.  In the Template Details tab:
        • Category: Foundation
        • Select Search
      • Select Location
      • Scroll to the bottom of the page and select Copy Template
        • Template Name: Multi Company Location
        • Template Description: Supports loading multiple companies in a single workbook
        • Category: Foundation
        • If you want the template to be available for specific companies
          • Template Type: Custom Template
          • Company: Select the default company.  Adding more companies is later in process
          • If you want the template to be available for all companies
            • Template Type: Standard Template
              • This configures the template as a BMC Template which is available to all companies
              • Optional: If you want the change the template type back to Custom Template and still have it available to all companies you’ll need to perform a database update.  I suggest saving the existing values for these fields future reference in case something doesn’t work now or in the future

    UPDATE ARSystem.dbo.DMT_SavedJobTemplates

    SET Vendor_Assignee_Groups = ';-20700;-20701;-1000000000;', DataTags = 'config-dmt', Job_Template_Type = '1'

    WHERE Job_Name LIKE 'Multi Company%'

    • Select Save
    • Select the Refresh button in the top right of the screen
    • Select Multi Company Location
      • If you selected the Template Type as a Custom Template
        • Update Company Access to include all of the companies that require access to this template
      • In the Steps section, open the Load Location step
      • Change Atrium Integration Name: Multi_Company_Location
      • Verify Template Type is the correct type
        • BMC Template which is available to all companies
        • Custom Template which is restricted to specific companies
      • Select Save
      • The Step stays open so you’ll need to select the Job Template (Search) link in the upper middle of the page
      • None of the Validation or Promote steps need to be changed
    • Perform the above steps for the People and PeopleOrg templates
      • Select the BMC template, Copy Template, Update fields, Refresh, Select new template, Update the Load step

     

    Add the multi company spreadsheets to the Data Management tool

    • From the Remedy mid-tier, Open Data Management | Spreadsheet Management
    • Select Create
      • Description: Multi Company Location
      • Company: Select your company name ( - Global - isn’t available)
        • Option 1: If you need to have the spreadsheets available to other companies then you’ll need to follow this process for each company
        • Option 2: To make the spreadsheets available to all companies you’ll need to perform a database update.  I suggest saving the existing values for these fields future reference in case something doesn’t work now or in the future.  You’ll need to perform the database update after you save the template

    UPDATE ARSystem.dbo.DMT_Spreadsheet

    SET Company = '- Global -', DataTags = 'config-fnd', Assignee_Groups = ';0;'

    WHERE Description LIKE 'Multi Company %'

      • Spreadsheet Type: Custom Template
      • Spreadsheet Category:  Foundation
      • Spreadsheet Group: Location
      • Select the Attach Spreadsheet icon
        • Choose File, find and attach Multi_Company_Location.xlsm
        • Select OK
      • Select SAVE
    • Follow the same process for the Multi_Company_People and Multi_Company_PeopleOrg

     

    Creating a Job

    • From the Data Management Job Console select Create
    • Input a Job Name
    • Optionally input a Job Description
    • Company: Always select your company, the spreadsheets will create the data in the appropriate companies
    • Select Save
    • The Steps section displayed, select Create | Using Custom Templates
      • If you didn’t change the template type when configuring the templates then go back and update the template or select BMC Template
    • Select the appropriate template
    • Open the Load step
    • Verify the Atrium Integrator Name is accurate, if it isn’t then select the appropriate Multi_Company AI job.  Be sure to update the template with the correct AI Job name
    • Attach the appropriate spreadsheet
    • Select Save
    • The Step stays open so you’ll need to select the Job link in the upper middle of the page (Ex: DJB000000000102)
    • Change the Status from Draft to Built
    • Select Save
    • The Run button is now available, select it
    • Wait for the job to complete, verify no errors occurred, and optionally look in the different forms for the new data