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:
- 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
- Select the spreadsheet (Location in this example).
- Click Download.
- From the BMC Remedy Mid Tier, select Applications > Data Management > Spreadsheet Management and make the following selections from the list:
- Add a Company column to the spreadsheet:
- Rename the Location.xlsm spreadsheet to Multi_Company_Location.xlsm.
- Open Multi_Company_Location_xlsm.
- Add Company as the last column in the following worksheets:
- SIT_LoadSiteGroup
- SIT_LoadSiteCompanyAssoc
- CTM_LoadRegion
- 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:
- Double-click the SIT_LoadSiteGroup icon.
- Select the Fields tab.
- 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.
- Use the pull-down menu to change the Company mapping from JobCompany to Company, then click OK.
- Repeat Step 8 and 9 for CTM:LoadRegion and SIT:LoadSiteCompanyAssoc.
- Use the pull-down menu to change the Company mapping from JobCompany to Company, then click OK.
- 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:
- Double-click the Location icon:
- Click the Select a Transformation button (next to Specify by name and directory):
- Select the Multi company support transformation and click OK:
- Click OK.
- Double-click the Location icon:
- 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:
- Click New Search for the job template for the Multi-company location support job.
- Open the template in Modify mode.
- Expand the Load step.
- Select the Load Location step.
- Click View.
- 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). |
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. |
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). |
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). |
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.
Related topic
Foundation data onboarding for multiple companies with validation
Comments
Nice write up! However, it would be really nice if the step listed below had a better emphasis on NOT using spaces. I inadvertently missed that step and had a space in the name and caused me an extended period of time to figure out that a space in the name was breaking the back end processes. Even better if the process had some sort of validation on it to prevent dummies like me populating names with spaces in the field in the first place. LOL
Perhaps it got addressed in 9.X.
"5. Complete the form:
Cheers,
-d
Thank you for your comment David. I have moved the content into a Warning note so it's not missed. I have sent out a note to the development team about your suggestion to have a validation in place.
Regards,
Priya
Awesome! That will surely will help the next person!
Thank You!
Tip: When creating the various Transformations and Jobs in the repository I find it very helpful to append "-Multi-Company" to the end rather than add them to the front. This helps when locating the various names as they'll be sorted together.
Example
Location
Location-Multi-Company
Support_Group
Support_Group-Multi-Company
...
Also under the "Management Job Console".
The word "job" needs to be capitalized as in "Job".
Under "Create a custom Data Management job template" step 5. the "Company" field is required. I just used "Calbro Services"
Thanks a lot David k Hill for the tip. Will discuss with the UDM SME and get back to you.
Regards,
Sirisha
Murali Viswanathan
In step "Select File > Save As and name the transformation Multi company support.
A new copy of the transformation is created" there is a hanging "6." number. (Minor typo)
Hi David k Hill,
Thanks for your feedback. I have addressed your comment.
Regards,
Sirisha
In the 'Create a custom transformation' section, the 7th step reads
"7. Repeat step 2 for CTM_LoadRegion and SIT_LoadSiteCompanyAssoc."
it should say
"7. Repeat step 6 for CTM_LoadRegion and SIT_LoadSiteCompanyAssoc.".
Also there is an additional step missing between 9 & 10 in the 'Create a custom transformation' section:
"9b. Repeat Step 8 and 9 for CTM:LoadRegion and SIT:LoadSiteCompanyAssoc"
Hi Tony Reel,
I have addressed your comment.
Regards,
Sirisha
For the Generic Catalog you don't have to modify anything to load data for multiple companies. I believe you shouldn't do as described in 'Transformations that can be modified for multi-company support' and add a 'Company' field to the CFG_LoadGenericProdSerAssoc, CFG_LoadGenericCatalog tabs as there is no Company field in their associated Forms to map that data to.
Hi Tony Reel,
Will confirm with the SMEs and get back to you.
Regards,
Sirisha
Hi Tony Reel,
Per the SME inputs, I removed CFG_LoadGenericProdSerAssoc and CFG_LoadGenericCatalog from the above table.
Regards,
Sirisha
Tony Reel
For LDAP jobs, after "Register the custom Atrium Integrator job with the Data Management Job Console" need instructions to create the LDAP variables.
Log in or register to comment.