Data Loaders

About the Rounds Pro Data Loaders

You can use the Rounds Pro Data Loaders to import content into the Rounds Pro module in bulk.

The Rounds Pro Data Loaders are used to create records in the following families and relevant relationships between them:

  • Rounds Pro Picklist Values Data Loader
  • Rounds Pro Step Template Data Loader
  • Rounds Pro Step Readings Data Loader
  • Rounds Pro Route Masters Data Loader
    • Create Route
    • Create Step
    • Human Resource
    • Teams

About the Rounds Data Loaders Requirements

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

About the Rounds Pro Data Loaders General Loading Strategy

This section describes any prerequisites to loading the data and the order in which the data will be loaded.

Data Import

When importing data using the Rounds Pro Data Loaders:

  • The batch records is limited .xls spreadsheet file.
  • You must use the data loader workbook while considering the rules described in Workbook Layout and Use.
  • When importing huge number of records using the Rounds Pro Data Loader, you can split the records in the spreadsheet into batches and assign a batch ID for each batch. Records within a spreadsheet of a batch should be coded with the same Batch ID. Each list of batch records should be loaded using an individual spreadsheet. Using the batch ID, you can import the records to the Data Loaders.
  • Below are the recommendations of the number of records per batch that can be loaded into Data Loaders using the Batch IDs:
    • Picklists: 1000 records per batch (Same for both Reading and Action types)
    • Step Templates: 1000 records per batch
    • Steps: 1000 records per batch
    • Conditions: 1000 records per batch
    • Routes: 10 Routes (Not more than 1500 steps per route, no more than 5 user or team assignments per route)
    • Step Readings: 1000 records per batch
    Note: Small batches are less efficient compared to larger batches. There is overhead with each batch that can be reduced by reducing the number of batches.

Schedule and Date Fields

  • The Rounds Pro Data Loaders require that Schedule values are expressed using the JSON format used in the APM database. Valid schedule expressions may be easily generated via the schedule setting feature on a schedule field in a Route or Checkpoint Task record in Record Manager, and then copying the resulting text value to the worksheet.

  • The Route Data Loaders use a non-standard date format. Data should be formatted as yyyy-mm-dd hh-mm-ss (for example, 2015-05-23 17-45-34).

  • All date values are assumed to be in UTC. The time zone of the user loading the data is not relevant.

About the Rounds Pro Data Loader Workbooks Layout and Use

Overview of Rounds Data Loader Workbooks

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

To import data using the Rounds Data Loaders, APM provides separate data loader workbooks for:

  • Rounds Pro Picklist Values
  • Rounds Pro Steps
  • Rounds Pro Step Template
  • Rounds Pro Step Conditionals
  • Rounds Pro Route Masters
  • Rounds Pro Step Readings

The workbooks support the baseline Rounds Pro functionality in APM. The workbooks must be used to perform the data load.

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 or 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.

Data Loader Batch Fields

The Rounds Pro 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 Pro Picklist Values Data Loader Workbook

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

Note: Picklist Values records do not support Site Filtering.

On the worksheet, you will specify the picklist values that will be used in Step Templates and Steps.

Field Caption

Field ID

Data Type (Length)

Comments

Picklist Batch ID

RTE_PICK_PCKLST_IDCharacter (50)This field is required for identification during the data load process and must be unique. The value in this field is not imported to APM.
Picklist IDMI_RTE_PICK_PCKLST_ID_CCharacter (50)This field is required and must be unique.

Value

SYCD_ID

Character (50)

This field is required.

Description

SYCD_DESC_TXCharacter (255)Provide a brief description.

Sequence

SYCD_SEQ_NBRNumericThis field is required and must be unique within Picklist Values.

Rounds Pro Step Template Data Loader Workbook

When importing data using the Rounds Pro Step Template Data Loader, the data loader will create one Step 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 Step Template record, the existing record will be updated. If the worksheet specifies values for Category or Picklist Value that do not already exist in the database for the specified type (numeric or character) of Step Reading the record will not be imported.

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

Table 1. Step Template Worksheet

Field Caption

Field ID

Data Type (Length)

Comments

Template Batch ID

RTE_PICK_PCKLST_IDCharacter (50)This field is required for identification during the data load process. It should match the ID of the Template Batch to which the Step Template will be linked. Use <Dummy ID> if you want to import standalone records not linked to a Template Batch. The value in this field is not imported to APM.
Template IDMI_RTE_PICK_PCKLST_ID_CCharacter (50)This field is required and must be unique. The value in this field is not imported to APM.

Step Type

MI_STP_TMP_STEP_TYPE_C

Character (50)

This field is required.

Reading Type

MI_ML_STP_TMP_READING_TYPE_CCharacter (50)This field is required. The valid values are:
  • Character
  • Numeric

Reading Picklist

MI_ML_STP_TMP_RDNG_PCKLST_CNumericThis field is required if selected as the Reading Type.
Reading Picklist ValueMI_ML_STP_TMP_RDNG_VALS_CCharacter (50)This field is required if Reading Picklist is selected.
Action PicklistMI_ML_STP_TMP_ACTN_PCKLST_CCharacter (50)This field is required if it is selected.
Action Picklist valueMI_ML_STP_TMP_ACTN_VALS_CCharacter (50)This field is required if Action Picklist is selected.
Site Reference NameMI_SITE_NAMECharacter (50)This field is required and must match an existing Site name.
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.
DescriptionMI_STP_TMP_DESC_CCharacter (255)Please provide a brief description.
LockedMI_STP_TMP_IS_LOCK_LBooleanThe default value for this field is False.
StatusMI_STP_TMP_STAT_CCharacter (50)The default value for this field is Active.
Is Shutdown RequiredMI_STP_TMP_REQ_EQUIP_SHUT_LLogicalThe default value for this field is False.
Unit of MeasureMI_STP_TMP_UOM_CCharacter (50)The field must be a valid value in the global Unit of Measure list. This field is disabled if the Reading Type field is Character.
FrequencyMI_STP_TMP_FREQ_C<need information>
Lower Level 1 ValueMI_ML_STP_TMP_LO_LVL1_ALRT_VALCharacter (50)Must use one of the Picklist Values specified for the Step Reading. This field is disabled if the Reading Type field is Numeric.
Lower Level 1 MessageMI_ML_STP_TMP_LO_LVL1_MSG_CCharacter (50)None
Lower Level 2 ValueMI_ML_STP_TMP_LO_LVL2_ALRT_VALCharacter (50)Must use one of the Picklist Values specified for the Step Reading. This field is disabled if the Reading Type field is Numeric.
Lower Level 2 MessageMI_ML_STP_TMP_LO_LVL2_MSG_CCharacter (50)None
Lower Level 3 ValueMI_ML_STP_TMP_LO_LVL3_ALRT_VALCharacter (50)Must use one of the Picklist Values specified for the Step Reading. This field is disabled if the Reading Type field is Numeric.
Lower Level 3 MessageMI_ML_STP_TMP_LO_LVL3_MSG_CCharacter (50)None
Upper Level 1 ValueMI_ML_STP_TMP_UP_LVL1_ALRT_VALCharacter (50)Must use one of the Picklist Values specified for the Step Reading. This field is disabled if the Reading Type field is Numeric.
Upper Level 1 MessageMI_ML_STP_TMP_UP_LVL1_MSG_CCharacter (50)None
Upper Level 2 ValueMI_ML_STP_TMP_UP_LVL2_ALRT_VALCharacter (50)Must use one of the Picklist Values specified for the Step Reading. This field is disabled if the Reading Type field is Numeric.
Upper Level 2 MessageMI_ML_STP_TMP_UP_LVL2_MSG_CCharacter (50)None
Upper Level 3 ValueMI_ML_STP_TMP_UP_LVL3_ALRT_VALCharacter (50)Must use one of the Picklist Values specified for the Step Reading. This field is disabled if the Reading Type field is Numeric.
Upper Level 3 MessageMI_ML_STP_TMP_UP_LVL3_MSG_CCharacter (50)None

Rounds Pro Step Conditionals Data Loader Workbook

When importing data using the Rounds Pro Step Conditionals Data Loader, the data loader will create one Step Conditional 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 Step Conditional record, the existing record will be updated.

Step Conditionals Worksheet (MI_STP)

Field Caption

Field ID

Data Type (Length)

Comments

