Data Loader

This documentation assumes that your organization has completed the deployment of the Rounds module. The Rounds Data Loaders should be used only after the Rounds module has been implemented.

Security Settings

The Security User performing the data load operation must be associated with either the MI Data Loader User or MI Data Loader Admin Security Role, and must also be associated with the MI Operator Rounds Administrator Security Group or a Security Role that is associated with this Security Group.

Assumptions

• All imported date values are in UTC.
• Functional Locations and/or Equipment have already been loaded into APM.
• Blank values are not copied to the entities. If there are blank values, those fields will be skipped.
• No customization (that is, rules changes) is done on the related families that override the baseline families.

Rounds Routes and Reading Data Loader Data Model

The following data model illustrates the Routes and Readings Data Loaders:

Rounds Template Data Loader Data Model

The following data model illustrates the Template Data Loader:

Overview of Rounds Data Loader Workbooks

This section provides a high-level overview and explanation of how the data loader workbooks are constructed.

In order to import data using the Rounds Data Loaders, APM provides separate data loader workbooks for:

• Rounds Allowable Values.
• Rounds Templates.
• Rounds Routes.
• Rounds Readings.

The workbooks support the baseline Rounds functionality in APM. The workbooks must be used to perform the data load. You can modify the data loader workbooks to include custom fields used by your organization.

Each data loader workbook contains one worksheet for each node that will be populated in the data model.

Each worksheet in these data loader workbooks contain field values that must be mapped to the appropriate APM family/field combination. The individual sections in this document specify the source and target fields and relationships required between predecessors and successor.

The list of fields tabulated is not exhaustive. The user can add additional field columns to the worksheets and, provided that the field columns are properly mapped within the worksheets, the upload will still be successful.

Data Loader Batch Fields

The Rounds Data Loaders workbooks include columns of data that are used to identify batches of related records. These values are not imported to a field in the APM database. Generally, these will be the first few columns in each worksheet. They each use a prefix that identifies the relevant record type being imported, whereas columns that will be imported have IDs that match the field IDs in APM, which generally use the prefix MI_.

Rounds Allowable Values Data Loader Workbook

When importing data using the Rounds Allowable Values Data Loader, the data loader will create one Allowable Value record for each row of the worksheet. If the import worksheet specifies an Allowable Value that is an exact match for an existing record, the record is not imported. Since the unique identification of an Allowable Value record includes all its fields, it is not possible to update existing Allowable Value records using the data loader.

On the worksheet, you will specify the allowable values that will be used in Measurement Location Templates and Measurement Locations.

Rounds Templates Data Loader Workbook

When importing data using the Rounds Templates Data Loader, the data loader will create records as follows:

• Template Groups: The data loader will create one Template Group record for each row in the import worksheet. If the Template Group ID specified matches the Template Group ID of an existing Template Group, the existing record will be updated.

  The Template Groups will be loaded into APM, and will be filtered based on the designated site name in the Site Reference Name field.

  Note: If you want to set the site reference to be a Global Site Reference, enter the constant ‘*Global*’ in the MI_SITE_NAME column on the spreadsheet.

  The following site inheritance rules are applied when Template Groups are created using the data loader:

  • For the Template Groups where the site reference is set to Global, the Template Groups can contain ML Templates for any Site, and Global ML Templates.
  • For the Template Groups where the site reference is assigned to Site X, the Template Groups can contain Global ML Templates and ML Templates with Site X.
  • When a Global Template Group is applied to a asset assigned to Site X, only the Global ML Templates and Site X ML Templates will be applied.
• Measurement Location Templates: The data loader will create one Measurement Location Template record for each row in the data loader workbook. If the import worksheet specifies a Template ID value that matches the Template ID of an existing Measurement Location Template record, the existing record will be updated. If the worksheet specifies values for Category or Allowable Value that do not already exist in the database for the specified type (numeric or character) of Measurement Location, the record will not be imported.

  The Measurement Location Templates will be loaded into APM, and will be filtered based on the designated site name in the Site Reference Name field.

