General presentation

Objective of the application

Provide predictive information on Credit Management.

• Define which payer is a priority based on its history and the due amount.

Tool Leader: David TONDA

IT leader of the application: Guillaume THEVENET

Name of project: PCM Predictive Credit Management

PMO Project: 6958 Big Data for Credit Management

Reporting Coordinator: David TONDA

Usage information

Number of users: tbd

Critical period: none

Geographical perimeter: worldwide

InfoArea:

• All objects are stored in FIAR infoarea: IA_FMCO_FIAR
  
  

Process chain Display Component:

• 134 - PROJECT - PCM
  COMP_PCM
  

History

In Mar 2020, the data flow was updated to PCM 2.0

In 2021, BW change the source system SDK_PCM (SQL) to BigQuery(#4749200)

In Feb 2023, Po2 project requires to segregate data of Eco/Sco, therefore, company master data file is modified to add C_AUTHMA(authorization scope) to the openhub OH_PCM_05

Roles & Access

Aligned on FIAR Roles & Access

Dataflow overview

Summary

The dataflow can be divided in 3 main steps:

1. check that no other run are in progress (a) and then extract data from BW to PCM 
   1. Extract master data in full mode to csv files through openhub
   2. Load FIAR and CAMS data into specific DSO build for PCM project (DBFIAR20 and DBFIAR21),then extract to csv through openhub
   3. Load the data from the file via Talend 
2. treatment on PCM GCP
3. Load full with deletion DPFIAR13 from Big Query with new data source DTS_FI_PCM_01 by PC_FI_PCM_10. SQL server was replaced by Big Query

BW to Talend

DBFIAR20 loading process

Big Query to BW

DPFIAR13 and DPFIAR14  data loading

Report to get back from BigQuery after calculate by Data Science

WIth new version 2.0, BW get only 3 key figures

PRED_NOT_PIM     → K_PFNPI
PROBA_NOT_PIM  → K_PPNPI
AMOUNT_EUR      → 0DEB_CRE_LC

Architecture Overview

Obsolete 

SQL server was replaced by Big Query and after files are generate, they will be loaded by Talend by Data Engineering team

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

The whole process can be summarized has follow:

• Extraction of BW data (master and transactional data) in flat files (csv with "|" separator)
• copy and loading of these file into a SQL database
• Calculation of predictive information on SQL side to produce two table which define Priority per Amount & Priority per Payer
• Loading of these data in BW to be used on standard reporting

Program (a) → Obsolete

(created in SE38)

2 program to update the TVARVC variable Z_BW_PCM_PC_STATUS

The purpose of these program is to avoid launching process chain twice if previous run is still active.

If the process chain is already running, the process chain will fail requiring deeper analysis (normal duration should be less than one hour - only init run should exceed this duration)

Openhub (b)

7 openhub have been created for master data and 2 for transactional data

Updated on 12 Mar 2021: 18 Openhub (2 Transactional and 16 Master data)

All master data openhub are loaded in full mode. All extract data from master data except the TCURR one which is connected to a datasource.

All transactional data openhub are loaded in delta mode. (remark delta is not possible with multiprovider).

All openhub use a "|" separator because some field already contain ";" in the value and openhub doesn't encapsulate data.

All openhub use logical file name defined through FILE transaction. All files are stored in the following folder

KSH script (c) → Obsolete

KSH script have been build by D3S/adagio team.

They are stored in the following folder

pacm-ping.ksh is the initial test for the PCM project (not used anymore).

pacm-reset_db.ksh allow to reset SQL database from BW.

pacm-run.ksh is the script which launch treatment on SQL database side. It generate a log in the following folder: /exploit/TA/tmp/${STAMP}_log_run_$server.log

This scripts awaits two parameter:

• server 
  • WDCPAAP21.eua.solvay.com (in WBP, see OS command for more detail)
  • WDCPAAT21.eua.solvay.com (in WBQ / WBD)
• and user
  • PA_SVC_PRD (in WBP, see OS command for more detail)
  • PA_SVC_TST(in WBQ / WBD)

OS Command (d) → Obsolete

(created in SM49)

The ZPCM_RUN launch a .bat file which load extracted file in the D3S SQL database. It's included in process chain through the following variant:

This variant has been modified directly in WBP to launch treatment on the SQL production server

The ZPCM_RESET_DB reset the database. It's not included in process chain

Status table (e) → Obsolete 

The status table is loaded in DSO DPFIAR12

C_COMMAND should be used to run the "ZPCM_RUN" or the "ZPCM_RESET" depending on the value sent by SQL database. This functionality is not used and by default the process chain always launch the RUN. If the RESET is required, then it needs to be launched manually.

C_CODSTAT can take 3 different value which are checked in the process chain:

• 0: "normal" process
• 1: problem with file generated, the process chain will try to generate file again (only once)
• 2: other error, the process chain will fail and deeper analysis is required

These value are checked in the process chain through "Enhanced decision"

Dependencies with other applications

PCM data are loaded on top of FIAR data.

Data loadings

Info providers and objects loaded

PC_FI_PCM_07 (Main PC) modify by ticket 5915436

  PC_FI_PCM_01 Generate 18 files from Open Hub by job RSPROCESS weekday at 10:00 AM CET

  PC_FI_PCM_11 Same as PC_FI_PCM_01 just in case 01 can't generate files completely

  PC_FI_PCM_10 Delete DSO then load data to DPFIAR13 from BigQuery  and DPFIAR14 from SDK_PCM  job RSPROCESS weekday at 11:00 AM CET

  PC_FI_PCM_V2 generate 1 file:/exploit/BW/PREDICTCM/V2/dbfiar20_<DATE>_<TIME>_delta.csv from Open Hub by job RSPROCESS 3 times( 7am, 1pm , 7pm CET) per weekday

Target folder

Loading frequency

  PC_FI_PCM_01 load by job RSPROCESS workday at 10:00 AM CET

  PC_FI_PCM_10 load by  job RSPROCESS workday at 11:00 AM CET

  PC_FI_PCM_V2 load by  job RSPROCESS workday at 7am, 1pm , 7pm CET

Average performance

if possible, give some information on average process chain duration, amount of data loaded and total data volume example: daily process chain loaded in 30 min, weekly chain loaded in 1h15, with around 2k to 10k lines in DELTA mode for a total of 10M lines in cube. The purpose is to give a general overview of the volume of data managed y the application

Record Keeping

DSO are loaded with full historical data but we only send today - 3 years to SQL server.

Reporting

Queries End User Documentation

Query end user documentation should be created in the public "Customer Support Wiki" space under the corresponding BW application page : BW - Application. Technical query query documentation, if necessary should be added as a sub-page of this documentation using the BW Technical Query Documentation template.

Main queries

List the most important and complex queries only with a link to the documentation

Main functionalities

Give detail on all complex functionalities: list most important and/or complex KPI, query jump, alerts

Broadcast

All Credit Management broadcasts (and associated PCs) were deactivated on Business demand

Maintenance

Known bugs

Give the list and explanation on the known, not-solved, bugs.

Recurring procedure

If the service account:bqtobw@predict-credit-mgt-v2-dev.iam.gserviceaccount.com in Google Cloud Platform is expired, then the configuration of remote source:GBQ_Predict_Credit in BW/HANA should be adapted accordingly.

1. Copy the new JSON file to the folder defined in gbqadapter_prod.properties
2. Change the KeyFilePath to the folder of the new JSON file

Planned Evolution

Detail planned major evolution if already known. Example: complete decommissioning of application is planned in 2017 / Extension to solvay perimeter planned in 2nd semester of 2016