Source data cleansing and transformations

You can exclude or transform source system data in tables of the Staging database if the data does not match the expected Infor EPM structure.

To exclude or transform such data in relational tables, we recommend that you use SQL scripts in relational modeling. You can use INSERT, UPDATE, or DELETE statements in your scripts.

Note: You can execute a SQL script against multiple relational tables at a time.

You cannot use SQL scripts to perform transformations in tables of the Integration database because the structure of this database cannot be changed. In the Staging database, you create copies of the relevant tables of the Integration database and perform transformations and data cleansing in Staging.

You can create a matching table structure from Data Lake or a staging source connection. You can create a table from a query that contains a SELECT statement such as:

SELECT * FROM Accounts

See "Adding columns to a staging database table from a query" in Related topics.

When the data is correct, you can use relational modeling load queries to load data from the Staging database to the Integration database.

These options are available for data cleansing:

Automatic data cleansing through Infor API Gateway. When you create an ION workflow in ION, set the clearData option to TRUE. This automatically deletes the table in Staging throughout the workflow before the data load begins. The clearData option is displayed on the Request Parameters tab in ION API Properties.
Data cleansing through Data Lake. In Data Fabric, delete the unwanted data objects and then clear the data:
1. Select Data Lake > Purge, set the purge criteria, and click Purge to delete unwanted data objects.
2. Select Data Lake > Compass and use the Clear Data administrative option for the relevant data object.

See the Data Fabric documentation.