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 for DPx/DiPx instances only :

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 :

UTF8 files

Files including special characters and with UTF-8 encoding are listed in the setting file :

Note : the option Collation must be removed from the fmt 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

Once in the Datahub, data sourced from BW/SalesForce may be controlled in order to guarantee consistency : we talk about integrity controls (IC) which are performed on TEMP tables.

Different types of IC are predefined in Dynasys application.

An IC is always assigned to one table (actually one file), several IC can be set up to monitor 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'

Manual import of csv files

All DYS_BW / DYS_SF files

This manual process allows to refresh all TEMP tables, whatever the creation date of inbound files.

Files part of the exclusion list are ignored.

• Connect to the backend server
• Open a command prompt window as administrator
• Go to D:\DynaSys\ITF folder
• Launch GlobalFilesToSql-AllFiles-woCreateDt.cmd
• Log available in D:\DynaSys\ITF\Logs folder

One or several files

This manual process allows to refresh one (or several) TEMP table(s), whatever the creation date of inbound files.

Files part of the exclusion list are ignored.

• Connect to the backend server
• Open D:\DynaSys\ITF\GlobalFilesToSql-OneFile-woCreateDt.cmd file
• Add the files you want to import

• Close and save D:\DynaSys\ITF\GlobalFilesToSql-OneFile-woCreateDt.cmd file
• Open a command prompt window as administrator
• Go to D:\DynaSys\ITF folder
• Launch GlobalFilesToSql-OneFile-woCreateDt.cmd
• Log available in D:\DynaSys\ITF\Logs folder