Equipment and Functional Location Data Loader

About the Equipment and Functional Location Data Loader

The Equipment and Functional Location Data Loader allows a user to import data from an Excel workbook. The user is able to build out the asset hierarchy based on structure defined in the Excel workbook.

About the Equipment and Functional Location Data Loaders Data Model

The data for Equipment and Functional Location is loaded from a single Excel workbook containing multiple worksheets. This includes Functional Locations and Equipment.

The Functional Location can be linked to a parent Functional Location using the relationship Functional Location Has Functional Location(s).

The Equipment and Functional Location Data Loader General Loading Strategy

This topic describes any prerequisites to loading the data and the order in which the data will be loaded for Equipment and Functional Location Data Loaders.

Before You Begin

Equipment Taxonomy data must be present prior to loading Equipment and Functional Location data.

About This Task

Note: Before reading this section, refer to the Data Model section.

The Equipment and Functional Location data load must be performed in a specific sequence to successfully populate fields, create records, and link them to the predecessor and/or successor records.

Procedure

Create the Functional Location.
Create the Equipment.
The Equipment and Functional Location Data Loaders have the listed limitations.
- The Functional Location hierarchy can be constructed in the loader by assigning the parent Functional Location (superseding Functional Location) to the child record.
- CMMS-ID is a required field that is intended to identify the original source of the data and part of the key value.
- After loading Equipment records into APM with a specific site reference, you cannot update the Equipment records to have global site references by reimporting the workbook with the site reference column updated to global on the Equipment worksheet. To update Equipment records to have global site references, you must update the predecessor Functional Locations with the site reference value *Global* on the worksheet.
  In APM, records inherit their site references from their predecessor records. Additionally, when the Equipment and Functional Location Data Loader is run, it loads Equipment records first, and then loads Functional Location records. Therefore, to change the equipment record’s site reference to global, you would need to re-import the workbook with the Functional Location record indicating a *Global* site reference.

About the Equipment and Functional Location Data Loaders Workbook Layout and Use

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

In order to import data using the Equipment and Functional Location Data Loaders, APM provides an Excel workbook, Equipment and Functional Location.xlsx, which supports baseline data loading of equipment and functional locations in APM. This workbook must be used to perform the data load.

The master Excel workbook contains one worksheet for each node that will be populated in the data model.

The following table lists the worksheets that are included in the Equipment and Functional Location Data Loaders workbook.

Note: Worksheets in the workbook not being used may be left blank, but should not be deleted from the workbook.


Worksheet	Description
Configuration	The Configuration worksheet is needed to describe the type of data that you will be loading and how that data should be handled during the data load.
Equipment	This worksheet is used to specify data for import to the Equipment family.
EquipmentToSuperiorEquipment	This worksheet is used to link Equipment to Superior Equipment records.
FunctionalLocations	This worksheet is used to specify data for import to the Functional Location family.
FuncLocsToEquipment	This worksheet is used to link existing Functional Location records to existing Equipment records.
FuncLocsToSuperiorFuncLocs	This worksheet is used to link existing Functional Locations to superior Functional Locations.

Configuration Worksheet

The Configuration worksheet tells the Data Loader what types of data are being loaded and how the data is to be loaded, and is standard for all data loads regardless of the type of data that you are loading. The following table outlines the options that are valid or the values that are expected in each of the columns on the Configuration worksheet.


