Home

Oracle® Financial Services Data Foundation

1. Overview of Account Summary Population Table to Table seeded definitions are provided for loading data into Common Account Account Summary Population 5 1 Summary SI No Source Table Name T2T Definition Name Target Table Name 10 11 STG ANNUITY CO NTRACTS STG BILLS CONTRA CTS STG BORROWINGS STG CARDS STG CASA STG CREDIT DERIV ATIVES STG_FUTURES STG_FX_CONTRACT S STG GUARANTEES STG IJARAH STG INVESTMENTS T2T STG ANNUITY CONTRACTS CAS T2T STG BILLS CA S T2T STG BORROWI NGS CAS T2T STG CARDS C AS T2T STG CASA CA S T2T STG CREDIT D ERIVATIVES CAS T2T STG FUTURES CAS T2T STG FX CONT RACTS CAS T2T STG GUARAN TEES CAS T2T STG JJARAH C AS T2T STG INVESTM ENTS CAS FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY 5 2 Oracle Financial Services Data Foundation User Guide SI No Source Table Name T2T Definition Name Target Table Name 12 13 14 15 16 17 18 19 20 21 22 STG_ISTISNA STG_LC_CONTRACT S STG LEASES CONT RACTS STG LOAN CONTR ACTS STG MM CONTRAC TS STG MUDARABAH STG MURABAHAH
2. View Logs 207 D 1 v Data Quality Rule Logs gt ck Name Log Message lessaze Date Message Tre Total Rows Rows impacted Assgement Type Assignment Severty Severty Message Batch Execution of DQ Rules Create a Batch for executing DQ Rules and add a Task to the selected Batch Add component as RUN DQ RULE and in Dynamic Parameter List add a DQ Group A single Batch can have multiple number of Tasks and each Task is executing the DQ Group Batch execution facilitates the execution of multiple Groups simultaneously You can also use the Include or Exclude functionality to determine which all groups have to be executed The following batches need to be executed in OFSDF V_BATCH_ID V_BATCH_DESCRIPTION XXXX_ALM Data Quality batch for ALM tables XXXX_CAMPAIGN Data Quality batch for CAMPAIGN tables XXXX_COLLATERAL Data Quality batch for Collateral tables XXXX_COLLECTIONANDRECOVERY Data Quality batch for Collection and Recovery tables XXXX CRM Data Quality batch for CRM tables XXXX CUSTOMER Data Quality batch for Customer tables 8 18 Oracle Financial Services Data Foundation User Guide V_BATCH_ID V_BATCH_DESCRIPTION XXXX_EXPOSURE XXXX_GLANDACCOUNTING XXXX LRM XXXX MARKETRISK XXXX MASTER XXXX MISCELLANEOUS XXXX OPRISK XXXX ORIGINATION XXXX PRODUCTPROCESSORS XXXX RATES XXXX RATING XXXX SECURITIZATION XXXX TRANSACTIONS XXXX TRANSACTIONSUMMARY Data Quality batch for EXPOSURE
3. 3 8 The status of execution can be monitored using the Batch Monitor screen You can access this from the Left Hand Side LHS menu as follows From the Home menu select Operations then select Batch Monitor Note For a more comprehensive coverage see Oracle Financial Services Oracle Financial Services Data Foundation User Guide Analytical Applications Infrastructure User Guide The status messages in Batch Monitor are N Not Started O On Going F Failure S Success The ICC execution log can be accessed on the application server in the following directory FIC_DB_HOME log ficgen The file name will have the batch execution id Sample dbfiles home oracle OFSAAI ficdb log ficgen The detailed SCD component log can be accessed on the application server in the directory FIC_HOME go one folder up from there and then accessing the following path ftpshare lt infodom name gt logs The file name will have the batch execution id Sample dbfiles home oracle ftpshare OFSAADEMO logs Check the profile file in the installation home if you are not able to find the paths mentioned earlier Load DIM_ACCOUNT through SCD The SCD population in DIM_ACCOUNT table generates individual numeric SKEYs for every account number with an additional leg skey Below are the columns that will be loaded during SCD population V ACCOUNT NUMBER e N ACCT SKEY N RCV LEG ACCT SKEY e FIC MIS DATE This approach replaces the
4. e P address Select the IP address from the list e Executable scd lt map ref num Example scd 61 Refer the following sections for details e Wait When the file is being executed you have the choice to either wait till the execution is complete or proceed with the next task Click the list box of the field provided for Wait in the Value field to select Yes or No Clicking Yes confirms that you wish to wait for the execution to be complete Clicking No indicates that you wish to proceed e Batch Parameter Clicking Yes would mean that the batch parameters are also passed to the executable being started else the batch parameters will not be passed to the executable Important Always select Y in Batch Parameter For the Parameter Executable earlier mentioned the map ref num values are e 1 if you want to process all the dimensions The Executable parameter mentioned earlier would be scd 1 e If you want to process for a single dimension query the database table SYS TBL MASTER and give the number in the map ref num column for the dimension you want to process These are the ones which come seeded with the install e Execute the batch from Batch Execution by choosing the batch created following the steps mentioned in the preceding sections for a date Note A seeded batch lt Infodom gt _SCD is provided which has all the required dimensions as different tasks that are part of SCD Checking the Execution Status
5. 2 9 Grouped List of Business Data Tables in Current Staging Area Model Stage Customer Details Pst undis me gt bears ee el E CUR Sus Telefe Tr SS eee m nase Warchant Corn wegen een EI m nn me L ket sen a A The key groups of business data tables in the model are as follows Group Name Purpose Product Processors Tables for Financial Instruments and Contracts Note that these tables can accommodate nearly 80 types of instruments and derivatives across Banking and Trading books Transactions Tables to hold Transaction Event level data from the bank s systems These tables are available by both contract and channel and there is additionally a set of Transaction Summary tables that contain transaction data in a summarized form required by the Profitability application Exposures Exposures are basically contracts on the asset side of the balance sheet The tables in this category are primarily used by Risk applications Mitigants Mitigants are used to address specific types of risk posed by exposures The tables in this category are primarily used by Risk applications GL Contains tables holding information pertaining to the General Ledger Customer Consists of Marketing related Customer activity and plan data relevant to CRM analytics Note that core customer information is in the Product Processors and reference data about customers is elsewhe
6. Logical Data Model to OFSDF Staging Area sss 2 20 3 Dimension Loading Process Dimension Tables Population ssssssssseeeeen emnes 3 1 Overview of SCD Process iicet dida 3 1 Prerequisites i e ec eee aeo ee e I CP RES RATE CAPE RER De decane a 3 2 Tables Used by the SCD Component ssssssseeeeeeen nennen enne 3 3 Executing the SCD Goniponent ce era lan 3 7 Checking the Execution Status sie etie lette e tit rit e intenta e dra bad ca ia bad 3 8 Load DIM ACCOUNT through SCD seen eene 3 9 4 Customer Summary Population Overview of Common Customer Summary Tables sssssssssseee 4 1 Pretequisites coepto Rn dedu tede rto ae peccet tec dah 4 1 Executing the Customer Summary Population T2T sss 4 2 Error Messages toi titeres 4 2 5 Account Summary Population Overview of Account Summary Tables sssssssssssee eene 5 1 Overview of Account Summary Population sss 5 1 Prerequisites nu 2ER ie 5 4 Fact Common Account SummAaty eienn ee enne eene e nnne nnns 5 5 Executing the Account Summary Population T2T sssssssssseee mee 5 5 Checking the Execution Status oce autein ime dei eg d de ne e eda 5 7 Account Summary I2Ts ein regere diee a a eiae irritus 5 7 6 Time Dimension Population Overview of Time Dimension Population sssssssseee eee 6 1
7. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications This software or hardware and documentation may provide access to or information on content products and services from third parties Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third party content products and services Oracle Corporation and its affiliates will not be responsible for any loss costs or damages incurred due to your access to or use of third party content products or services Contents Send Us Your Comments Preface 1 Introduction to OFSDF O A 1 1 Components ot OESDE ui ca 1 2 Relationship to Oracle Financial Services Analytical Applications ssesss 1 3 OFSDF Prerequisite Components and Tools sss 1 4 2 Understanding OFSDF Background ceu EHER 2 1 OFSDE Architecture ne ana nen 2 1 Differences from Traditional Warehouse Architecture sse 2 4 OFSDF Physical Data Model sss eee emnes 2 4 Staging Data Mod l Joist danita eee ee eds e ote tr etre 2 5 Reporting Data Model tiei seti elato ir ra a 2 13 OFSDF Logical Data Model ssssssssee eee emen 2 17 Relationship to the OFSDF Physical Model 2 18 OFSDF LDM Content Details ae a d e n e eed vag ded na 2 19 Mapping from
8. Prerequisites PD 6 1 Tables Used by the Time Dimension Population Transformation ssssss 6 2 Executing the Time Dimension Population Transformation sssseseeee 6 2 Checking the Execution Status sss eene nennen 6 4 7 Using OFSDF Delivery Mech ritsm iiie ette ep bb hr bi ph d eni dapib dp aded 7 1 Installing DESDE eee e een ite LEE ER 7 2 OFSDF Supporting Documentation sssssssssssee eee eee enne 7 2 Data Dictionaty PROP OO hera ist te tu ida adea ee rne er PE nean 7 2 Download Specifications sss eene nennen enne ens 7 3 Extending OFSDF Physical Data Model sss eee 7 3 Customization Process Guidelines esssssssssssseee eene 7 3 Staging Area Design GuidelinesS oooomocconononnonnocononnenncnncnnonnnnnnnononnnnnnonnnnnnonnnnnnnnnonannnnnennonno 7 4 Results Area Design Guidelines sse ene 7 5 Upgrading Data Modelit ipii Le adie ueteri iesu esu ua 7 6 8 Data Quality Rules Execution A Data Quality Framework eite etude kat ek rsen ker a ke da daa 8 1 Data Quality Rules For Staging Tables cece eerie een 8 13 Data Quality Groups for Staging Tables sssssssssss eee eee 8 14 Naming Conventions Used in OFSDF Data Model Naming Conventions Used in OFSDF LDM and PDM eee A 1 OFSDF Logical Data Model Naming Conventions A 1 LDM Logical V
9. STG MUSHARAKA H STG MUTUAL FUN DS STG OD ACCOUNT S STG OPTION CONT RACTS T2T STG ISTISNA C AS T2T STG LC CAS T2T STG LEASES C ONTRACTS CAS T2T STG LOANS C AS T2T STG MM CAS T2T STG MUDARA BAH CAS T2T STG MURABA HAH CAS T2T STG MUSHAR AKAH CAS T2T STG MUTUAL FUNDS CAS T2T STG OD CAS T2T STG OPTIONS CAS FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY FCT COMMON AC COUNT SUMMARY Account Summary Population 5 3 SI No Source Table Name T2T Definition Name Target Table Name 23 STG REPO CONTRA T2T STG REPO CO FCT COMMON AC CTS NTRACTS CAS COUNT SUMMARY 24 STG RETIREMENT T2T STG RETIREME FCT COMMON AC ACCOUNTS NT ACCOUNTS CA COUNT SUMMARY S 25 STG_SALAM T2T_STG_SALAM_C FCT COMMON AC AS COUNT SUMMARY 26 STG SUKUK T2T STG SUKUK C FCT COMMON AC AS COUNT SUMMARY 27 STG SWAPS CONTR T2T STG SWAPS C FCT COMMON AC ACTS ONTRACTS CAS COUNT SUMMARY 28 STG TD CONTRAC T2T STG TD CONT FCT COMMON AC TS RACTS CAS COUNT SUMMARY 29 STG TRUSTS T2T STG TRUSIS C FCT COMMON AC AS COUNT SUMMARY 30 STG COMMITMENT T2T STG COMMIT FCT COMMON AC CONTRACTS MENT CONTRACTS COUNT SUMMARY 31 DIM CUSTOMER STG CUSTOMER DE
10. User Guide Unique Constraint Violation This occurs when attempting re load or loading existing records for the already executed AS OF DATE Customer Summary Population 4 3 5 Account Summary Population Overview of Account Summary Tables Account Summary tables are loaded from the staging product processor tables using the Table to Table T2T component of Oracle Financial Services Analytical Applications Infrastructure OFSAAT framework Customer account level data from the Oracle Financial Services Analytical Applications OFSA A staging product processor tables must be consolidated into a standardized relational Business Intelligence BI data model This consolidation is done to have all the staging product processor table data in a single Fact table The Account Summary table data can be used for building cubes which allow rollup of data for a dimension or a combination of dimensions This relational BI model consists of the following vertically partitioned Account Summary tables that are organized by application subject area e FCT CRM ACCOUNT SUMMARY e FCT PFT ACCOUNT SUMMARY e FCT FTP ACCOUNT SUMMARY e FCT REG CAP ACCOUNT SUMMARY e FCT ECO CAPITAL ACCOUNT SUMMARY The preceding Account Summary tables are part of data model but there are no seeded T2T definitions available to populate these tables T2T processes must be custom configured to populate these tables to use measures defined on these tables for reporting
11. VARCHAR2 60 Name of the column in the Staging Table NULL oe SCD_TYP_ID NUMBER 3 SCD type for the column NULL PRTY_LOOKUP_REQD_FLG CHAR 1 Column to determine whether Lookup is NULL required for Priority of Source against the Source Key Column or not COL_DATATYPE VARCHAR2 15 The list of possible values are VARCHAR DATE NULL NUMBER based on the underlying column datatype COL_FORMAT VARCHAR2 15 NULL The possible values for column type the COL_TYPE column in SYS_STG_JOIN_MASTER are 1 PK Primary Dimension Value may be multiple for a given Mapping Reference Number 2 SK Surrogate Key Dimension Loading Process 3 5 3 DA Dimensional Attribute may be multiple for a given Mapping Reference Number 4 SD Start Date 5 ED End Date 6 LRI Latest Record Indicator Current Flag 7 CSK Current Surrogate Key 8 PSK Previous Surrogate Key 9 SS Source Key 10 LUD Last Updated Date Time 11 LUB Last Updated By Sample Data This is the row put in by the solution installer for the Line of Business dimension MAP REF NUM 6 COL NM V LOB CODE COL TYP PK STG COL NM V LOB CODE SCD TYP ID PRTY LOOKUP REQD FLG N COL DATATYPE VARCHAR COL FORMAT 61 Note For any new dimension added the column details will have to be inserted to this table manually e DIM xdimensionname V The database view which SCD uses as the source 3 6 Oracle Financial Services Data Foundation User Guide E
12. and specify the details You can define nine specific validation checks based on Range Data Length Column Reference Specific Value List of Value Code Null Value Blank Value Referential Integrity Duplicity and Custom Check Business For more information refer to Oracle Financial Services Analytical Applications Infrastructure User Manual Note A minimum of one Validation check must be defined to generate a query Click Generate Query The details are validated and the validated query along with the status is displayed in the Generated Query section If Generic Check is selected perform the following Select Table Name from the drop down list The list displays all the tables which are marked for Data Quality Rule in a data model which has the table classification property code set to 340 e Click the below button and define the Filter condition using the Specify Expression screen Click Add button in the Condition grid The Specify Expression screen is displayed Define the Condition expression The Expression is displayed with the IF and Else conditions along with the Severity status as either Error or Warning You can change the Severity by selecting from the drop down list 8 4 Oracle Financial Services Data Foundation User Guide Note You can add an Assignment only when the Severity is selected as Warning Assignments are added when you want to correct or update record s in base column data selected co
13. button is disabled if you have selected multiple DQ Names The DQ Definition screen is displayed Update the details as required For more information see Create Data Quality Rule section Click Save to update the changes Copy Data Quality Rule You can copy the existing Data Quality Rule to quickly create a new DQ definition based on the existing rule details or by updating the required parameters To copy an existing Data Quality Rule definition in the Data Quality Rule Summary screen 1 Select the check box adjacent to the required DQ Name in the list whose details are to be duplicated Click Copy button from the Data Quality Rules tool bar Copy button is disabled if you have selected multiple check boxes The DQ Definition screen is displayed Edit the DQ definition Name and other details as required For more information see Create Data Quality Rule section Click Save The defined Data Quality Rule definition is displayed in the Data Quality Rule Summary screen with the status as Saved Approve Reject Data Quality Rule You Authorizer can Approve a pre defined Data Quality Rule definition for further execution or Reject an inappropriate DQ definition listed within the Data Quality Rule Summary screen You should be mapped to DO Authorizer function role to Approve or Reject a DO definition To Approve Reject Data Quality Rule in the Data Quality Rule Summary screen 1 Select the checkbox adjacent to the r
14. enables integrated reporting of customer information Note Any customer level application specific attributes and measures that are computed by applications should be part of the application specific customer summary entities 5 Aggregate Entities Depending on performance requirements for each application information can be reported out of aggregate entities However a drill through to the base entity from the aggregate entity is mandatory 6 Reporting and local currency support Include additional attributes in the fact tables to store reporting and local currency equivalent of base measures These attributes need to be computed by looking into the exchange rates 7 Support for full history Any new tables in the Results area should be designed to support maintenance of full history Upgrading Data Model The model upgrade process is achieved through the ERwin Model Compare and Merge utility Refer to ERwin documentation for details on Menu options process of comparing and merging models 7 6 Oracle Financial Services Data Foundation User Guide 8 Data Quality Rules Execution Data Quality Framework Data Quality Framework consists of a scalable rule based engine which uses a single pass integration process to standardize match and duplicate information across global data Data Quality Framework within the Infrastructure system facilitates you to define rules and execute them to query validate and correct the transfo
15. has to be provided For example default value is DRCY USD Here USD acts as reporting currency parameter to T2T 5 6 Oracle Financial Services Data Foundation User Guide 9 Repeat steps 4 to 8 for adding the remaining T2Ts within the same batch definition 10 Execute the batch created in the preceding steps Checking the Execution Status The status of execution can be monitored using the Batch Monitor screen Note For a more comprehensive coverage of configuration and execution of a batch refer to Oracle Financial Services Analytical Applications Infrastructure User Guide The status messages in Batch Monitor are e N Not Started e O On Going e F Failure e S Success The execution log can be accessed on the application server in the following directory FIC_DB_HOME log t2t The file name will have the batch execution id The file name will have the batch execution id e FCT_COMMON_ACCOUNT_SUMMARY e FCT_CRM_ACCOUNT_SUMMARY Account Summary T2Ts T2T definitions can be retrieved as an excel document for reference from the metadata browser of the Unified Metadata Manager UMM component of OFSAAI Account Summary Population 5 7 6 Time Dimension Population Business data commonly represents information as of a point in time for example a balance as of a point in time or as of a particular span of time for example income for the month of March The rollup of a particular balance depending on the
16. id info or visit http www oracle com pls topic lookup ctx acc amp id trs if you are hearing impaired 1 Introduction to OFSDF This chapter introduces you to Oracle Financial Services Data Foundation OFSDF its components its relationship with Oracle Financial Services Analytical Applications Infrastructure OFSAAI and the key prerequisites for running OFSDF 2 Understanding OFSDF This chapter explains the background of OFSDF its functional architecture along with the differences from traditional warehouse architecture OFSDF product package consists of the Logical Data Model and Analytical Data Warehouse model The individual components of Physical and Logical Data Model and Mapping Logical Data Model to OFSDF Staging Area are explained in detail 3 Dimension Loading Process 4 Customer Summary Population 5 Account Summary Population 6 Time Dimension Population Business data commonly represents information as of a point in time for example a balance as of a point in time or as of a particular span of time for example income for the month of March The rollup of a particular balance depending on their nature could be a simple additive rollup wherein the child member balances are added up to arrive at the parent node balance for example Ending Balance or non additive rollups wherein a node formula is used to specify how to rollup the child member balances for example 3 month rolling average 7 Using OFSDF This chapter d
17. is abbreviated form of the entity s primary subject area e lt Table Name is a meaningful name for the entity with no embedded spaces between words Table Name may be abbreviated Logical View Name Physical View Name Description Account Address FSW ACCT ACCOUNT AD Account Address entity DRESS belonging primarily to Account ACCT subject area Letter Of Credit FSW CON LETTER OF CR Letter of Credit entity belongs EDIT primarily to the Contracts CON subject area OFSDF Physical Data Model Naming Conventions The OFSDF PDM consists of tables grouped into two distinct areas namely the Staging Area and Results Area The Staging Area consists of tables for data sourcing and the Results Area consists of the star schemas datamarts for reporting and BI ERwin modeler tool allows two views namely Logical view and Physical view for each model file Accordingly the OFSDF PDM Physical Data Model file can be viewed in logical view mode and physical view mode Different naming conventions and standards are applied to the two views of the OFSDF Physical Data Model PDM Logical View Mode Naming Conventions page A 4 PDM Physical View Mode Naming Conventions page A 5 PDM Logical View Mode Naming Conventions In the logical view model OFSDF PDM model tables and columns have descriptive names that readily convey the meaning and use of the element In the logical view names of tables and columns can have more than one word w
18. of core business processes is as follows Business Area Comments Retail Banking Corporate Banking Capital Markets Trading and Securities End to end content support for Retail Assets and Liabilities Retail lending across a variety of loan products Current Accounts and Term Deposits End to end process support for Commercial Assets and liabilities Corporate Commercial lending operations Structured Lending Collateral and Limits models and Trade Finance Process support for the trade lifecycle from quoting to settlement clearance Coverage of a wide variety of security and derivative asset classes 2 18 Oracle Financial Services Data Foundation User Guide Business Area Comments Preliminary coverage of Insurance liabilities in the non P amp C insurance domains Insurance The OFSDF physical data model should be seen as a deployable physicalized subset of this reference data model for known analytical use cases spanning Risk Performance Customer Insight and Compliance as illustrated below OFSDF Physical Data Model LDM Content Base across Banking Capital Markets Insurance Subjects 1 Product OFSDW and 2 Party OFSAA Staging OFSDW OFSAA 3 Location Area Reporting Area 4 Calendar 5 Contract Instrument Mapped Subset Account of LDM for 6 Transactions and known analytical Events use cases Mappings Provided from LDM to Staging Area OFSDF LDM Cont
19. of the Corporate Credit Risk Solution allows reporting on Credit Limits Part of the Customer Insight solution set providing support for Retail Institutional and Channel Analytics Support for Market Risk analytics 2 16 Oracle Financial Services Data Foundation User Guide Name Definition PFT BI Supports reporting related to Profitability analysis part of the Enterprise Performance Management solution area Reputational Risk Support for Reputational Risk measurement and analysis Retail Credit Risk The set of tables subject areas supporting Retail Credit Risk Analysis Retail Pooling Reporting support for Pooling for Retail Exposures required by ALM and Retail Credit Risk analysis Strategic Risk Support for reporting related to Strategic Risk measurement of these risks is a qualititative process and part of the ICAAP framework aCRM Reporting related to analytical Customer Relationship Management Within the above higher level areas there are over 165 subject areas consisting of star schemas supporting detailed analysis Each Subject Area typically corresponds to one or possibly more star schemas depending on the reporting need There are currently 500 fact and dimension tables in the reporting area The details of the various naming conventions used in OFSDF Data Model are explained in Appendix A page A 1 For column level details see the Technical Whitepaper on Data Model Document Generation which de
20. other vendors or custom built solutions By providing a single data repository for reporting needs this layer provides a foundation for departmental as well as cross departmental and cross functional reporting 2 2 Oracle Financial Services Data Foundation User Guide Additionally the schema of the reporting area is specifically built for Financial Services analytics As an illustration the reporting area has a Run dimension that is shared across all BI reporting use cases Similarly the aggregation of shared measures reporting into a common set of output structures Account Summary allows for cross functional reporting which is increasingly the norm in Financial Institutions Single point of control and operation The Oracle Financial Services Analytical Applications Infrastructure is a separate Oracle product that offers a set of tools that are built on a common metadata foundation These tools are used to control and manage the lifecycle of data from sourcing to reporting There is a collection of frameworks to manage the following lifecycle steps of data within OFSDF 1 Metadata Management 2 Data Quality 3 Data Movement 4 Scheduling and runtime operations a Security User management 6 Analytical Process Definition and Execution Processing Area As explained earlier the primary purpose of the OFSDF is to serve as a container for analytical processing from sourcing to consumption Such processing is usually delivere
21. refer to business activities of a bank or FSI To provide more detail on these banks need to capture additional data that provides context for these activities This data may be variously called as reference data or master data and covers various business dimensions of a given transaction or account For example a bank has a master list of products that it sells to customers Product Master Similarly it has a list of customers Customer master A trading firm may hold a list of securities it transacts in Securities master These and other lists provide context for each business transaction or account Banks typically maintain Master data for this purpose With this background the following lists the key categories of business data and reference data in the staging data model Business Data To view the current set of business data tables in the staging area open the OFSDF Staging Area model in the ERwin data modeling tool and click on subject areas as shown below Right click and switch to the Staging Data Tables subject area 2 8 Oracle Financial Services Data Foundation User Guide Staging Data Tables Subject Area la This provides an ordered visually grouped list of the business data tables in the current staging area model Above each group in the diagram is the group name as a label to categorize the tables as follows Understanding OFSDF
22. specific reporting models 3 Logical Data Model LDM The OFSDF Logical Data Model is independent of OFSAA analytical applications and more aligned to the underlying business processes of a financial institution However there is significant content overlap in the lower level details of the LDM entities attributes This is captured in the form of attribute level mappings between LDM entities attributes and their corresponding equivalents in the OFSDF staging area data model This mapping is made available as part of the OFSDF release packaging 4 Synchronized Releases The Staging and Reporting models that are part of an OFSDF release are updated to reflect prior application specific releases This means that the latest release of OFSDF 7 4 0 0 0 reflects all prior application releases across OFSAA from a data model perspective with respect to the Staging and Reporting models 2 Infrastructure 1 The Oracle Financial Services Analytical Applications Infrastructure OFSAAT is the same infrastructure that is used to deploy and manage an OFSDF instance 2 Thesame collection of tools OFSAAT is used to manage a deployed instance of the Oracle Financial Services Data Foundation OFSDF These are the tools used to manage the data lifecycle in OFSDF e Model Upload e Unified Metadata Manager UMM e Data Quality Framework e T2T framework e Mart Management framework OFSDF Prerequisite Components and Tools The key prerequisit
23. table has to be loaded prior loading any of the other Account Summary Population 5 5 Account Summary tables You can execute the T2T component from OFSAA Infrastructure ICC framework accessed through the application Batch Operations screen Fact Common Account Summary A seeded batch lt Infodom gt _STG_TO_FCAS has to be executed for the required MIS Date Alternatively following steps will help you create a new batch 1 From the Home menu click Operations and select Batch Maintenance 2 Click New Batch symbol in Batch Name container Enter the Batch Name and Description 3 Click Save 4 Clickthe check box in the Batch Name container to select the Batch you created in the earlier step 5 Enter the Task ID and Description 6 Select Load Data from the Components list 7 Selectthe following from the Dynamic Parameters List e Datastore Type Select the appropriate datastore from the list Datastore Name Select the appropriate name from the list e IP address Select the IP address from the list e Load Mode Select Table to Table from the list e Source Name Select lt T2T Source Name from the list File Name Select the T2T name for the source stage channel table you want to process 8 Click Save Data file name will be blank for any Table to Table Load mode Default value refers to currency calculation If there is any need for currency conversion in T2T transactions Default value
24. tables Data Quality batch for GL and Accounting group Data Quality batch for LRM tables Data Quality batch for MARKET RISK tables Data Quality batch for master tables Data Quality batch for Miscellaneous tables Data Quality batch for OP RISK tables Data Quality batch for Origination tables Data Quality batch for Product Processors group Data Quality batch for Rates tables Data Quality batch for RATING tables Data Quality batch for Securitization tables Data Quality batch for Transactions tables Data Quality batch for Transaction Summary tables The DQ Batches and corresponding groups are detailed in the following spreadsheet Data Quality Rules Execution 8 19 DQ_BATCH_GROUP_ MAPPING xlsx For more information regarding Batch Execution refer to Operations module of Oracle Financial Services Analytical Applications Infrastructure User Manual 8 20 Oracle Financial Services Data Foundation User Guide A Naming Conventions Used in OFSDF Data Model This Appendix chapter explains the various naming conventions used in OFSDF Logical Data Model and Physical Data Model In addition the domains in PDM and LDM are also listed with their descriptions This appendix covers the following topics e Naming Conventions Used in OFSDF LDM and PDM Naming Conventions Used in OFSDF LDM and PDM ERwin Data Model consists of Logical and Physical data structures for each model file The following section explain
25. the drop down list Message Select the required Message for the Severity from the drop down list You can also add multiple assignments by clicking Add button in Assignment grid Note Minimum of one condition needs to be defined to save the Rule 4 Click Save The defined Data Quality Rule definition is displayed in the Data Quality Rule Summary screen with the status as Saved View Data Quality Rule You can view individual Data Quality Rule definition details at any given point To view the existing Data Quality Rule definition in the Data Quality Rule Summary screen 1 Select the check box adjacent to the required DO Name 2 Click View button from the Data Quality Rules tool bar The DQ Definition screen displays the details of the selected Data Quality definition The Audit Trail section at the bottom of DQ Definition screen displays metadata information about the Data Quality Rule defined Modify Data Quality Rule You can update the existing Data Quality Rule definition details except for the Definition Name Table and Base Column selected To update the required Data Quality Rule definition details in the Data Quality Rule Summary screen 1 Select the check box adjacent to the required DO Name Note You can only edit those rules which have status either as Saved or as Rejected 8 6 Oracle Financial Services Data Foundation User Guide Click Edit button from the Data Quality Rules tool bar The Edit
26. 255 TER TABLE SYS TBL MASTER ADD SESSION ENABLE STATEMENT VARCHAR2 255 TER TABLE SYS TBL MASTER ADD SESSION DISABLE STATEMENT VARCHAR2 255 Sy IAS pal oc Se During upgrade to OFSAAI 7 3 2 4 0 ensure to backup SYS TBL MASTER table and to drop the preceding four columns if these scripts are executed in any of the OFSAAI versions prior to 7 3 2 4 0 Otherwise an upgrade to OFSAAI 7 3 2 4 0 may throw an error since the columns are existing e For improving performance hints for the MERGE query which is generated 3 10 Oracle Financial Services Data Foundation User Guide internally by the SCD can be provided under MERGE_HINT Session alters could be mentioned under SESSION_ENABLE_STATEMENT and SESSION_DISABLE_STATEMENT columns e SESSION ENABLE STATEMENTS will be executed before the MERGE in the SCD and SESSION DISABLE STATEMENTS will be executed after the SCD MERGE e Since all the tasks under the SCD batch for DIM ACCOUNT works on the same target the SESSION DISABLE STATEMENTS in SYS TBL MASTER cannot be provided when tasks are executed In this case there can be a separate SQL file to contain all the SESSION DISABLE STATEMENTS to be executed once after all the tasks in the SCD are done The SESSION DISABLE STATEMENT will hold a null in SYS TBL MASTER table e SESSION ENABLE STATEMENTS are required to be mentioned only for the firs
27. 2920637 validation 13146318 validation 30Nov 12929759 testing 13241934 VALIDATION code 13146318 validation 30Nov R2 13146328 VALIDATION copy 13342273 testing 33 3 APPROVE REJECT length Rejectio BBBB Boundary value min max CHECKS FOR VARCHAR SPECIFIC CODE CustomChecktype Customized chktype DATA LENGTH DEMO DIMA1 Validation code 2 Inthe Data Quality Group Definition section perform the following e Enter the Group Name by which you can identify the DQ Group e Enter a description or related information about the DQ Group e Select the Folder available for selected Information Domain from the drop down list 3 In the Map DQ Rules section perform the following Select the required DO Rule from the Available Rules list and click Select You can also search to select a specific DO Rule by entering the required keyword and clicking Find button e To select all the listed DO Rules click Select All You can also deselect a DQ Rule by selecting from the Mapped Rules list and 8 10 Oracle Financial Services Data Foundation User Guide clicking Deselect or clicking Deselect All to deselect all the mapped rules You can search to deselect a specific DQ Rule by entering the keyword and clicking button You can search to deselect a specific DQ Rule by entering the keyword and clicking Find button 4 Click Save The defined DQ group is listed in the Data Quality Rule Summary screen and can be exec
28. Oracle Financial Services Data Foundation User Guide Release 7 4 Part No E39096 01 May 2014 ORACLE Oracle Financial Services Data Foundation User Guide Release 7 4 Part No E39096 01 Copyright 2014 Oracle and or its affiliates All rights reserved Primary Author Aneesh Kurian Contributing Author Surag Ramachandran Arpana Danayak Contributor Sunil Mathew Guruprasad Thiruvachi Ashok Mallegowda Oracle and Java are registered trademarks of Oracle and or its affiliates Other names may be trademarks of their respective owners Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International Inc AMD Opteron the AMD logo and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices UNIX is a registered trademark of The Open Group This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws Except as expressly permitted in your license agreement or allowed by law you may not use copy reproduce translate broadcast modify license transmit distribute exhibit perform publish or display any part in any form or by any means Reverse engineering disassembly or decompilation of this software unless required by law for interoperability is prohibi
29. Reset button in the Group Execution details tool bar to reset the selection Delete Data Quality Group You can remove the DQ Group definition s which are created by you and which are no longer required in the system by deleting from Data Quality Groups Summary screen To delete 1 Select the check box adjacent to the required Group Name whose details are to be removed 2 Click Delete button from the Data Quality Groups tool bar 3 Click OK in the information dialog to confirm deletion Data Quality Rules For Staging Tables Data Quality DO Rules are framed and created based on Staging Tables Each rule is based on specified staging table column Specific Check or table Generic Check The rules created for each of the tables are detailed the following spreadsheet x Hl DQ CHECK RLLES x Isx In Specific Check a particular column is checked based on rule s predefined checks where as in Generic Check any columns are not specified Generic Check is useful if you have a check which is not Specific or you use IF ELSE conditions or CASE statements Data Quality Rules Execution 8 13 The following screen displays the Specified DQ Rules Data Quality Groups for Staging Tables To execute any DQ rule a Group must be created and the rules for execution has to be mapped with this group For each staging table a group has to be created and rules for these particular table have to be mapped under this Group For more informatio
30. STER WHERE V COMPONENT DESC DEFAULT GAAP 3 12 Oracle Financial Services Data Foundation User Guide Use this view under TABLE NAME in FSI DIM ACCOUNT SETUP DETAILS table If there are different GAAP codes for two distinct account numbers for the same MIS date then the function has to be executed for each GAAP code by changing the V COMPONENT VALUE manually in SETUP MASTER table The SETUP MASTER table should have only one record WHERE V COMPONENT DESC DEFAULT GAAP Note If STG OPTION CONTRACTS is loaded for two MIS dates and EN POPDIMACCOUNT is executed then all records irrespective of the MIS date will get loaded To resolve this remove the expression under SOL TEXT in FSI DIM ACCOUNT SETUP DETAILS for STG OPTION CONTRACTS and use the same expression to create a view and use this view as the TABLE NAME in FSI DIM ACCOUNT SETUP DETAILS Dimension Loading Process 3 13 4 Overview of Common Customer Summary Tables Customer Summary Population Fact Common Customer Summary table stores attributes pertaining to customer related data on an as is basis received from the source system Data is populated into this table using T2T Customer balances are derived from account summary Customer relationship table drives the relationship between accounts and customers Common customer summary data is populated for all the active customers in customer dimension Prerequisites Following are
31. TAILS _CAS T2T_FCT_COMMON _CUSTOMER FCT_COMMON_CU STOMER_SUMMAR Y Prerequisites e All the post install steps mentioned in the Oracle Financial Services Analytical Applications Infrastructure OFSAAT Installation and Configuration guide and the solution installation manual have to be completed successfully 5 4 Oracle Financial Services Data Foundation User Guide e Application User must be mapped to a role that has seeded batch execution function BATPRO e Before executing a batch check if the following services are running on the application server For more information on how to check if the services are up and on and how to start the services if you find them not running see Oracle Financial Services Analytical Applications Infrastructure User Guide 1 Iccserver 2 Router 3 AM Server 4 Messageserver e Batches will have to be created for executing This is explained in Executing the Account Summary Population T2T section e Dimension Population should have been done before you execute the T2T batch Fact Common Account Summary Following are the lists of tables used in the population of Fact Common Account Summary table Below mentioned Dimension tables are required to be loaded prior to executing the T2T e DIM DATES DIM ACCOUNT e DIM CUSTOMER e DIM PRODUCT DIM CHANNEL DIM BANDS DIM ORG STRUCTURE Executing the Account Summary Population T2T Fact Common Account Summary
32. VARCHAR2 30 Staging Table Name NOT NULL SRC PRTY NUMBER 2 Priority of the Source when multiple sources are NULL mapped to the same target SRC PROC SEQ NUMBER 2 The sequence in which the various sources for the NOT NULL DIMENSION will be taken up for processing SRC_TYP VARCHAR2 30 The type of the Source for a Dimension that is NULL Transaction Or Master Source Dimension Loading Process 3 3 Column Name Data Type Column Description DT_OFFSET NUMBER 2 The offset for calculating the Start Date based on the NULL Functional Requirements Document FRD SRC_KEY NUMBER 3 NULL Sample Data This is the row put in by the solution installer for the Line of Business dimension MAP_REF_ NUM TBL NM STG TBL NM SRC PRTY SRC PROC SEQ SRC TYP DT OFFSET SRC KEY DIM LOB STG LOB MASTER 23 MASTER Note For any new dimension added a row will have to be inserted to this table manually e SYS STG JOIN MASTER The solution installer will populate this table for the seeded dimensions 3 4 Oracle Financial Services Data Foundation User Guide Column Name Data Type Column Description MAP REF NUM NUMBER 3 The Mapping Reference Number for this unique NOENDEL mapping of a Source to a Dimension Table COL_NM VARCHAR2 30 Name of the column in the Dimension Table NOT NULL COL_TYP VARCHAR2 30 Type of column The possible values are given in NOT NULL the following section STG_COL_NM
33. alues and this new record becomes the current record Each record contains the effective time and expiration time to identify the time period between which the record was active e Type 3 SCDs Creating a current value field A Type 3 SCD stores two versions of values for certain selected level attributes Each record stores the previous value and the current value of the selected attribute Dimension Loading Process 3 1 Prerequisites When the value of any of the selected attributes changes the current value is stored as the old value and the new value becomes the current value For more information on SCDs see e Oracle Data Integrator Best Practices for a Data Warehouse at http www oracle com technetwork middleware data integrator overview odi best practices datawarehouse whi 129686 pdf e Oracle Warehouse Builder Data Modeling ETL and Data Quality Guide at http docs oracle com cd E14072_01 owb 112 e10935 pdf Additional online sources include e http en wikipedia org wiki Slowly_changing_dimension e http www oracle com webfolder technetwork tutorials obe db 10g r2 owb owb10 gr2_gs owb lesson3 slowlychangingdimensions htm e http www oraclebidwh com 2008 11 slowly changing dimension scd e http www informationweek com news software bi showArticle jhtml articleID 2 04800027 amp pgno 1 e http www informationweek com news software bi showAticle jhtml articleID 5 9301280 An excellent published resource that c
34. areas are visible by clicking on the Subject Areas view in the ERwin data modeler tool as show below 2 14 Oracle Financial Services Data Foundation User Guide Subject Areas View in ERwin Data Modeler Tool D Stored Procedures Subject Areas B Main Subject Area f aCRM amp CA B CRM Attrition Analysis fg aCRM Attrition Analysis for Mortgage E aCRM Behavior Analysis aCRM Product Level Holding Analysis 3 aCRM Response Analysis 18 aCRM Risk Profile Analysis fl aCRM Service Request Analysis 18 ALM Bl 8 ALM Bl Account Summary 18 ALM BI Aggregate Base Currency ALM Measures fg ALM BI Aggregate Base Currency CashFlows fg ALM BI Aggregate Base Currency EAR fgl ALM BI Aggregate Base Currency EAR Average J ALM BI Aggregate Consolidated CCY ALM Measures f ALM BI Aggregate Consolidated CCY Stochastic VAR B ALM BI Aggregate Consolidated Currency EAR ALM BI Aggregate Consolidated Currency EAR Average ALM BI Aggregate Consolidated Currency LR Gap fB ALM BI Aggregated Base Currency LR Gap Analysis As can be seen above the Subject Areas are organized to support detailed level analysis related to a set higher level analytical reporting solutions The list of solutions supported by the OFSDF are Name Definition ALM Subject Areas corresponding to Asset Liability Management ALM Understanding OFSDF 2 15 Name De
35. as DQ Name Table Name Access Type Check Type Folder Creation Date Created By Last Modification Date and Status of the Rule A defined rule is displayed in Saved status until it is Approved Rejected by the approver An Approved rule can be grouped in order for execution and a Rejected rule is sent back to the user with the Approver comments You can add view modify copy approve reject or delete Data Quality Rules within the Data Quality Rule Summary screen You can also make use of Search and Pagination options to search for a Data Quality Rule based on DQ Name Table Name Folder or Check Type and view the existing Data Quality Rules within the system For more information refer to Oracle Financial Services Analytical Applications Infrastructure User Manual Create Data Quality Rule You can create a Data Quality Rule definition by specifying the DQ Definition details along with the type of validation check on the required table and defining the required validation conditions to query and correct the transformed data To create Data Quality Rule in the Data Quality Rule Summary screen 1 Click Add button in the Data Quality Rules tool bar Add button is disabled if you have selected any check box in the grid The Data Quality Definition screen is displayed 8 2 Oracle Financial Services Data Foundation User Guide DQ Definition Windows Internet Explorer Data Quality Definition DO Rule 1 Data Quiatty Rule Definitio
36. as Group Name Description Creation Date Created By Last Modification Date and Last Modified By You can Create and Execute DQ Group definitions and view modify copy or delete DQ Group definitions within the Data Quality Groups Summary screen You can also make use of Search and Pagination options to search for a DQ Group definition based on Group Name Description or Rule Name and view the existing DQ Group definitions within the system Create Data Quality Group You can create a DQ Group definition by defining the DQ Definition details and mapping the required DQ Rules which are authorized and approved within the system Create Data Quality Group You can create a DQ Group definition by defining the DQ Definition details and mapping the required DQ Rules which are authorized and approved within the system To create DQ Group in the Data Quality Groups Summary screen 1 Click Add button in the Data Quality Groups tool bar Add button is disabled if you have selected any check box in the grid The Data Quality Group Definition screen is displayed Data Quality Rules Execution 8 9 New DQ Group DQ Definition Mapping Windows Internet Explorer Data Quality Group Definition Data Quality Groups gt Data Quality Group Definition New mode amp Data Quality Group Definition Group Name DQ_Group_1 f i f Description Data Quality Group Definition 1 Folder FUSamhm Map DQ Rules Available Rules Mapped Rules 1
37. ce under a variety of economic scenarios To facilitate this the Oracle Financial Services Advanced Analytical Infrastructure OFSAAT provides a Stress Testing framework allowing risk analysis to be performed under a variety of known scenarios corresponding to different input parameter values to risk models The reporting model provides support for this kind of analysis via a Run Dimension it allows analytical engines to load multiple result sets identified by scenarios and hence permits reporting related to baseline and stress conditions in economic terms Support for Cross Functional Reporting Understanding OFSDF 2 13 The third critical feature of the Reporting area design is the support for cross functional reporting Typically Business Intelligence and Reporting Solutions work off a dedicated purpose specific data store called a data mart Data marts are function specific data stores typically star schemas eg Marketing data marts Risk Data Marts Customer Data mart that provide the necessary reporting and analytics relevant to a particular business function in the FSI Increasingly the trend is for cross functional analytics and reporting Majority of emerging needs relate to the analytical problems at the intersection of the distinct areas of Risk Performance Customer Insight and Compliance Reporting Area Details Similar to the staging data model the reporting model is divided into Subject Areas These subject
38. ch details how to extract the data dictionary from ERwin section 7 2 Oracle Financial Services Data Foundation User Guide Download Specifications As detailed in the staging area section the mapping from the Staging Data Model to use cases called a download specification provides an efficient way to manage the sourcing of data into the OFSDF staging area This is done by mapping the staging model at a column level to use cases This mapping information is embedded in ERwin at a column level using metadata called User Defined Properties UDPs The Download specifications can be extracted using pre built templates in a manner similar to the Data Dictionary Instructions for how to do so are also provided in the Technical Whitepaper on Data Model Document Generation which details how to extract the data dictionary from ERwin section Extending OFSDF Physical Data Model Oracle Financial Services Data Foundation OFSDF Physical Data Model PDM design evolves as the analytical use cases covered by the OFSDF and enhanced as improvements are engineered as a part of the product lifecycle While the model satisfies a very large number of analytical use cases across Risk Finance Marketing and Compliance subject areas customers may need to customize the model for a specific installation These custom changes however may impact the ability of the OFSDF installation to be upgraded to later versions of the product The guidelines outlined in this
39. ck if the following services are running on the application server 1 Iccserver 2 Router 3 AM Server 4 Messageserver 5 Olapdataserver For more information on how to check if the services are up and on and how to start the services if you find them not running refer to Oracle Financial Services Analytical Applications Infrastructure User Guide 4 Batches will have to be created for executing the function For more details refer to section How to Define a Batch page B 1 Tables Used by the Time Dimension Population Transformation For more details on viewing the structure of earlier tables refer to Oracle Financial Services Analytical Applications Data Model Data Dictionary or the OFSDF Erwin Data Model Executing the Time Dimension Population Transformation You can execute the function from the Operations formerly Information Command Center ICC framework module of OFSAAI This component for OFSDF 7 4 1 0 0 has been seeded with the Batch ID INFODOM POP DATES DIM which can be executed from Batch Execution section of OFSAAI In the Parameter List enter the Start Date and End Date For example 19940101 19941231 You can also define a new Batch and an underlying Task definition from the Batch Maintenance window of OFSAAI For more information on defining a new Batch refer to section How to Define a Batch page B 1 6 2 Oracle Financial Services Data Foundation User Guide To define a new task for a Batch de
40. cks for a 2 6 Oracle Financial Services Data Foundation User Guide specific table in the staging model These checks include 1 Value checks Nulls Value ranges business checks on numeric fields 2 Referential Integrity checks which are otherwise implemented as foreign key constraints Important This is also why the ER model of the staging area in ERwin does not contain any relationships the staging area is a physical data model which is deployed using the Analytical Application Infrastructure which manages it In summary the design of the staging area data model is to allow efficient data loading for analytics It thus has crucial differences from a general purpose repository of operational transactional data across a bank Details of the Staging Data Model The CSA model consists of over 400 tables and nearly 9000 attributes These tables are organized into multiple subjects currently by analytical use case solution need However it makes sense to first understand the staging area tables in terms of content before understanding how they map to analytical use cases There are two broad categories of staging data regardless of the use case or analytical application that requires it 1 Business Data This set of tables captures the actual business events and the resulting state of a bank from those business events The OLTP systems or Transactional Systems capture this information resulting from the execution of th
41. d in the form of discrete units called analytical applications spanning different analytical use cases ranging from Finance to Risk to Compliance These applications consist of custom built computational engines and numerical libraries and may execute processes on the data that range from simple aggregations to complex multi step stochastic processes such as Monte Carlo simulation Hence analytical applications place varying demands on the data infrastructure in terms of volumes and speed and hence place different demands on data architecture In practice the normalized 3NF design favored for Enterprise Data Warehouses often fails to be efficient or performant when it comes to analytical processing across a wide range of use cases Therefore the OFSDF recognizes the need for distinct application specific working stores separate from the staging and reporting area For example the OFSAA Asset Liability Management application ALM has a distinct set of ALM specific tables as does the Market Risk solution Understanding OFSDF 2 3 Important The structure of these processing area stores is decided by the actual analytical application and engine used The OFSAA suite of applications is organized this way with each application managing a specific set of tables schemas within the processing area The processing area tables schemas are NOT part of the OFSDF This is because the OFSDF is intended to be an open platform Other analytica
42. data types that are attached to each column within the model The following table lists the domains and their descriptions Serial No Domain Name Domain Description Date Timestamp Number Amount Code Flag Frequency DATE TIMESTAMP NUMBER 10 NUMBER 22 3 NUMBER S CHAR 1 NUMBER S A 8 Oracle Financial Services Data Foundation User Guide Serial No Domain Name Domain Description 8 ID VARCHAR2 25 9 Percent NUMBER 10 6 10 Percent_Long NUMBER 15 11 11 Phone_Fax_Number NUMBER 15 12 Rate NUMBER 10 6 13 Term NUMBER 5 14 Alphanumeric Code VARCHAR2 10 15 Name VARCHAR2 60 16 Currency_Code VARCHAR2 3 17 Short_Description VARCHAR2 60 18 Description VARCHAR2 255 19 Account_Number VARCHAR2 25 20 System Identifier NUMBER 20 21 Long Description VARCHAR2 4000 Naming Conventions Used in OFSDF Data Model A 9 B How to Define a Batch Batch Definition Create a batch from the OFSAAI Batch Maintenance screen as follows 1 From the OFSAAI Home menu navigate to Operations gt Batch Maintenance 2 In the Batch Maintenance window Select button from the Batch Name tool bar The New Batch Definition window is displayed 3 Enter the Batch details as tabulated Field Description Batch Name The Batch Name is auto generated by the system You can edit to specify a Batch name based on the following conditions The Batch Name should b
43. e bank s different business processes Broadly this information can be categorized as 1 Events Business transactions whether financial or non financial represent business happenings events that are relevant for analytical purposes For example a financial transaction by a customer on a current account is a specific event Events happen at a specific point in time and are recorded by OLTP systems In the staging area model there are several transaction tables that capture this detail for both financial and non financial transactions 2 State The net effect of business transactions is to change the bank s overall financial non financial state An example of this when a customer opens a checking current account and deposits money into it the account tracks the net effect of all withdrawals and deposits using a numeric quantity called a balance The account will also contain a list of all events Withdrawals deposits fees etc that resulted in the balance This state information is typically captured by product specific systems in a bank or FSI For example there is a lending system that captures details of loans and a current account Understanding OFSDF 2 7 system that captures details of checking and savings accounts which are distinct products In both cases the accounts are governed by contracts which refer to the terms and conditions governing business on that account 2 Reference Master Data Events and state
44. e through SOLPLUS Details are Time Dimension Population 6 3 Function Name FN_DIM_DATES Parameters P BATCH RUN ID P AS OF DATE P ST DT and P ED DT Sample Parameter Values Batch1 20091231 20081131 and 20091231 Note This DT should be executed for each year for which data is present in the source table Checking the Execution Status The Batch execution status can be monitored through Batch Monitor section of OFSAAI Operations module The status messages in batch monitor are N Not Started O On Going F Failure S Success The execution log can also be accessed on the application server in the directory FIC DB HOME log date where file name will have the Batch Execution ID You can access the database level operations log by querying the FSI MESSAGE LOG table Filter the Batch Run ID column for identifying the relevant log Note Check the profile file in the installation home if you are unable to find the above mentioned path 6 4 Oracle Financial Services Data Foundation User Guide 7 Using OFSDF This chapter details on how the OFSDF models are delivered and how they can be installed and configured into the required environment The first two sections gives you an understanding of the Delivery Mechanism and OFSDF Installation The Data Dictionary and Download Specifications sections explains how the Self documenting ERwin file includes the data dictionary and Download Specifications within ERw
45. e unique across the Information Domain The Batch Name must be alphanumeric and should not start with a number The Batch Name should not exceed 41 characters in length The Batch Name should not contain special characters and How to Define a Batch B 1 Field Description Batch Description Enter a description for the Batch based on the Batch Name Duplicate Batch Optional Select the check box to create a new Batch by duplicating the existing Batch details On selection the Batch ID field is enabled Batch ID If duplicate Batch is It is mandatory to specify the Batch ID if Duplicate Batch selected option is selected Select the required Batch ID from the list Sequential Batch Select the check box if the Batch has to be created sequentially based on the task specified For example if there are 3 tasks defined in a Batch task 3 should have precedence as task 2 and task 2 should have precedence as task 1 4 Click Save to save the Batch definition details The new Batch definition details are displayed in the Batch Name section of Batch Maintenance window with the specified Batch ID B 2 Oracle Financial Services Data Foundation User Guide
46. en by the OFSDF physical data model Here Physical Data Model OFSDF Staging OFSDF Reporting This user guide only details the structure and organization of the data repository that is covered by the data models in the OFSDF 2 Amanagement toolkit provided by OFSAAI that is used to manage the data repository by providing a collection of tools and frameworks based on a common metadata foundation This user guide DOES NOT cover the details of the Analytical Application Infrastructure as that is a separate product with its own documentation The architecture illustrates the following key concepts 1 A unified data sourcing area for analytics The OFSDF Staging Data Model provides the basis for central unified data sourcing layer for a wide variety of analytical needs The staging layer faces the operational OLTP and front office systems of a bank It consists of tables to capture key business and operational data from these systems which is then processed by one or more analytical engines 2 Aunified reporting consumption layer Analytical results can be simple to complex quantitative and qualitative measures of a bank s Risk Compliance Customer and Financial Performance The OFSDF Reporting data model is a dimensional data model spanning these key analytical functions It forms the foundation of OFSAA Business Intelligence applications but can clearly be used as the result data store for any equivalent engines and processes provided by
47. ent Details The OFS LDM is organized into a small set of high level subject areas each of which represents a key category of data that is produced by the business processes in a bank Understanding OFSDF 2 19 Key Subjects in FS Logical Data Model Party Calendar State Contracts Events FS Logical Transactions Data Model Within the above subject areas the FS LDM has 800 entities The various naming conventions used in OFSDF Logical Data Model are explained in Appendix A page A 1 For column level details see the Technical Whitepaper on Data Model Document Generation which details how to extract the data dictionary from ERwin section Mapping from Logical Data Model to OFSDF Staging Area In addition to the data dictionary and download specifications the OFSDF also provides a mapping between the relevant columns in the OFSDF physical staging area model and the OFSDF Logical Data Model These mappings are currently made available as Excel spreadsheets and part of the OFSDF package Following are the Excel spreadsheets Click the corresponding spreadsheet icon that you want to view R HHI LDM_Staging_ mapping_Appli cation xlsx 2 20 Oracle Financial Services Data Foundation User Guide LDM_Staging_ mapping_Cont ract xlsx LDM Staging mapping Orga LDM Staging mapping Part y xlsx Understanding OFSDF 2 21 LL LDM Stagi ng mappin g Accounti ng xlsx a dab LDM Stagi ng mappin _Campai
48. equired DO Name Ensure that you select the Saved DQ definition based on the Status indicated in the Data Quality Rules grid Perform one of the following e To Approve the DO definition click Approve button The User Comments screen is displayed Enter the notes or additional information to the user and click OK The selected DQ definition is approved and a confirmation dialog is displayed e To Reject the DQ definition click Reject button The User Comments screen is displayed Enter the notes or additional information to the user and click OK Data Quality Rules Execution 8 7 The selected DQ definition is rejected and a confirmation dialog is displayed Note The authorizer can approve reject only one definition at a time The Approved Rejected status of the DQ definition is indicated in the Status column of the Data Quality Rule Summary screen You can mouse over i button to view the Approver comments in a pop up Delete Data Quality Rule You can remove Data Quality Rule definition s which are created by you and which are no longer required in the system by deleting from Data Quality Rule Summary screen 1 Select the check box adjacent to the required DQ Name whose details are to be removed 2 Click Delete button from the Data Quality Rules tool bar 3 Click OK to confirm deletion Data Quality Groups Summary Data Quality Groups Summary within the Data Integrator framework of Infrastructure system facilitates
49. er so that a uniform Model Enhancement design recommendation can be provided to all customers OFSDF product management to evaluate if the enhancement request is applicable more broadly to other customers and if the change should in fact is to be taken as a design requirement for subsequent releases Staging Area Design Guidelines Following are the Staging Area Design Guidelines 1 Ensure that the naming conventions as detailed in Appendix A page A 1 section are followed Entity relationships and constraints are enforced through OFSAAI data management toolkit and are not enforced via database referential integrity checks The model should not be changed to enforce referential integrity checks and other data quality checks via database definitions All Staging Area tables must have a column that identifies the system from where data is sourced source system ID The code columns in master data tables and tables that contain dimension data should be designed to hold alphanumeric values The Domain dictionary maintains the list of attribute domains New columns must be identified with an existing domain instead of explicitly defining column data type and valid values See guidelines in Appendix A page A 1 section on the use of defined Domains Tables e g reference or look up tables with static data needed for only a specific application or use case should be a part of the application specific processing area and should not be par
50. es for running the OFSDF are as listed below 1 4 Oracle Financial Services Data Foundation User Guide Component Provider Purpose Oracle Financial Oracle Services Analytical Applications Infrastructure version 7 3 2 1 0 Oracle Database Oracle Enterprise Edition 11gR2 ERwin data modeler Computer Associates CA version 7 3 OFSAAT is the platform on which the OFSDF is deployed and operated It represents the OFSDF runtime environment and consists of a number of tools used to manage the data lifecycle within OFSDF from sourcing to reporting OFSDF is certified on Oracle Database releases 11gR2 and later ERwin is a data modeling tool that provides a visual environment to manage the complex enterprise data environment Important OFSAAI the infrastructure platform has its own prerequisites as well as supporting documentation Introduction to OFSDF 1 5 2 Understanding OFSDF This chapter explains the background of OFSDF its functional architecture along with the differences from traditional warehouse architecture OFSDF product package consists of the Logical Data Model and Analytical Data Warehouse model The individual components of Physical and Logical Data Model and Mapping Logical Data Model to OFSDF Staging Area are explained in detail This chapter covers the following topics e Background e OFSDF Architecture e OFSDF Physical Data Model e OFSDF Logical Data Model e Mapp
51. esults Area Design Guidelines 1 Ensure that the naming convention for results tables and columns detailed in Appendix A page A 1 section is followed Dimensional conformance should be maintained The same dimensional information should not be represented in different forms In addition dimension table design should be compatible with the slowly changing dimension process design and so should have the required columns Design considerations for adding new Dimensions and for adding columns to existing dimensions are detailed in a separate OFSDF Model extensions guide The common accounts summary fact table FCT COMMON ACCOUNTS SUMMARY consolidates measures at an account level granularity for all applications Account level attributes captured from source systems in staging and those attributes that do not vary between runs should be part of the common accounts summary table This enables integrated reporting of account information Note Any account level application specific attributes and measures that are computed by applications should be part of the application specific account summary entities Using OFSDF 7 5 4 The common customer summary fact table FCT COMMON CUSTOMER SUMMARY consolidates measures at a customer level granularity for all applications Customer level attributes captured from source systems in staging and those attributes that do not vary between runs should be part of the common customer summary table This
52. etails on how the OFSDF models are delivered and how they can be installed and configured into the required environment The first two sections gives you an understanding of the Delivery Mechanism and OFSDF Installation The Data Dictionary and Download Specifications sections explains how the Self documenting ERwin file includes the data dictionary and Download Specifications within ERwin itself For details on what Download Specifications are see Staging Area page 2 5 In addition the Extending Data Model section has guidelines for customization and designing the Staging and Results Area of Physical Data Model 8 Data Quality Rules Execution A Naming Conventions Used in OFSDF Data Model This Appendix chapter explains the various naming conventions used in OFSDF Logical Data Model and Physical Data Model In addition the domains in PDM and LDM are also listed with their descriptions B How to Define a Batch Related Information Sources 1 Technical Whitepaper on Data Model Document Generation 2 Installation Guide for Oracle Financial Services Data Foundation 7 4 0 0 0 3 Installation and Configuration Guide for OFSAAI release 7 3 2 1 0 1 Overview Introduction to OFSDF This chapter introduces you to Oracle Financial Services Data Foundation OFSDF its components its relationship with Oracle Financial Services Analytical Applications Infrastructure OFSAAI and the key prerequisites for running OFSDF This chapter co
53. features of the CSA are as follows 1 Mapping to Analytical Use Cases Since the primary purpose of the OFSDF is to be a data repository supporting analytics each database object in the OFSDF physical data model is necessarily mapped to a corresponding analytical use case These mappings are captured in the data model in the form of additional metadata called User defined Properties UDPs and can be leveraged to reduce the scope of data gathering efforts by focusing on clearly defined end use cases such as Basel II Market Risk Analytics ALM and others These mappings can readily be extracted into a Download Specification page 7 3 which lists the data demands for a specific analytical use case An example is shown below Understanding OFSDF 2 5 Download Specification Example The Mappings can be generated from the OFSDF ERwin file using ERwin s reporting tools 2 Schema Design The data model of the Common Staging Area is designed to facilitate loading efficiency This means that it is denormalized The primary focus of the staging area is to efficiently deliver data from operational systems such as Core Banking Trading and Wealth Management to multiple analytical applications that process this data Typically this data is extracted from source systems and loaded into OFSDF directly or alternatively into a pre defined file based operational image area from which it is subsequently loaded into the OFSDF schema In a la
54. finition Basel II Pillar I and III Pillar II Capital Planning Channel Management Common Account Summary Corporate Credit Risk Analytics Economic Capital GL Reconciliation ICAAP LRM Limit Analysis CI Market Risk Subject Areas corresponding to the Basel II Regulatory framework and its reporting requirements as specified in the framework These Subject areas provide support for reporting related to Capital Planning Support for analytics related to Channel Management which is part of the overall Customer Insight solution set A critical subject area that collects account level results from multiple analytical processes The Common Account Summary allows for cross functional Analytics such as Risk Adjusted Performance Measurement by combining outputs from Profitability and Risk solutions Support for detailed analytics and reporting on Corporate Credit Risk including Commercial Lending Credit Facilities Limits and Collateral Support for Aggregated Economic Capital Reporting based on risk assessments for Market Credit and Operational Risk Support for reporting specific to the Oracle GL reconciliation module that is part of the OFSAA product line This allows a reporting view of the reconciliation processes and outputs Supports reporting related to the Internal Capital Adequacy Assessment process Supports reporting related to Liquidity Risk Management processes within an organization Part
55. finition 1 Select the check box adjacent to the newly created Batch Name in the Batch Maintenance window Click Add button from the Task Details grid The Task Definition window is displayed Enter the Task ID and Description Select Transform Data component from the drop down list Select the following from the Dynamic Parameters list e Datastore Type Select the appropriate datastore type from the list e Datastore Name Select the appropriate datastore name from the list e IP address Select the IP address from the list Rule Name Select fn DimDates from the drop down list of available transformations This is a seeded Data Transformation which is installed as part of the OFSDF solution installer If you don t see this in the list contact Oracle support e Parameter List Enter the Start Date and End Date e Start Date This is the starting date from which the Transformation will populate DIM DATES table This date should be specified in YYYYMMDD format For example 20081131 e End Date This is the end date to which the Transformation will populate DIM DATES table This date should also be specified in YYYYMMDD format For example 20091231 Click Save The Task definition is saved for the selected Batch Execute the batch You can execute a Batch definition from the Batch Execution section of OFSAAI Operations module The function can also be executed directly on the databas
56. function load in which the table DIM ACCOUNT is getting loaded through the function FN POPDIMACCOUNT This loads the following columns into DIM ACCOUNT table V ACCOUNT NUMBER Dimension Loading Process 3 9 N ACCT SKEY N RCV LEG ACCT SKEY e FIC MIS DATE Where the sources are the different product processor tables present in the solution which are configured in FSI DIM ACCOUNT SETUP DETAILS table DIM ACCOUNT SCD Batch INFODOM DIM ACCOUNT SCD has been introduced with 30 tasks under it These 30 tasks represent the 30 SCD processes where different product processors would be the source and DIM ACCOUNT would be the target MAP REF NUMs 188 to 217 have been introduced into SYS TBL MASTER table and subsequently into SYS STG JOIN MASTER Depending on the requirement by an application a task can be excluded or included from the batch execution Improve SCD Performance SCD performance can be improved by providing hints and session alter statements This requires the presence of the following four columns in SYS TBL MASTER merge hint e select hint e session enable statement e session disable statement These columns are present in the OFSAAI versions 7 3 2 4 0 and above If these have to be used in OFSAAI versions 7 3 2 2 0 or 7 3 2 3 0 execute the following SOL queries TER TABLE SYS TBL MASTER ADD MERGE HINT VARCHAR2 255 TER TABLE SYS TBL MASTER ADD SELECT HINT VARCHAR2
57. g n xlsx LDM_Stagi ng_mappin g_Campaig n 7 3 xlsx LDM Stagi ng mappin g Collectio n Recove LL LDM Stagi ng mappin g Credit C ard 7 3 xlsx 2 22 Oracle Financial Services Data Foundation User Guide iil LDM Stagi ng mappin g Transacti on xlsx Understanding OFSDF 2 23 3 Dimension Loading Process Dimension Tables Population Data Foundation solutions use the SCD component to handle dimensional data changes Overview of SCD Process A Slowly Changing Dimension SCD is a dimension that stores and manages both current and historical data over time in a data warehouse SCDs are dimensions that have data that changes slowly rather than changing on a time based regular schedule It is considered and implemented as one of the most critical ETL tasks in tracking the history of dimension records There are three types of SCDs and you can use Warehouse Builder to define deploy and load all three types of SCDs e Type 1SCDs Overwriting In a Type 1 SCD the new data overwrites the existing data Thus the existing data is lost as it is not stored anywhere else This is the default type of dimension you create You do not need to specify any additional information to create a Type 1 SCD e Type 2SCDs Creating another dimension record A Type 2 SCD retains the full history of values When the value of a chosen attribute changes the current record is closed A new record is created with the changed data v
58. he storage of outputs from analytical tools applications and engines in a manner that is conducive to BI reporting The warehouse model is typically deployed into production via a set of management tools called the Oracle Financial Services Analytical Application Infrastructure OFSAAI The AAIisa separate product and is a prerequisite for OFSDF see Infrastructure page 1 4 As part of the OFSDF package there are additionally a number of scripts provided for basic operations such as internal data movement between the staging and reporting areas Relationship to Oracle Financial Services Analytical Applications The OFSDF is very closely related to the Oracle Financial Services Analytical Applications OFSAA in the following ways 1 Data Model 1 OFSDF Staging model provides the complete data sourcing foundation for OFSAA applications All application specific input data requirements are captured as part of the staging data model OFSDF Staging Model is a combination of all staging models supplied with each OFSAA application 2 OFSDF Reporting model provides the complete reporting data model common to all the OFSAA Business Intelligence BI applications This includes a single set of conformed dimensions as well as unified fact tables used for cross functional reporting For more information see OFSDF Reporting model Introduction to OFSDF 1 3 page 2 13 OFSDF reporting model is the superset of all the Bl application
59. iew Mode Entity Naming Conventions sse A 2 LDM Physical View Mode Table Naming Conventions sss A 3 OFSDF Physical Data Model Naming Conventions sss A 4 PDM Logical View Mode Naming Conventi0NS ocoooccnnocnnonooonnononnnonnnnannonnonnnonncnnnnnnnnoos A 4 PDM Physical View Mode Naming Conventions sss A 5 Domains PDM and LDM D eite te epe A e acd ue ada deeds A 8 B How to Define a Batch Batch Definition iniu ee eS a ee ae teet De eee B 1 Send Us Your Comments Oracle Financial Services Data Foundation User Guide Release 7 4 Part No E39096 01 Oracle welcomes customers comments and suggestions on the quality and usefulness of this document Your feedback is important and helps us to best meet your needs as a user of our products For example e Are the implementation steps correct and complete e Did you understand the context of the procedures e Did you find any errors in the information e Does the structure of the information help you with your tasks e Do you need different information or graphics If so where and in what format e Are the examples correct Do you need more examples If you find any errors or have any other suggestions for improvement then please tell us your name the name of the company who has licensed our products the title and part number of the documentation and the chapter section and page number if available Se
60. in itself For details on what Download Specifications are see Staging Area page 2 5 In addition the Extending Data Model section has guidelines for customization and designing the Staging and Results Area of Physical Data Model This chapter covers the following topics e Delivery Mechanism Installing OFSDF e OFSDF Supporting Documentation e Data Dictionary e Download Specifications e Extending OFSDF Physical Data Model Delivery Mechanism OFSDF being a collection of data model artifacts includes both a readily deployable model the OFSDF Physical Data Model as well as a reference data model the OFSDF Logical Data Model Both the data models Physical and Logical are delivered as ERwin files The OFSDF hence requires a license of the ERwin Data modeling tool ERwin is the current and only supported modeling tool to view and edit the model Currently the minimum version of ERwin supported is 7 1 ERwin 8 0 is not supported as of version 7 4 0 0 0 Using OFSDF 7 1 Installing OFSDF As detailed earlier OFSDF requires the Oracle Financial Services Analytical Application Infrastructure release 7 3 2 1 0 to deploy and operate Please refer to the separate OFSDF Install Guide for step wise instructions how to configure and install OFSDF into an AAI instance OFSDF Supporting Documentation The preceding sections have provided an overview of the organization of the OFSDF and its various component data models Append
61. ing from Logical Data Model to OFSDF Staging Area Background It is important to note that OFSDF architecture differs from traditional data warehouse architecture in key ways OFSDF was built to specifically address the key challenges of building a scalable practical data management platform to support Financial Services Analytics needs The differences from traditional BI architecture are highlighted and explained in the following sections OFSDF Architecture The following figure depicts the OFSDF Functional Architecture Understanding OFSDF 2 1 Platform Functional Architecture Staging Foundation Processing Analytics Results Reporting amp BI f Dimensions untransformed Messaging gt Unifi source attributes SCD SOA Transactions Ledger Reference Data Batch Feeds Hierarchies Replication Additional Po c Designed for Query Data Quality Performance Full History Store GL Reconciliation Aggregate AND Transaction main granulari ty Unified Result Structures Conformed dimensions Versioned Results Analytical Process Workflow Statistical Security amp Qualit Definition Execution Tools Modeli User admin Adjustments The OFSDF architecture can be thought of as two distinct interacting components The dark green colored cylindrical portions denote the data repository and the red colored portion denotes the management toolkit 1 A data repository whose physical structure is giv
62. ion see View Data Quality Group Summary Log section View Data Quality Group You can view individual Data Quality Group definition details at any given point To view the existing DO Group definition in the Data Quality Group Summary screen Data Quality Rules Execution 8 11 1 Select the check box adjacent to the required Group Name The mapped DQ Rules are displayed in the Data Quality Rules section 2 Click View button from the Data Quality Groups tool bar The Data Quality Group Definition screen displays the DQ definition details Modify Data Quality Group You can update the existing DO Group definition details except for the Group Name To updated the required DO Group definition details in the Data Quality Groups Summary screen 1 Select the check box adjacent to the required Group Name 2 Click Edit button from the Data Quality Groups tool bar The Data Quality Group Definition screen is displayed 3 Update the details and click Save to update the changes Copy Data Quality Group You can copy the existing DO Group details to quickly create a new DQ definition based on the existing details or by updating the required parameters To copy an existing DO Group definition in the Data Quality Groups Summary screen 1 Select the check box adjacent to the required Group Name in the list whose details are to be duplicated 2 Click Copy button from the Data Quality Groups tool bar Copy button is disabled if you have
63. ir nature could be a simple additive rollup wherein the child member balances are added up to arrive at the parent node balance for example Ending Balance or non additive rollups wherein a node formula is used to specify how to rollup the child member balances for example 3 month rolling average This chapter covers the following topics e Overview of Time Dimension Population e Prerequisites e Tables Used by the Time Dimension Population Transformation e Executing the Time Dimension Population Transformation e Checking the Execution Status Overview of Time Dimension Population Time dimension population transformation is used to populate the DIM DATES table with values between two dates specified by the user The database components used by the transformations are 1 Database function FN DIM DATES 2 Database procedure PROC DIM DATES POPULATION that is called by the function FN DIM DATES mentioned earlier Prerequisites The following are the prerequisites for Time dimension population Time Dimension Population 6 1 1 All the post install steps mentioned in the Oracle Financial Services Analytical Applications Infrastructure OFSAAI Installation and Configuration guide and the solution installation manual of Oracle Financial Services Data Foundation have to be completed successfully 2 Application User must be mapped to a role that has seeded batch execution function BATPRO 3 Before executing a Batch che
64. is the common data store for the outputs of these processes Outputs are computed quantitative measures and Key Performance Indicators that involve simple to complex mathematical and statistical processing using the raw data which is performed by specialized engines and computational models In the OFSDF design the Reporting Data Model design ensures that the historical data is maintained The key features of the design of the Reporting Area model are as follows 1 Design The Reporting Area data model is a dimensional data model This means that it consists primarily of central fact tables de normalized related to multiple dimension tables also called a Star Schema Additionally the dimension tables are shared across the star schemas in the reporting mode meaning they are Conformed Dimensions This means that Drill across reporting is naturally supported by the OFSDF design Additionally in keeping with the key principle of the OFSDF the Reporting Model is organized by use cases to facilitate reporting and Blin a wide variety of areas Support for multiple scenarios of analysis Increasingly as a result of the 2008 crisis the Financial Services industry is moving towards scenario based forward looking risk analysis instead of retroactive analysis The reporting data model has been designed to support scenario analysis of the sort required by financial institutions that need to measure and report risk and performan
65. ith the first letter of each word capitalized Staging Area structures and Results Area structures have different name prefixes and suffixes as outlined in the table below A 4 Oracle Financial Services Data Foundation User Guide PDM Area Prefix Suffix Example Staging Data All Tables Stage Stage Internet Sourcing Transactions Transaction Transactions Stage Card Tables Settlement Transactions Master Tables Master Stage Product Master Results All Fact Tables Fact Fact Process Datamart Cashflow All Dimension Dimension Account Tables Dimension PDM Physical View Mode Naming Conventions In the physical view model OFSDF PDM tables and columns may have abbreviated words joined by underscore character to form more meaningful and descriptive names Table names in the physical view are capitalized PDM Area Prefix Suffix Example Staging Data All Tables STG_ STG_LOAN_CO Sourcing NTRACTS Interface tables _INTF STG ORG UNI for data from T ATTR INTF MDM and core banking systems Transaction _TXNS STG CARDS SE Tables TTLEMENT TX NS Master Tables MASTER STG CUSTOME R MASTER Results AII Fact Tables FCT_ FCT_ACCOUNT Datamart _SUMMARY Naming Conventions Used in OFSDF Data Model A 5 PDM Area Prefix Suffix Example All Dimension DIM DIM PRODUCT Tables The table below lists the prefix and or suffix used for columns names in the physical view of the OFSDF PDM The prefix or suffix depends on the class a
66. ix A page A 1 explains the naming conventions used in OFSDF data model The OFSDF is a detailed model with nearly 850 entities across both the Staging and Results Area in the physical data model with another 800 entities in the Logical Data Model Since it is delivered as an ERwin file all the detailed metadata for the model Table Column Entity Attribute Relationship definitions are embedded in the file itself The advantage of this approach is that any site specific customizations to OFSDF can be performed within ERwin and the updated documentation is retained in the file in the form of additional metadata The 2 key detailed artifacts of OFSDF documentation that can be extracted from within the ERwin data model are 1 Data Dictionary 2 Download Specifications For more information on Dimension Management and AMHM refer to the Dimension Management chapter in Oracle Financial Services Analytical Applications Infrastructure 7 3 User Guide and Dimension Load Procedure section in Oracle Financial Services Analytical Applications Data Model Utilities 7 1 7 2 User Guide Data Dictionary The data dictionary for OFSDF can be extracted from the ERwin file using ERwin s reporting capability using a pre built set of templates for data extraction Instructions for how to do so are provided in a separate accompanying document that provides step by step instructions See the Technical Whitepaper on Data Model Document Generation whi
67. l applications and engines can equally provision data out of OFSDF by mapping their input requirements appropriately to the OFSDF staging area model Differences from Traditional Warehouse Architecture This table summarizes the differences of the OFSDF from a traditional Data Warehouse architecture Component FSDF Traditional BI Architecture Staging Layer Common staging Area CSA Usually a filesystem based area 3rd Normal Form Operational Data Store Enterprise Data Warehouse Data Marts Reporting Model where data from source systems are staged and is implemented as database schema Does not provide a physicalized model for a 3NF store The FS LDM can be physicalized as a 3NF store if desired Operational fine grained reporting will be fulfilled from the reporting area Set of star schemas with conformed dimensions Ralph Kimball approach where file based extracts operational images from source systems are staged prior to loading into a target schema Typically a 3rd normal form repository of atomic data that is loaded via ETL from the staging area Bill Inmon approach Set of star schemas With the preceding understanding in mind the following sections describe the data models in the OFSDF in greater detail OFSDF Physical Data Model The OFSDF Physical Data Model is the primary deployed structure in the OFSDF As detailed in the architecture section it has the following key characteristic
68. lumn data There can be one or more assignments tagged to a single condition However selecting severity as Error indicates there are no corrections and only facilitates in reporting the quantity of bad records e Select the check box adjacent to the required Condition expression and click Add button in the Assignment grid The assignment details are populated Note You can add an Assignment only if the Severity is Warning There can be one or more assignments tagged to a single condition e Specify the Assignment details as tabulated Field Column Name Assignment Type Assignment Value Description Select the Column Name from the drop down list Select the Assignment Type as one of the following No Assignment is the default selected assignment which does not have any target column update but the message details are pushed Direct Value enter the Assigned Value Another Column select the required Column as Assigned Value from the drop down list Code select the required Code as Assigned Value from the drop down list if any code leaf values exist for the selected base column If not you are alerted with a message indicating that No Code values existsfor the selected base column Select the Assignment Value from the drop down list according to the Assignment Type selected Data Quality Rules Execution 8 5 Field Description Message Severity Select the Message Severity as either 1 or 2 from
69. n regarding Group Execution refer to the section Execute Data Quality Group page 8 11 The following spreadsheet displays the total groups and corresponding Rules mapped to that Group x HHI DQ GROUP MAPPTW G xlsx DQ Group Execution You can execute the DO Rules either from Data Quality Group Summary window or via a Batch execution To execute the Data Quality Groups from the Data Quality Groups Summary window 8 14 Oracle Financial Services Data Foundation User Guide Select the check box adjacent to the Group you want to execute The corresponding DQ Rules are displayed in Data Quality Rules grid fi i Data Quality Groups Summary N Data Qsalty Groupe NT m O oorsowacoras rss 76 ACCOUNTNG MEAD nina PIURA DOFSOWRCOOI2 FSOFTASEOC TG ACCOUNTNO MEAD 19203 2 16 29 FSOFTSUSER Click Execute button The Group Execution window is displayed Data Quality Rules Execution 8 15 Data Quality Groups Summary u ea Cescreten Que vere Data Quality Groups og oodao Group Execution Windows Internet Explorer Group Execution ooo Data Quality Rules hare 3 Enter the MIS Date 4 Click Execute button from the Group Execution window The execution is triggered and the following message is displayed Quality Groups T information 14793 Webpage Diele Qwalrty Autes Once the execution is complete you can
70. n to validate attributes Access Type O Read Only Reaawrte Specific Check eed Ge Ge In the DQ definition section perform the following e Enter the Name by which you can identify the DQ definition e Enter a description or related information about the definition Select the Folder available for selected Information Domain from the drop down list Select the Access Type as either Read Only or Read Write Select the Check Type from the drop down list You can mouse over i icon for information e Select Specific Check if the defined conditions are based on individual checks on a single column Select Generic Check if the defined conditions are based on multiple columns of a single base table These checks are not pre defined and can be specified user defined as required If Specific Check is selected perform the following e Select Table Name and Base Column Name from the drop down list The list Data Quality Rules Execution 8 3 displays all the tables which are marked for Data Quality Rule in a data model which has the table classification property code set to 340 e Optional If you have selected Base Column of type Varchar Char select the Substring check box enter numeric values in Parameters Position and Length fields e Click the below button and define the Filter condition using the Specify Expression screen e Define the required Validation Checks by selecting the appropriate grid
71. nd data type of the column Column name prefix to indicate column datatype Column Data Type Prefix Varchar v_ Number n Date d Flag f Column name suffix for common classes of columns Column Class Suffix Method _method Percentage _pct Rate _rate Balance _bal _balance Amount _amt _amount Term _term Type _type Frequency _freq A 6 Oracle Financial Services Data Foundation User Guide In addition frequently occurring keywords in column names may be abbreviated as shown in table below Name Abbreviated Form Name Abbreviated Form Accrual accr Local Currency Icy Account acct Line Of Business lob Accounting Currency acy Maximum max Address addr Minimum min Adjustment adj Mortgage mort Advance adv Message msg Amount amt Multiplier mult Application app Number num Average avg Over Draft od Balance bal Option opt Business bus Origination org Currency ccy Percent pet Consolidation cons Payment pmt Customer cust Prepayment ppmt Description desc Product prod Dimension dim Source src Detail dtl Status stat Earnings at Risk ear Statistics stats End Of Period eop Temporary temp Naming Conventions Used in OFSDF Data Model A 7 Name Abbreviated Form Name Abbreviated Form Error err Total tot Flag flg Transaction txn Frequency freq Value at Risk var Future fut Value val Forex fx Generation gen General Ledger el Hierarchy hier History hist Domains PDM and LDM Domains are Logical
72. nd your comments to us using the electronic mail address financialservices_ww oracle com Please give your name address electronic mail address and telephone number optional If you need assistance with Oracle software then please contact your support representative or Oracle Support Services If you require training or instruction in using Oracle software then please contact your Oracle local office and inquire about our Oracle University offerings A list of Oracle offices is available on our Web site at www oracle com vii Preface Intended Audience Welcome to Release 7 4 of the Oracle Financial Services Data Foundation User Guide This document is the user guide and reference guide for the Oracle Financial Services Data Foundation OFSDF release 7 4 0 0 0 and is intended for System Administrator and all users who are instrumental in configuring and administering OFSDF with Oracle Financial Services Analytical Applications Infrastructure OFSAAI platform See Related Information Sources on page x for more Oracle product information Documentation Accessibility For information about Oracle s commitment to accessibility visit the Oracle Accessibility Program website at http www oracle com pls topic lookup ctx acc amp id docacc Access to Oracle Support Structure Oracle customers have access to electronic support through My Oracle Support For information visit http www oracle com pls topic lookup ctx acc amp
73. orate Merchant Banking Wealth Management Trading and Securities Services This is a logical data model which means that it cannot be readily used The structures modeled in the OFSDF LDM provide an abstract graphical model of the Financial Services domain using Entity Relationship modeling It can be thought of as a detailed blueprint for organizing data within a Financial Services institution It provides a reference guide for institutions to understand the salient data related to a specific business process To actually use the OFSDF in practice customers deploy the OFSDF Analytical Warehouse Model which is derived from the LDM blueprint 1 2 Oracle Financial Services Data Foundation User Guide Component Purpose Financial Services Analytical Warehouse Data Model Supporting scripts A physical data model that supports data sourcing and reporting related to key analytical use cases in the Financial Services industry The Warehouse Model is a physical data model unlike the OFSDF LDM meaning that it is readily deployable and consists of database object definitions and additional supporting scripts It is organized into two distinct sets of tables based on purpose The Staging model which facilitates data sourcing from the bank s internal operational systems such as Lending systems Trading Systems Collateral Management systems and Master Data Management systems The Reporting model which facilitates t
74. overs SCD in detail is The Data Warehouse Toolkit The Complete Guide to Dimensional Modeling by Ralph Kimball and Margy Ross The SCDs used in Data Foundation solutions are listed in the following spreadsheet ei SCD xls 1 The SCD executable should be present under installation home gt ficdb bin The file name is scd 2 The user executing the SCD component should have execute rights on the file mentioned as prerequisite in point 2 3 The setup tables accessed by SCD component are SYS TBL MASTER and SYS STG JOIN MASTER SYS TBL MASTER stores the information like which is the source stage table and 3 2 Oracle Financial Services Data Foundation User Guide the target dimension tables The source sometimes can be the database views which could be simple or a complex view SYS_STG_JOIN_MASTER stores the information like which source column is mapped to which column of a target dimension table It makes use of data base sequence to populate into surrogate key columns of dimension tables Tables Used by the SCD Component The database tables used by the SCD component are e SYS TBL MASTER The solution installer will populate one row per dimension for the seeded dimensions in this table Column Name Data Type Column Description MAP REF NUM NUMBER 3 The Mapping Reference Number for this unique NDENETE mapping of a Source to a Dimension Table TBL NM VARCHAR2 30 Dimension Table Name NOT NULL STG TBL NM
75. re Rates Economic indicators Interest Rates and other information relevant to analytical processing for Risk and Asset Liability Management applications 2 10 Oracle Financial Services Data Foundation User Guide Reference Master Data To view the current set of Master Reference data tables in the staging area open the OFSDF Staging Area model in the ERwin data modeling tool and open up the subject areas menu on the left hand side as shown below Right click and switch to the Staging Master Tables diagram to get the list of the master tables currently in staging Understanding OFSDF 2 11 Staging Master Tables Rollback Segments eer i f ji pn Me Mele lee lel GU UO RU EL ERU ERU ER ER ERI SR RU UI ERU HH i j j B a 3 E E E E E E 9 E E E E E a li Similar to the Business Data tables the Staging Master Tables subject area provides a single folder view of all of the reference master information currently required by the staging area 2 12 Oracle Financial Services Data Foundation User Guide Reporting Data Model Overview The Reporting Data Model is the point where outputs of analytical processing are aggregated for reporting and Business Intelligence BI tools Similar to the Common Staging Area being the foundation for data provisioning to analytical applications engines and processes the Reporting Data Model
76. rge bank it is not unusual to have 10s to 100s of millions of accounts and transactions Standard ETL Extract Transform Load approaches can fail to address this requirement adequately because of the complexity imposed by the target schema If the target schema is increasingly normalized then the ETL into this schema is correspondingly more complex requiring careful load ordering to prevent integrity related load failures as well as integrity validation prior to loading Such complex ETL processing is time consuming and is prone to failure To address this the CSA is designed to support a simplified loading process De normalized tables allow efficient data loading and subsequent transformations can be done to verify data integrity through a series of data quality checks This represents an ELT Extract Load Transform approach to data sourcing which is far more suited for an analytical data repository 3 Application managed Referential Integrity RD In conjunction with the database design of the staging schema a key feature is the management of Referential Integrity primarily in the application tier rather than within the database Rather than imposing foreign key relationships in the database which could cause complex loading order dependencies relationships between staging tables are managed by the Data Quality DQ framework a toolkit within the Analytical Application Infrastructure that captures the relevant data quality che
77. rmed data existing in an Information Domain You can access Data Quality Framework by expanding the Data Integrator Framework within the Unified Metadata Manager section in tree structure of LHS menu Data Quality Framework consists of the following sections e Data Quality Summary e Data Quality Group Summary Data Quality Summary Data Quality Summary within the Data Integrator framework of Infrastructure system facilitates you to create a DO Data Quality definition and define nine specific validation checks based on Range Data Length Column Reference Specific Value List of Value Code Null Value Blank Value Referential Integrity Duplicity and Custom Check Business You can also correct data for range column reference list of values null value and blank value parameters The defined Data Quality Rule checks can be logically grouped and executed together You Business Analysts need to have ETL Analyst function role mapped to access the Data Quality Summary framework within the Infrastructure system You can access Data Quality Summary by expanding the Data Quality framework within the Unified Metadata Manager section in tree structure of LHS menu Data Quality Rules Execution 8 1 gt Data Qulaity Rules Windows Internet Explorer Data Quality Rule Summary ORRCR72 The Data Quality Rule Summary screen displays the list of pre defined Data Quality Rules with the other details such
78. s 2 4 Oracle Financial Services Data Foundation User Guide Readily Deployable The Physical Data Model is a readily deployable physical schema It is provided as an ERwin data model file for details on ERwin see http erwin com products modeler and consists of tables grouped into distinct subject areas depending on function The tables are either used to gather source data Staging Area or as containers of outputs results from analytical processing and engines for reporting purposes Reporting Area Use case Driven The OFSDF Physical Data model is driven by a set of clearly identified analytical use cases spanning Risk Performance Customer Insight and Compliance Extensible While the OFSDF Physical Data Model satisfies a very large number of analytical use cases across Risk Finance Marketing and Compliance subject areas customers may find the need to customize the model for a specific installation These customizations may be done in accordance with guidelines published in Using OFSDF section page 7 1 of this manual The OFSDF Physical Data Model is divided into two primary areas Staging Data Model Overview Design The Common Staging Area Model CSA represents the point of entry of data into the OFSDF The CSA provides a simplified unified data sourcing area for inputs required by analytical applications and engines It consists of over 400 tables and nearly 9000 columns organized into distinct subjects The salient
79. s the various naming conventions used in Oracle Financial Services Data Foundation OFSDF Logical Data Model LDM and Physical Data Model PDM e OFSDF Logical Data Model Naming Conventions page A 1 e OFSDF Physical Data Model Naming Conventions page A 4 Domains PDM and LDM page A 8 OFSDF Logical Data Model Naming Conventions ERwin modeler tool allows two views namely Logical view and Physical view for each model file Accordingly the OFSDF LDM file can be viewed in logical view mode and physical view mode Different naming conventions and standards are applied to the two views of the OFSDF LDM Note The physical view of the OFSDF LDM is not the same as the OFSDF PDM The PDM is a distinct model and is shipped as a separate Naming Conventions Used in OFSDF Data Model A 1 ERwin file e LDM Logical View Mode Entity Naming Conventions page A 2 e LDM Physical View Mode Table Naming Conventions page A 3 LDM Logical View Mode Entity Naming Conventions Entities in the logical view are named in such a way that the names themselves convey the functional meaning of the entity The first letter of each word in an entity name is capitalized e g Application Stage Entity names also contain common suffixes for the classes of entities listed in the table below Class of entity Suffix Example Type Type Asset Liability Type Group Group Product Group Code Code Collection Status Code History History Account Balance Hi
80. section will help minimize the impact of custom changes to the model when the installation needs to be upgraded to a later version of OFSDF This section consists of the following sub sections e Customization Process Guidelines page 7 3 e Staging Area Design Guidelines page 7 4 e Results Area Design Guidelines page 7 5 e Upgrading Data Model page 7 6 Customization Process Guidelines Itis strongly recommended to consult OFSAA Support Field Engineers Consulting Staff before making any changes to the PDM for the following reasons Tables in the PDM common Staging Area are designed to meet the complex needs of data sourcing for many different financial services analytical use cases and as such have a large number of columns and the need for the modification should be reviewed with OFSAA consultants e The Results Area star schemas have been designed with a set of common fact tables Using OFSDF 7 3 and dimension tables to support integration of results from multiple analytical applications and any customization should be reviewed in order to ensure that the unified reporting capabilities of the model are preserved After a review with OFSAA field consultants an extension to the model should first be logged as a request for product enhancement via the standard support process This allows 1 Product support and product management teams to identify if a similar enhancement request was submitted on behalf of another custom
81. selected multiple check boxes The Data Quality Group Definition screen is displayed 3 Editthe DO Group Name and other details as required 4 Click Save The new DQ Group definition is displayed in the Data Quality Groups Summary screen View Data Quality Group Summary Log You can view the execution log details of Data Quality Rules in the View Logs screen The View Logs screen displays the details such as Check Name Log Message Message Date Message Time Total Rows Rows Impacted Assignment Type Assignment Severity and Severity Message of the executed Data Quality Rules To view the Data Quality Rule execution log details in the Data Quality Groups Summary screen 8 12 Oracle Financial Services Data Foundation User Guide 1 Select the check box adjacent to the Group Name in the Data Quality Groups grid The Data Quality Rules associated with the selected Group are displayed in the Data Quality Rules grid 2 Select the check box adjacent to the DO Name in the Data Quality Rules grid 3 Click View Logs button The View Logs screen is displayed 4 Inthe View Logs screen select the Information Date from the drop down list Based on the selection you can select the Group Run ID and Iteration ID from the corresponding drop down list 5 Clickthe below button from the Group Execution details tool bar E The Data Quality Rule Logs grid displays the execution log details of the selected Data Quality Rule You can also click
82. story Associative entities Map Customer Account Map Attribute names also contain common set of suffixes for the classes of attributes as listed in table below Class of Attribute Suffix Required Values If any Unique Identifier ID Reference Identifiers Reference ID Code Code Number Number Name Name Description Description A 2 Oracle Financial Services Data Foundation User Guide Class of Attribute Suffix Required Values If any Boolean Values Flag Indicator Indicator Percentage Percent Rate Rate Amount Amount Balance Balance Term Term Frequency Frequency Unit Unit Record created by Record modified by Record created on Record modified on Record validity start date Record validity end date YorN More than one value A B C etc Created By Modified By Created On Modified On From Date End Date LDM Physical View Mode Table Naming Conventions In the physical view of the OFSDF LDM follows a different naming convention for entities and attributes in the model and these reflect naming standards that are more readily acceptable to table and column naming constraints of common database systems The entity names in the physical view mode of the LDM are capitalized and each entity follows the following naming pattern FSW_ lt Subject Area gt _ lt Table Name gt Naming Conventions Used in OFSDF Data Model A 3 e FSW is a constant prefix e Subject Area
83. t task in the batch Here the target is the same for all the tasks under a batch In case any of the tasks are to be executed separately then the SESSION ENABLE STATEMENTS should be mentioned for any one of the tasks which is included in the batch for the execution Example MERGE HINT and SESSION ENABLE STATEMENT in SYS TBL MASTER Table Name Stage Table Name Merge Hint Session Enable Statement DIM ACCOUNT STG LOAN CONTR alter session enable ACTS V parallel DIM_ACCO parallel dml query UNT 10 alter table DIM ACCOUNT nologging parallel 10 e All the tasks can be executed in parallel This might cause the N RCV LEG ACCT SKEY to have an incremental value as compared to N_ACCT_SKEY e Execute the SQL file with all the SESSION DISABLE STATEMENTS after the successful completion of the SCD batch e Once the DIM ACCOUNT table is populated using this approach you will not be able to use the initial approach FN POPDIMACCOUNT as this will lead to skey conflict e Ensure that you have set the value of the sequence Dimension Loading Process 3 11 seq_dim_account_scd as max value of skey in DIM_ACCOUNT 1 before moving from old to new approach e TheF LATEST RECORD INDICATOR for an existing DIM ACCOUNT data already loaded by the function should be updated to Y before running the SCD failing which a new skey might get generated for the same account number e SCD execution occurs based on the GAAP code
84. t of the common Staging Area in OFSDF OFSDF download specifications identify the tables and columns for which data needs to be sourced for a specific analytical use case Any new tables and or column should have its APPLICATION USAGE UDP set with the appropriate application 7 4 Oracle Financial Services Data Foundation User Guide value so that the generated download specification includes the customized column and table The master list of UDP s are maintained as a central dictionary in ERwin All columns added or modified as a part of the customization should be marked as such 1 The column level UDP named CUSTOM must be marked YES identifying the column as a custom property 2 The Customization Reason UDP should be specified Valid values are provided as a drop down list and can be Pending Enhancement Request or Specific to Customer The Type of Change UDP should be set to the appropriate type of change as provided in the drop down list Length Datatype Logical Name Description and Addition Results Area Design Guidelines The Results Area consists of a set of star schemas with conformed dimensions and common fact tables Integration of results from multiple application use cases is achieved by having common fact tables for customer and account level measures The design of the results area allows for drill down and drill across BI reporting which should be preserved after customization Following are the R
85. tails how to extract the data dictionary from ERwin section OFSDF Logical Data Model The previous sections have discussed the components of the OFSDF Physical Data Model or the OFSDF Analytical Warehouse model this is the actual deployable physical database model that addresses a number of analytical needs out of the box as illustrated earlier The Logical Data Model is a reference data model of the Financial Services Domain which captures the data created by the key business processes in Financial Services A reference data model is different from the Physical Data Model in these ways Understanding OFSDF 2 17 Area FS LDM OFSDF Physical Model Purpose Format Structure Usage Scope Generic blueprint of the data produced by the business processes of a Financial Services institution Entity Relationship Diagrams organized into key Top Level subject areas Cannot be directly used needs to be used within a physical database design step to arrive at a database schema All the key business processes activities and their supporting reference data requirements Designed for analytical processing Risk Profitability Customer Insight Physical Tables Columns Definitions Readily deployable The business and reference data required for a set of analytical use cases However can be extended to serve other needs Relationship to the OFSDF Physical Model The coverage of the OFS LDM in terms
86. ted The information contained herein is subject to change without notice and is not warranted to be error free If you find any errors please report them to us in writing If this is software or related documentation that is delivered to the U S Government or anyone licensing it on behalf of the U S Government the following notice is applicable U S GOVERNMENT END USERS Oracle programs including any operating system integrated software any programs installed on the hardware and or documentation delivered to U S Government end users are commercial computer software pursuant to the applicable Federal Acquisition Regulation and agency specific supplemental regulations As such use duplication disclosure modification and adaptation of the programs including any operating system integrated software any programs installed on the hardware and or documentation shall be subject to license terms and license restrictions applicable to the programs No other rights are granted to the U S Government This software or hardware is developed for general use in a variety of information management applications It is not developed or intended for use in any inherently dangerous applications including applications that may create a risk of personal injury If you use this software or hardware in dangerous applications then you shall be responsible to take all appropriate fail safe backup redundancy and other measures to ensure its safe use
87. the lists of tables used in the population of Fact Common Customer Summary and these tables are required to be loaded prior to running the T2T DIM CUSTOMER DIM BANDS DIM EDUCATION DIM CUSTOMER TYPE DIM GENDER DIM INDUSTRY DIM CHANNEL DIM GEOGRAPHY DIM MARITAL STATUS Customer Summary Population 4 1 DIM_MANAGEMENT DIM_PROFESSION DIM_CREDIT_RATING DIM_VINTAGE DIM_MIGRATION_REASONS FCT_COMMON_ACCOUNT_SUMMARY FCT_LIMITS_SUMMARY STG_CUSTOMER_DETAILS STG_PARTY_RATING_DETAILS STG_PARTY_FINANCIALS Dimensions tables are loaded through the SCD process The fact tables FCT_COMMON_ACCOUNT_SUMMAY is loaded from the respective T2T processes Executing the Customer Summary Population T2T Fact Common Customer Summary T2T can be executed by executing task present in the seeded batch lt INFODOM gt _STG_TO_FCCS Following steps will help you to execute the batch 1 Go to the Batch Execution screen Select the seeded batch lt INFODOM gt _STG_TO_FCCS where INFODOM is the information domain where application is installed Select the AS OF DATE for which source customer information is required to be loaded into the table Click Execute Batch Monitor the status of the batch from Batch Monitor screen of OFSAAI Error Messages Following are the most common error message which will be logged in the T2T log file present in the FIC DB HOME logs t2t folder 4 2 Oracle Financial Services Data Foundation
88. uted for processing Execute Data Quality Group You can execute a defined DQ Group Definitions along with the mapped Rules and validation checks in the Data Quality Rule Summary screen You can also execute a DO Group in the Batch Execution screen of Operations module To Execute a DQ Group in the Data Quality Rule Summary screen 1 Select the checkbox adjacent to the required Group Name 2 Click Execute button from the Data Quality Groups tool bar Execute button is disabled if you have selected multiple check boxes The Group Execution screen is displayed 3 Inthe Batch Details section perform the following Select the MIS Date using the Calendar MIS Date refers to the date with which the data for the execution would be filtered In case MIS date is not present in the target table execution happens ignoring the date parameter Note The DQ Batch ID is auto populated and is not editable e Specify the percentage of Threshold limit in numeric value This refers to the maximum percentage of records that can be rejected in a job If the percentage of failed records exceeds the Rejection Threshold the job will fail If the field is left blank the default the value is set to 10076 4 Click Execute A confirmation message is displayed and the DO Group is scheduled for execution Once the DQ Group is executed you can view the details of the execution along with the log information in the View Logs screen For more informat
89. vers the following topics e Overview e Components of OFSDF e Relationship to Oracle Financial Services Analytical Applications e OFSDF Prerequisite Components and Tools The Oracle Financial Services Data Foundation OFSDF is an analytical data warehouse platform for the Financial Services industry OFSDF combines an industry data model for Financial Services along with a set of management and infrastructure tools that allows Financial Services Institutions to develop deploy and operate analytical solutions spanning key functional areas in Financial Services including 1 Enterprise Risk Management 2 Enterprise Performance Management 3 Customer Insight 4 Financial Crime and Compliance Management OFSDF is a comprehensive data management platform that helps institutions to manage the analytical data life cycle from sourcing to reporting and business intelligence BI using a unified consistent platform and toolset Introduction to OFSDF 1 1 Components of OFSDF OFSDF consists of the following components which are described in additional detail in the following sections Component Purpose Financial Services Logical Data Model FS LDM A reference model of the Financial Services domain spanning Banking and Capital Markets This model captures the business processes of a typical Financial Services institution in detail These primarily include core banking business activities such as Retail Banking Corp
90. view the details from the Data Quality Rules Summary window To view the execution logs 1 Select the check box adjacent to the rule of which you want tot see the execution log 8 16 Oracle Financial Services Data Foundation User Guide 2 Data Quality Groups Summary 1 Cata Quel Grouse a Sesca Name Descotes Ree ore a Data Quality Grovps Lia DORSO yrs nt Nare Foren 0 ACCOUNTNO MAD FSOFTSSEOC D sro AccovwTWO ntan MASTER FSOFTASEOC c FSOFTESEOC FSOFT4SEOC Y TSOFT4900C O 575 400 s PSOFTASRGC D 570 amum DNS SAMAR PSONTASLOC O sto amcan PSO SOC T D sto amucanons OS Fsorreuser O o sAcxcener mas PSOFPASEOC PSLP TUSEA __ Data Quality Rules Mace C corsowacsees FSUFT4SEGC 70 ACCOUNTNO HEAD 190012 22 425 FITER Click View Logs button The View Logs window is displayed View Logs 9 Data Quality Gr View Logs Group Execution details information Date Select ntocmatos Date LI Group Rund i teraton D Data Quality Rule Logs O FTE Check Name Log Message Message Date Message Time Total Rows Rows impacted Assgement Type Assgement Severty Severty Uesssge No Records Feund Lem Select the Information Date from the drop down list Select the Group Run ID from the drop down list Select the Iteration ID from the drop down list Click View Logs button The details of the selected Group Execution are displayed Data Quality Rules Execution 8 17
91. which is configured in SETUP MASTER table This has been introduced to tackle the scenario of multiple GAAP codes Whether or not there exist multiple GAAP codes SETUP MASTER should be manually configured as follows V COMPONENT CO V COMPONENT DE V COMPONENT VA DE SC LUE DEFAULT GAAP DEFAULT GAAP USGAAP Where V COMPONENT VALUE should be manually populated with the required GAAP code Handling Multiple GAAP Codes for the Same Account Number for the Same MIS Date in SCD When multiple GAAP codes exist for the same account number for the same MIS date configure the SETUP MASTER table manually as mentioned in the preceding section V COMPONENT VALUE will hold the GAAP code for which the SCD is to be executed If there are different GAAP codes for two distinct account numbers for the same MIS date then the SCD has to be executed for each GAAP code by changing the V COMPONENT VALUE manually in setup master table The SETUP MASTER table should have only one record WHERE V COMPONENT DESC DEFAULT GAAP Handling Multiple GAAP Codes for the Same Account Number for the Same MIS Date in the Function For FN POPDIMACCOUNT function you have to create views and use these views instead of the tables in the FSI DIM ACCOUNT SETUP DETAILS table For all Product Processors create a view on the table with filter on the GAAP code as where V GAAP CODE SELECT V COMPONENT VALUE FROM SETUP MA
92. xample Dim_Bands_V These views come as part of install for the dimensions seeded with the application Note For any new dimension added a view will have to be created similar to DIM_BANDS_V e DIM xdimensionname Output table to which SCD writes the dimension data A sequence should be added for every user defined dimension Example create sequence SEQ DIM lt DIM gt minvalue 1 maxvalue 999999999999999999999999999 increment by 1 Executing the SCD Component To execute the SCD component from Operations module of OFSAAL create a batch according to the following steps Note For a more comprehensive coverage of configuration and execution of a batch see Oracle Financial Services Analytical Applications Infrastructure User Guide e From the Home menu select Operations then select Batch Maintenance e Click New Batch symbol in Batch Name container and enter the Batch Name and Description e Click Save Select the Batch you created in the earlier step by clicking the check box in the Batch Name container e Click New Task symbol in Task Details container e Enter the Task ID and Description e Select Run Executable from the Component ID list e Click Parameters Select the following from the Dynamic Parameters List and then click Save e Datastore Type Select the appropriate datastore from the list e Datastore Name Select the appropriate name from the list Dimension Loading Process 3 7
93. you to logically group the define DQ definitions and schedule for execution DQ definitions can be executed either through Data Quality Groups Summary screen of Data Integrator framework or in Batch Execution screen of Operations module When a Data Quality Group is executed for processing the details of the execution are captured in a log file You Business Analysts need to have ETL Analyst function role mapped to access the Data Quality Summary framework within the Infrastructure system You can access Data Quality Groups Summary by expanding the Data Quality framework within the Unified Metadata Manager section in tree structure of LHS menu 8 8 Oracle Financial Services Data Foundation User Guide Data Quality Groups Windows Internet Explorer Data Quality Groups Summary Search Group Name Description Rule Name Data Quality Groups BB Gm O 1to4of4 Group Name amp Description Creation Date Created By Last Modification Date Last Modified By Asd exe DQ rule execution 10 04 2011 STUSER 10 04 2011 10 04 2011 STUSER 10 04 2011 10 04 2011 STUSER 10 04 2011 estGl 10 03 2011 STUSER 10 03 2011 2 Data Quality Rules 1 to 1 of1 DQ Name Table Name Column Name Creation Date Created By asd as ACCOUNT d closed date 10 04 2011 STUSER Local intranet far 1008 The Data Quality Groups Summary screen displays the list of pre defined Data Quality Groups with the other details such

Oracle® Financial Services Data Foundation

Contents

Download Pdf Manuals

Related Search

Related Contents

Oracle&reg; Financial Services Data Foundation

Contents

Download Pdf Manuals

Related Search

Related Contents

Oracle® Financial Services Data Foundation