• Checkpoint Template Sequence: The data loader will create one Checkpoint Template Sequence record for each row in the worksheet. Since the unique identification of a Checkpoint Template Sequence record includes all its fields, it is not possible to update existing Checkpoint Template Sequence records using the Rounds Templates Data Loader. Updates to existing Template Groups to add or reorder the related Measurement Location Templates must be performed using the Rounds Designer user interface.

  Each Checkpoint Template Sequence record will take the same Site Reference Name that is designated to the Template Group to which it is assigned.

• Checkpoint Conditions: The data loader will create one Checkpoint Condition record for each row in the Checkpoint Condition worksheet. This worksheet defines the conditions under which the successor Checkpoints for Checkpoint Conditions are displayed in Rounds Data Collection. This worksheet also works together with the Measurement Location worksheet to determine the Checkpoint sequence for Template Groups with Checkpoint Conditions.

  The values specified in the CPC ID field in this worksheet are used by the Checkpoint Template Sequence worksheet to specify when Checkpoints have predecessor Checkpoint Conditions. Additionally, this worksheet uses the values specified in the CTS ID field in the Checkpoint Template Sequence worksheet to specify predecessor Checkpoints for Checkpoint Conditions.

  Note: For more details regarding how to create a Template Group with Checkpoint Conditions using the Rounds Templates Data Loader, see the Example Rounds Templates Data Loader Workbook with Checkpoint Conditions section.

Template Group Worksheet (MI_DTACLTMP)

Measurement Location Worksheet (MI_ML_TMPLT)

* For a Numeric ML Template, the limit values must be in descending order (that is, lower level 3<lower level 2<lower level 1<upper level 1<upper level 2<upper level 3).

Checkpoint Template Sequence Worksheet (MI_HSCPTMP)

Checkpoint Condition Worksheet (MI_CHKPCOND)

Lubrication Requirement Worksheet (MI_LR_TMPLT)

Rounds Routes Data Loader Workbook

When importing data using the Rounds Routes Data Loader, you can:

• Create Route, Measurement Locations, Lubrication Requirement and Checkpoint Task records.
• Assign or unassign a Security User to the Route.

When importing data using the Rounds Routes Data Loader, this data loader will create records as follows:

• Routes: The data loader will create one Route record for each row in the Route worksheet. The Next Date field on the Route record is populated automatically by existing rules based on the value in the Schedule column. The user can however overwrite the Next Date Value by providing a value in the worksheet. If the Schedule is invalid, the Route record will not be created. The Route ID column in the data loader is used for identifying the Routes. If a Route with this ID is already present in the database, it will update the Route.

  The Route created will be loaded into APM, and will be filtered based on the designated site name in the Site Reference Name field.

  Note: If you want to set the site reference, to be a Global Site Reference, enter the constant ‘*Global*’ in the MI_SITE_NAME column on the spreadsheet.
  Note: Adding a value in the Schedule column is not mandatory.
• Security Users: The data loader will create relationships assigning each Route, identified by Route ID, to each Security User listed.

  When you specify a Route ID, only the corresponding users specified in this sheet will be assigned to the Route, regardless of whether other users were previously assigned to it.

  For example, if three users are listed, and you want five users to be listed (that is, you want to add two users), you must include all five users in the sheet. If you include only the two new users, the original three users will be removed from the Route.

  If you include a Route ID in this sheet with no Security Users listed, all existing user assignments will be removed.

  Note that the relationship created is between the Route and the Human Resource record linked to the Security User; therefore, the Human Resource records must already exist before the data loader is used.

• Measurement Locations / Lubrication Requirement records / Checkpoint Tasks: The data loader will create one Measurement Location or a Lubrication Requirement record and one related Checkpoint Task record for each row of the Measurement Location import worksheet. Measurement Location records / Lubrication Requirement records are processed in batches using the Route ID. The MLs/ LRs are identified by the Checkpoint ID column. If a Measurement Location / Lubrication Requirement with this Checkpoint ID is already present in the database, then the ML will be updated.

  The Next Date, Non-compliance Date, and the Non-compliance Next Check Date fields on the Checkpoint Task record will be populated automatically by existing rules based on the value in the Schedule column. If the Schedule is invalid, the record will not be created.

  Note: Adding a value in the Schedule column is not mandatory.

  Each Measurement Location or Lubrication Requirement record will take the same Site Reference Name that is designated to the predecessor Asset. If there no Site Reference Name that is assigned to the Asset, the ML or LR takes the Site Reference Name from the Route to which the ML or LR is assigned.

• Sequence Number: The User can provide the Sequence number for the Measurement Locations / Lubrication Requirement records under a Route in the Sequence column on the import worksheet. The data loader will consider the sequence provided and order the Measurement Locations / Lubrication Requirement records based on it. However, if there is any random / incorrect / blank / non-sequential numbers in this column, the data loader will correct them and place them sequentially while importing them. Any new insertion of Measurement Locations / Lubrication Requirement records under a Route will always be at the end of the Route with maximum sequence number under the Route. The data in the Sequence column is ignored when the Measurement Location / Lubrication Requirement record is standalone or the Measurement Location / Lubrication Requirement record is being updated.
  Note: Re-sequencing the Measurement Locations and Lubrication Requirements under a Route is possible using data loaders in V4.1.5.0. or later.

  The data loader will populate an Asset entity key into the new Measurement Location / Lubrication Requirement records, based on matching the value specified in the Related Asset Key column (that is, Functional Location ID for FLOCs or Equipment Technical Number for Equipment) in the Measurement Location import worksheet. If there is no Related Asset Key, it will use the related Asset ID field to search the Equipment and Functional Location families for a matching record and link it to the Measurement Location / Lubrication Requirement record and also populate the Related Asset Key.

  Note: The Asset ID is not required. If a matching Asset ID is not found, the ML is created without linking to any asset and a warning is added to the import log.
  • Provided that the Measurement Location / Lubrication Requirement record has received an Asset Key, the family rules will populate additional information in the Measurement Location / Lubrication Requirement records and create a relationship between the asset (predecessor) and Measurement Location / Lubrication Requirement records (successor).
  • The data loader will link each new Measurement Location / Lubrication Requirement record to an existing Route record that matches the value specified in the Route ID column. If a matching Route ID is not found, the records will not be created.
  • Standalone Measurement Locations / Lubrication Requirement records can be created without being linked to a Route by specifying <Dummy ID> as the Route ID and the Route Batch ID as 1000000000.
  • The data loader will link each Measurement Location / Lubrication Requirement record to the Action record which is specified by the Action ID column and is related to the Strategy record specified by the Strategy ID Column.

  If the Asset Health Indicator service is configured and started, Health Indicator records will be added automatically for each imported Measurement Location record that is linked to an Equipment or Functional Location.

  Note: Health Indicator records are not added for Lubrication Requirements, since there is no Health Indicator Mapping.