Field Caption	Field ID	Data Type (Length)	Comments
Number of Rows to Chunk	OPTION_NUMBER_ROWS_TO_CHUNK	Character	Option to break-up or chunk data.
Load Data From Worksheet	LOAD_DATA_WORKSHEET	Boolean	Identifies if data from the corresponding worksheet identified in the Data Worksheet ID column will be loaded or not. True: The corresponding worksheet will be processed. False: The corresponding worksheet will not be processed.
Data Worksheet ID	DATA_WORKSHEET_ID	Character	This column contains the name of the <data> worksheet where the actual data is located. It needs to have the same name as the <data> worksheet in the data loader workbook.
Batch Size	BATCH_SIZE	Character	Modifying this field is required to determine the number of records processed in each batch. Enter the batch size you want, and the Data Loader will process that many records per batch. For example, if you want to use a batch size of 100, enter 100, and the data loader will process 100 records per batch. Note: The recommended batch size is 100. If the Batch Size column is removed from the source workbook, the data loader will default to a batch size of 100. In addition to processing the data in batches, the log file reports progress by batch.
Primary Family ID	PRIMARY_FAMILY_ID	Character	Depending on the type of data that you are working with, this will contain the Relationship Family ID or the Entity Family ID. You can also allow the data in source file to determine the Family ID by encapsulating the Field ID that contains the Family ID data in brackets (<>). For example, if in the <data> worksheet there is a column with an ID of PRIMARY_FAMILY_ID, where each row contains the corresponding Family ID, then in this column you should put the value of <PRIMARY_FAMILY_ID>. If the Family ID in the Meridium, Inc. metadata contains spaces, then you have to use this feature.
Primary Family Key Fields	PRIMARY_FAMILY_KEY_FIELDS	Character	This column contains the Field IDs associated with the Primary Family that are used to uniquely identify a record. If more than one field is to be used, then each Field ID needs to be separated by a \| (Pipe) character. In the case where you are loading data into a relationship, if no keys fields exist or are used, use the <none> constant. If the Primary Action is ACTION_INSERTONLY, then no key fields need to be specified, so you can use the <none> constant.
Family Type	FAMILY_TYPE	Character	The value is this column should be Entity or Relationship depending on the type of data that is being loaded.
Predecessor Family ID	PRED_FAMILY_ID	Character	When the Family Type is Relationship, this column will contain the value of the Entity Family ID that is the predecessor in the relationship. Otherwise, it should contain the <none> constant. You can also use the data in each of the rows to determine the Predecessor Family ID.
Predecessor Family Key Fields	PRED_FAMILY_KEY_FIELDS	Character	This column contains the Field ID or IDs associated with the Predecessor Family that are used to uniquely identify the predecessor record. If more than one field is to be used, then each Field ID needs to be separated by a \| (Pipe) character. If the Predecessor Action is ACTION_INSERTONLY, then no key fields need to be specified, so you can use the <none> constant.
Successor Family ID	SUCC_FAMILY_ID	Character	When the Family Type is Relationship, this column will contain the value of the Entity Family ID that is the successor in the relationship. Otherwise, it should contain the <none> constant. You can also use the data in each of the rows to determine the Successor Family ID.
Successor Family Key Fields	SUCC_FAMILY_KEY_FIELDS	Character	This column contains the Field ID or IDs associated with the Successor Family that are used to uniquely identify the successor record. If more than one field is to be used, then each Field ID needs to be separated by a \| (Pipe) character. If the Successor Action is ACTION_INSERTONLY, then no key fields need to be specified, so you can use the <none> constant.
Primary Action	PRIMARY_ACTION	Character	The value in this column will determine the action that will be applied to the Primary Family records. If the Family Type is Entity, then the possible values are: ACTION_INSERTONLY ACTION_INSERTUPDATE ACTION_UPDATEONLY ACTION_DELETE ACTION_PURGE Deleting a record and purging a record will both delete the current record, the difference being that the purge action will delete the record and all of the links or relationships tied to that record. The delete action will simply attempt to delete the record, and if it is related to another record, the delete will fail. If The Family Type is Relationship, then the possible values are: ACTION_INSERTONLY ACTION_INSERTUPDATE ACTION_UPDATEONLY ACTION_DELETE
Predecessor Action	PRED_ACTION	Character	The value in this column will determine the action that will be applied to the Predecessor Family records. The possible values are: ACTION_INSERTONLY ACTION_INSERTUPDATE ACTION_UPDATEONLY ACTION_DELETE ACTION_PURGE ACTION_LOCATE If The Family Type is Entity, then the values need to be ACTION_NONE
Successor Action	SUCC_ACTION	Character	The value in this column will determine the action that will be applied to the Successor Family records. The possible values are: ACTION_INSERTONLY ACTION_INSERTUPDATE ACTION_UPDATEONLY ACTION_DELETE ACTION_PURGE ACTION_LOCATE If The Family Type is Entity, then the values need to be ACTION_NONE
Insert with Null Values?	OPTION_INSERT_ON_NULL	Boolean	When setting field values on a new record, if a value coming across is NULL, the field values will be set to NULL if this option is set to True.
Update with Null Values?	OPTION_UPDATE_ON_NULL	Boolean	When setting field values on an existing record, if a value coming across is NULL, the field values will be set to NULL if this option is set to True.
Replace an Existing Link?	OPTION_REPLACE_EXISTING_LINK	Boolean	The Replace Existing Relationship option is used to determine how a relationship is to be maintained by its cardinality definition. For example, the relationship Location Contains Asset that is defined in the Configuration Manager. It has a cardinality defined as Zero or One to Zero or One, has a Location LP-2300, and contains the Asset P-2300. If, in the data load, you assign the Asset P-5000 to be contained in the Location LP-2300, and you have set the Replace Existing Link property to True, then the data loader will link P-5000 to LP-2300 and unlink P-2300 from LP-2300. This assumes that P-5000 is not currently linked to another location. The same is true for a relationship that is defined as Zero or One to Zero or Many, or Zero or Many to Zero or One.
Allow Change of Family?	OPTION_ALLOW_CHANGE_OF_FAMILY	Boolean	Allows the data loader to move an entity from one family to another. For example, this would allow an entity that is currently assigned to the Centrifugal Pump family to be moved to the Reciprocating Pump family. All relationships will be maintained as long as the family to which the entity is being moved allows the same relationships. Note: Because of the extra processing required, by selecting this option, the interface performance will decrease.

Equipment Worksheet

On the Equipment worksheet, you will specify Equipment that you want to load into APM.

Note: Each row in this worksheet represents a unique asset. You should not include the same asset more than once.


Field Caption	Field Column Name	Data Type (Length)	Comment
Maintenance Plant	MI_EQUIP000_MAINT_PLANT_C	Character (50)	This field is required, and is used to group or batch the equipment records.
Equipment ID	MI_EQUIP000_EQUIP_ID_C	Character (225)	This is a key field.
Equipment Technical Number	MI_EQUIP000_EQUIP_TECH_NBR_C	Character (255)	None
CMMS System	MI_EQUIP000_SAP_SYSTEM_C	Character (255)	This is a key field.
Site Reference Name	MI_SITE_NAME	Character (255)	Important: Site Reference records must preexist in APM. The data loader does not create Site Reference records, but simply provides foreign key data in the asset records, as determined in the source workbook. If the site reference record does not preexist, then you will receive an error. Enter the site name to designate which site the Equipment record, once loaded into APM, will be filtered by. -or- Enter Global to indicate a that the site reference should be left global. Meaning that it will not be filtered by site in APM. Note: Only super users are permitted to update Site Reference records.
Equipment Short Description	MI_EQUIP000_EQUIP_SHRT_DESC_C	Character (255)	None
Equipment Long Description	MI_EQUIP000_EQUIP_LNG_DESC_T	Text	None
Object Type (Taxonomy Mapping Value)	MI_EQUIP000_OBJ_TYP_C	Character (50)	None
Equipment System Status	MI_EQUIP000_SYS_ST_C	Character (255)	None
Manufacturer	MI_EQUIP000_MFR_C	Character (255)	None
Model Number	MI_EQUIP000_MOD_NO_C	Character (255)	None
Equipment Serial Number	MI_EQUIP000_SN_C	Character (255)	None
Active	'MI_EQUIP000_ACTIVE_F	Logical	None
Equipment uniquely identified by SAP System - Equipment ID	MI_EQUIP000_UNIQUE_ID_C	Character (550)	This field uniquely identifies the equipment using the format <CMMS System> - <Functional Location ID>. This value allows the Data Loader to associate records between the Meridium database and the Predix database. Note: You should not use this field if you have an on-premises implementation of APM.

FunctionalLocations Worksheet

On the FunctionalLocations worksheet, you enter information for Functional Locations and the Functional Location hierarchy.

Note: Each row in this worksheet represents a unique asset. You should not include the same asset more than once.


