Check and Import EDDs – Professional

<< Click to Display Table of Contents >>

EQuIS 6.6 Training Exercises  >>  EQuIS Data Processor >

Check and Import EDDs – Professional

Overview

EQuIS Professional EDP Workflow

Importing any EDD into EQuIS via Professional EDP is completed using the following five-step procedure:

 

Check the EDD

1. Launch EDP and load an EDD format

2. Load the EDD

3. Resolve data errors (if any)

Import the EDD

4. Create the package

5. Commit the package to the EQuIS database

 

Exercise Scenario

One of our laboratories has completed their analysis of some samples and has produced an EDD of the results from their Laboratory Information Management System (LIMS). They have sent the EDD to us to check and import into EQuIS using the EQEDD EDD format. The laboratory is new to EQuIS and they are not sure how to resolve the errors in the EDD.  We have agreed to troubleshoot the errors and then manually upload the EDD into EQuIS.

 

Opera Glasses

EQuIS power users check EDDs for data quality and then import the data into the EQuIS database.

 

Goal

Exercise Objectives

Launch EQuIS Professional EDP and Load an EDD Format

Create Blank EDDs and EDD Descriptions

Upload and View EDDs in EQuIS Professional EDP

Resolve Data Errors in EDDs in EQuIS Professional EDP

Import EDDs into EQuIS via EQuIS Professional EDP

 

Optional Exercises

Add Reference Values to EQuIS

Remove Data from EQuIS

 

Skills, Software and Permissions Required

EQuIS Professional v6.6 or higher must be installed on the workstation

.NET Framework 4.0 or higher must be installed on the workstation

Read/write permissions are required for the desired facilities

 

Goal

Objective: Launch EQuIS Professional EDP and Load an EDD Format

Tasks

Launch EQuIS Professional EDP

Load an EDD Format

 

Launch EQuIS Professional EDP

1.Open the Windows Start menu and choose All Programs> EarthSoft> EQuIS Professional.  

2.Expand the desired connection under My Connections.

For this exercise, select the Training connection.

3.Expand the desired EQuIS database.

For this exercise, expand the SummerValley database.

4.Single-click the Facility name from the facility list that appears (expand Facility Group folders as needed) and double-click on the desired License Library to log into the facility.

For this exercise, expand the North America folder and double-click on the Gold King Mine facility.

5.The main EQuIS Professional window opens.

pro-main-window

6.Select EDP pro-edp-button from the Import group of the Home ribbon.

7.EQuIS Professional EDP opens in a separate window.

EDP_Window

 

Idea

Maximize the EDP window to view all icon labels on the Home ribbon. When the EDP window is not maximized, hover the mouse over icons to view labels.

 

 

Load an EDD Format

The EDD format file is the essence of data checking in EDP and EQuIS. Structured in XML, the EDD format file set contains the definitions and restrictions for each individual field in available data tables. The format files control data checks, such as range checking, reference values, formatting and enumerations. Data providers may submit data for EQuIS in any one of a number of standard or custom EDD formats. It is essential that the correct EDD format file is applied for data to be successfully uploaded into the EQuIS database.

 

1.Select Format edp-format-button from the Open group of the EDP Home ribbon.

2.Browse to the desired Format directory and select the Format to open. Standard EarthSoft formats are typically located in the EQuIS install folder, C:\Program Files\EarthSoft\EQuIS\Formats\EarthSoft.

For this exercise, browse to the EQEDD Format directory in the EQuIS install folder, typically
C:\Program Files\EarthSoft\EQuIS\Formats\EarthSoft and open "EQEDD.zip".

Goal

Objective: Create Blank EDDs and EDD Descriptions

The Blank EDD and EDD Description tools facilitate data collection and processing. The Blank EDD tool creates an empty Excel template that follows the format file’s specific layout for data entry. The EDD Description tool defines the specifications of the format. These tools are useful references and may be incorporated into broader Standard Operating Procedures (SOPs). See this article for additional information on both tools.

