1 / 91

Oracle Financial Services Profitability Analytics Product Presentation 1.3

Oracle Financial Services Profitability Analytics Product Presentation 1.3. Agenda. Product Positioning FSI Profitability Analytics Complementary components Positioning OFSA OBI EE MW and Applications Product Overview Physical Scripts & Aggregations Application Metadata

candid
Télécharger la présentation

Oracle Financial Services Profitability Analytics Product Presentation 1.3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Financial Services Profitability AnalyticsProduct Presentation1.3

  2. Agenda • Product Positioning • FSI Profitability Analytics • Complementary components Positioning • OFSA • OBI EE MW and Applications • Product Overview • Physical Scripts & Aggregations • Application Metadata • End user Layer and Reports Appendix Additional content on OBI EE MW positionning

  3. Typical Pain PointsSimple questions are often painfully difficult to answer…. • Can you report on Customer activity across Lines of Business? • Can you reconcile Employees performance with overall profitability ? • What is the profitability and the behavior of Customers across their Product holdings, over time ? • What is the demographic profile of the most profitable Customers ? • What acquisition channels are giving us profitable/ loss making Customers ? • Which Customer profile falls among the higher scores over a period of time ? Can marketing efforts be targeted to retain / attract such Customers ? • Which branches are profitable or unprofitable ? • What is the margin Contribution of each product ? • What is allocated costs to various products and What is the break up of these allocated costs ? • What is the average Contribution per account to the profitability of a product ? • What is the performance of my associates ? • How to adjust pricing strategies ?

  4. Typical Pain PointsTypical Business Challenges Growing number of accounts or revenue is no guarantee of growing profit. Not all relationships equally profitable • Customers who rigorously manage their personal finances to maintain a low Balance in their Current account or write a large number of low value checks limit the ability for a bank to earn revenue and are costly to service. • Customers who accepted every product that has been cross sold to them, but rarely use them, is likely to generate more cost than revenue.

  5. Typical Pain PointsTypical BI Challenges Faced • Single siloed reporting deployments within functions and departments • Limited number of users accessing OFSA detailed information, mostly in product and function siloed fashion • Report-centric model with backlog of new requests in IT • Top management requests get first priority, while needs of other Business users go unmet • Few users have timely and actionable information needed to optimize actions and decisions • Particularly difficult for middle management and “front line” users Results in OFSA rich dimensional profitability information under leveraged and ROI poorly realized

  6. OFS Profitability Analytics MajorValue Ads • Timely and broadly distribute granular profitability insight to support business decisions • Align all roles in Bank around single source of enterprise insight • Empower each user with deep self-capability of accessing ad hoc enterprise intelligence, drilling from high level to finest operational systems details • Integrate profitability information within all key decision-support systems (Sales automation, Loan pricing models, etc…) • Reduce burden on IT for producing reports, lower TCO and reduce insight cycle time • Robust, Performant, Pervasive • Enterprise Semantic Model (model centric vs. report centric architecture) • Empowering Business Users with Embedded Insight • Insight Driven Actions • Contextual Visualization with Guided Decisions • Business Process Optimization • Low TCO, … • Federate multiple sources of data into a single source of truth • Truly Match Revenues and costs of generating revenues • Reconcile Product, Customer, Org aggregated info with detailed Core system source • … Deep OFSA Analytical Data OBIEE Role Based Content Previously unavailable Business Information

  7. OFS Profitability Analytics Major Value Ads A Business Intelligence application that integrates robust OBI EE structure with deep OFSA Model, unleashing power of back office data and turning it into fully operational intelligence • Wide Functional Coverage for Banking Profitability Intelligence • Architected for Performance in Heavy Data Volume Environments • Low Total Cost of Ownership, Limited data movement, Template-Like design to ease implementation • Unleash power of OFSA Data content : Extends the wealth of back office data to executives, managers, and front line employees with Role based dashboards driving insight from robust detail customer relationship level information • Pervasive compelling and actionable insight, with comprehensive drill down pathes and analysis, increasing alignment and effectiveness Channels Expenses Accounts Perfor- mance Customer Profita- bility

  8. Oracle FSI Profitability AnalyticsTurning OFSA data into Operational Intelligence Addressing role based requirements 4. 5. Converting data into actionable intelligence Perfor- mance Profita- bility Customer Accounts Channels Expenses 3.Pervasive, Rich and Interactive Front End Front End Integration Operational Reporting Interactive Dashboards OBI EE FRONT END Ad Hoc B.I. Management Segmentation Web Services Openness Alerting Predictive Finance 2.Comprehensive, Powerful & Scalable Model Front Line operations OBI EE SERVER OBI EE Application Metadata Rich Business Semantic Layer Robust Architecture 1.Optimized OFSA data accesses Product Mgmt OFSA & SOURCES Ops. Sources OFSA Application Accounting Systems FDM Core Booking Apps Atomic data Aggregates Marketing CIF Computation Engines Other Sources

  9. Oracle FSI Profitability AnalyticsValue Layers of a Pervasive BI Solution Intelligent Interaction “What should I do now, at the moment of contact” Insight-driven Action “What should I do next?” Aligned Performance Management Increasing Business Value “How well are we meeting targets from bottom up” Dimensional Insight “Expenseslore my data” TransactionalReporting “Here are your reports” Complexity / Effort

  10. OFS Profitability Analytics Solving Business Challenges Operational Apps • Determine Profitable Customer Profile & Attributes, Migrate Customers to Higher Profitability Segments • Identify Product Line Contribution • Understand Key Cost Drivers & Impact on Profitability • Know Organizational and Employees level Contribution (Branch Networks, Regions, employees) • Produce Clear & Measurable Organizational Objectives & Performance Indicators • Align Whole Organization Around Consistent Business Performance Metrics, extend the Power of Analytics to Broader Audience • Link Departmental & Individual Goals to Corp Objectives(KPIs by Role) Example of measurements : • Fee income-to-total revenues, • Operating Efficiencies, Operating Expensesense-to-total revenues, • Sales Productivity on various revenue components, • Customer behavior and migrations over time, • Products per customers and household • Sales per associate, per product, per branch, • Trended information and variations information, • Return on Assets and return on Equity, • Balance Sheet Positioning -(net interest income), Annual benefit Time Efficiency Focus Source: Gartner Group Analytics Annual benefit Time Effectiveness Focus Source: Gartner Group

  11. OFS Profitability Analytics Solving Business Challenges • Gain from more focused use of capital, impact of predicted trends, risk exposure containment, enterprise-wide risk management etc. • Gain from focused and more efficient operations (service, sales, funding, marketing …), consistent customer experience across multiple channel , better cross-selling. • Gain from understanding customers’ values across all product lines, response to total customer needs, pro-active approach to customer requirements, focus on building and retaining high valued customer base • Gain from understanding transaction / service costing and benefit, identifying and retaining high profitable customers and orienting business to most profitable activities • Gain from, alignment of entire organization around single view of business measurement, maintain optimum organization structure, measure / score results, reward results etc.

  12. Referrals Cross-Sales Up-sell Sales Growth Base Sales Volume Development & Retention Costs Acquisition Cost DURATION OF RELATIONSHIP Increasing value of every step of Banking Customer Lifecycle … 5% increase in retention drives profits up 25 – 85%... Whom to target When to target What product to offer Approve/decline Credit amount Pricing Product Features Decisions Line management Re-pricing/renewal Service differentiation Segment & Acquire Line management Re-pricing/renewal Transaction authorizations Cross-sell Originate Grow & Service Increased market share Reduce marketing costs Increase revenue Adapt Pricing Reduce Risk Reduce capital Allocation Reduce origination costs Reduced application fraud Retain Upsell Xsell Business Results Increase usage & satisfaction Reduce attrition Reduce customer service costs Increase focus on high value customers Increase share of wallet Reduce revenue Cyclicality and risk Increase product per customers

  13. OFS BI applicationsBridging Profitability with Operational CRM Profitability BI Operations BI CRM BI Existing Loans Yield on Earning Assets SeCurrentities Number of Loans Outs. Loans Retained Interest Income Payoffs Number of Leads Loans in Portfolio Average Loan Balance Average Earning Assets Loan Loss Provisions Net Interest Income Non-Performing Loans Loans Acquired Lead Conversion Rate Interest Bearing Liabilities1 Borrowed Funds Interest Expensesense Application Close Rate Loan Applications Avg. Deposit Balance Avg. Interest Paid on Funds Commercial Deposits Revenue Loan Origination process Loan Funding Rate Core Deposits Number of Customers Customer Churn/ Retention Deposit Charges Number of Accounts Customer Acquisition Non Core Deposits Number of Accounts per Customer Number of Leads Mortgage Banking Income Deposit Charges per Account Existing Customers Lead Conversion Rate Loan Fees Number of Loans Outstanding Non-Interest Income Opportunity Close Rate Debit/ Credit Card Fees Number of Debit/Credit Cards Fee per Loan Operating Income Sales Rep Productivity1 Annual Debit/Credit Card Fees Investment Banking & Other Fees1 Annual Fee per Debit/Credit Card Interchange Fees Interchange Fee Rate Number of Credit Cards Cross Sell & Penetration Service to Sale Sales Cost SG&A Cost Marketing Cost Credit Card Sales Volume Average Spent per Credit Card New Cards Opened Number of Leads Operating Cost Customer Service Cost Lead Conversion Rate Teasers & Incentives Amortization of Intangibles Tax Expenses Other SG&A Cost Sales Rep Productivity1

  14. Summary Detailed OFS Profitability Analytics Empowering All Users – Not Just the Few • Corporate / Shareholders Financial Statements • Executives • Concise metrics on KPIs at corporate level • Highly correlated with overall strategic plans • Management • Focused on Key Performance Indicators for a business unit Report employee performance versus goals Broader scope of business • Employee Users • Targeted analytics to improve job performance • Broadest scope for ad-hoc analytics • Customers Highly focused analytics to monitor customer activity and guide behavior BOTTOM LINE RESULTS STRATEGIC TACTICAL & PROCESS EFFECTIVENESS ACTIONABLE & INCREASING EFFICIENCY INFORMATIVE

  15. Lead Customer ExampleTier 2 Regional Bank, within US Top 25 Extending wealth of back office data to executives, managers, and front line employees Reporting Executive Dashboard Top Bottom Products Role based dashboards driving insight from robust detail account level data containing statistical information, revenue, Expensesense and derived calculations from OFSA RAROC Scorecard Profitability Transactions Through compelling visual presentation and rich drill down and analysis, OBI EE increases the effectiveness of the OFSA application, supporting reporting and analysis….

  16. Agenda • Product Positioning • FSI Profitability Analytics • Complementary components Positioning • OFSA • OBI EE MW and Applications • Product Overview • Physical Scripts & Aggregations • Application Metadata • End user Layer and Reports Appendix Additional content on OBI EE MW positionning

  17. Daily Business Intelligence Internal Controls Manager Balanced Scorecard OracleE-Business Suite External Systems OFSA Applications and ModelSingle, Unified Architecture FEM Model Foundation (EPF in 11i)supports Oracle CPM FSI Analytics: FTP Enterprise Planning and Budgeting • Robust, shared relational dimensional model • Common engines & utilities • Seamless integration with Oracle General Ledger, open APIs for any source data Financial Consolidation Hub Profitability Manager Enterprise Performance Foundation (FEM in 4.5) Oracle DB

  18. Profitability Manager:Analyze Cost & Calculate Profitability • Build, Model and Control Profitability • model costs and profitability of products, customers, channels... • Enterprise to Lowest Level Results • Business Rules Based Engine • Scenario Testing (Rule Sets) • Integrates Multi Data Sources • Integration with Oracle Applications (including Oracle G/L) • Shares data with EPB & FCH, for planning, profitability analysis on consolidated results • Model Supports Transactional Data • Open to 3rd Party Data Sources • Easy to Use • Build from a Finance Perspective • Point-Click Environment • Embedded Activity-Based Management • Robust ABC Engine

  19. Mgmt Ledger P&L Drivers Definitions Customer Data Ad Hoc Activity Rule Sets Statistics Role Based Users Control Business Process Rates Cleansing Validation Methods Control SeCurrentity Reconciliation Audit Oracle OFSA Profitability ManagerAnalytics Methodology General Ledger Primed Data Transactional Data Rules Engine Other Other

  20. Oracle OFSA Profitability ManagerAnalytics Methodology Achieve greater insight into the profitability of global operations and provide actionable information for driving performance improvement Profitability Analysis and Costing • Achieve global viewof profitability from multiple data sources • Calculate profitability based on user-definable rules and formulas • Analyze profitability along any dimension including geography, product, channel, segment, customer, organization, etc Cost Pools Models % Distributions Methods Rate X Volume ProfitabilityEngine Drivers Statistics Resources Costs / Revenue Activities Transactions

  21. Agenda • Product Positioning • FSI Profitability Analytics • Complementary components Positioning • OFSA • OBI EE MW and Applications • Product Overview • Physical Scripts & Aggregations • Application Metadata • End user Layer and Reports Appendix Additional content on OBI EE MW positionning

  22. WebServices MS Office Plug-in Interactive Dashboards Reporting & Publishing Ad-hoc Analysis Proactive Detection and Alerts Disconnected Analytics OLTP & ODS Systems Data Warehouse Data Mart SAP, Oracle PeopleSoft, Siebel, Custom Apps Files Excel XML Business Process Oracle BI Suite Enterprise EditionUnified Business Intelligence Infrastructure Enterprise Business Model and Abstraction Layer Intelligent Caching Services Oracle BI Server Multidimensional Calculation and Integration Engine Intelligent Request Generation and Optimized Data Access Services

  23. Oracle BI Suite Enterprise EditionSingle enterprise information model with one “version of the truth” • Unified BI metadata model • Consistent definition of business measures, metrics, calculations • “Model once, deploy everywhere” • Same model accessed by all BI tools, all users, across all data • Consistent view of information across departments assures enterprise-wide alignment Enterprise Semantic Model Presentation Layer • Simplified view • Logical SQL interface Semantic Object Layer • Dimensions • Hierarchies • Measures • Calculations • Aggregation Rules • Time Series Physical Layer • Map Physical Data • Connections • Schema

  24. Oracle BI Suite Enterprise EditionSimplify access to information spanning multiple data sources • Easily construct new reports, dashboards, or alerts from data spanning multiple data sources • Users shielded from complexity of physical data • Self-service, “drag and drop,” seCurrente access • More complete and timely information for all users • Historical and real-time data seamlessly linked • Powerful, cross-departmental analysis • How does supplier performance impact customer satisfaction and revenue? • How is my marketing spend affecting pipeline and revenue?

  25. Oracle BI Suite Enterprise EditionDrive pervasive use across departments, users, and processes Unsurpassed Usability Anywhere, Anytime • Rich, interactive Web interface • Guided analytics • Seamless user Expenseserience • Robust personalization • Simplified business model • Abstraction from physical sources • Complex calculations made easy • Dashboards, ad hoc, alerts, reports • Any device • Full mobile disconnected analytics • Integrated with business processes • Embedded analytics • Real-time predictive • Portal integration • Extranet

  26. End User ProductsEnterprise Edition • Guided Expenseserience leads user from insight to action • Intuitive and interactive dashboards/scorecards • Personalized, function-specific and role-based Oracle InteractiveDashboards • Ad-hoc data Expensesloration • True business user self sufficiency • Create, modify and author analysis, pivots and dashboards Oracle Answers • Dynamic problem/opportunity detection and notification • Automated analytic workflows and processes • Tailored delivery for wide range of mobile devices Oracle Delivers • Schedule & distribute enterprise-class reports • Sophisticate “pixel-perfect” report layout controls Oracle Reporting & Publishing

  27. Tailored and Personalized InteractionsTransparent Business Intelligence

  28. This is no longer about a stack of reports. Guided Analytics drives alignment and actions through the organization by modeling and making accessible discovery and decision making best practices. Guided AnalyticsLeading Users from Discovery to Action

  29. Delivers/Analytic Agents provides true real-time in context sense and respond capabilities across systems tailored for any user on any device. Sense and RespondReal-Time Proactive Monitoring and Response

  30. Web eMail Wireless Operational End user adoption is about information consistency and availability where and when its needed. The Common Enterprise Information Model breaks down data silos and makes information available on-demand. Common Enterprise Information Model“Model once, deploy everywhere” Operational Sources Delivered or Accessed Where and When Needed in Time to Drive Action Data Warehouse Aggregate Data (“Cubes”)

  31. End user adoption is about information consistency and availability where and when its needed. The Common Enterprise Information Model breaks down data silos and makes information available on-demand. Common Enterprise Information Model“Model once, deploy everywhere”

  32. Open and Extensible SOA and Open Standards • Full BI functionality Expensesosed via Web Services • Websphere Portlet SDK • Open SQL-Based API Web Browser Support Microsoft Office and Reporting Services • Support for Netscape and Mozilla Firefox • Bi-directional support for Microsoft

  33. Oracle Interactive Dashboards Oracle Answers Oracle Delivers Oracle Disconnected Analytics Oracle Briefing Books Oracle BI Server Oracle BI Administration Tool Share Common: SeCurrentity Services Metadata Services Reporting Objects (Views) Query Intelligence Engine User Expenseserience Administration OracleBI Enterprise EditionCommon BI Infrastructure All Oracle BI Suite Modules:

  34. Product StrategyKey Differentiators • Enterprise Semantic Model • Model Centric vs. Report Centric Analytics • Pervasive Business Insight • Empower Business Users with Embedded Insight • Real Time Predictive Insight • Activity Monitoring and Predictive Analytics • Insight Driven Actions • Contextual Visualization with Guided Decisions • Business Process Optimization • Sense & Respond with Intelligent Business Processes • Fastest Time to Value • Pre-Packaged Analytic Applications

  35. Broadest Spectrum of BI Functionality Visualize Analyze Publish Integrate Data Model Collaborate Monitor SeCurrente & Manage Compare Predict Sense Respond

  36. FOR ADDITIONAL CONTENT ON OBI EE MW POSITIONNING PLEASE REFER TO APPENDIX SLIDES AT THE END OF THIS PRESENATION

  37. Proactive Detection and Alerts Interactive Dashboards Reporting & Publishing Ad-hoc Analysis Disconnected Analytics MS Office Plug-in WebServices Oracle BI ApplicationsMulti-source Family of Apps Built on OBI EE Sales Service &Contact Center Marketing Order Management& Fulfillment Supply Chain Financials HumanResources FSI Profit-ability Oracle BI Applications • Common Enterprise Information Model Oracle BI Apps built on Oracle BI EE Suite • Prebuilt Hierarchies, Drill Paths, SeCurrentity, dashboards, reports • Based on industry and analytic best practices IVR, ACD, CTI Hyperion MS Excel Other Data Sources Packaged ETL Maps UniversalAdapters

  38. These steps require different types of BI and DW technology Develop detailed understanding of operational data sources Design a data warehouse by subject area License an ETL tool to move data from operational systemsto this DW These steps require significant resources with specialized skills / Expensesertise Build ETL programs for every data source License interactive user access tools Research analytic needs of each user community Build analytics for each audience These steps typically take a long time to perfect as knowledge of best practices is learned License / create information delivery tools Set up user seCurrentity & visibility rules Perform QA & performance testing Manage on-going changes/upgrades Oracle BI ApplicationsSteps Required to Build a BI Solution

  39. Oracle BI ApplicationsChange the Economics of BI Oracle BI Applications solutions approach: • Faster time to value • Lower TCO • Assured business value Build from Scratchwith Traditional BI Tools Oracle BI Applications Training / Roll-out Define Metrics& Dashboards Greater Business Insight DW Design Back-end ETL andMapping Training / Rollout Easy to use, easy to adapt Role-based dashboards and thousands of pre-defined metrics Define Metrics& Dashboards Prebuilt DW design, adapts to your EDW DW Design Back-end ETL andMapping Prebuilt Business Adapters for Oracle, PeopleSoft, Siebel, SAP, others Months or Years Weeks or Months Source: Patricia Seybold Research, Gartner, Merrill Lynch, Oracle Analysis

  40. Key Financial Services OBIE EE Customers

  41. Business Intelligence CustomersBusiness Analytic Tools and Applications – Select References Consumer Goods High Tech Communications Automotive Finance / Banking Other Media / Energy Aero / Industrial Insurance / Health Life Sciences

  42. Agenda • Product Positioning • FSI Profitability Analytics • Complementary components Positioning • OFSA • OBI EE MW and Applications • Product Overview • Physical Scripts & Aggregations • Application Metadata • End user Layer and Reports Appendix Additional content on OBI EE MW positionning

  43. Dashboards by Role Metadata Oracle BI Presentation Services Reports, Analysis / Analytic Workflows Metrics / KPIs Logical Model / Subject Areas Oracle BI Server Physical Map OFSA Aggregation Tables PL-SQL ODI Aggregation Conversion & Calculation Extraction Oracle FSI Profitability AnalyticsApplications Architecture Administration • Role Based Dashboards • Analytic Workflow • Guided Navigation • SeCurrentity / Visibility • Alerts & Proactive Delivery 3 • Logical to Physical Abstraction Layer • Calculations and Metrics Definition (Trending, Percentiles, Migrations, Time comparisons…) • Natural Drilling Paths to Finest Details • Dynamic SQL Generation • Template Based Design easy configuration 2 Direct Access to Source Data • Minimum Data Moving, • Reduced Calculations & De-normalization • Conformed Dimensional Aggregations • DB Performance Optimization • Database specific indexing • Multistage and Customizable 1 Other Datasources OFSA Federated Data Sources

  44. Dashboards by Role Metadata Oracle BI Presentation Services Reports, Analysis / Analytic Workflows PL-SQL ODI Aggregation Conversion & Calculation Extraction Application ArchitecturePhysical Scripts and Aggregations Administration OFSA FEM detailed datamodel • A number of instrument siloed-fragmented fact tables optimized for processing purposes but not adapted to transversal analysis : • require to be transversally aggregated for any business analysis, • carry with different set of columns in each table, • hold large volume of detailed data, • carry original Currency information only, • Normalized and leveraging intersection tables to join to large dimension (Parties) Metrics / KPIs Logical Model / Subject Areas Oracle BI Server Physical Map Direct Access to Source Data OFSA Aggregation Tables Other Datasources OFSA Federated Data Sources

  45. FSI PA Loading scripts Aggregate and optimize FEM data-structure to efficiently support multi-dimensional Business Intelligence : Gathers instrument data for a set of selected 100+ pertinent measurements and 100+ dimensional attributes Processes FX conversion to single functional Currency, Denormalize facts and dimensions, Computes specific selected calculations Compute metrics aggregation tables at several dimensional levels (product, employee, org…) Script layered architecture for easy implementation Provides isolation, modularity and extensibility Ability to extend with additional Sources Architected for performance (incremental extractions, Indexing, etc) Dashboards by Role Metadata Oracle BI Presentation Services Reports, Analysis / Analytic Workflows PL-SQL ODI Aggregation Conversion & Calculation Extraction Application ArchitecturePhysical Scripts and Aggregations Administration Metrics / KPIs Logical Model / Subject Areas Oracle BI Server Physical Map Direct Access to Source Data OFSA Aggregation Tables Other Datasources OFSA Federated Data Sources

  46. Dashboards by Role D2 D1 Metadata Oracle BI Presentation Services A D4 B Reports, Analysis / Analytic Workflows D3 C D1 D2 … A A A A A A A A A H1 H2 B B B B B B B B B D4 C C C C C C C C C i 1 D3 … … … … … … … … … H1 H2 D2 D1 PL-SQL ODI Aggregation 7 6 5 4 3 Conversion & Calculation 2 1 D4 D3 Extraction H2 H1 Application ArchitecturePhysical Scripts and Aggregations Administration Extended OFSA FDM AGGREGATE Metrics / KPIs Logical Model / Subject Areas Oracle BI Server 2 Physical Map AT SELECTED DIM LEVELS : AGGREGATIONS Direct Access to Source Data OFSA Aggregation Tables CONVERT OFSA FDM 1 ON SELECTED METRICS : CONVERSION DENORMALIZATION COMPUTATIONS Other Datasources OFSA Federated Data Sources

  47. Application ArchitecturePhysical Scripts Sequence C_INST 18 C_COA_AGG 11 H_COA_AGG 21 C_ORG_AGG 12 H_ORG_AGG 22 C_COA_ORG_AGG 13 H_COA_ORG_AGG 23 C_COA_EMP_AGG MONTH_D 14 H_COA_EMP_AGG QTER_D 24 YEAR_D C_HH_AGG 15 C_HH_AGG 25 16 C_COA_ORG_CF_AGG H_COA_ORG_CF_AGG 26 C_CUST_AGG H_CUST_AGG 17 27 FEM_CHECKING LEDGER_STAT E OFS_BI_LEDGER_STAT FEM_COMMERCIAL_LOANS FEM_PARTIES OFS_BI_STRATS_MV FEM_CONSUMER_LOANS D FEM_PARTY_ACCT_REL FEM_SAVINGS FEM_TERM_DEPOSITS B H_INST_F FEM_WHOLESALE_FUNDING C D FEM_MORTGAGES 10 FEM_CREDIT_LINES FEM_INVESTMENTS OFS BI Config Tables A FEM_RETIREMENT_ACCOUNTS Exchange_ Rate FX_RATES FEM_MERCHANT_CARDS A FEM_MORTGAGE_BACK_SEC ATT_INST FEM_LEASES A PFT_PARTY_PROFIT_DETAIL FEM_OFF_Balance_SHEET FEM_PARTY_PROFITABILITY FEM_TRUSTS Currentt Instruments fact tables append and metrics aggregations C Attributes Table refresh with load of most Currentt value of attributes for distinct accounts out of history in instruments tables + Compilation of Exch Rates + Appending Currentt snapshot date to list of sshots A FEM_OTHER_SERVICES Full refresh of aggregate Currentt period facts MVs / Partiionning D FEM_CREDIT_CARDS E OFS_BI Ledger Stat Table append Instruments Facts Table append with Currentt month metrics Fx converted values from all instrument tables – denormalizing few attributes – calculation of specific metrics (pctls, # of cust,…) B E Possibilities of extensions to source from additional PFT tables (not included in OOB script)

  48. Application ArchitectureAggregations Structure OFSA Table DESC ATTRIBUTES TABLES FLATTENED HIERARCHIES PFT_ACCOUNT_CATEGORY_DSC INSTRUMENTS ATTR. 2 OFS BI Table OFS_RPT_HIER_COMMON_COA_ID 5 PFT_ACCOUNT_GROUP_DSC OFS_BI_LEDGER_STAT 4 6 ATT_INST OFS BI View OFS_RPT_HIER_GL_ACCT_ID OFSA_ACCOUNT_OFFICER_DSC OFS_BI_STRATS_MV OFS_RPT_HIER_ORG_UNIT OFSA_GEOGRAPHIC_LOC_DSC MONTH_D FEM_PARTIES PARTIES ATTR. … OTHER XXX_DSC TABLES 30 TABLES QTER_D HIER_PARTIES YEAR_D HH_V H_COA_AGG C_COA_AGG C_ORG_AGG H_ORG_AGG FEM_CHECKING_TRNS 3 C_COA_ORG_AGG 1 H_COA_ORG_AGG FEM_COMMERCIAL_LOANS_TRNS FEM TRANSACTIONS TABLES INSTRUMENT FACT AGGREGATIONS C_COA_EMP_AGG FEM_CONSUMER_LOANS_TRNS H_COA_EMP_AGG FEM_SAVINGS_TRNS C_HH_AGG C_HH_AGG 8 FEM_TERM_DEPOSITS_TRNS C_COA_ORG_CF_AGG H_COA_ORG_CF_AGG FEM_WHOLESALE_FUNDING_TRNS C_CUST_AGG H_CUST_AGG FEM_MORTGAGES_TRNS C_INST FEM_CREDIT_LINES_TRNS H_INST_F FEM_INVESTMENTS_TRNS FEM_RETIREMENT_ACCOUNTS_TRNS FEM_MERCHANT_CARDS_TRNS CALENDAR FEM_MORTGAGE_BACK_SEC_TRNS FEM_LEASES_TRNS FEM_OFF_Balance_SHEET_TRNS 7 9 FEM_TRUSTS_TRNS CONFIG & PROCESS TABLES FEM_OTHER_SERVICES_TRNS INST_CONFIG EXCHANGE_RATE FEM_CREDIT_CARDS_TRNS ATT_INST_BACKUP AS OF DATE CONFIG ATT_INST_BACKUP_TEMP PROCESS_LOG

  49. Application ArchitectureAggregations Structure • Pre-aggregated metrics by major dimensions in materialized views format • By Organizations, Product, Customers, Time, • Optimization of views building procedures (incremental process, indexing) • Minimum data moving • Detailed data level leveraged directly in source tables (transactions, customers, LOVS…) • Selected reduced set of fields aggregated • Specific functional content prebuilt in views • Specific calculations initiated @ aggregations level (Percentiles, Penetration, Weighted avgs). Balances

More Related