Field Caption	Field ID	Data Type (Length)	Comments
Maintenance Plant	MI_FNCLOC00_MAINT_PLNT_C	Character (50)	None
Functional Location Internal ID	MI_FNCLOC00_INTERNAL_ID_C	Character (30)	This is a key field.
Functional Location	MI_FNCLOC00_FNC_LOC_C	Character (50)	None
CMMS System	MI_FNCLOC00_SAP_SYSTEM_C	Character (255)	This is a key field.
Site Reference Name	MI_SITE_NAME	Character (255)	Important: Site Reference records must preexist in APM. The data loader does not create Site Reference records, but simply provides foreign key data in the asset records, as determined in the source workbook. If the site reference record does not preexist, then you will receive an error. Enter the site name to designate the site by which the Functional Location record, once loaded into APM, will be filtered. -or- Enter Global to indicate a that the site reference should be left global. Meaning that it will not be filtered by site in APM. Note: Only Super Users are permitted to update Site Reference records.
Functional Location Description	MI_FNCLOC00_FNC_LOC_DESC_C	Character (255)	None
Functional Location Long Description	MI_FNCLOC00_FNC_LOC_LNG_DESC_C	Text	None
Object Type (Taxonomy Mapping Value)	MI_FNCLOC00_OBJ_TYP_C	Character (50)	None
System Status	MI_FNCLOC00_SYS_STATUS_C	Character (255)	None
Is a Process Unit?	SC_FNCLOC00_IS_A_PROCE_UNIT_L	Logical	None
Functional Location uniquely identified by SAP System - Functional Location Internal ID	MI_FNCLOC00_UNIQUE_ID_C	Character (550)	This field uniquely identifies the functional location using the format <CMMS System> - <Functional Location ID>. This value allows the Data Loader to associate records between the Meridium database and the Predix database. Note: You should not use this field if you have an on-premises implementation of APM.

FuncLocsToEquipment


Field Caption	Field ID	Data Type (Length)	Comments
Maintenance Plant	MI_FNCLOC00_MAINT_PLNT_C	Character (50)	None
Functional Location Internal ID	MI_FNCLOC00_INTERNAL_ID_C	Character (30)	This is a key field.
Functional Location	MI_FNCLOC00\|MI_FNCLOC00_FNC_LOC_C	Character (50)	None
CMMS System	MI_FNCLOC00_SAP_SYSTEM_C	Character (255)	This is a key field. Functional Location CMMS System.
Functional Location uniquely identified by System - Functional Location ID	MI_FNCLOC00\|MI_FNCLOC00_UNIQUE_ID_C	Character (550)	This field uniquely identifies the functional location using the format <CMMS System> - <Functional Location ID>. This value allows the Data Loader to associate records between the Meridium database and the Predix database. Note: You should not use this field if you have an on-premises implementation of APM.
Equipment ID	MI_EQUIP000_EQUIP_ID_C	Character (225)	This is a key field.
CMMS System	MI_EQUIP000_SAP_SYSTEM_C	Character (255)	This is a key field. Equipment CMMS System.
Equipment uniquely identified by System - Equipment ID	MI_EQUIP000\|MI_EQUIP000_UNIQUE_ID_C	Character (550)	This field uniquely identifies the equipment using the format <CMMS System> - <Equipment ID>. This value allows the Data Loader to associate records between the Meridium database and the Predix database. Note: You should not use this field if you have an on-premises implementation of APM.

FuncLocsToSuperiorFuncLocs


Field Caption	Field ID	Data Type (Length)	Comments
Maintenance Plant	<PRED_FAMILY_ID>\|MI_FNCLOC00_MAINT_PLNT_C	Character (50)	None
Functional Location Internal ID	<PRED_FAMILY_ID>\|MI_FNCLOC00_INTERNAL_ID_C	Character (30)	This is a key field.
Functional Location	<PRED_FAMILY_ID>\|MI_FNCLOC00_FNC_LOC_C	Character (50)	None
CMMS System	<PRED_FAMILY_ID>\|MI_FNCLOC00_SAP_SYSTEM_C	Character (255)	This is a key field. Functional Location CMMS System.
Functional Location uniquely identified by System - Functional Location ID	<PRED_FAMILY_ID>\|MI_FNCLOC00_UNIQUE_ID_C	Character (550)	This field uniquely identifies the functional location using the format <CMMS System> - <Functional Location ID>. This value allows the Data Loader to associate records between the Meridium database and the Predix database. Note: You should not use this field if you have an on-premises implementation of APM.
Predecessor Family ID	PRED_FAMILY_ID	Character (255)	None
Functional Location Internal ID	<SUCC_FAMILY_ID>\|MI_FNCLOC00_INTERNAL_ID_C	Character (30)	This is a key field.
Functional Location	<SUCC_FAMILY_ID>\|MI_FNCLOC00_FNC_LOC_C	Character (50)	None
CMMS System	MI_EQUIP000_SAP_SYSTEM_C	Character (255)	This is a key field. Equipment CMMS System.
Functional Location uniquely identified by System - Functional Location ID	<SUCC_FAMILY_ID>\|MI_FNCLOC00_UNIQUE_ID_C	Character (550)	This field uniquely identifies the functional location using the format <CMMS System> - <Functional Location ID>. This value allows the Data Loader to associate records between the Meridium database and the Predix database. Note: You should not use this field if you have an on-premises implementation of APM.
Successor Family ID	SUCC_FAMILY_ID	Character (255)	This is a key field.

