Loading Foundation data for multiple companies

This topic explains how to add the Company column in the Location, People, and PeopleOrg spreadsheets and customize their related Pentaho transformations. Performing this customization allows any user with the Data Management Tool (DMT) Admin or User role to load foundation data for multiple companies.

In BMC Remedy IT Service Management (BMC Remedy ITSM) 8.1 or later, any end user with the DMT Admin or User role can load foundation data, process data (templates), and transactional data for a company they belong to by selecting their company in the Data Management console. This is a change from BMC Remedy ITSM 7.6.x versions where the DMT was supported for Remedy administrators only. As part of this change, the Company column in the Location, People, and PeopleOrg spreadsheets was removed, which prevents end users from loading data for companies they do not belong to. The following steps explain how to add the Company column and modify related transformations so users can load data for companies they do not belong to. The Location spreadsheet is used as an example, but the same procedures apply to the People and PeopleOrg spreadsheets.

Warning

Adding the Company column to the Location, People, and PeopleOrg spreadsheets allows end users to load data for companies that they do not belong to, which presents a security risk. You should be fully aware of this risk before performing this customization. After you perform this customization, ensure that you give the DMT Admin Role only to users with unrestricted company access so they can load data for all your companies.

Complete the following steps in the order shown:

Create a custom spreadsheet to support multi-company data loads
Create a custom transformation
Create a custom Atrium Integrator job
Register the custom Atrium Integrator job with the Data Management Job Console
Use the Spreadsheet Management Console to attach the multi-company support spreadsheet
Create a custom Data Management job template
Run the newly created multi-company job
Review the transformations that can be modified for multi-company support
Modify workflow to promote custom fields

Create a custom spreadsheet to support multi-company data loads

Download the spreadsheet from Spreadsheet Management:
1. From the BMC Remedy Mid Tier, select Applications > Data Management > Spreadsheet Management and make the following selections from the list:
  - Spreadsheet Type: BMC Template
  - Spreadsheet Category: Foundation
2. Select the spreadsheet (Location in this example).
3. Click Download.
Add a Company column to the spreadsheet:
1. Rename the Location.xlsm spreadsheet to Multi_Company_Location.xlsm.
2. Open Multi_Company_Location_xlsm.
3. Add Company as the last column in the following worksheets:
  - SIT_LoadSiteGroup
  - SIT_LoadSiteCompanyAssoc
  - CTM_LoadRegion
4. Save your changes.

Create a custom transformation

Using the BMC Atrium Integrator Spoon client, connect to the AR System server by specifying server name and AR System server admin credentials.
Select File > Open.
In the Select Repository Object window, scroll down the list to the Location object with type Transformation and select it.
Click OK.

The transformation opens in a new window:
Select File > Save As and name the transformation Multi company support.
A new copy of the transformation is created.
In the Multi company support transformation, add a field to SIT_LoadSiteGroup:
1. Double-click the SIT_LoadSiteGroup icon.
2. Select the Fields tab.
3. Add a Company file with the following values:
- - Name: Company
  - Type: String
  - Length: -1
  - Precision: -1
  - Trim type: none
  - Repeat: N
  - Click OK.
Repeat step 6 for CTM_LoadRegion and SIT_LoadSiteCompanyAssoc.
Double-click SIT:LoadSiteGroup and open the Field Mapping tab.
1. Use the pull-down menu to change the Company mapping from JobCompany to Company, then click OK.
2. Repeat Step 8 and 9 for CTM:LoadRegion and SIT:LoadSiteCompanyAssoc.
Select File > Save to save the entire transformation.

Create a custom Atrium Integrator job

Open the Location job and select File > Open.
Scroll to Location with type Job, then click OK.

The Location job opens:
Select File > Save As and name the job Multi-company support.
A copy of the job is created.
Select the Multi company support repository object:
1. Double-click the Location icon:
2. Click the Select a Transformation button (next to Specify by name and directory):
3. Select the Multi company support transformation and click OK:
4. Click OK.
Select File > Save to save the entire job.

Register the custom Atrium Integrator job with the Data Management Job Console

From the BMC Remedy Mid Tier, log on as a user with DMT Admin permissions.
Select Applications > Data Management > Job Console > Other Function > Atrium Integrator Job.
Click Create.
Complete the form:
- Atrium Integrator Job: Multi_Company_Support
  
  Do not use spaces in the Job name, use underscores instead.
