2.5 Fact Data Flow
Most of the Fact tables are mapped to staging counterparts through Table to Table (T2T) mappings. Data from source systems can be loaded into staging through flat file or source system interfaces. T2T process then loads data to fact tables. For example, Fact Common Account Summary, Fact Opportunity, and so on. Some of the Fact tables are loaded with processed fact information from other fact tables. For example, Fact CRM Customer Summary, Fact Account Profitability, and so on.
Table 2-2 Fact Table Flow
Fact Entity Name | Source | Source Entities | Method of populating measures |
---|---|---|---|
Fact Common Account Summary | Stage | Stage Annuity Contracts, Stage Bill Contracts, Stage Borrowings, Stage Cards, Stage CASA Accounts, Stage Guarantees, Stage Investments, Stage LC Contracts, Stage Leases Contracts, Stage Loan Contracts, Stage Money Market Contracts, Stage Over Draft Accounts, Stage Term Deposit, Stage Trusts, Stage Commitment Contracts, Stage Mutual Funds | T2T |
Fact PFT Account Summary | Instrument | Annuity Contracts, Borrowings, Checking and Savings Account, Credit Cards, Credit Lines, Guarantees, Investments, Leases, Loan Contracts, Mortgages, Term Deposits, Trusts, Stage Mutual Funds | T2T |
Fact FTP Account Summary | Instrument | Annuity Contracts, Borrowings, Checking and Savings Account, Credit Cards, Credit Lines, Guarantees, Investments, Leases, Loan Contracts, Money Market Contracts, Mortgages, Term Deposits, Trusts Stage Mutual Funds | T2T |
Fact CRM Account Summary | Stage | Stage Annuity Contracts, Stage Bill Contracts, Stage Borrowings, Stage Cards, Stage CASA Accounts, Stage Guarantees, Stage Investments, Stage LC Contracts, Stage Leases Contracts, Stage Loan Contracts, Stage Money Market Contracts, Stage Over Draft Accounts, Stage Term Deposit Contracts, Stage Trusts, Stage Commitment Contracts | T2T |
Fact Common Customer Summary | Stage | Stage Customer Details, Stage Party Rating Details, Stage Party Financials | T2T |
Fact CRM Customer Summary | Stage and Fact | Stage Customer Master, Stage Customer Details, Fact Common Account Summary | T2T |
Fact Account Feature Map Stage | Stage | Account Feature Map | T2T |
Fact Customer to Customer Relationship | Stage | Stage Customer to Customer Relationships | T2T |
Fact Opportunity | Stage | STG_OPPORTUNITY | T2T |
FCT_OPPORTUNITY_ACTIVITY | Stage | STG_OPPORTUNITY_ACTIVITY | T2T |
Fact Account Profitability | Fact | Fact Common Account Summary, Fact FTP Account Summary, Fact PFT Account Summary, Fact Regulatory Capital Account Summary, Fact Economic Capital Account Summary | DT |
Fact Account Customer Relationship | Stage | Stage Customer Relationships | T2T |
Fact Account Manager Relationship | Stage | Stage Account Manager Relationship | T2T |
Fact Forecast And Plan Data | Stage | Stage Forecast and Plan Data | |
Exchange Rate History | Stage | Stage Exchange Rates | T2T |
Exchange rates | View | View on Stage Exchange Rates | T2T |
Fact Party Account Role Map | Stage | Stage Party Account Role Map | T2T |
Fact Party Financials | Stage | Stage Party Financials | T2T |
Fact Account Segment MOB Summary | Fact | Fact Account Profitability, Fact Common Account Summary, Fact Account Segment Score | DT |
Fact Account Segment Score | Fact | Fact Common Account Summary | DT |
The OFS PACS uses some materialized views registered as Derived Entity that must be refreshed as and when the dependent table has fresh data. The MVs can be refreshed by running the batches created for the purpose.
Table 2-3 Derived Entities and Dependent Objects
Materialized View | Referenced Name | Referenced Object Type |
---|---|---|
ACNTSMRM | FCT_COMMON_ACCOUNT_SUMMARY | Table |
FCT_CRM_ACCOUNT_SUMMARY | Table | |
CUSTDETM | DIM_CUSTOMER | Table |
DIM_CUSTOMER_TYPE | Table | |
DIM_GENDER | Table | |
FCT_COMMON_CUSTOMER_SUMMARY | Table | |
FCSTCUSA | VW_ACCT_VAL_FCST_CUSTAGG_IPA | Table |
FCSTLTVM | VW_FORECAST_LTV_IPA | Table |
FCSTREPA | VW_ACCT_VAL_FCST_REPAGG_IPA | Table |
FSIUSRD | FSI_USER_DATA_ACCESS | Table |
MGMTPFTM | ACNTSMRM | Table |
FCT_ACCOUNT_MGR_REL | Table | |
FCT_ACCOUNT_PROFITABILITY | Table | |
MVCACPRO | A_DIM_REP_CURRENCY | Table |
DIM_ACCOUNT | Table | |
DIM_CONSOLIDATION | ||
DIM_CURRENCY | Table | |
DIM_CUSTOMER | Table | |
DIM_CUSTOMER_TYPE | Table | |
DIM_DATES | Table | |
DIM_LOB | Table | |
DIM_ORG_UNIT | Table | |
DIM_PRODUCT | Table | |
DIM_REP_LINE | Table | |
FCT_COMMON_CUSTOMER_SUMMARY | Table | |
FCT_CRM_ACCOUNT_SUMMARY | Table | |
MVUSRACC | Table | |
MVCCUSAG | A_DIM_REP_CURRENCY | Table |
MGMTPFTM | DIM_ACCOUNT | Table |
MVUSRACC | DIM_ACCOUNT | Table |
FCT_COMMON_ACCOUNT_SUMMARY | Table | |
FSIUSRD | Table | |
USRMGRMV | FSI_M_USER_MANAGER_MAP | Table |
WTHREPMV | WITH_REP_LINE_DIRECT_INDIRECT | Table |
MVCCUSAG | DIM_CONSOLIDATION | Table |
DIM_CURRENCY | Table | |
DIM_CUSTOMER | Table | |
DIM_CUSTOMER_TYPE | Table | |
DIM_DATES | Table | |
DIM_LOB | Table | |
DIM_ORG_UNIT | Table | |
DIM_PRODUCT | Table | |
DIM_REP_LINE | Table | |
FCT_ACCOUNT_PROFITABILITY | Table | |
FCT_COMMON_CUSTOMER_SUMMARY | Table | |
FCT_CRM_ACCOUNT_SUMMARY | Table | |
MVUSRACC | Table | |
MVCPROAG | A_DIM_REP_CURRENCY | Table |
DIM_ACCOUNT | Table | |
DIM_CONSOLIDATION | Table | |
DIM_CURRENCY | Table | |
DIM_CUSTOMER | Table | |
DIM_CUSTOMER_TYPE | Table | |
DIM_DATES | Table | |
DIM_LOB | Table | |
DIM_ORG_UNIT | Table | |
DIM_PRODUCT | Table | |
DIM_REP_LINE | Table | |
DIM_VINTAGE | Table | |
FCT_ACCOUNT_PROFITABILITY | Table | |
FCT_COMMON_CUSTOMER_SUMMARY | Table | |
FCT_CRM_ACCOUNT_SUMMARY | Table | |
MVUSRACC | Table |