Key Fields play a vital part in the structure of AGS 3.x data. This article shows some of the problems commonly encountered when transferring data in the AGS SAMP and test tables and gives guidence on how to minimise and eliminate these problems.

Keep checking back for the next in this series on sample referencing as we explain how DIGGSML solves these problems soon.

An Introduction to the Problem

AGS fields are marked in the AGS publication as either "Key" or "Common". Common fields are used to transfer descriptive data and do not have any integrity implications. Key fields ensure that each record in an AGS file can be uniquely defined and that each line of data can be referenced to its parent information.

Incorrect key fields in the laboratory test tables are one of the most common reasons for AGS data failing integrity checks and often results in orphan records or phantom parents. This fact sheet will assist data suppliers and receivers in effectively checking AGS files and reducing the problems caused by incorrect key fields.

Best AGS Data practice for working with your laboratory

1. Supply your laboratory with AGS data as soon as possible in the project – preferably at the same time as the test schedules. This will remove problems with re-keying of sample information.
2. Remove the SAMP and HOLE tables from the AGS data returned from the laboratory and check the remaining file together with the original AGS file supplied to the laboratory. This will ensure that the test information returned is correctly registered to the original samples.
3. Repeat the above procedure with the latest version of the SAMP and HOLE table from your SI system. This will ensure that the test information returned will be compatible with your current data and is ready for import.

Key Fields Are Unique

Key fields enable each line of data in an AGS group to be uniquely referenced. For example, HOLE_ID is the only key field in the HOLE group and so each HOLE_ID must be unique within the HOLE table.

Very few groups in an AGS file have only one key field. For example if the Depth Related Remarks group (DREM) had HOLE_ID as its only key field it wouldn’t be possible to have more than one remark per hole. Include the depth of the remark (DREM_DPTH) in the list of key fields and it is possible to have as many remarks for each hole as long as they are at different depths. When key fields are combined to make a unique key it is often referred to as a combined key.

The SAMP table has 4 Key Fields HOLE_ID, SAMP_TOP, SAMP_TYPE and SAMP_REF and when combined these must be unique for every sample in the AGS file

Key Fields Define a Relationship

The key fields in a group also define the data's relationship to its parent group. This may sound like database gobbledegook if you are not familiar with database design but it can be easily explained with the aid of an example.

Single Key Fields

The DREM group (as discussed above) contains a HOLE_ID field. This enables the user (or receiving computer) to look up the HOLE_ID in the HOLE table to understand more information about the hole – for example the Easting and Northing information. In this instance the HOLE table is the parent of the DREM group. The relationship between these two tables is called a Parent - Child relationship, a Parent can have many Children but a Child can only have one Parent (in database speak this is called a 1-to-many relationship).

If a remark exists for HOLE_ID "BH3" in the DREM table but there is no "BH3" in the HOLE table then this DREM data would be classified as an orphan record - an item of data that has no parent record.

Multiple Key Fields

The AGS publication contains information of the parent child relationships in the hierarchy table in section 10.3. DREM is one of the simplest relationships; it gets more complicated when you look at a Lab testing data level such as the CLSS table. The CLSS table is a child of the SAMP table. The SAMP table has 4 key fields (HOLE_ID, SAMP_TOP, SAMP_TYPE and SAMP_REF) and so the CLSS table must include these 4 fields correctly to match to a sample.

The CLSS table then requires 2 further key fields to ensure that tests carried out on different specimens within the same sample are unique referenced - this results in the Key fields in the CLSS table being (HOLE_ID, SAMP_TOP, SAMP_TYPE, SAMP_REF, SPEC_REF and SPEC_DPTH).

Checking AGS Data from a Laboratory

The AGS states that all parent tables must be present in an AGS file for it to be valid. This means that a laboratory must report the SAMP and HOLE table records for each classification test. If the laboratory were to issue a file with only the CLSS table in it then any AGS checking software will state that every CLSS result is an orphan as there is no sample data in the file. Most software used to create Laboratory AGS will create HOLE and SAMP groups that contain just the Key Fields for the Laboratory data and if an AGS file is produced using this method then it will validate using the common AGS checkers. However if the data has changed at source then merging the laboratory data with the current data can result in Phantom Parents being produced.

Problems with Orphan Sample records and Phantom Parents can occur due to one of the 3 following reasons. Being aware of these reasons will help you to avoid them in your project.

Problem 1 - The laboratory mistypes some of the key fields

If the laboratory’s IT system does not allow for the import of AGS data or the client has not given them AGS data, the only option available to them is to retype the data. Retyping data is never 100% perfect and so some errors will be introduced to the system.

Problem 2 - The laboratory does not enter some of the key fields

If the laboratory’s IT system does not allow 4 client reference fields, the laboratory may therefore decide to only use HOLE_ID, SAMP_TOP and SAMP_REF. They will still produce AGS data but as the CLSS table only contains 3 out of the 4 required key fields the data will not link to the SAMP group and all the records will be seen as orphans

Problem 3 - The engineer changes the data

Key Fields are descriptive and may change. For example the 4 fields that uniquely define a sample also describe it (it's the first D sample in BH1 at 4.56 metres depth) and so if the engineer decides that this descriptive data is wrong and changes it they effectively create a new sample and delete the old one.

If AGS data has already been passed to the laboratory for testing then they will return data with the old referencing system and thus create an orphan record when checked against the latest version of the data. This problem is caused by the engineer and the data should be fixed by them on receipt. It is for this reason that the best practice notes at the front of this fact sheet recommend you check the data with the sample information that you supplied to the laboratory before you check it against the most current version of the sample data.

This is a vital check to do as if the laboratory passes back your original SAMP table with their data (standard practice) and you import it into your database system after you have changed some of the sample references manually then you will create Phantom samples or holes. Getting out of this problem can be extremely difficult so you must do everything possible to avoid it.

Summary

Key fields within the AGS format can be a cause of some confusion, this article has hopefully explained that this is not necessarily justified, there are a lot of situations where problems may arise with multiple Key fields in tables but with careful handling these can be eliminated.