OnPremise History Data Migration & Management

The history data for each entity (Customer, Concern, Contact & CustomerFacility) needs to be migrated from on-prem to cloud in order to show the history of changes to the respective entity data. The history will be displayed for every entity between From date to To date.

Displaying History Data in UI (Portal):

The below screen shot shows, how the customer entity history details will be displayed in UI for particular customer.

The below screen shot shows, how the contact entity history details will be displayed in UI for particular contact.

The below screen shot shows, how the concern entity history details will be displayed in UI for particular concern.

The below screen shot shows, how the customer facility entity history details will be displayed in UI for particular customer facility.

Migrating History Data from On-Prem to Cloud:

As part of the data migration process from on-premises to the cloud, it is essential to also migrate historical data. Below are two proposed options for this migration.

Option 1:

Step 1: Identify the columns of historical data to be migrated from on-premises to the cloud and obtain approval from the business (Sneha).

Sl.No	Customer Entity Tables	Fields
1	C_PTR_PARTY_RL	RL_TYP_CD, PARTY_RL_NM, PREFD_COMM_LANG_CD, PTR_UID, IS_ACTV_IND, CTRY_ROWID, ISO_LANG_ROWID, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
2	C_PTR_CUST	PTR_STS_RSN_CD, PTR_CUST_TYP_CD, PTR_CUST_STAT_CD, FORCOM, BROK, ACC_GRP_CD, URL, INTL_DIALNG_ROWID_PH, PH_NUM, COMM_TYP, CUST_GRP_TYP, VIP_NON_ROLL, NMB_FLAG, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
3	C_PTR_ALT_CODES	TYP_TYPE_CD, CODE, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
4	C_TAX_NUM	TAX_NUM_TYP_CD, TAX_NUM, CTRY_ROW_ID, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
5	C_TAX_INFO	IS_NATUL_PRSN_TAX, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
6	C_PTR_REF_NUM	REF_TYPE_CD, REF_NUM, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
7	C_PTR_WEB_BL	PARTY_BRAND_ROWID, NON_WBL_FLAG, WBL_FLAG, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
8	C_PTR_FIN_INFO	BVD_MJR_SCTR, MKT_CPTL, OPN_RVN_TRN_OVR, DT_ACC, PRFT_MRGN_PCT, CR_RTNG, RECMD_CR_LMT, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
9	C_PTR_REL	PRNT_RL_ROWID, CHLD_RL_ROWID, PARTY_RL_REL_TYP_CD, PARTY_RL_REL_DESC, VALID_FROM_DT, VALID_THRU_DT, REL_TYPE_CODE, IS_MASTER_CONTACT, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
10	C_PTR_SEG	PTR_REL_ROWID, PTR_REL_CLSSN_CD, IS_ACTIVE_IND, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
11	C_CTM_PSTL_ADDR	PO_BOX, STREET, HOUSE_NUM, ADDR_LN_2, ADDR_LN_3, DSTRCT, CITY_ROWID, PSTCD, TRTY_ROWID, CTRY_ROWID, CITY, LAT_GEOSPTL, LNG_GEOSPTL, PSTCD_ROWID, ADDRESS_COORDINATES_TYPE, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
12	C_PTR_NAME_ALIAS	ALIAS_NAME, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM

Sl.No	Contact Entity Tables	Fields
1	C_PTR_PARTY_RL	RL_TYP_CD, PARTY_RL_NM, IS_ACTV_IND, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
2	C_PTR_CONT	FRST_NM, LST_NM, PRIM_SALTN_CD, JOB_TITLE, DEPT, PH_NUM, EXTN, MOB_NUM, FAX, INTL_DIALNG_ROWID_PH, INTL_DIALNG_ROWID_MOB, INTL_DIALNG_ROWID_FAX, GRP_CONT_IND, INTL_SALTN_ROWID, PRMRY_EMAIL, MOB_VALIDN_REQ_TS, SCDRY_EMAIL, MOB_VALIDN_REQ_ID, INTL_FRST_NM, INTL_LST_NM, PRFD_COMM_LANG_CD, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
3	C_PTR_ALT_CODES	TYP_TYPE_CD, CODE, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
4	C_PTR_CONT_TYPE	CONT_TYP_TYPE_CD, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
5	C_PTR_CONT_BRANDS	CONT_BRAND_CD, VALID_FROM_DT, VALID_THRU_DT, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
6	C_PTR_REL	PRNT_RL_ROWID, CHLD_RL_ROWID, PARTY_RL_REL_TYP_CD, PARTY_RL_REL_DESC, VALID_FROM_DT, VALID_THRU_DT, REL_TYPE_CODE, IS_MASTER_CONTACT, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM
7	C_STDG_COMM_PREFD	PTR_PARTY_RL_ROWID, DOC_TYP_ROWID, STDG_COMM_PREFD_STAT, PTR_CONT_RL_ROWID, OTH_COMM_DTL, PTR_BRAND_RL_ROWID, COMM_PRPS_TYP_ROWID, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, LAST_ROWID_SYSTEM

Sl.No	Cust Facility Entity Table	Fields
1	C_FCT_FACILITY	FACILITY_NAME,EXT_OWNED,CLASS_CD,STATUS_CD,EXT_EXPOSED,DODAAC,URL,create_date,creator,last_update_date,updated_by,last_rowid_system
2	C_FCT_ALT_CODES	TYP_TYPE_CD, CODE,CREATE_DATE,CREATOR,LAST_UPDATE_DATE,UPDATED_BY,LAST_ROWID_SYSTEM
3	C_FCT_ADDR_REL
4	C_CTM_PSTL_ADDR	PO_BOX,STREET,HOUSE_NUM,ADDR_LN_2,ADDR_LN_3,DSTRCT,PSTCD,CITY,LAT_GEOSPTL,LNG_GEOSPTL,CREATE_DATE,CREATOR,LAST_UPDATE_DATE,UPDATED_BY,LAST_ROWID_SYSTEM
5	C_PTR_CUST_FCT_REL	RELATIONSHIP_TYPE,VALID_FROM_DATE,VALID_THRU_DATE,CREATE_DATE,CREATOR,LAST_UPDATE_DATE,UPDATED_BY

Sl.No	Concern Entity Table	Fields
1	C_PTR_PARTY_RL	RL_TYP_CD,PARTY_RL_NM, PREFD_COMM_LANG_CD,PTR_UID,IS_ACTV_IND, CTRY_ROWID,ISO_LANG_ROWID,CREATE_DATE,CREATOR,LAST_UPDATE_DATE,UPDATED_BY,LAST_ROWID_SYSTEM
2	C_PTR_ALT_CODES	TYP_TYPE_CD, CODE,CREATE_DATE,CREATOR,LAST_UPDATE_DATE,UPDATED_BY,LAST_ROWID_SYSTEM

Note: The attribute list may vary based on business need.

Step 2: Load the on-prem history table to cloud corresponding temporary table (structure should be same)

Example: On-prem C_PTR_CUST_HIST --> postgresql (C_PTR_CUST_HIST)

Step 3: Query from cloud history temporary tables to identify only the changed attributes/records and load into actual corresponding history table.

Step 4: When there is request to display history in UI based on customer code, based on that customer code get the records from the relevant tables and display as mentioned in above section.

Step 5: While displaying the data in UI, when user click on particular date (event) then we need to display the corresponding before value and after value.

Option2:

Step 1: Identify columns of history to be migrated from on-prem to cloud and get approval from business (Sneha) Step 2: Load the on-prem history table to cloud corresponding temporary table (structure should be same)

Example: On-prem C_PTR_CUST_HIST --> postgresql (C_PTR_CUST_HIST)

Step 3: Query from cloud history temporary tables to identify only the changed attributes/records and required columns into denromalized table (table which will all required columns) and load the data. Step 4: Create elastic index and load snapshot of history data from denromalized table to elastic index. Multiple records (belongs to same customer) from denromalized table will be merged into single JSON object.

