GSC Dashboard - 4. Data Loading with Talend

• F100_GSC_DATA_EXTRACT

•  J130_mainExtract_Files_Src_to_GCS

•  J120_GSC_mainExtract_BWQ_Data

•  J120_GSC_Extract_BW_data_Source_14_EXT

•  J110_GSC_mainExtract_Gsheet_data

1. In the job F100_PUR_DATA_EXTRACT:
   1. A parallel execution of all data sources is launched

The flow triggers the extraction of all sources needed for the Global Supply chain dashboard.

•  J110_GSC_mainExtract_Gsheet_data

1. Parallel extraction of all Google Sheet sources:
   1. A parallel extraction of all Google Sheet is launched
   2. Simple transformation are done to align with specifications
2. Upload files to GCS
   1. The files are uploaded into the GSC folder of the target bucket

•  J130_GSC_mainExtract_Files_Src_to_GCS

1. Filename creation:
   1. A specific filename following our standard naming convention is created to properly upload files into Cloud Storage
2. Parallel GCS upload
   
   1. Each Google Sheet table is read
   2. Simple transformation are done to align with specifications resulting in a CSV stored in the Tmp folder
   3. Each file is uploaded into the GSC folder of the target bucket

•  J120_GSC_Extract_BW_data_Source_14_EXT

1. Login to Salesforce:
   1. The job starts by establishing a connection to Salesforce using the account sbs-is-appli-qlikview.support@solvay.com.crm
2. Extraction of the Salesforce objects
   
   1. The list of Salesforce objects is extracted in sequence
   2. Depending on the source some mappings can be applied
   3. Each source is uploaded to GCS as a CSV file

•  J120_GSC_mainExtract_BWQ_Data_Source

1. Parellel execution of Xtract jobs:
   1. The job triggers in parallel all the jobs needed to extract data from BW.

• F200_GSC_Gcs_To_Stg
• J200_GSC_Gcs_To_Stg

1. First the job retrieves the value of the Business date from the file business_date.csv file. This is to target only the files of the current execution in GCS.

2. The flow is used to call the job J200_GSC_Gcs_To_Stg.

1. The job launches a parallel load per source type into the Staging tables
   1. A list of the possible CSV file with the associated Staging table name, and separator is used to create a sequence
   2. Each staging table is truncated before a new upload
   3. The job gets all the files starting with the desired prefix and for each of them calls the standard job J001_GCS_to_STAGING_LOCAL

• F300_GSC_STG_TO_ODS

• J300_GSC_STG_TO_ODS

1. The flow is used to call the job J300_GSC_STG_TO_ODS

1. Parallel loading of the ODS table by source type
   1. A parallel loading of ODS table is done by source type (BW, Google Sheet, Salesforce)
2. Load in sequence all ODS tables of a source
   1. Given a source type the list of staging and ODS tables is hardcoded.
   2. ODS table is truncated
   3. Th standard job J001_STAGING_TO_ODS is called to load the ODS table

Given the fact that staging and ods are identical and tables are always truncated, this step is unnecessary.

• F400_GSC_ODS_to_Prep_DM_Files

  F400_PURCHASING_STEP
  • F410_GSC_ODS_to_DTM
  • F420_GSC_ODS_to_DTM_Part_1
  • F420_GSC_ODS_to_DTM_Part_5
  • F420_GSC_ODS_to_DTM_Part_6
  • F430_GSC_ODS_to_DTM

1. This job is used to call a series of subjob that creates files needed to create the dimensions and fact tables

• F410_GCS_ODS_to_DTM

The job computes steps 19-28 of the specification document

1. The job reads data from the ODS table and apply the necessary mappings;
2. The job output PIVOT and FACT files which are stored on the remote engine and will be uploaded to to the DM in the J500 jobs.
3. The jobs creates the table FACT_gsc_target

• F430_GCS_ODS_to_DTM

The job computes steps 41-49 of the specification document

1. The job reads data from the ODS table and apply the necessary mappings;
2. The job output PIVOT and FACT files which are stored on the remote engine and will be uploaded to to the DM in the J500 jobs.
3. The job also builds the files necessary to create the airfreight KPI following these specifications

• F420_GSC_ODS_to_DTM_PART_1

The job computes steps 33,34, and 35-49 of the specification document

1. The job reads data from the ODS table and apply the necessary mappings;
2. The job output PIVOT and FACT files which are stored on the remote engine and will be uploaded to to the DM in the J500 jobs.

• F420_GSC_ODS_to_DTM_PART_5

The job computes steps 33,34, and 60-69 of the specification document

1. The job reads data from the ODS table and apply the necessary mappings;
2. The job output PIVOT and FACT files which are stored on the remote engine and will be uploaded to to the DM in the J500 jobs.

The job uses the tUnpivotRow component (you can find it here).

• F420_GSC_ODS_to_DTM_PART_6

The job creates the table FACT_POR which is displayed by the Perfect Order rate dashbaord.

1. The job reads data from the ODS table and apply the necessary mappings. The main ODS tables are:
   1. ODS_0000_F025_F_W_GSC_MVSDSO45_SD0001
   2. ODS_0000_F026_F_W_GSC_Mvsdso51_0001
   3. ODS_0000_F020_F_W_GSC_Sf_Complaint_Case
2. Each step outputs a FACT file which is stored in the Tmp folder
3. The files are stacked to create a unique output bulk file which is used to create the FACT_POR table.

• F501_GSC_Files_To_FACT

• 501_GSC_ODS_to_DTM_FACT

This jobs is used to call the job J501_GSC_ODS_to_DTM_FACT

1. First the job truncates the table DM.FACT_gsc_tmp
2. The job reads all files whose name starts with FACT_FILE inside the InOut folder and uploads them into the FACT_gsc_tmp table. If files is successfully inserted the local file is deleted from the remote engine
3. The job truncates the table DM.FACT_gsc
4. The job copies data from FACT_gsc_tmp into FACT_gsc
5. The job truncates the table DM.FACT_gsc_tmp

• J500_PURCHASING_PIVOT_TO_DTM

1. The job starts by truncating the table BDG_purchasing_tmp
2. Read Pivot files:
   • The job iterqates over all files of the Pivot folder (whose name starts with PIVOT_TABLE_ ).
   • Each file is processed and a lookup is done with the two following Google Sheets to get a uniform GBU text.
   • An BigQueryBulkOutput file is created
3. Upload data in the BDG_purchasing_tmp table:
   1. The job upload all bulk files into Cloud Storage and each file is appended into the BDG_purchasing_tmp table
4. The job truncates the table BDG_purchasing.
5. . The job inserts copies data into from the BDG_purchasing_tmp table. During the copy the columns mont_year and vc_year are converted into string.

• J510_PURCHASING_FACT_TO_DTM

1. The job starts by truncating the table FACT_purchasing_tmp
2. Read Pivot files:
   • The job iterqates over all files of the Pivot folder (whose name starts with FACT_TABLE_ ).
   • Each file is processed to have the desired schema of the fact table
   • An BigQueryBulkOutput file is created
3. Upload data in the FACT_purchasing_tmp table:
   1. The job upload all bulk files into Cloud Storage and each file is appended into the FACT_purchasing_tmp table
4. The job truncates the table FACT_purchasing.
5. . The job inserts copies data into from the FACT_purchasing_tmp table

• J520_PURCHASING_SPEND_TO_DTM

1. Read SPEND_TARGET_1 file:
   • The job reads the file SPEND_TARGET_1.csv in the folder Spend_Targe
   • A BigQueryBulkOutput file is created
2. Upload the file bulk file in Google Cloud Storage
3. The job uses the bulk file to overwrite the values in the table DIM_spend_target.