Data Integration

Data Integration

refers to the technical and business processes used to combine data from multiple sources to provide a single, unified view of data

Integration Process

In InnowayIT application you can bring data from multiple source systems by using the following process and methods:

1. Integration Object: A logical representation of InnowayIT object or external object e.g., application. More than one integration objects can be created for an Innowayit base object. For example to integrate some attributes from one source and others from a different source.

2. Integration Object Map: Defines the data attributes of integration object and its properties e.g., field name, key column flag, field data type etc. See fields in table below

3. Integration Job: Defines the type of integration and details such as frequency, source file, source folder etc.

4. Folder: the data flow begins when a batch file is pushed into an InnowayIT folder

5. Integration Service: an integration service detects a new file and executes the relevant Integration Job. An integration Job Instance and an Integration Log is created for each file.

6. Landing Table: data is moved from the file to a landing table based on mapping in the integration object linked to the integration job. If there is a discrepancy in the structure of the source file and Integration Object Map the process is interrupted and exception is raised.

7. Base Table: once data is moved from file to landing table a sub-procedure loads data from landing to base table based on the Integration Object Mapping. Status of each field is updated in the landing table as:

   1. Inserted: for new records, where record matching key column(s) value is not found in the base table

   2. Updated: for existing records, where record matching key column(s) value is found and non-key value have changed

   3. Existing record: for existing record, where record matching key column value is found but there is no change in non-key values

8. Integration Job Instance: created during step 5

9. Integration Log: created during step 5

An Example:

Assume Provider table has two fields: name and website

An integration object, mapping and job is created by integration analyst.

A new provider.csv file arrives with 4 records. When the job runs 8 (4 records x 2 fields) will be inserted into the landing table.

Two records are existing with no change (RED), 1 record is new (GREEN) and 1 record is existing but with a new website address (ORANGE).

Data Integration Constraints

1. Data type constraint - each data point in the source file should match with the data type in the mapping object e.g. VARCHAR(100) should not have a field length above 100, INT should only contain integer

2. Lookup constraints - if the mapping field has a lookup type of lov, the data points in the source file should be within the LOV TYPE of the field.

3. Foreign Key constraint - if the mapping field has a look type of table, the data point in the source file should map to a unique value in a foreign key table, and only the key of the table will be saved in the base table

Integration Map Fields

Following are the fields for integration object map: