How XML data is compared using the Key Fields

Importing data into the Client Tool is one of the most common and important functions that you will perform. It is important to understand how data are handled during the import. In the first post in this series, key fields were explained. The key fields are important because they identify unique pieces of data for a data type. For example, the key fields of Unit ID and System ID identify a unique system associated with a monitoring plan.

Monitoring plan data which are imported into the Client Tool are merged with the existing monitoring plan data. This is done to insure that the monitoring plan history is maintained.

How the data are merged is determined by a comparison of the data between the data in the XML file and the data in the Client Tool database. The comparison is performed on the key fields for each type of monitoring plan data. The data types and corresponding key fields are listed below in the table. Some data types have more than one set of key fields which are used for comparison. This allows for comparison of records that are active (EndDate is null) and records that are no longer active (EndData is not null).

There are two results that can occur based on the comparison of the key fields.

1. If the key fields match between the data in the XML file and the data in the Client Tool, the Client Tool proceeds to update any other data fields associated with that data type. For example, if the MonitoringLoadData key fields match, but the MaximumLoadValue is different, the Client Tool will update the MaximumLoadValue in the Client Tool with the value from the XML file.
2. If a match on key fields cannot be made, the Client Tool will insert a new record based on the data in the XML file. For example, if the MonitoringLoadData in the XML does not match any of the MonitoringLoadData in the Client Tool, the Client Tool will add a new record using all of the values from the MonitoringLoadData in the XML file.

Because the data are merged, monitoring plan data are never deleted during an import. If there are data (based on key fields) which are in the Client Tool but not in the XML file, the data in the Client Tool will not be deleted or altered in any way.

Next: How Merging Monitoring Plan Data Affects Correcting Monitoring Plan Data

Labels: Importing Data

Understanding Key Fields

One area of possible confusion in the ECMPS Client Tool is how monitoring plan data are handled when the data from an XML file are imported into the Client Tool. This does not need to be an area of confusion, and to that end, there will be a series of posts to explain how data are imported into the ECMPS Client Tool. The first parts of the series will focus on how monitoring plan data are imported.

One of the reasons for the possible confusion about import is that the Client Tool imports data differently than MDC. In MDC, for a given ORIS code, year, and quarter, the data in the EDR which were imported into MDC, replaced any existing data for the ORIS code, year, and quarter in MDC. As we will see, this is not the way that the ECMPS Client Tool imports monitoring plan data.

The most important thing to keep in mind about importing monitoring plan data into the ECMPS Client Tool is that data are always merged. In other words, the monitoring plan data which are in the XML file are merged with the monitoring plan data which are already in the Client Tool. Instead of simply replacing all of the data in the Client Tool with all of the data in the XML file, the data in the import file are compared to the data in the Client Tool to determine what action should be taken. The action might be to update the data already in the Client Tool or it might be to add new data to the Client Tool. The action is never to delete data in the Client Tool.

The comparison of the data is done by attempting to match data in the XML file with data in the Client Tool based on what are known as the key fields. Key fields are simply the pieces of data for a particular type of data that can be used to identify the data as unique. For example, for monitoring system data, the key fields are the monitoring location (unit ID, stack ID, pipe ID) and the system ID. This combination of key fields produces a unique record in a database because for a given monitoring location, there can only be one system with a particular system ID.

Here is this example in concrete terms. If the unit ID is U1 and the system ID is S01, the unique monitoring system key fields are U1 + S01. This combination of key fields cannot be repeated for this particular unit. Another system, S02, for this unit would have key fields U1 + S02, but you could never have another record with key fields of U1 + S01. If you attempted to import an XML file which had two system records with the key fields of U1 + S01, the import process would throw an error.

Next: Comparing XML data to data in the Client Tool using the key fields

Labels: Importing Data