Conditional Step Batch IDROUNDS_STP_COND_BATCH_IDCharacter (50)This field is required for identification during the data load process and must be unique. The value in this field is not imported to APM.
Step IDMI_STP_ID_CCharacter (50)A unique ID to identify a step.
Related Asset IDROUNDS_STP_REL_ASSET_IDCharacter (50)If this value matches an existing Asset ID, the Conditional Step will be linked to this asset.
Related Asset Family IDROUNDS_STP_REL_ASSET_FAMILYIDCharacter (50)If this value matches an existing Asset Family ID, the Conditional Step will be linked to this asset.
Step NameMI_STP_COND_STP_NAMECharacter (50)The name of the Primary step. This field is required and must match an existing Step name.
OperatorMI_STP_COND_OPERATORCharacter (50)This field specifies the operators that determine if the reading value entered for the primary Step meet the condition specified
Conditions Field ValueMI_STP_COND_FLD_VALCharacter (50)
Conditional Step NameMI_STP_COND_STP_NAME_CONDCharacter (50)The name of the Conditional step.
Conditional Step IndexMI_STP_COND_STP_INDEXNumeric (50)
Immediate Parent StepMI_STP_COND_STP_PARENTCharacter (50)This field contains the name of the current step in the route to which you want to add conditional steps.

Rounds Pro Routes Data Loader Workbook

When importing data using the Rounds Pro Routes Data Loader, you can
  • Create Route Master
  • Assign or unassign a Security User to the route
When importing data using the Rounds Pro Routes Data Loader, this data loader will create records as follows:
  • Route Master: The Route Master data loader does the following:
    • Creates a Route Master record or a route template.
    • Associates or links existing steps created in the step dataloader or in the step designer.
    • Assigns the route to a user.
    • Assigns the route to a team.
    • Validates that the users, teams and steps exist already when trying to link these items together.
    Note: Update a Route Master or delete a Route Master functionality is not available.

    The Route Master is created, associated to all the steps included and is assigned to all individuals and/or teams included.

    The Route Master 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.
  • Steps: The data loader generates one step record for the existing steps in each row in the Step worksheet. Using this Step data loader, the steps are associated to the required route. The data loader creates one step record for each row in the Step worksheet. Step must exist before including it in the Step data loader.

    The Next Date, Non-compliance Date, and the Non-compliance Next Check Date fields on the Step 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.

  • Human Resource:

    A relationship is created between the Route and the Human Resource record. Therefore, the Human Resource records must already exist before the data loader is used.

  • Teams:

    A relationship is created between the Route and the teams records. Therefore, the Teams records must already exist before the data loader is used.

Route Master Worksheet (MI_RTE_MST)

Field Caption

Field ID

Data Type (Length)

Comments

Route Batch IDRTE_MST_BATCH_IDCharacter (50)This field is required for identification during the data load process and must be unique. The value in this field is not imported to APM.
Route IDMI_RTE_MST_ID_CCharacter (50)This field is required and must be unique.
  • When you use the data loader to create Route, Measurement Locations and Checkpoint Task records.
  • When you use the data loader to create Route, Measurement Locations and Checkpoint Task records and assign the security users to Routes.
Note: This field is not required and may not be populated on this worksheet when you only assign or un-assign the security users to Routes.
Route DescriptionMI_RTE_MST_DESC_CCharacter (255)A description of the route.
ScheduleMI_RTE_MST_SCHED_CCharacter (50)This option generates the route instances from an associated route.
EAM Job KeyMI_RTE_MST_EAM_JOB_KEY_CCharacter (50)This field is used to link a Route Master to a given EAM Job identifier.
StatusMI_RTE_MST_STATUS_FCharacter (50) This field indicates if the route has been added to the My Routes list.
Is Schedule ActiveMI_RTE_MST_SCHEDULE_ISACTIVELogical

This field indicates if the Route Schedule is active.

A schedule can be created for a route in the Plan Routes section, after you select the schedule option.

Next Due DateMI_RTE_MST_NEXT_DUE_DTNumeric (50)The date when the next instance of the route will be generated if the route master corresponding to the instance has an active schedule.
FrequencyMI_RTE_MST_FREQ_NNumeric (50)This field displays a number for the duration at which a route schedule recurs.
Frequency IntervalMI_RTE_MST_FREQ_INTVAL_CCharacter (50)The time unit to measure the duration at which the route schedule recurs.
Floc Equipment Reference NumberMI_RTE_MST_FLOC_EQUI_REF_NUM_CCharacter (50)This field contains a unique alphanumeric number.
Floc Equipment StatusMI_RTE_MST_FLOC_EQUI_STS_CCharacter (50)This field displays the equipment status, Out of Service, Online, or Available.
Route Master Compliance Tracking FlagMI_RTE_MST_CMPLNC_ISACTIVEButtonEnable the button to activate compliance tracking for a schedule on a route.
Route Master Compliance Horizontal IntervalMI_RTE_MST_CMPLNC_HRZN_INTRVLCharacter (50)This field contains the time units to measure the frequency based on which the route instance must be completed.
Route Master Compliance Horizon UnitMI_RTE_MST_CMPLNC_HRZN_UNITNumeric(50)A number to specify the frequency in time units, such as days, weeks, and so on, based on which the route instance must be completed.
Basis For OccurrenceMI_RTE_MST_BASIS_FOR_OCCUR_CCharacter (50)You can select EAM or Schedule.