Step 5: When there is request to display history in UI based on customer code, based on that customer code get the records from the relevant tables and display as mentioned in above section.

Step 6: While displaying the data in UI, when user click on particular date (event) then we need to display the corresponding before value and after value.

Maintaining Modernization History data:

Once the on-prem system is decommissioned and customer onboarding starts on cloud platform, the ingestAPI or PG to elastic search sync service need to insert the history (event data) into the corresponding base table History tables.

On modern platform we need to create for each corresponding base table the history table with same structure along with customer code as identifier.

There are 3 options proposed to maintain and display the History data:

Option 1:

1. Create separate history table for each base table on cloud platform, the structure should be same and every table should have the entity code as identifier column.
2. When there is an create/update event on any table(parent or child) insert a record into the corresponding history tables.
3. When there is request to display history in UI based on customer code, based on that customer code get the records from the relevant tables and display as mentioned in above section.
4. While displaying the data in UI, when user click on particular date (event) then we need to display the corresponding before value and after value.

Option 2:

1. Create one denormalized table for each entity like CUSTOMER_HISTORY_DETAILS table for customer
2. When there is an create/update event on any table(parent or child) insert a record into denormalized history table.
3. Create elastic index and load snapshot of history data from denromalized table to elastic index. Multiple records (belongs to same customer) from denromalized table will be merged into single JSON object.
4. When there is request to display history in UI based on customer code, based on that customer code get the records from the relevant tables and display as mentioned in above section.
5. While displaying the data in UI, when user click on particular date (event) then we need to display the corresponding before value and after value.

Option 3:

1. User the elastic DB history capability to maintain and display history data in UI.
2. On every create/update event in PD DB will be synced to elastic DB, and elastic DB automatically will track/maintain the history records. Note: we need to confirm this from Elastic.

Based on how complex it is to consume and display logic in UI, we need to go with option 1 or 2. Below table gives the pros and cons of both options.

Option	Pros	Cons
Option 1	The history data will be in relational DB, so retrieval point of view it is easy Specific section data can be retrieved easily	Complexity of retrieval logic?
Option 2	Data will be maintained in JSON objectsRetrieval of data is easy	Complexity of retrieval logic?Difficult to retrieve specific section data to display
Option 3	No need to maintain the history data in relational PG DB.Retrieval of History data will be easy using elastic API	Need to understand elastic capability on History data maintenance.

Suggested solution:

1. For on-prem data
   1. Migrate the on-prem history data to PG relational DB
   2. Read and display as per the requirement in portal
2. For modern platform (once on-prem is decommissioned)
   1. Persist the history data in PG DB (2 columns table - code & JSONObject) or in Elastic DB in index. Possible recommended formats are given below.
   2. Read and display as per the requirement in portal

Sample data formats:

Below is the sample JSON format used for on-prem history data if we are going with maintaining history data in elastic DB.

{
 "CustomerHistoryEntity":
 {
 "customerCode":"IN123456",
 "CustomerInformation":\[
 {
 "Date":"",
 "PartyRoleType":"",
 "PartyName":"",
 "PreferredCommunicationLang":"",
 "PartyStatus":"",
 "Country":"",
 "invoiceISOLanguageCodePreference":""
 },
 {
 "Date":"",
 "PartyRoleType":"",
 "PartyName":"",
 "PreferredCommunicationLang":"",
 "PartyStatus":"",
 "Country":"",
 "invoiceISOLanguageCodePreference":""
 }
 \],
 "CustomerDetails": \[
 {
 "Date":"",
 "CustomerStatusCode":"",
 "CustomerStatusReasonCode":"",
 "CustomerType":"",
 "ForwardersCompensation":"",
 "Brokerage":"",
 "CustomerGroupType":"",
 "url":"",
 "internationalDialingCode":"",
 "phoneNumber":"",
 "CommunicationType":"",
 "vipNonRoll":"",
 "nmbFlag":""
 },
 {
 "Date":"",
 "CustomerStatusCode":"",
 "CustomerStatusReasonCode":"",
 "CustomerType":"",
 "ForwardersCompensation":"",
 "Brokerage":"",
 "CustomerGroupType":"",
 "url":"",
 "internationalDialingCode":"",
 "phoneNumber":"",
 "CommunicationType":"",
 "vipNonRoll":"",
 "nmbFlag":""
 }
 \],
 "CustomerAlternateCodes": \[
 {
 "Date":"",
 "AlternateType":"",
 "AlternateCode":""
 },
 {
 "Date":"",
 "AlternateType":"",
 "AlternateCode":""
 }
 \],
 "CustomerTax": \[
 {
 "Date":"",
 "TaxNumberType":"",
 "TaxNumber":"",
 "TaxCountry":""
 },
 {
 "Date":"",
 "TaxNumberType":"",
 "TaxNumber":"",
 "TaxCountry":""
 }
 \],
 "CustomerReferenceNumbers": \[
 {
 "Date":"",
 "ReferenceTypeCd":"",
 "ReferenceNumber":""
 },
 {
 "Date":"",
 "ReferenceTypeCd":"",
 "ReferenceNumber":""
 }
 \]
 }
}

