4.5 - Import process of csv files

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.