Steps Worksheet (MI_STP)

Field Caption

Field ID

Data Type (Length)

Comments

Route Batch IDRTE_MST_BATCH_IDCharacter (50)This field is required for identification during the data load process and must be unique. The value in this field is not imported to APM.
Route IDMI_RTE_MST_ID_CCharacter (50)This field is required and must be unique.
  • When you use the data loader to create Route, Measurement Locations and Checkpoint Task records.
  • When you use the data loader to create Route, Measurement Locations and Checkpoint Task records and assign the security users to Routes.
Note: This field is not required and may not be populated on this worksheet when you only assign or un-assign the security users to Routes.
Related Asset IDMI_STP_REL_ASSET_ID_CCharacter (50)If this value matches an existing Equipment ID (for Equipment) or Functional Location (for Functional Locations) the Checkpoint will be linked to this asset.
Step IDMI_STP_ID_CCharacter (50)A unique ID to identify a step.
Step TypeMI_STP_STEP_TYPE_CCharacter (50)The type of the step, whether a strategy step, template step, or a normal step. This is a required field.
Step Sequence NumberMI_STP_SEQ_NUMCharacter (50)A unique number to identify the step sequence.

Human Resource Worksheet (MI Human Resource)

Field Caption

Field ID

Data Type (Length)

Comments

Route Batch IDRTE_MST_BATCH_IDCharacter (50)This field is required for identification during the data load process and must be unique. The value in this field is not imported to APM.
Route IDMI_RTE_MST_ID_CCharacter (50)This field is required and must be unique.
  • When you use the data loader to create Route, Measurement Locations and Checkpoint Task records.
  • When you use the data loader to create Route, Measurement Locations and Checkpoint Task records and assign the security users to Routes.
Note: This field is not required and may not be populated on this worksheet when you only assign or un-assign the security users to Routes.
First NameMI_HR_FIRST_NAME_CHRCharacter (50)The first name of the human resource personnel.
Last NameMI_HR_LAST_NAME_CHRCharacter (50)The last name of the human resource personnel.

Teams Worksheet (MI_TEAMS)

Field Caption

Field ID

Data Type (Length)

Comments

Route Batch IDRTE_MST_BATCH_IDCharacter (50)This field is required for identification during the data load process and must be unique. The value in this field is not imported to APM.
Route IDMI_RTE_MST_ID_CCharacter (50)This field is required and must be unique.
  • When you use the data loader to create Route, Measurement Locations and Checkpoint Task records.
  • When you use the data loader to create Route, Measurement Locations and Checkpoint Task records and assign the security users to Routes.
Note: This field is not required and may not be populated on this worksheet when you only assign or un-assign the security users to Routes.
Team NameMI_TEAMS_TEAM_NAME_CHRCharacter (50)A unique ID to identify a team.

Rounds Pro Step Reading Data Loader Workbook

The Rounds Pro Step Readings Data Loader is used to import Step Readings data in to APM. The Data Loader has been created as an additional way to get the data into APM without disrupting the Rounds Pro data model. For example, all Step Readings must be associated with an instance, which helps track and report the readings. The same behaviour is retained when using the Data Loader; that is, all Step Readings are associated with an instance. However, in case a Step Reading does not contain an instance, the Data Loader will create an adhoc instance.
Note: You cannot upload Readings for a Route Instance that is closed or checked out by another user.

The Step Reading Worksheet (MI_STP_RDNG)

