Data Loading

Loading historical AFEs, RTDs and other documents through the Data Loader

Overview

Aucerna Execute’s Data Loader tool is useful for efficiently loading historical AFEs, RTDs, and other documents into Aucerna Execute using Excel based loader templates.

The Data Loaders are available to administrators with the Data Loader administrator privilege.

The Data Loader screens are designed to make loading historical data into Aucerna Execute as simple as possible. As such, many of the validations that would be performed for a user creating a document in Aucerna Execute do not apply to documents loaded with the Data Loaders.

The Data Loaders are accessed from the Configuration screen (Tools > Configuration) by clicking the “load data” button next to the document type you wish to load data for.

In previous versions of Aucerna Execute, the historical loader tool was only available for AFEs and required loading all of the data for each AFE in a single operation. Collecting and validating this information was often a daunting task.

The new AFE Data Loaders approach this differently by breaking up the job of loading historical data into smaller batches of related information.

For example, loading historical AFE data might look like this:

Load top-level AFE information for all historical AFEs (AFE Number, Description, Total Estimate, Our WI, Status, AFE Type, …) using the top-level AFE loader.
Load partners information on any historical AFEs created in the last 3 years using the AFE partner loader.
Load detailed estimates on any historical AFEs that have not yet been closed using the AFE estimate loader.

The important points are:

You are very quickly able to load valid placeholder documents into Aucerna Execute using the top-level loader.
You can then fill in more detailed information (partners, estimates, approvers, …) using specific detail loaders for some or all of the placeholder documents.
You don’t have to collect all of the historical data for historical AFEs/RTDs at one time.
You don’t have to load a complete set of historical data for all historical AFEs/RTDs.

While the loaders are available on many document types within Aucerna Execute this document will focus, primarily, on the AFE.

Basic Usage

While all data loaders look fairly similar the business rules they enforce vary with with document type being loaded.

To load historical data for any supported type of document in Aucerna Execute you navigate to the appropriate Data Loader screen by clicking the corresponding load data button on the Configuration (Tools > Configuration) screen.

Selecting a template

Each document type will have one or more loaders available that load a particular type of data. For example the AFE has the top-level AFE loader “AFEs”, as well as detail loaders for loading approval details, comments, estimates, partners, wells, etc.

Click image to expand or minimize.

Typically the top-level loader (“AFEs”, in this case) would be used first to create the new documents and then, if desired, the detail loaders would be used to load detailed information against those new documents.

Downloading a template

Once a template is selected, download the loader template by clicking the “Download Template” button.

This will download an empty loader spreadsheet of the selected type that can be edited using Microsoft Excel.

Unlike previous versions of Aucerna Execute, a new loader sheet does not need to be generated each time a field is added unless you want to populate that new field.

The Loader Template

A downloaded loader template will look something like the following:

Click image to expand or minimize.