- Type: job
- Directory: /
- Log Level: Minimal
- Select Attachment Required
- Data Tag: cust
Click Save.

Use the Spreadsheet Management Console to attach the multi-company support spreadsheet

From the BMC Remedy Mid Tier, log on using a user name with DMT Admin permissions.
Select Applications > Data Management > Spreadsheet Management.
Click Create and complete the form:
- Description: Support Multi-company upload for Location
- Company: Name of the company for which you want to make this spreadsheet available
- Spreadsheet Type: Custom Template
- Spreadsheet Category: Foundation
- Spreadsheet Group: Location
- Attach the Multi_Company_Location.xlsm spreadsheet.

Create a custom Data Management job template

From the BMC Remedy Mid Tier, log on with UDM Admin permissions.
Select Applications > Data Management > Job Console > Other Function > Search template.
Search for Location.
When the Location template is available, select Copy template.
Complete the form:
- Template Name: Multi-company location support
- Template Description: Multi-company
- Category: Foundation
- Template Type: Custom Template
Click Save.
View the job template that you just created:
1. Click New Search for the job template for the Multi-company location support job.
2. Open the template in Modify mode.
3. Expand the Load step.
4. Select the Load Location step.
5. Click View.
6. Change Atrium Integrator Name to Multi_Company_Support and click Save.

Run the newly created multi-company job

Log on as a user with DMT Admin permission with unrestricted access.
Populate data with Multi_Company_Location_xlsm.
Select Application > Data Management > Job Console > Job Function > Create Job.
Specify the job name and select a company, then click Save.
Select Step > Create > Using BMC template and select the Multi Company location support template.
In the Load step, do the following:
- Attach the spreadsheet.
- Change job status from Draft to Built.
- Run the job to load the data.

Transformations that can be modified for multi-company support

Transformation name	Step	Modifications
Financials	FIN_LoadConfigCostCentersRep	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
Financials	FIN:LoadConfigCostCentersRep	Map Company to Company* in field mapping instead of JobCompany.
Generic_Catalog	CFG:LoadGenericProdSerAssoc	Map Company to Company* in field mapping instead of JobCompany.
Generic_Catalog	CFG:LoadGenericCatalog	Map Company to Company* in field mapping instead of JobCompany.
Geography	CTM_LoadPostalCodes	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
Geography	CTM:LoadPostalCodes	Map Company to Company* in field mapping instead of JobCompany.
People	FIN_LoadCostCenterUDAAssoc	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
	FIN:LoadCostCenterUDAAssoc	Map Company to Company* in field mapping instead of JobCompany.
	CTM_LoadPeople	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
	CTM:LoadPeople	Map Company to Company* in field mapping instead of JobCompany.
PeopleOrg	CTM_LoadPeopleOrganization	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
PeopleOrg	CTM:LoadPeopleOrganization	Map Company to Company* in field mapping instead of JobCompany.
Support_Group	CTM_LoadSupportGroup	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
	CTM:LoadSupportGroup	Map Company to Company* in field mapping instead of JobCompany.
	CTM_LoadSupportGroupAlias	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
	CTM:LoadSupportGroupAlias	Map Company to Company* in field mapping instead of JobCompany.
	CTM_LoadSGPOnCall	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
	CTM:LoadSGPOnCall	Map Company to Company* in field mapping instead of JobCompany.
	CFG_LoadBusinessTimeHolidays	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
	CFG:LoadBusinessTimeHolidays	Map Company to Company* in field mapping instead of JobCompany.
	CFG_LoadBusinessTimeWorkdays	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
	CFG:LoadBusinessTimeWorkdays	Map Company to Company* in field mapping instead of JobCompany.
	CTM_LoadSGPAssignments	Add Company* field as the last column in the spreadsheet (as covered in the example for location spreadsheet).
	CTM:LoadSGPAssignments	Map Company to Company* in field mapping instead of JobCompany.

Modify workflow to promote custom fields

If you have used the above steps 1-8 to add custom fields to the spreadsheet and transformation, then you need to follow the additional steps mentioned in Modifying workflow to promote custom fields. These additional steps are required if you have to modify the workflow that updates the data from the staging forms to the target forms.