To ensure their LIMS correctly generates EDDs in the EQEDD format, the data provider may request the “specifications” of the EDD format. The data provider may also need the reference value file (.rvf) to generate your EDD. This can be generated from the Professional tab of EDP for the loaded format file.

Tasks

Create a Blank EDD Template

Create an EDD Description File

 

Create a Blank EDD Template

1.Select Blank EDD edp-blank-edd-button from the Tools group of the Home ribbon.

 

Idea

Selecting the arrow next to the Blank EDD edp-blank-edd-button icon will provide the ability to export a blank EDD with either Valid Values or Parent Values (or both) available as drop-down lists.

 

2.In the Save dialog, name the Blank EDD file as desired, choose the desired location, and select Save.

 

Idea

By default, the Blank EDD will save to the same directory as the EDD format. Browse to a different location (e.g., C:\Documents\My EQuIS Work) if computer permissions restrict saving to the default directory. Hold the Shift key to save to the temp directory.

 

A Microsoft Excel file is created with the format columns and field types, along with the color-coding as displayed in EDP to indicate required fields, primary keys, and look-up values. Separate worksheets are named for each section of the format file. The blank EDD file may be loaded into EDP for data checking after data has been entered.

edp-blank-edd

 

Create an EDD Description File

1.Return to EQuIS Professional EDP.

2.Select EDD Description edp-blank-edd-button from the Tools group of the Home ribbon.

3.In the Save dialog box, name the EDD Description file as desired and then select Save.

 

Idea

By default, the EDD Description will be named after the EDD format (for example, "EQEDD.xls"), similar to the Blank EDD. Change the filename of the EDD Description to avoid saving over the Blank EDD file.

 

Information about the currently loaded format file (EQEDD) is exported into a Microsoft Excel spreadsheet, including whether a field is a primary key (i.e., required), data type, related look-up tables, and EQuIS Schema mapping. The description includes a separate worksheet detailing each section of the EDD format file.  

 

edp-edd-description

 

 

Goal

Objective: Upload and View EDDs

EQuIS Professional EDP checks a populated EDD for errors and highlights errors with different colors to signify the error type, facilitating error correction. Errors may be corrected easily, directly within EQuIS Professional EDP.

 

EDD errors often occur because of issues with formatting, logic, or terminology. Some common EDD errors include:

 

Error Color/Type

Reason for Error

Missing Required Field

A cell is not populated and the field is required to be populated by the format.

Value Not Found in List

The contents of the cell should match one of the available options in the drop-down list for that cell and they currently do not.  (Note the drop-down list comes from an enumeration list.)

Value Exceeds Field Length

The number of characters in the cell exceed the number allowed for that field in the format.

Orphan Row

The contents of the cell should match the contents of the parent cell and currently do not.

Reference Value Not Found

The contents of the cell should match one of the available options in the drop-down list for that cell and they currently do not. (Note the drop-down list comes from a look-up table.)

 

Tasks

Open an EDD in EQuIS Professional EDP

Create an EDP Error Log Summary and Report Errors to the Data Provider

 

Open an EDD in EQuIS Professional EDP

For this scenario, the laboratory (data provider) has provided data in an Excel spreadsheet in the EQEDD format. They need some assistance troubleshooting and resolving the errors in the EDD.

1.Select EDD edp-edd-button from the Open group of the EDP Home ribbon.

2.Browse to the desired EDD file (typically an .xls, .zip or multiple .txt files) and double-click on the file to open it.

For this exercise, browse to the Training Materials directory and open the "Tutorial_EDD_Errors.Gold_King_Mine.EQEDD.xls" file.

3.Data will load into one or more sections of the format, depending on which sections were populated in the EDD file. Populated section names will be Red or Green, depending on if they contain errors or are error-free, respectively. Empty sections of the format will remain black.

For this exercise, data will load into the Lab sections of the format (LabSample_v1, TestResultsQC_v1, and TestBatch_v1), with the LabSample_v1 and TestResultsQC_v1 section names highlighted in Red, indicating errors. All Field sections are empty.

4.Single-click on the top-most Red section (if any) to view the data in that section. Since data in the various tables are related, it is important to resolve data errors in a top-down manner.

For this exercise, single-click on the LabSample_v1 section to view the data in that section.

edp-tutorial-edd-errors

 

Idea

The default appearance of the Column Headers, Errors, and Informational messages can be modified to enhance the user experience. This functionality can be accessed by clicking on the EDP Application Menu pro-edp-button then selecting the Options button and expanding the Appearance menu.

 

 

Create an EDP Error Log Summary

It is easy to generate a summary list of data errors and reference value errors to assist in troubleshooting. This is useful for EQuIS database managers when communicating errors back to data providers. Sending the error log summary to the database manager will also provide them with a summary of any errors or missing reference values that may need to be added to the EQuIS database.

 

1.Select Error Log edp-error-log-button from the Error Log group on the EDP Home ribbon.

2.If desired, in the Save Error Log As dialog box, change the default name or location of the error log summary file.

3.After saving, the .html file opens, typically in an Internet browser.

 

Idea

Depending on the computer settings, the .html file may open in an application besides an Internet browser. In that case, close the saved .html and re-open the file within a browser to view it properly.

 

edp-error-log

 

 

Goal

Objective: Resolve Data Errors in EDP

Troubleshoot and resolve data errors within an EDD. Two different cells with the same error type may or may not be resolved in the same way. The steps below are recommendations for this scenario but may vary with other EDDs. When in doubt, check with the data provider to confirm what the contents of a cell should be before taking a best guess to resolve an error.

In this scenario, there are several errors within the laboratory provided EDD that need to be resolved.  Although we have provided an Error Log Summary to the laboratory, we have also agreed to help them resolve these errors.

 

Warning Small

When resolving errors, edits made to data files within EDP are not automatically saved.
If data files are modified, save the data files frequently during the EDP session.

 

Tasks

Resolve Missing Required Field Errors

Resolve Value Not Found in List Errors

Resolve Value Exceeds Field Length Errors

Resolve Orphan Row Errors

Resolve Reference Value Not Found Errors

Save Corrected EDD

 

Resolve Missing Required Field Errors

1.Select Errors Only from the View group on the EDP Home ribbon to display only the problematic data.

 

Idea

If desired, toggle off the display of the Comment Rows by selecting or clearing the Comment Rows check box in the View group.

 

2.Hover the mouse over any cells highlighted with Red to reveal the error type of "Missing required field."

For this exercise, hover the mouse over the sample_type_code field in Row 2.

3.Populate these fields with the appropriate values and note that the errors are resolved.

For this exercise, populate the sample_type_code field in Row 2 with N (for "Normal"), and then single-click in another field and note that the error is resolved.

 

Resolve Value Not Found in List Errors

1.Hover the mouse over any cells highlighted with Fuchsia to reveal the error type of "Value not found in list".

For this exercise, note the SAMPLE_SOURCE field is highlighted in fushia for Row 13.

2.Select the drop-down list and select an appropriate value from the drop-down list.

For this exercise, select FIELD from the drop-down list.

 

Resolve Field Length Errors

1.Hover the mouse over any cells highlights with Yellow to reveal the error type of "Value exceeds field length."

For this exercise, note that Row 17 has an error in the COMPOSITE_YN field, which is highlighted in yellow.

2.Hover the mouse over the column header of the field to reveal a tool-tip, which indicates the limit of the field.

For this exercise, hover over the COMPOSITE_YN field and note that the length of this field should be 1 and that “No” is a length of 2 and is causing the error.

3.Change the contents of the cell to an appropriate value that is equal to or shorter than the length of the field.

For this exercise, change “No” to “N”. (Note that Row 17 disappears from view, as all the errors in the row have been resolved).

 

Resolve Orphan Row Errors

1.Turn Errors Only off to better troubleshoot any Orphan Row errors.

 

Idea

Depending on the error, it may be helpful to turn Errors Only off to view all the data and compare rows with errors to error-free rows.

 

2.Hover the mouse over any cells highlighted with a Purple gradient to reveal the error type of "Orphan Row."

For this exercise, note that Row 18 has an error in its PARENT_SAMPLE_CODE field.

3.Any value in the orphan row cell must match the corresponding value in the parent of this record.

For this exercise, any value in PARENT_SAMPLE_CODE must match the corresponding value in the SYS_SAMPLE_CODE record that is the parent of this record.

 

Idea

Generate an EDD Description to help determine the parent of the orphan.

 

4.Change the cell to match the appropriate parent reference.

For this exercise, the corresponding PARENT_SAMPLE_CODE record is “1508188-002CMS”.  Change the PARENT_SAMPLE_CODE field to “1508188-002CMS”. Note that all errors in the LabSample_v1 section are now resolved and the section is highlighted green.

edp-tutorial-edd-good

 

5.Resolve any other Orphan Row errors in other sections.

For this exercise, select the TestResultsQC_v1 section of the EDD and note that Row 16 has another “Orphan row” error.

Any value in SYS_SAMPLE_CODE in this section of the format must match one of the corresponding SYS_SAMPLE_CODES in the LabSample_v1.

The corresponding parent SYS_SAMPLE_CODE record in the LabSample_v1 section is “1508188-001AMS”.  Change the SYS_SAMPLE_CODE field in this section to “1508188-001AMS”.

6.After resolving data errors, click Refresh edp-refresh-button > Refresh Table from the Data group to refresh the section if the changes are not reflected.

 

Resolve Reference Value Errors

The error message “Reference Value Not Found” in EDP indicates that the reference value is incorrect, or does not exist in the reference value file.

 

1.While viewing an EDD in EDP, hover the mouse over any “Reference value not found” errors in the EDD.

For this exercise, in the TestResultsQC_v1 section, note that Row 7 contains an error (the number 7 is highlighted in red). Use the scroll bar to scroll to the right to look for the error. The highlighted cell shows a TEST_TYPE = INITIALL.

edp-error-reference_value

2.Check the drop-down menu to see if there is a synonymous value for the flagged value.

Note the remainder of the values show the TEST_TYPE = INITIAL, which is in the drop-down list.

3.Where applicable, apply the synonymous value to resolve the error.

Click in the highlighted cell and change the TEST_TYPE to INITIAL. After this change is made, click on another cell.

 

Save Corrected EDD

1.Once the errors in each section are resolved, select the Save> EDD option from the EDP Application menu pro-edp-button in the top-left corner.

2.Save the work as an Excel Workbook (.xls) by changing the file type.

3.Re-name the saved EDD and save any additional changes to the EDD to this file.

 

Idea

It is a good idea to preserve the original EDD by renaming the EDD after making modifications. The original EDD remains available for reference, which is useful when communicating with data providers.

 

 

Warning Small

Prior to uploading EDDs, review data for potential errors that might not be flagged as errors by EDP. Examples of these other potential issues are correct sampling dates, matrix types being consistent with sample type, etc.

 

 

Goal

Objective: Upload Data via EQuIS Professional EDP

Once all errors in an EDD dataset have been resolved, upload the data into EQuIS.  In EQuIS Professional EDP, this process is comprised of two parts: Creating a data package and committing the data to the EQuIS database.

 

Tasks

Create a Data Package

Commit the Package to the EQuIS Database

 

Create a Data Package

During the Create step, EDP checks all of the data against the business rules included in the selected format file, and then places the data into the EQuIS data table structure. The data is not placed into the EQuIS database, but into the Database/Facility Preview tab, allowing for the review of data and ensuring that the data was placed into the EQuIS table structure as expected.

edp-database-preview-tab

 

1.From EQuIS Professional EDP, select the Professional ribbon at the top of the screen.

2.Select Create edp-create-button from the Package group of the Professional ribbon.

3.A processing window opens. When processing is complete, this window displays a record of how many rows were loaded into the Database Preview tab.

4.Click the Status column header in the processing window, and drag and drop the column header into the gray area at the top of this window. (This organizes the create processing information by status.)

5.Select the plus (+) node left of the blue highlighted row to see the contents of the processing log.

