Understanding the ETL Process in ACE

The Extract, Transform, Load (ETL) enables organizations to efficiently import, process, and manage data from various external sources.

Importing Data into ACE (Extract)

The extraction phase involves importing data from external files or systems into ACE. Supported file formats include:

• CSV Files: Standard comma-separated files.

• TSV Files: Standard tab-separated values files where data fields are separated by tab characters instead of commas.

• PSV Files: Standard pipe-separated values files where data fields are separated by a vertical bar (|) instead of commas.

• Excel Files: Both .xls and .xlsx formats are supported.

Note

Files saved from MS Excel as “Strict Open XML Spreadsheet” with the xlsx extension will not work.

Users can upload these files manually or automate the process using Secure File Transfer Protocol (SFTP). Each file must adhere to specific ACE load methods, as detailed in the ACE Import Interface guide.

Data Mapping and Validation (Transform)

Once data is extracted, it is transformed based on the ETL Profile Configuration. This includes:

• Field Mapping: Aligning data fields from the source file to corresponding fields in ACE.

• Data Validation: Ensuring data integrity by checking for missing values, incorrect formats, and duplicates.

ACE provides import layouts for various data types, including account assignments, payments, and transactions. The layout documentation specifies the required fields and their attributes, ensuring consistency and accuracy during the transformation process. These layout documents can be found in the Import Layouts documentation.

Integrating Data into ACE (Load)

In the final phase, the transformed data is loaded into ACE.

Setting Up an ETL Import Profile

ETL Import Profiles serve as reusable configurations for loading data with consistent field mapping and file formats. They can be used from jobs that are configured, scheduled or on demand. The process involves:

1. Navigating to Accounts → ETL Import → Profiles → New.

2. Uploading a file to configure the ETL Profile and map the fields.

3. Testing the profile with or without a job to verify it loads data as expected.

4. Saving the profile for future use.

For a detailed walkthrough, refer to the article on creating an ETL Import Profile for Account Assignments.

Troubleshooting ETL Processes

Common challenges during the ETL process include file formatting errors, field mapping errors,  transformation failures, and load errors. To address these issues:

• File Formatting: Ensure the file adheres to layout requirements and includes required fields.

• Field Mapping Errors: Verify that fields are mapped correctly in the ETL profile.

• Load Errors: Check for duplicate records per ETL Profile configuration and/or schema mismatches.

The ETL Troubleshooting Guide offers comprehensive steps to resolve these issues.

Summary

The ETL process in ACE is used for data integration and the regular importing of data ensuring that data from various sources is accurately imported, transformed, and loaded into the system. By leveraging ETL Import Profiles and adhering to predefined import layouts, organizations can maintain data integrity and streamline their operations.

For further assistance, consult the ETL Category for documentation or contact ACE support.