• Checkpoint Conditions: The data loader will create one Checkpoint Condition record for each row in the Checkpoint Condition worksheet. This worksheet defines the conditions under which the successor Checkpoints for Checkpoint Conditions are displayed in Rounds Data Collection. This worksheet also works together with the Measurement Location worksheet to determine the Route sequence for Routes with Checkpoint Conditions.

  The following table shows the relationships between fields in this worksheet and fields in the Measurement Location worksheet that determine which Checkpoints precede or succeed which Checkpoint Conditions.

  Measurement location Worksheet Fields	Checkpoint Condition Worksheet Fields
  ML Identifier (ROUNDS_ML_ID)	Parent ML Batch ID (PARENT_ROUNDS_ML_ID)
  Checkpoint ID (MI_CHECK_PT_CHEC_ID_C)	Parent ML ID (MI_CHECK_PT_PR_CHEC_ID_C)
  Parent Condition Batch ID (MI_CHKPCOND_BATCH_ID)	Condition Batch ID (MI_CHKPCOND_BATCH_ID_C)
  Parent Condition Identifier (MI_CHKPCOND_ID_C)	Condition Identifier (MI_CHKPCOND_ID_C)

  When the values in the Parent ML Batch ID and Parent ML ID fields for a Checkpoint Condition match the values in the ML Identifier and Checkpoint ID fields, respectively, for a Checkpoint, this indicates that the Checkpoint Condition is a successor of the Checkpoint.

  When the values in the Condition Batch ID and Condition Identifier fields for a Checkpoint Condition match the values in the Parent Condition Batch ID and the Parent Condition Identifier fields, respectively, for a Checkpoint, this indicates that the Checkpoint Condition is a predecessor of the Checkpoint.

  Note: For more details regarding how to create a Route with Checkpoint Conditions using the Rounds Routes Data Loader, see the Example Rounds Routes Data Loader Workbook with Checkpoint Conditions section.

Route Worksheet (MI_ROUTE000)

Security User Worksheet (MI_Security User)

Measurement Location Worksheet (MI_MEAS_LOC)

Checkpoint Task Worksheet (MI_CP_TASK0)

Checkpoint Condition Worksheet (MI_CHKPCOND)

Rounds Readings Data Loader Workbook

When importing data using the Rounds Readings Data Loader, the data loader will create one Reading record for each row of the import worksheet. The data loader will attempt to link each reading to a Checkpoint record (that is, Measurement Location record or Lubrication Requirement record) based on the related Measurement Location and Lubrication Requirement records specified in the import worksheet. To properly identify the Checkpoint to which Readings are linked, the import must specify either the Entity Key or the Checkpoint ID. If no matching Checkpoint is found, the reading is not imported. The Asset ID and Unit of Measure (if applicable) will be populated on the Reading based on the related Checkpoint, and the fields on the related Checkpoint Task will be updated according to the applicable schedule.

Each Reading record will take the same Site Reference Name that is designated to the Measurement Location or Lubrication Requirement to which it is assigned.

On the worksheet, you will specify the Readings that you want to link to Checkpoints (that is, Measurement Locations or Lubrication Requirements).

When Readings are imported, the Status of the Reading is set, and the Checkpoint Task related to the Measurement Location or Lubrication Requirement is updated automatically. In order for the status of each Reading to be set as expected, Readings should be ordered earliest first on the worksheet.

Example Rounds Templates Data Loader Workbook with Checkpoint Conditions

This section provides samples of the worksheets in the Rounds Templates Data Loader workbook to illustrate the process of creating a Template Group containing Checkpoint Conditions using the Rounds Templates Data Loader.

Overview

When imported into APM, this example workbook creates a Template Group with six Checkpoints and two Checkpoint Conditions, as shown in the following image:

This section assumes that you are familiar with the basic process of creating Templates in a data loader and you are familiar with the fields used in these worksheets. For more information on the fields displayed in the following examples, see the Rounds Templates Data Loader Workbook section for Rounds Data Loaders.

Example Rounds Routes Data Loader Workbook with Checkpoint Conditions

This section provides samples of the worksheets in the Rounds Routes Data Loader workbook to illustrate the process of creating a Route containing Checkpoint Conditions using the Rounds Routes Data Loader.

Overview

When imported into APM, this example workbook creates a Route with six Checkpoints and two Checkpoint Conditions, as shown in the following image:

This section assumes that you are familiar with the basic process of creating Routes in a data loader and you are familiar with the fields used in these worksheets. For more information on the fields displayed in the following examples, and for more information on the worksheets contained in the Rounds Routes workbook, see the Rounds Routes Data Loader Workbook section for Rounds Data Loaders. This section also does not contain information on the Security User worksheet or the Checkpoint Task worksheet.