Data Import

Dynasys is daily refreshed from BW and SalesForce.

A global and unique process (common to all instances of one company) allows to manage files provided by those two applications. The goal is to :

• select or exclude some csv files
• make data compatible with our Unicode Standard
• import csv files in the Datahub (TEMP tables)
• force basic data in those tables
• apply defined controls to strategic data tables, if necessary revert to a previous snapshot of data

Results of import flows are centralized into DYS_GLOBAL_IO_FLOWS table,  filtered on Flow_type = 'DYN_IN' and Flow_Sub_Type = 'Import_Files' and Activated = '1'

> Flow_Table = name of the inbound file without its extension

> Original_Date = date of the last file imported in U00

> Last_Exec = date of the last file proceed

Here's the algorithm dedicated to inbound csv files :

Read files

• "Files creation dt" check

If current day = Monday, the file must have been generated on Day-1 at the latest

Otherwise, the creation date of the file must be Day 0.

• "File to exclude" check

The setting file D:\DynaSys\Batchs\Settings\dscp_settings.ini on the backend server contains a list of files to exclude.

If the file is part of this list, it won't be processed.

Force "infinity"

The list of files in which infinity values are replaced by zero are defined in the setting file :

Switch to unicode 

Files to convert to Unicode Standard are listed in the setting file :

Status = ?

Once the file is uploaded into its TEMP table, then the related line of DYS_GLOBAL_IO_FLOWS table is updated :

• status = 1
• original_Date = creation date of the file
• Last_exec = current date

If the file is defined in the list of excluded files, then the related line of DYS_GLOBAL_IO_FLOWS table is updated :

• status = 0
• Last_exec = current date

Rework data

This process allows to force some values in TEMP tables or remove lines that are not consistent.

For example :

• Force null values to zero
• Remove line / force value to zero when a numeric field contains a non-numeric value
• Remove lines for which one field contains a specific character

Apply integrity controls

Content of TEMP tables may be controlled to guarantee some consistency : we talk about integrity controls (IC).

Different types of IC are predefined in Dynasys application.

An IC is always assigned to one table, several IC can be set up to check one table.

Four types of IC :

• Volume Min : the number of lines of TEMP table must be greater or equal than a predefined quantity
• Volume Max : the number of lines of TEMP table must be lower or equal than a predefined quantity
• Sum by criteria : sum(<criteria>) of TEMP table must be equal to sum(<criteria>) of BCKUP table
• % gap by GBU : the ratio between sum(quantity) of TEMP table and sum(quantity) of BCKUP table must lower than a predefined percentage, by GBU and within a predefined period

If all IC assigned to one table are verified, then this TEMP table is copied into its "twin" BCKUP table.

Otherwise, the TEMP table is refreshed from BCKUP table.

Integrity controls are defined into DYS_GLOBAL_IO_FLOWS table,  filtered on Flow_type = 'DYN_IN_IC' and Activated = '1'