<< Click to Display Table of Contents >> Check and Import EDDs – Professional

Copyright © 2020  • Modified: 18 Jun 2020

Overview

EQuIS Professional EDP Workflow

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

 

Check the EDD

1. Launch EDP and load an EQuIS format.

2. Load the EDDs.

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.

 

 

•Launch EQuIS Professional EDP and Load an EQuIS 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 7 must be installed on the workstation

•.NET Framework 4.7.2 or higher must be installed on the workstation

•Read/write permissions are required for the desired facilities

 

Tasks

•Launch EQuIS Professional EDP

•Load an EQuIS Format

 

Launch EQuIS Professional EDP

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

2.In the Connection window, enter EQuIS Enterprise Server Name and user authentication information. Click the Connect icon to view available facilities.

For this exercise, select the Training database connection and trainee credentials.

3.Navigate to the target EQuIS database and then find the desired facility, expanding folders (Facility Groups) if needed.

For this exercise, expand the Training Database and then the North America folder.

4.Double-click the Facility name from the facility to login to the facility.

For this exercise, double-click on the Gold King Mine facility.

5.The main EQuIS Professional window opens.

6.Select EDP from the Import group of the Home ribbon.

7.EQuIS Professional EDP opens in a separate window.

 

 

 

Load an EQuIS Format

The EQuIS format file is the essence of data checking in EDP and EQuIS. Structured in XML, the EQuIS 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 EQuIS formats. It is essential that the correct EQuIS format file is applied for data to be successfully uploaded into the EQuIS database.

 

1.Select Format 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".

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 values 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 from the Tools group of the Home ribbon.

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

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.

 

Create an EDD Description File

1.Return to EQuIS Professional EDP.

2.Select EDD Description 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.

 

 

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 EDD description file includes a separate worksheet detailing each section of the EQuIS format file.  

 

 

 

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:

 

 

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

 

 

 

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

 

 

 

 

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.

 

 

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.

 

 

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.

 

 

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.

 

 

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.

 

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

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 option from the application backstage, accessible from the File menu 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.

 

 

 

 

 

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.

 

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

2.Select Create 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.

 

 

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

 

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.

 

 

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.

 

 

 

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 from the Package group on the Professional tab in Professional EDP. There are seven different Commit options:

 

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.

 

 

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.

 

 

 

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 .

2.Select Format 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 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.

 

 

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

 

 

 

 

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.

 

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.