The Data Import Process

Objectives

After completing this lesson, you will be able to:

  • Identify the components of Importing in APMe

Components of Importing

To import data, open Integration Data- File Search. Select New (+) to create a new InFile.

Infile: As we saw earlier, an Infile is the parent "container" that aggregates data from one or many files. InFile fields include:

  • InFileId: A unique name for the InFile
  • Name: A descriptive name for the InFile. If the name is not specified, the InFile ID becomes the default name.
  • PayoutTypeId: Pointer to the PayoutType record to which this record is associated. This field serves three purposes:
    • When executing the InEntity Posting process for a specific PayoutTypeID, only InFiles with the appropriate PayoutTypeID will be processed.
    • SQL Scripts and Batch Entity updates can be constrained to a specific PayoutType to ensure they aren’t unnecessarily executed for every Import/Post process.
    • During InFile/InEntity Posting, the PayoutTypeID is transferred from InFile to the resulting TranHead records. This serves to segment the transactions into distinct groups. Once a TranHead has been assigned to a particular PayoutType it can not be changed to another PayoutType.
  • Effective Date: Defaults to the system date when the InFile is created, but can be set to any date.
  • Billing Month: Defaults to the Bill Month of the current Active Period, but can be set manually.
  • Billing Year: Defaults to the Bill Year of the current Active Period, but can be set manually.
  • Statistics: During the import, posting and payout process, APMe updates the status of the records that have been imported and marks them as their status changes. Statuses include:
    • Ready: The number of records have been successfully imported into the staging tables and are available to be posted.
    • Skipped: This status is only relevant for Transactions. A transaction is marked skipped when 2 or more transactions are loaded in the same infile with the same InpKey. APMe will mark the duplicate transactions as skipped and generate a new transaction with the accumulated values of the duplicate transactions.
    • Computed: This status is only relevant for Transactions. This number represents the number of records in this InFile that have been computed and posted to BrokerHistory and the payout has not yet been Finalized.
    • Finalized:This status is only relevant for Transactions. This number represents the number of records in this InFile that have been computed and posted to BrokerHistory and the payout has been Finalized.
    • Hold: This status indicates the number of the records that have been marked to be excluded from processing.
    • Error: This status indicates the number of records that have failed validation during the last posting attempt.
  • Import Request: The Import Process imports data into staging (InFile) tables. An Import Request is a process used to execute existing import formats. While an import format tells the system how to format source data, the import request process tells the system how to integrate it into the staging table correctly. The components of an Import Request are:
    • ImportFormat: The formatted imports that map the data and files into the system. The Import Request process calls these formatted files to import into the InFile tables.
    • Date Processed: Date stamp when the Import Request was initiated.
    • Time From: Time stamp when the Import Request was initiated.
    • System Status: Process status of the Import Request.
    • File Name:  Name of the imported file.
    • Acknowledgement Status: Import requests that end in error status must be acknowledged before they can be posted. File Sweep can be configured to automatically acknowledge errors to prevent disruption of automated process.

Validation During the Import Process

The import process includes two default validation checks.

  1. Data Type:Prohibits importing alpha characters into a numeric field.
  2. Data Length:Prohibits importing a field longer that specified in the database schema.

Additional validations can be manually added in the Entity Edit functionality or in the Import Format field mapping definition.

In the example in the Import Request section, the Import request ended in Error. Select the View Messages or View Log icons to reveal descriptions of the error.

In this case, the CustomerId value was 48 characters, but the InCustomer.CustomerId field has a limit of 30 characters. 

Hint
Avoid relying on errors in Import Requests to ensure data integrity, as errors cannot be resolved in the user interface. The record(s) in error will need to be identified/resolved and reimported in another Import Request. The best practice is to add validations as part of the post process so the errors can be corrected in the user interface if possible.

Manually Import a List of Customers

In this exercise, you will manually import a list of customers using the TrainCustomerImport.xlsx file that was provided by your instructor.

Steps

  1. Create an InFile record and add basic data.

    1. From the Integration Portal, select Inbound Data  - File Search

    2. Select New (+).

    3. Populate the fields with the following:

      • File ID: CustomerImport
      • Name: Customer Import
      • Eff Date: 1/1/2022
      • Billing Month: 6
      • Billing Year: 2022
      • Payout Type ID: CLIENT Payout
      • Comments: Customer Import for Training
    4. Select Save.

  2. Add an import file.

    1. In the Import Requests section, select Add Import Request.

    2. On the Import Format dropdown, select TrainingCustomer.

    3. Set the Upload Type to Client.

    4. In the File Content field, select Choose File.

    5. Navigate to TrainCustomerImport.xlsx and select Open.

    6. Select Next.

      The Import Request Summary screen of the wizard appears with the ID of the new Import Request.

    7. Select Complete.

      The data is uploaded into the appropriate staging tables based on the Import Format. The File Detailscreen is updated as shown below.

  3. Manually Resolve Import Errors

    It looks like our import had an error! In this step, we will find and resolve the error.

    1. Open Integration - Import Data - File Search.

    2. Select the CustomerImport record.

    3. Select View Messages to reveal descriptions of the error.

    4. Hover over the Error row to display the full description of the error. In this file, line 13 is invalid because the CustomerId is too long.

      Note
      There is no way to correct this error in the user interface since the data hasn’t been committed to the database. To correct this issue, we’ll correct the data in the source file and reimport.
    5. Open the TrainCustomerImport.xlsx file.

    6. Delete the first 12 rows of data.

    7. Move row 13 up to row 2, just below the header.

    8. Change the Customer ID in the row to Training12 and save the file.

  4. Repeat the import process

    1. Select Add Import Request.

    2. From the Import Format dropdown, select TrainingCustomer.

    3. Set the Upload Type to Client.

    4. Select Choose File and set the fixed file.

    5. Select Open.

    6. Select Next.

    7. Select Complete.

      Note
      The Import Requests list should look like the image below.

Log in to track your progress & complete quizzes