Below is the sample JSON format used for modernization history data if we are going with maintaining history data in elastic DB.

{
 "CustomerEntityHistory":
 \[
 {
 "date":"20-10-2021",
 "CustomerInformation":
 {
 "name":
 {
 "before":"",
 "after":""
 },
 "url":
 {
 "before":"",
 "after":""
 }
 },
 "TaxRefInformation":
 {
 "refType":
 {
 "before":"",
 "after":""
 },
 "refCode":
 {
 "before":"",
 "after":""
 },
 "refCountryCode":
 {
 "before":"",
 "after":""
 }
 },
 "CustomerBrands":
 {
 "brandId":
 {
 "before":"",
 "after":""
 }
 },
 "CustomerRelationships":
 {
 "relationshipType":
 {
 "before":"",
 "after":""
 },
 "parentCustomer":
 {
 "before":"",
 "after":""
 },
 "childCustomer":
 {
 "before":"",
 "after":""
 },
 "validFromDate":
 {
 "before":"",
 "after":""
 },
 "validThroughDate":
 {
 "before":"",
 "after":""
 }
 },
 "CustomerCBUDetails":
 {
 "brandId":
 {
 "before":"",
 "after":""
 },
 "cbuId":
 {
 "before":"",
 "after":""
 },
 "cbuType":
 {
 "before":"",
 "after":""
 },
 "validFromDate":
 {
 "before":"",
 "after":""
 },
 "validThroughDate":
 {
 "before":"",
 "after":""
 }
 }
 },

{
 "date":"20-10-2022",
 "CustomerInformation":
 {
 "name":
 {
 "before":"",
 "after":""
 },
 "url":
 {
 "before":"",
 "after":""
 }
 },
 "TaxRefInformation":
 {
 "refType":
 {
 "before":"",
 "after":""
 },
 "refCode":
 {
 "before":"",
 "after":""
 },
 "refCountryCode":
 {
 "before":"",
 "after":""
 }
 },
 "CustomerBrands":
 {
 "brandId":
 {
 "before":"",
 "after":""
 }
 },
 "CustomerRelationships":
 {
 "relationshipType":
 {
 "before":"",
 "after":""
 },
 "parentCustomer":
 {
 "before":"",
 "after":""
 },
 "childCustomer":
 {
 "before":"",
 "after":""
 },
 "validFromDate":
 {
 "before":"",
 "after":""
 },
 "validThroughDate":
 {
 "before":"",
 "after":""
 }
 },
 "CustomerCBUDetails":
 {
 "brandId":
 {
 "before":"",
 "after":""
 },
 "cbuId":
 {
 "before":"",
 "after":""
 },
 "cbuType":
 {
 "before":"",
 "after":""
 },
 "validFromDate":
 {
 "before":"",
 "after":""
 },
 "validThroughDate":
 {
 "before":"",
 "after":""
 }
 }
 }

 \]
}