Data hydration for Microsoft Cloud for Financial Services
This article lists all tables and essential fields that need to be hydrated with data for the first-party applications to operate.
Unified customer profile tables
You need to hydrate the following tables for the Unified customer profile application to operate. Follow the sequence of hydration of each table to avoid failures because of referential integrity.
| Data Category | Type of Data | Table | Sequence of hydration |
|---|---|---|---|
| Master | Bank data | Bank | 1 |
| Master | Bank data | Branch | 2 |
| Master | Customer data | Contact / Account | 3 |
| Master | Life event and goal data | Life event | 4 |
| Master | Life event and goal data | Financial goal | 5 |
| Master | Customer relationship data | Group | 6 |
| Master | Customer relationship data | Group member | 7 |
| Master | Customer relationship data | Relationship | 8 |
| Master | Currency data | Currency | 9 |
| Master | Financial product data | Financial market product | 10 |
| Transaction | Financial holding data | FH account/FH saving/ FH loan/ FH investment/ FH line of credit | 11 |
| Transaction | Financial holding data | Financial Holding (one record corresponding to each FH subtype) | 12 |
| Transaction | Financial holding instrument data | FI card/ FI direct debit/ FI overdraft/ FI position / FI standing order | 13 |
| Transaction | Financial Holding instrument (one record corresponding to each FI subtype) | 14 | |
| Transaction | Financial Holding (one record corresponding to each FI record) | 15 | |
| Relationship | Financial holding relationships | Customer financial holding (FH record is related either to customer financial holding or group financial holding) | 16 |
| Relationship | Financial holding relationships | Group financial holding (FH record is related either to customer financial holding or group financial holding) | 17 |
Note
Contact, Account, Group and Group Member tables have circular relationship with some other tables in the data model. You need to adjust of sequence the data hydration based on dependencies.
Unified customer profile fields
The following table enumerates all essential fields that need to be populated with data for their respective tables.
| Table | Fields to hydrate (in order of sequence) |
|---|---|
| Bank | msfsi_integrationkey |
| Bank | msfsi_name |
| Branch | msfsi_integrationkey |
| Branch | msfsi_name |
| Branch | msfsi_bank |
| Contact | msfsi_integrationkey |
| Contact | fullname |
| Contact | governmentid |
| Contact | telephone1 |
| Contact | emailaddress1 |
| Contact | address1_composite |
| Contact | jobtitle |
| Contact | annualincome |
| Contact | msfsi_branch |
| Contact | msfsi_ismanagedbybanksystem |
| Life event (optional) | msfsi_name |
| Life event (optional) | msfsi_lifemomenttype |
| Life event (optional) | msfsi_lifemomentcategory |
| Life event (optional) | msfsi_lifemomentdate |
| Life event (optional) | msfsi_contact |
| Financial goal(optional) | msfsi_name |
| Financial goal(optional) | msfsi_iscompleted |
| Financial goal(optional) | msfsi_targetdate |
| Financial goal(optional) | msfsi_targetvaluedefault |
| Financial goal(optional) | msfsi_customer |
| Group (optional) | msfsi_Name |
| Group (optional) | msfsi_Type |
| Group member (not optional if Group is there) | msfsi_name |
| Group member (not optional if Group is there) | msfsi_group |
| Group member (not optional if Group is there) | msfsi_member |
| Group member (not optional if Group is there) | msfsi_role |
| Relationship (optional) | msfsi_contactfrom |
| Relationship (optional) | msfsi_contactto |
| Relationship (optional) | msfsi_relationshiptype |
| Currency | currencyname |
| Currency | ISOcurrencycode |
| Currency | currencysymbol |
| Currency | currencyprecision |
| Currency | exchangerate |
| FH Account | msfsi_integrationkey |
| FH Account | msfsi_name |
| FH Account | msfsi_accountingclassification |
| FH Account | msfsi_financialholdingcategory |
| FH Account | msfsi_financialholdingtype |
| FH Account | transactioncurrencyid |
| FH Account | msfsi_balance |
| FH Account | msfsi_balancedefault |
| FH Account | msfsi_BalanceExchangerate |
| FH Account | msfsi_BalanceDisplayValue |
| FH Account | msfsi_AverageBalance |
| FH Account | msfsi_availablebalance |
| FH Account | msfsi_UnclearedBalance |
| FH Account | msfsi_numberoftransactions |
| FH Account | msfsi_blockedamount |
| FH Account | msfsi_interestrate |
| FH Account | msfsi_interesttype |
| FH Loan | msfsi_integrationkey |
| FH Loan | msfsi_name |
| FH Loan | msfsi_accountingclassification |
| FH Loan | transactioncurrencyid |
| FH Loan | msfsi_balance |
| FH Loan | msfsi_BalanceDefault |
| FH Loan | msfsi_CollectionRisk |
| FH Loan | msfsi_principalamount |
| FH Loan | msfsi_principalamount_default |
| FH Loan | msfsi_lastpaymentdate |
| FH Loan | msfsi_lastpaymentamount |
| FH Loan | msfsi_lastpaymentamount_default |
| FH Loan | msfsi_nextpaymentdate |
| FH Loan | msfsi_nextpaymentamount |
| FH Loan | msfsi_nextpaymentamount_default |
| FH Loan | msfsi_installmentamount |
| FH Loan | msfsi_numberofinstallmentspaid |
| FH Loan | msfsi_loanstartdate |
| FH Loan | msfsi_maturitydate |
| FH Loan | msfsi_TotalInterestPaid |
| FH Loan | msfsi_DisbursedAmount |
| FH Loan | msfsi_DisbursedAmount_default |
| FH Loan | msfsi_DisbursementDate |
| FH Loan | msfsi_interestrate |
| FH Loan | msfsi_interesttype |
| FH Loan | msfsi_interestreviewperiod |
| FH Loan | msfsi_nextinterestreviewdate |
| FH Loan | msfsi_modeofpayment |
| FH Loan | msfsi_repaymentaccount |
| FH Loan | msfsi_capitalarrears |
| FH Loan | msfsi_capitalarrears_default |
| FH Loan | msfsi_interestarrears |
| FH Loan | msfsi_interestarrears_default |
| FH Loan | msfsi_feearrears |
| FH Loan | msfsi_OverdueDate |
| FH Line of Credit | msfsi_integrationkey |
| FH Line of Credit | msfsi_name |
| FH Line of Credit | msfsi_accountingclassification |
| FH Line of Credit | msfsi_financialholdingcategory |
| FH Line of Credit | msfsi_financialholdingtype |
| FH Line of Credit | msfsi_balance |
| FH Line of Credit | msfsi_balancedefault |
| FH Line of Credit | msfsi_balanceexchangerate |
| FH Line of Credit | msfsi_laststatementbalance |
| FH Line of Credit | msfsi_laststatementbalance_default |
| FH Line of Credit | msfsi_nextpaymentdate |
| FH Line of Credit | msfsi_monthlypaymentpercentage |
| FH Line of Credit | msfsi_creditlimit |
| FH Line of Credit | msfsi_creditlimit_default |
| FH Line of Credit | msfsi_lastpaymentduedate |
| FH Line of Credit | msfsi_laststatementdate |
| FH Line of Credit | msfsi_nextstatementdate |
| FH Line of Credit | msfsi_minimumpaymentdue |
| FH Line of Credit | msfsi_minimumpaymentdue_default |
| FH Line of Credit | msfsi_interestrate |
| FH Savings | msfsi_integrationkey |
| FH Savings | msfsi_name |
| FH Savings | msfsi_accountingclassification |
| FH Savings | msfsi_financialholdingcategory |
| FH Savings | msfsi_financialholdingtype |
| FH Savings | msfsi_balance |
| FH Savings | msfsi_balancedefault |
| FH Savings | msfsi_balanceexchangerate |
| FH Savings | msfsi_maturitydate |
| FH Savings | msfsi_accruedinterest |
| FH Savings | msfsi_accruedinterest_default |
| FH Savings | msfsi_projectedinterestamount |
| FH Savings | msfsi_projectedinterestamount_default |
| FH Savings | msfsi_balanceatmaturity |
| FH Savings | msfsi_balanceatmaturity_default |
| FH Savings | msfsi_initialsource |
| FH Savings | msfsi_maturityinstructionsdetails |
| FH Savings | msfsi_term |
| FH Savings | msfsi_interestrate |
| FH Savings | msfsi_blockedamount |
| FH Savings | msfsi_blockedamount_default |
| FH Investment | msfsi_integrationkey |
| FH Investment | msfsi_name |
| FH Investment | msfsi_portfolioname |
| FH Investment | msfsi_accountingclassification |
| FH Investment | msfsi_financialholdingcategory |
| FH Investment | msfsi_financialholdingtype |
| FH Investment | msfsi_balance |
| FH Investment | msfsi_balancedefault |
| FH Investment | msfsi_balanceexchangerate |
| FH Investment | msfsi_openeddate |
| FH Investment | msfsi_cashbalance |
| FH Investment | msfsi_cashbalance_default |
| FH Investment | msfsi_gainloss |
| FH Investment | msfsi_gainloss_default |
| FH Investment | msfsi_performance1y |
| FH Investment | msfsi_performance3y |
| FH Investment | msfsi_performanceytd |
| FH Investment | msfsi_performance |
| FH Investment | msfsi_balanceexchangerate |
| Financial Holding | msfsi_integrationkey |
| Financial Holding | msfsi_name |
| Financial Holding | msfsi_accountingclassification |
| Financial Holding | msfsi_financialholdingcategory |
| Financial Holding | msfsi_financialholdingtype |
| Financial Holding | msfsi_financialholdingcode |
| Financial Holding | msfsi_details |
| FI Card | msfsi_integrationkey |
| FI Card | msfsi_name |
| FI Card | msfsi_financialinstrumenttype |
| FI Card | msfsi_cardnetwork |
| FI Card | msfsi_status |
| FI Card | msfsi_cardtype |
| FI Card | msfsi_productname |
| FI Card | msfsi_embossingname |
| FI Card | msfsi_cardnumber |
| FI Card | msfsi_issuedate |
| FI Card | msfsi_expirydate |
| FI Card | msfsi_activationdate |
| FI Card | msfsi_purchasinglimit |
| FI Card | msfsi_purchasinglimit_default |
| FI Card | msfsi_withdrawallimit |
| FI Card | msfsi_withdrawallimit_default |
| FI Card | exchangerate |
| FI Card | msfsi_numberoftransactions |
| FI Card | msfsi_numberofcashwithdrawal |
| FI direct debit | msfsi_integrationkey |
| FI direct debit | msfsi_name |
| FI direct debit | msfsi_financialinstrumenttype |
| FI direct debit | msfsi_creditoridentifier |
| FI direct debit | msfsi_creditorname |
| FI direct debit | msfsi_mandateid |
| FI direct debit | msfsi_debtoraccount |
| FI direct debit | msfsi_mandatelimit |
| FI direct debit | msfsi_mandatelimit_default |
| FI direct debit | exchangerate |
| FI direct debit | msfsi_mandatestartdate |
| FI direct debit | msfsi_mandateenddate |
| FI direct debit | msfsi_nextitemdate |
| FI direct debit | msfsi_nextitemamount |
| FI direct debit | msfsi_lastitemdate |
| FI direct debit | msfsi_lastitemamount |
| FI direct debit | msfsi_lastitemamount_default |
| FI direct debit | msfsi_lastitemstatus |
| FI direct debit | msfsi_lastitemstatusreason |
| FI overdraft | msfsi_integrationkey |
| FI overdraft | msfsi_name |
| FI overdraft | msfsi_financialinstrumenttype |
| FI overdraft | msfsi_overdraftlimitused |
| FI overdraft | msfsi_overdraftlimitused_default |
| FI overdraft | msfsi_overdraftlimit |
| FI overdraft | msfsi_overdraftlimit_default |
| FI overdraft | msfsi_overdraftrate |
| FI overdraft | exchangerate |
| FI standing order | msfsi_integrationkey |
| FI standing order | msfsi_name |
| FI standing order | msfsi_financialinstrumenttype |
| FI standing order | msfsi_creditorname |
| FI standing order | msfsi_frequency |
| FI standing order | msfsi_debtoraccount |
| FI standing order | msfsi_standingorderamount |
| FI standing order | msfsi_firstpaymentdate |
| FI standing order | msfsi_dayofmonth |
| FI standing order | msfsi_dayofweek |
| FI standing order | msfsi_nextitemdate |
| FI standing order | msfsi_nextitemamount |
| FI standing order | msfsi_lastitemdate |
| FI standing order | msfsi_lastitemamount |
| FI standing order | msfsi_lastitemamount_default |
| FI standing order | msfsi_lastitemstatus |
| FI standing order | msfsi_lastitemstatusreason |
| FI standing order | exchangerate |
| Financial Holding instrument | msfsi_integrationkey |
| Financial Holding instrument | msfsi_name |
| Financial Holding instrument | msfsi_financialholdinginstrumenttype |
| Financial Holding instrument | msfsi_financialinstrumentcode |
| Financial Holding instrument | msfsi_details |
| Financial Holding instrument | msfsi_financialholding |
| Customer Financial Holding | msfsi_name |
| Customer Financial Holding | msfsi_customerid |
| Customer Financial Holding | msfsi_financialholdingid |
| Customer Financial Holding | msfsi_financialholdingrole |
| Group Financial Holding | msfsi_name |
| Group Financial Holding | msfsi_group |
| Group Financial Holding | msfsi_financialholding |
Unified client profile
Unified client profile includes some additional data elements for wealth management. You need to hydrate the following additional tables for the Unified client profile application to operate. Follow the sequence of hydration of each table to avoid failures because of referential integrity.
| Data category | Type of data | Table | Sequence of hydration |
|---|---|---|---|
| Master | Financial product data | Financial market product | 1 |
| Transaction | Financial holding data | FH Investment | 2 |
| Transaction | Financial holding data | Financial Holding | 3 |
| Transaction | Financial holding instrument data | FI position | 4 |
| Transaction | Financial holding instrument data | Financial Holding instrument (one record corresponding to each FI subtype) | 5 |
| Transaction | Financial holding instrument data | Financial Holding (one record corresponding to each FI record) | 6 |
| Relationship | Financial holding relationships | Customer financial holding (FH record is related either to customer financial holding or group financial holding) | 7 |
Unified Client Profile Fields
The following table enumerates all essential fields that need to be populated with data for their respective tables.
| Table | Fields to hydrate (in order of sequence) |
|---|---|
| Financial market product | msfsi_integrationkey |
| Financial market product | msfsi_name |
| Financial market product | msfsi_financialmarketproducttype |
| Financial market product | msfsi_currentprice |
| Financial market product | msfsi_currentpricedefault |
| Financial market product | msfsi_exchangecode |
| Financial market product | msfsi_performance1y |
| Financial market product | msfsi_performance3y |
| Financial market product | msfsi_performanceytd |
| Financial market product | msfsi_tickersymbol |
| Financial market product | exchangerate |
| FH Investment | msfsi_integrationkey |
| FH Investment | msfsi_name |
| FH Investment | msfsi_portfolioname |
| FH Investment | msfsi_accountingclassification |
| FH Investment | msfsi_financialholdingcategory |
| FH Investment | msfsi_financialholdingtype |
| FH Investment | msfsi_balance |
| FH Investment | msfsi_balancedefault |
| FH Investment | msfsi_balanceexchangerate |
| FH Investment | msfsi_openeddate |
| FH Investment | msfsi_cashbalance |
| FH Investment | msfsi_cashbalance_default |
| FH Investment | msfsi_gainloss |
| FH Investment | msfsi_gainloss_default |
| FH Investment | msfsi_performance1y |
| FH Investment | msfsi_performance3y |
| FH Investment | msfsi_performanceytd |
| FH Investment | msfsi_performance |
| FH Investment | msfsi_balanceexchangerate |
| Financial Holding | msfsi_integrationkey |
| Financial Holding | msfsi_name |
| Financial Holding | msfsi_accountingclassification |
| Financial Holding | msfsi_financialholdingcategory |
| Financial Holding | msfsi_financialholdingtype |
| Financial Holding | msfsi_financialholdingcode |
| Financial Holding | msfsi_details |
| FI position | msfsi_integrationkey |
| FI position | msfsi_financialinstrumenttype |
| FI position | msfsi_financialmarketproduct |
| FI position | msfsi_currentvalue |
| FI position | msfsi_currentvaluedefault |
| FI position | msfsi_positioncount |
| FI position | msfsi_averagesecuritycost |
| FI position | msfsi_averagesecuritycostdefault |
| FI position | msfsi_positiontype |
| FI position | exchangerate |
| Financial Holding instrument | msfsi_integrationkey |
| Financial Holding instrument | msfsi_name |
| Financial Holding instrument | msfsi_financialholdinginstrumenttype |
| Financial Holding instrument | msfsi_financialinstrumentcode |
| Financial Holding instrument | msfsi_details |
| Financial Holding instrument | msfsi_financialholding |
| Customer Financial Holding | msfsi_name |
| Customer Financial Holding | msfsi_customerid |
| Customer Financial Holding | msfsi_financialholdingid |
| Customer Financial Holding | msfsi_financialholdingrole |