6.Right-click anywhere in the processing window and select the Save button to save the create process log to the desired location.

 

Idea

If errors remain in the Format tab before the Create step, the data that includes the errors will remain in the Format tab in EQuIS Professional EDP. Always check the Format tab after the Create step to ensure that all records were created.

 

7.Click the Finish button if there are no records with a status of ERROR.

edp-create-log

 

The sections of the EDD in the Format tab should be black to indicate all the records were successfully created. Note that any column header ("Comment") rows denoted with a # symbol were left behind.

 

8.Click the Database/Facility Preview tab towards the bottom of the EDP window and review the data tables that were populated with the data. Populated tables are displayed in Green text.

edp-database-preview-tab

Idea

Review the Database/Facility Preview tab each time a data package is created.

 

 

9.Select a populated table and scroll to the right of the table preview. The field called EBatch is the value that is assigned to each record during the create process.

edp-facility-preview_and_ebatch

 

Idea

Be sure to save the data package if it will not be immediately committed to the EQuIS database. Select Save Package from the Package group on the Professional ribbon.

 

 

Commit the Package to the EQuIS Database

Once the create step is complete, and the data and reference values in the Database/Facility Preview tab are reviewed, Commit the data to EQuIS. The commit step inserts the data into the appropriate tables within the EQuIS database. During the commit step, the data is checked against data that is currently in the EQuIS database to ensure referential integrity.

 

1.Select Commit edp-commit-button from the Package group on the Professional tab in Professional EDP. There are seven different Commit options:

Commit Option

Definition

Insert Only

Inserts new records but does NOT replace existing records. If the record already exists, the new record remains in the package and the existing record is NOT modified.

Merge Only

Merges data if a record already exists in the EQuIS database, but does NOT insert new records. This type of commit will not replace values in an existing record unless the existing value is null. Null values in the existing record are replaced by values in the new record.

Update Only

Updates data if a record already exists in the EQuIS database, but does NOT insert new records. All values in an existing record are replaced by values in the new record, except null values. Any null values in the new record will NOT replace non-null data in the existing record.

Replace Only

Replaces data if a record already exists in the EQuIS database, but does NOT insert new records. The existing record is COMPLETELY replaced by the new record.

Insert and Merge

Inserts new records but does NOT replace existing, non-null records. This type of commit will not replace values in an existing record unless the existing value is null. Null values in the existing record are replaced by values in the new record.

Insert and Update

Inserts new records AND updates existing, non-null records. All values in an existing record are replaced by values in the new record, except nulls. Any null values in the new record will not replace non-null data in the existing record.

Insert and Replace

Inserts new records AND replaces existing records. The existing record is COMPLETELY replaced by the new record.

 

Both "Insert and Merge" and "Insert and Update" (or "Merge Only" and "Update Only") merge the existing record and the new record. For each field, if only one of the values (new or existing) is null, then the non-null value (new or existing) is kept. The only difference is when both the existing record and the new record have a value for a particular field.

 

“Insert and Merge” or “Merge Only” give the existing record precedence—the existing value is kept and the new value is ignored.

 

“Insert and Update” or “Update Only” give the new record precedence—the new value overwrites the existing value.

 

2.Select the appropriate commit type.

For this exercise, select Insert and Update.

3.A processing window appears that contains a record of how many values were loaded into each table within the EQuIS database.

4.Click the Status column header in the processing window and drag and drop the column header into the gray area at the top of the window

5.To view the content of the processing log, click the plus (+) node left of the blue highlighted row.

6.To save the commit process log, right-click anywhere in the processing window and select the Save button.

7.If there are no records with a status of “ERROR,” click the Finish button.

edp-commit-log

 

Idea

Verify that all of the tables in the Database Preview tab are blank and contain no data. If the tables in the Database Preview tab are not blank, the remaining data was not committed to the EQuIS database and the data should be reviewed for errors.

 

Administrative tools

Optional Exercise: Add Reference Values to EQuIS