The following table provides information about the various columns in the Rounds Pro Step Readings Data Loader.
Field CaptionField IDData Type (Length)Comments
Reading Batch IDSTEP_READING_BATCH_IDCharacter (50)Enter a batch number, which is used to identify the batch. A value is required and must be unique. Data related to all the rows that contain the same batch number is imported in a single batch. However, the value in this field is not imported into APM.
Important: The maximum number of Readings supported per batch is 1000. You can upload more than 1000 records in the same Data Loader file, but provide a different batch ID for every 1000 records.
If you are loading Step Readings for an existing Route Instance, provide the same batch ID for all the Step Readings in the Route Instance.
Step NameMI_STP_ID_CCharacter (50)Identifies a step. A value is required and must be unique.
Asset IDROUNDS_STP_REL_ASSET_IDCharacter (50)Identifies the Asset ID associated with the step. The value in this field, combined with the value in the Step Name field, is used to uniquely identify a step under which the Step Reading is added. A value is required and must match the value in the Related Asset ID (MI_STP_REL_ASSET_ID_C) field in the Measurement Step family.
Reading Value CharacterMI_STP_RDNG_RDNG_VAL_CHR_CCharacter (2000)Identifies the Reading value if the Reading Type in the Parent Step is Picklist: Single Select and the Picklist Type is Character. In that case, any value entered in the Reading Value Numeric field is ignored.
Reading Value NumericMI_STP_RDNG_RDNG_VAL_NUM_NNumericIdentifies the Reading value if the Reading Type in the Parent Step is Picklist: Single Select and the Picklist Type is Numeric. In that case, any value entered in the Reading Value Character field is ignored.
Reading Taken DateMI_STP_RDNG_RDNG_TKN_DT_DDateTmeIdentifies the date and time at which the Reading was taken. A value is required. Enter a value in the following format: yyyy-MM-dd HH:mm:ss.
Depending on the time zone of the user who uploads the Data Loader workbook, the value in this field is converted into the UTC format.
Note: The user ID of the user who uploads the Data Loader workbok is stored in the Reading Taken By field.
CommentMI_STP_RDNG_CMMNTS_CCharacter (255)None
Action TakenMI_STP_RDNG_ACTN_TKN_CCharacter (100)Enter a value that matches one of the values in the Actions section of the Picklist Management page.
Route Instance NameMI_RTE_INST_NAME_CCharacter (200)Enter the name of an existing Route Instance in which you want to create the Step Reading. If you leave this field blank, an adhoc Route Instance is created for every 1000 Step Readings. The adhoc Route Instance Name uses the following format: Readings-Data Loader <date on which the data has been uploaded in UTC format>.

About the Rounds Pro Data Loaders Load Verification

After each worksheet is loaded:
  • Review the data loader log to identify any errors or warnings
  • Update the data loader workbook to correct any errors, and then re-import
  • Repeat until the import of each record is error free
Note: It is recommended that a query is executed in APM on the relevant family to ensure that records have been created and populated as expected. Imported records should also be spot checked in the Rounds Pro Manager and the Rounds Designer to verify that the expected relationships have been created.

To perform the load verification of the loaded data loaders, log in to the Rounds Pro as a Rounds Pro Administrator user.

In the Applications menu, select Health, and then select Rounds Pro Manager. The Rounds Pro Manager page appears. From the Rounds Pro Manager page, access the Rounds Designer and the Route details page to verify the data loaded using the data loaders.

Note: The data loaders will load new data records only. Any changes to the records that are loaded can be made through the Rounds Pro Manager only.

Data Loader Load Verification for Rounds Designer

Access the Picklist Management page and verify that the loaded data using Data loader appears as expected with all the records imported and that the values can be edited or new values added to the both Action and Reading Picklist type. Also verify that you can create new character (and numeric, if applicable) Picklists.

Access the Step Template page and verify that the loaded Step Templates list appear as expected and the Step template records displays all the details and can be edited as required.

Verify that the Health Indicator are created for the given levels as expected.

Access a Step and verify that the loaded Steps list appear as expected in the asset hierarchy.

Select a Step to view the step details and verify that the Step records displays all the imported values and can be edited as required.

Access a Step that was imported, and verify that the Health Indicators record is linked to the Step as expected.

Access a Step Condition and verify that all the data is loaded from the data loader for that conditional step.

Data Loader Load Verification for Route Masters

Access the Route Masters page.

Verify that the loaded Route Masters page appears as expected with all the data for the Routes, associated steps and is assigned to all individuals and/or teams included in the Data Loader.

In the Route Masters page, select a rout from the ROUTE NAME column, verify the following list for the selected route:
  • The existing assigned assets and steps appear as expected.
  • The existing steps created in the step data loader or in the step designer can be assigned to the route.
  • The assigned Assets and Steps can be reordered.
  • The asset details appearing as imported.
  • The Route name and route description appear as imported.
  • The route schedules have been populated.
  • The route has been to a user or team.
  • The Basic for Occurrence (EAM and Schedule) details are loaded as expected with all the required data.
  • The Route Preview details are appearing as expected with the loaded data.
  • It can be assigned to a new User or Team or remove the assigned team or user.

Verify that the updated changes in the routes are appearing as expected in the Route Masters page.