Some of the columns are color coded (in row #3) as follows:

KEY

Key columns are required and used to identify which document the row corresponds to.

In a top-level loader there will be one row per document to be created and the key columns are used to ensure that the new document doesn’t already exist in the database.

In a detail-level loader there may be one or more records per document and the key columns are used to identify which pre-existing document should be updated.

REQUIRED

Required columns must be included in the loader sheet and can not be removed. Usually these columns also require that a value be provided.

NOT EMPTY

Not Empty columns must always have a value if the column is present but the entire column can be removed if you would rather have Aucerna Execute default the value for that column.

The non-color-coded columns, and those marked as “NOT EMPTY”, can be removed from the loader sheet (by deleting the column in Excel) if data for those columns does not need to be loaded or does not exist in the source data.

It’s possible to build a customized loader sheet by reordering columns and removing unnecessary columns (those not marked as KEY or REQUIRED). This custom loader sheet can be saved and used again and again.

The loader includes each field’s display name (in row #5) and the field path (in row #4).

Some columns, such as the AFE status, have a constrained list of possible values and the title cell will have the list of possible values, as an Excel comment, to make entry easier.

Click image to expand or minimize.

Loading Data

Fill the template by copying information from another system. Most top-level loader sheets will have one row per document. The AFE top-level loader sheet will have one row per AFE per version (Base, R1, R2, S1, …).

Detailed loaders require that the document you are loading detailed information onto already exists. The detail loaders will often contain more than one row per document (ie. the partner loader will have one row per partner on the AFE with the same AFE Number/Version key on each row).

Detail loaders replace any pre-existing detail information of that type on the AFE with the new data in the loader sheet. Great care must be taken when loading detailed information onto existing AFEs that already have those type of details. The exception to this behaviour is loading comments. Comments are always appended to the comments that are there already.

Once the data for the loader sheet has been entered, save the Excel sheet and drag it onto the “Drag-and-drop a file here or client to browse” panel to process it.

Aucerna Execute will validate the uploaded sheet and, if there are no issues discovered, present a message like the following.

Until the “Load Data” button is clicked, the loader data is not written to the Aucerna Execute database.

In some cases, the validation will raise warnings or errors resulting in a message like one of the following:

Errors must be resolved before the data can be loaded.

Warnings indicate something unusual in the data but do not necessarily indicate an error condition. It is strongly recommended that warnings be reviewed before selecting “Load Data”.

When warnings or errors are found in the import data, clicking the “Download Results” button will download a modified version of the uploaded loader sheet with an additional error column (added as the last column) that provides details on the errors or warnings encountered. It is safe to download this sheet, make corrections to it, and re-upload it, instead of than jumping back and forth between the original sheet and the error sheet.

Some types of errors can only be found when loading the data so it’s possible to receive error messages after clicking the “Load Data” button. In this case, a modified loader sheet will be returned that only the rows that could not be processed along with the corresponding error. Rows that were able to be processed successful are written to the database and removed from the generated loader sheet.

Default Account, Partner and Approving/Reviewing Positions

When loading top-level AFE data, Aucerna Execute may need to include a placeholder account, partner or approving/reviewing position on the AFE to make for a valid AFE.

For example: When loading an AFE with an 80% working interest, prior to loading partner details, Aucerna Execute doesn’t know which partners represent the remaining 20% interest. Aucerna Execute will add a placeholder company to the AFE with that 20%. This can later be replaced by the actual partner list using the partner detail loader.

Similarly, when loading top-level AFE data the total AFE estimate will be coded against a single historical placeholder account.

The placeholder partner, account and approving/review positions must first be created (if a suitable option doesn’t already exist) using the normal administrative tools. Once placeholder records exists, the account number, partner name/code, or position name must be entered in the appropriate spot under Tools > Configuration > Settings.

Click image to expand or minimize.

If the defaults are not configured trying to load AFE data will result in errors like the following:

Temporary AFE and RTD Numbers

All of the AFE loaders include AFE Number as one of the key columns (similarly RTD Number is included on all RTD loaders). In cases where an AFE/RTD has not yet been assigned, an AFE/RTD Number should use a temporary number that starts with BLANK_. This will allow the AFEs/RTDs to be loaded and give a usable key for loading detail records.

Once the historical AFEs/RTDs have been loaded along with all required detailed data, the BLANK_ numbers can be cleared from all AFEs or RTDs in the database.

From the Data Loader screen for either AFEs or RTDs click the “Clear Temporary Numbers” link in blue panel in the “Select Loader” block.

From here you can choose to clear all of the temporary numbers from all AFEs or RTDs in the database.

Only do this once all detail data for those AFEs and RTDs has been loaded.

Special Notes About AFEs

The following list outlines some special considerations when loading historical AFE information:

When loading top-level AFE information, the entire version chain for the AFE must be loaded at the same time. You can not just load S2 on its own but instead must also load the base version and S1 and any revisions.
When loading historical AFE versions use the correct historical AFE status (IAPP, REL, FAPP, …) on the historical versions.
When loading mail ballot AFEs:
1. run the top-level loader
2. run the “AFEs - Mail Ballot” loader to turn the mail ballot AFEs into mail ballots.
3. run the “AFEs - Partners” loader to load partner details.
4. run any additional detail loaders required
When loading top-level AFE data, be sure to load the correct AFE status! The detail loaders will not load data that contradicts the overall AFE status. For example: if you load the AFE with a status of FAPP (Fully Approved), all of the partners loaded must also be approved.