The reference values used to load data into EQuIS Professional EDP come directly from the EQuIS database. Recall that each EQuIS database has only one set of reference values for all of the facilities in that database. Add new reference values to the EQuIS database by using the right-click menu within EQuIS Professional EDP.

 

Warning Small

The exercises below are recommended for EQuIS Power Users and should only be used with permission from the Database Administrator to add reference values to the EQuIS database.

 

 

Tasks

Add New Reference Values to the EQuIS Database via Professional EDP

Approve New Reference Values via Professional EDP

 

Add New Reference Values to the EQuIS Database via EDP

One of the most common errors seen in EQuIS Professional EDP is "Reference Value Not Found". If a desired reference value is correct and is not found with a status_flag of R in the EQuIS database, it can be added directly within EQuIS Professional EDP.

1.Launch EQuIS Professional EDP pro-edp-button.

2.Select Format edp-format-button from the Open group of the EDP Home ribbon.

3.Browse to the desired format directory and select the format to open.

4.Load the EDD edp-edd-button that contains the reference values to be added.

5.Select the section of the format that contains the reference values to be added.

6.Hover the mouse over the first value to be added and right-click the field.

 

A menu appears that includes three options. Each option adds reference values to the EQuIS database. The differences are:

a.Add By Value – Allows adding of only the reference values that have currently been selected to the appropriate reference table.

 

Idea

The "Add By Value" option is recommended to add new reference values to the EQuIS database.

 

b.Add By Column – Allows the adding of the reference values Professional EDP cannot find as entered and/or approved in the EQuIS database.

c.Add Values, All Formats – Allows adding of all of the reference values Professional EDP cannot find as entered and/or approved. This option adds all values for all sections.

7.Select the Add By Value option to add the new reference value.

8.Select Refresh> Refresh All edp-refresh-button from the Data group of the EDP Toolbar to refresh the display.

9.Repeat the above steps for any other new reference values to be added.

 

Approve New Reference Values via Professional EDP

To use the newly added reference values in future EDP sessions, approve the reference values before uploading them to the EQuIS database.

 

1.Select the Database Preview tab located to the right of the Format tab at the bottom of the EQuIS Professional EDP window.

2.Note that any reference tables where new values were added are now green, indicating the new values populating the tables need to be approved.

3.Click the first table on the left side of the screen that is highlighted in green.

4.Note that the new values added in the previous section are now listed here.

5.Observe the STATUS_FLAG field, which indicates if the new reference value is approved for use, or if the new reference value is still pending review.

By default, when adding new reference values to an EQuIS database using EQuIS Professional EDP’s right-click feature, the reference values are assigned an R STATUS_FLAG to indicate the reference values needs review and approval.

6.Click in the STATUS_FLAG field and change the R to an A for each of the new reference values.

7.As desired, add additional information to the reference values’ record, (e.g., a description of the value, etc.). Information that is more comprehensive leads to better data decisions.

8.Click Save> EDD on the EDP Application menu to save the EDD.

 

Idea

Saving changes at this point does not save added reference values or modifications to the reference tables. To update these changes, upload the EDD to the EQuIS database. For instructions on how to upload an EDD to the EQuIS database, see the Upload Data via EQuIS Professional EDP section.

 

 

 

Administrative tools

Optional Exercise: Remove Data from EQuIS

In EQuIS Professional EDP, it is possible to Rollback an imported EDD based on its EBatch number.  Rollback will remove data associated with an EBatch number from the EQuIS database. Note:

Rollback will not permit parent-child relationships to be broken.

Rollback will not return a record to a previous state—the entire record will be removed from the EQuIS database

 

Rollback EDDs via EQuIS Professional EDP

1.In EQuIS Professional EDP select the Professional ribbon and click Rollback from the Package group.

2.The screen that appears contains the EBatch to easily identify the data set to be removed.

edp-rollback-window

 

3.Highlight the EBatch record, and then click the Next button.

For this exercise, highlight the EBatch record that was uploaded earlier in the exercise.

4.Highlight the record to confirm.

5.Click Rollback.

6.Click Finish.

7.Confirm that the rollback process was successful and that data was removed from EQuIS.

edp-rollback-completed