EquipmentToSuperiorEquipment Worksheet

On the EquipmentToSuperiorEquipment worksheet, you specify the hierarchy between an Equipment and its Superior Equipment. This generates the relationships between the entities in the database.


Field Caption	Field ID	Data Type (Length)	Comments
Maintenance Plant	<PRED_FAMILY_ID>\|MI_EQUIP000_MAINT_PLANT_C	Character (50)	This value identifies the plant responsible for maintenance. This field is not required to contain a value, but entering a value is recommended as it could be useful for searches or data analysis.
CMMS System	<PRED_FAMILY_ID>\|MI_EQUIP000_SAP_SYSTEM_C	Character (255)	This value identifies the parent family and EAM system as defined in APM.
Equipment ID	<PRED_FAMILY_ID>\|MI_EQUIP000_EQUIP_ID_C	Character (255)	This value identifies the parent family and equipment ID as defined in APM.
Predecessor Equipment Unique ID	<PRED_FAMILY_ID>\|MI_EQUIP000_UNIQUE_ID_C	Character (550)	This field uniquely identifies the equipment using the format <CMMS System> - <Equipment ID>.
Predecessor Family ID	PRED_FAMILY_ID	Character (255)	This value identifies the parent family as defined in APM.
CMMS System	<SUCC_FAMILY_ID>\|MI_EQUIP000_SAP_SYSTEM_C	Character (255)	This value identifies the child family and EAM system as defined in APM.
Equipment ID	<SUCC_FAMILY_ID>\|MI_EQUIP000_EQUIP_ID_C	Character (255)	This value identifies the child family and equipment ID as defined in APM.
Successor Equipment Unique ID	<SUCC_FAMILY_ID>\|MI_EQUIP000_UNIQUE_ID_C	Character (550)	This field uniquely identifies the equipment using the format <CMMS System> - <Equipment ID>.
Successor Family ID	SUCC_FAMILY_ID	Character (255)	This value identifies the child family as defined in APM. This is a key field.

The value of this field has the format YYYY-MO-DDTHH:MN:SS.ttt+OOOO, where:

YYYY is the 4-digit year.
MO is the 2-digit month.
DD is the 2-digit day.
HH is the 2-digit hour in 24-hour time.
MN is the 2-digit minute.
SS is the 2-digit second.
ttt is the 3 digit thousandths of a second.
OOOO is the 4-digit offset from UTC.

The Configuration worksheet is needed to describe the type of data that you will be loading and how that data should be handled during the data load.


Field Caption	Field ID	Data Type (Length)	Comments
Load Data From Worksheet	LOAD_DATA_WORKSHEET	Boolean	Identifies if data from the corresponding worksheet identified in the Data Worksheet ID column will be loaded or not. True: the corresponding worksheet will be processed. False: The corresponding worksheet will not be loaded into the Meridium database.
Data Worksheet ID	DATA_WORKSHEET_ID	Character	This column contains the name of the <data> worksheet where the actual data is located. It needs to have the same name as the <data> worksheet in the data loader workbook.
Batch Size	BATCH_SIZE	Character	Modifying this field is required to determine the number of records processed in each batch. Enter the batch size you want, and the Data Loader will process that many records per batch. For example, if you want to use a batch size of 100, enter 100, and the data loader will process 100 records per batch. Note: The recommended batch size is 100. If the Batch Size column is removed from the source workbook, the data loader will default to a batch size of 100. In addition to processing the data in batches, the log file reports progress by batch.