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 in the Staging database by creating a data connection to the BIFarmModel database. The BIFarmModel database contains standard integration tables. When creating a matching staging table, you use this data connection and create a table from a query that contains a SELECT statement such as:

SELECT * FROM dEPM_Account

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.