1 / 93

INSTRUCTIONS FOR USE

INSTRUCTIONS FOR USE. THIS PRESENTATION IS MEANT FOR TECHNICAL AUDIENCES TO COVER DETAILED ARCHITECTURE FOR THE ORACLE BI APPS. OTHER RELATED MATERIALS. Each application has its own presentation Financial Analytics Supply Chain Analytics Order Management and Fulfillment Analytics

azura
Download Presentation

INSTRUCTIONS FOR USE

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. INSTRUCTIONS FOR USE • THIS PRESENTATION IS MEANT FOR TECHNICAL AUDIENCES TO COVER DETAILED ARCHITECTURE FOR THE ORACLE BI APPS

  2. OTHER RELATED MATERIALS • Each application has its own presentation • Financial Analytics • Supply Chain Analytics • Order Management and Fulfillment Analytics • Human Resource Analytics • Sales Analytics • Service and Contact Center Telephony Analytics • There are presentations for specific topics • Oracle BI Apps Architecture Overview • Oracle BI Apps Consolidated Data Model

  3. Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

  4. Oracle BI Applications Architecture

  5. Agenda • Product Overview • Technical Architecture • Q&A

  6. <Insert Picture Here> Product Overview

  7. Sales Service &Contact Center Marketing Order Management& Fulfillment Supply Chain Financials HumanResources Oracle BI Applications Proactive Detection and Alerts Interactive Dashboards Reporting & Publishing Ad-hoc Analysis Disconnected Analytics MS Office Plug-in WebServices Oracle BI Suite EE IVR, ACD, CTI Hyperion MS Excel Other Data Sources BAW Schema Packaged ETL Maps UniversalAdapters Oracle BI ApplicationsSingle- and multi-source Analytic Apps Built on BI Suite EE

  8. ConsumerSector Insurance & Health Complex Mfg FinancialServices HighTech LifeSciences Public Sector Travel& Trans Comms& Media Auto Energy Service &Contact Center Churn Propensity Customer Satisfaction ResolutionRates Service RepEffectiveness Service CostAnalysis ServiceTrends Oracle BI ApplicationsMulti-Source Analytics with Single Architecture Sales Marketing Order Management& Fulfillment Supply Chain Financials HumanResources OrderLinearity Ordersvs. AvailableInventory Cycle TimeAnalysis BacklogAnalysis FulfillmentStatus CustomerReceivables Supplier Performance Spend Analysis Procurement Cycle Times Inventory Availability EmployeeExpenses BOM Analysis PipelineAnalysis TriangulatedForecasting Sales Team Effectiveness Up-sell / Cross-sell Cycle TimeAnalysis Lead Conversion Campaign Scorecard Response Rates Product Propensity Loyalty andAttrition Market Basket Analysis Campaign ROI A/R & A/PAnalysis GL / BalanceSheet Analysis Customer & ProductProfitability P&L Analysis ExpenseManagement Cash FlowAnalysis Employee Productivity Compensation Analysis HR Compliance Reporting WorkforceProfile TurnoverTrends Return on Human Capital Other Operational & Analytic Sources Prebuilt adapters: Oracle BI Suite Enterprise Edition

  9. <Insert Picture Here> Technical Overview

  10. Dashboards by Role Metadata Oracle BI Presentation Services Reports, Analysis / Analytic Workflows Data Warehouse / Data Model DAC Load Process Staging Area ETL Extraction Process Oracle BI Applications Architecture Administration • Role Based Dashboards • Analytic Workflow • Guided Navigation • Security / Visibility • Alerts & Proactive Delivery • Logical to Physical Abstraction Layer • Calculations and Metrics Definition • Visibility & Personalization • Dynamic SQL Generation Metrics / KPIs Logical Model / Subject Areas Oracle BI Server Physical Map Direct Access to Source Data • Abstracted Data Model • Conformed Dimensions • Heterogeneous Database support • Database specific indexing • Highly Parallel • Multistage and Customizable • Deployment Modularity Oracle SAP R/3 Siebel PSFT EDW Federated Data Sources Other

  11. Three approaches to accessing / loading source data Batch ETL (Full or Incremental) Micro ETL or Trickle Feed ETL Direct access to source data from Oracle BI Server ETL Layered architecture for extract, universal staging and load Provides isolation, modularity and extensibility Ability to support source systems version changes quickly Ability to extend with additional adapters Slowly changing dimensions support Architected for performance All mappings architected with incremental extractions Highly optimized and concurrent loads Bulk Loader enabled for all databases Data Warehouse Administration Console (DAC) Application Administration, Execution and Monitoring Dashboards by Role Metadata Oracle BI Presentation Services Reports, Analysis / Analytic Workflows Data Warehouse / Data Model DAC Load Process Staging Area ETL Extraction Process ETL Overview Administration Metrics / KPIs Logical Model / Subject Areas Oracle BI Server Physical Map Direct Access to Source Data DAC Oracle SAP R/3 Siebel PSFT EDW Federated Data Sources Other

  12. Dashboards by Role Metadata Business Analytics Warehouse Oracle BI Presentation Services Reports, Analysis / Analytic Workflows Load Source Independent Layer Staging Tables Data Warehouse / Data Model DAC Load Process Extract Staging Area SQL SQL SQL Power Connect Power Connect ETL Extract Extraction Process ABAP SQL Siebel OLTP Oracle Other PeopleSoft SAP App Layer App Layer ETL Overview Administration Metrics / KPIs Logical Model / Subject Areas Oracle BI Server Physical Map Direct Access to Source Data DAC Oracle SAP R/3 Siebel PSFT EDW Federated Data Sources Other

  13. Business Analytics Warehouse Load Source Independent Layer Staging Tables Extract SQL SQL SQL Power Connect Power Connect Extract ABAP SQL Siebel OLTP Oracle Other PeopleSoft SAP App Layer App Layer Data Extraction and Load Process Extract • Source-specific and Universal Business Adapters • Expose simplified business entities from complex source systems • Converts source-specific data to universal staging table format • Lightweight and designed for performance, parallelism • Extensible

  14. Oracle Data Warehouse Load Source Independent Layer Staging Tables Extract SQL SQL SQL Power Connect Power Connect Extract ABAP SQL Siebel OLTP Oracle Other PeopleSoft SAP App Layer App Layer Data Extraction and Load Process Extraction Modes • Direct Database Access • Oracle EBS • Siebel CRM • PowerConnect technology • SAP R/3 • PeopleSoft Enterprise • Universal Business Adapters • Used for sources with no pre-packaged business adapter • Transforms and loads data from universal sources through a flat file interface • Supply flat file(s) or tables meeting a defined standard format • Reduces implementation and maintenance significantly compared to building full ETL solution from scratch

  15. Oracle Data Warehouse Load Source Independent Layer Staging Tables Extract SQL SQL SQL Power Connect Power Connect Extract ABAP SQL Siebel OLTP Oracle Other PeopleSoft SAP App Layer App Layer Data Extraction and Load Process Source Interdependent Layer • Encapsulates warehouse load logic • Handles: • Slowly changing dimensions • Key lookup resolution / surrogate key generation • Insert/update strategies • Currency conversion • Data consolidation • Uses Bulk Loaders on all db platforms

  16. Business Component for Oracle Applications Source – Products Dimension Oracle EBS Source Table Source Qualifier Expression Transformation Mapplet output to Extract Mapping

  17. Extract Mapping for Oracle Applications Source – Products Dimension Business Component Mapplet for Oracle EBS Product Master Source Adapter Mapplet Temporary Staging Table

  18. Extract Mapping for Oracle Applications Source – Products Dimension Temporary Staging Tables (Product Master) Source Qualifier Expression Transformation Universal Staging Table

  19. Load Mapping for Oracle Applications Source – Products Dimension Source Independent Load (SIL) Mapplet W_PRODUCT_DData Warehouse Table Source Qualifier Universal Staging Table

  20. Example: Employee Dimension populated from PeopleSoft SDE_PSFT_EmployeeDimension_Phones SDE_PSFT_EmployeeDimension_Addresses SDE_PSFT_EmployeeDimension_Biography1 W_EMPLOYEE_D SDE_PSFT_EmployeeDimension_Biography2 SDE_PSFT_EmployeeDimension_JobInformation SDE_PSFT_EmployeeDimension SIL_EmployeeDimension

  21. Employee Dimension populated from PeopleSoftSDE_PSFT_EmployeeDimension_Phones PeopleSoft Source BC maplet (see below) Expression Transformation Temporary Staging Table PeopleSoft Source Table Source Qualifier Expression Transformation Maplet output to Extract Mapping

  22. Employee Dimension populated from PeopleSoftSDE_PSFT_EmployeeDimension_Addresses PeopleSoft Source BC maplet (see below) Expression Transformation Temporary Staging Table PeopleSoft Source Table Source Qualifier Expression Transformation Maplet output to Extract Mapping

  23. Employee Dimension populated from PeopleSoftSDE_PSFT_EmployeeDimension_Biography1 Temporary Staging Table PeopleSoft Source BC maplet (see below) Expression Transformation Lookup Procedures PeopleSoft Source Tables Source Qualifier Expression Transformation Maplet output to Extract Mapping

  24. Employee Dimension populated from PeopleSoftSDE_PSFT_EmployeeDimension_Biography2 Temporary Staging Table PeopleSoft Source BC maplet (see below) Expression Transformation PeopleSoft Source Table Source Qualifier Expression Transformation Maplet output to Extract Mapping

  25. Employee Dimension populated from PeopleSoftSDE_PSFT_EmployeeDimension_JobInformation Temporary Staging Table Lookup Procedures PeopleSoft Source BC maplet (see below) Expression Transformation PeopleSoft Source Table Source Qualifier Expression Transformation Maplet output to Extract Mapping

  26. Employee Dimension populated from PeopleSoftSDE_PSFT_EmployeeDimension maplet PeopleSoft Source Table Expression Transformation Source Qualifier Temporary Staging Table

  27. Employee Dimension populated from PeopleSoftSDE_PSFT_EmployeeDimension Expression Transformation Expression Transformation Lookup Procedures Maplet output to Extract Mapping Input to Maplet (from above)

  28. Employee Dimension populated from PeopleSoftSIL_EmployeeDimension

  29. Employee Dimension populated from PeopleSoftSIL_EmployeeDimension_SCDUpdate Expression Transformation Target Definition Source Definition Filter (SCD logic) Source Qualifier

  30. Incremental Extraction and Load • A variety of strategies used to optimize incremental extracts and loads • Overall Philosophy – Extract incrementally if possible, else load incrementally • Siebel Source • Use a combination of a date window and rowid comparisons • Oracle • Use a date window and last update date for extraction • Also use dates/record images to control updates on target • SAP • Use a date window and last update date for extraction • Also use dates/record images to control updates on target • Certain dimensions are fully extracted and but updates on target are controlled • PeopleSoft • Use a date window and last update dates for extraction wherever possible

  31. Data Warehouse Administration Console (DAC)Strong Competitive Differentiator • For warehouse developers and ETL Administrator • Metadata driven “ETL orchestration tool” • Application Configuration • Execution & Recovery • Monitoring • Allows: • Pin-point deployment • Load balancing / parallel loading • Reduced load windows • Fine-grained failure recovery • Index management • Database statistics collection

  32. Modular enterprise-wide data warehouse data model with conformed dimensions Sales, Service, Marketing, Distribution, Finance, Workforce, Operations and Procurement Integrate data from multiple data sources Code Standardization Real-time ready Transaction data stored in most granular fashion Tracks historical changes Supports multi-currency, multi-languages Implemented and optimized for Oracle, SQL Server, IBM UDB/390, Teradata Dashboards by Role Metadata Oracle BI Presentation Services Reports, Analysis / Analytic Workflows Data Warehouse / Data Model DAC Load Process Staging Area ETL Extraction Process Physical Data Model Overview Administration Metrics / KPIs Logical Model / Subject Areas Oracle BI Server Physical Map Direct Access to Source Data Oracle SAP R/3 Siebel PSFT EDW Federated Data Sources Other

  33. Customers Suppliers Customers Suppliers HR / Workforce Sales Operations Service Marketing Distribution Finance Procurement Customers Suppliers Common Enterprise Information Data Model Features: • Conformed dimensions • Transaction data stored in most granular fashion • Tracks full history of changes • Prebuilt and extensible • Built for speed • Benefits: • Enterprise-wide business analysis (across entire value chain) • Access summary metrics or drill to lowest level of detail • Accurate historical representations

  34. Oracle BI Apps: Selected Key EntitiesUnified multi-source data model Sales • Opportunities • Quotes • Pipeline Order Management • Sales Order Lines • Sales Schedule Lines • Bookings • Pick Lines • Billings • Backlogs Marketing • Campaigns • Responses • Marketing Costs Supply Chain • Purchase Order Lines • Purchase Requisition Lines • Purchase Order Receipts • Inventory Balance • Inventory Transactions Finance • Receivables • Payables • General Ledger • COGS Call Center • ACD Events • Rep Activities • Contact-Rep Snapshot • Targets and Benchmark • IVR Navigation History Service • Service Requests • Activities • Agreements Workforce • Compensation • Employee Profile • Employee Events Pharma • Prescriptions • Syndicated Market Data Financials • Financial Assets • Insurance Claims Public Sector • Benefits • Cases • Incidents • Leads Conformed Dimensions • Customer • Products • Suppliers • Cost Centers • Profit Centers • Internal Organizations • Customer Locations • Customer Contacts • GL Accounts • Employee • Sales Reps • Service Reps • Partners • Campaign • Offers • Employee Position Hierarchy • Users • Modular DW Data Model includes: • ~350 Fact Tables • ~550 Dimension Tables • ~5,200 prebuilt Metrics • (2,500+ are derived metrics) • ~15,000 Data Elements

  35. Types of Fact Tables • All base facts are based on single business function, e.g. Sales Order Lines, Sales Cycle Lines, etc. • Types of Fact Tables • Transactional Facts • Mirror of transactional data from the source • Lowest atomic grain of each transactional record • E.g. Employee Events Fact, Order Lines Fact • Snapshot Facts • Snapshot of balances across time • E.g. Employee Daily Snapshot, Inventory & Account Balances, AR and AP aging snapshots & Opportunity Pipeline • Cycle Lines Facts • Derived from multiple fact tables, typically store process cycle times • E.g. Sales Order Cycle Lines, Purchase Cycle Lines • State Transition Facts • Required for specialized transition count metrics • Customer State transitions (# New, # Inactive customers, etc) • Aggregate Facts • Performance enhancement on key metrics • E.g. Payroll Aggregate (out of box Monthly, but configurable to other grains)

  36. Business Process Conformance • Native source specific transaction types are transformed and appended to standardized warehouse codes • Standardized warehouse codes are superset of all supported sources • Native source specific transaction types are retained for source specific analysis • E.g. Ethnic Codes, HR Event Types, Credit Memo, Debit Memo types

  37. Business Process ConformanceExample: Accounts Receivable Process

  38. Business Process ConformanceExample: Accounts Receivable Process • All source transaction type code values are stored and appended with standardized warehouse codes • All higher layers in the architecture and metrics use same standardized warehouse codes • Stored source transaction type code values enables integration back into each transactional system

  39. Business Process ConformanceExample: FLSA Status Derivation Process • All source FLSA Status code values are stored and appended with standardized warehouse codes • All higher layers in the architecture and metrics use same standardized warehouse codes • Stored source FLSA Status code values enables integration back into each transactional system

  40. Enhanced Multi Currency Support • Support for multiple currencies • 3 global, one local and transactional (enterprise only) exchange rates available • Financials local amount is stored as a field and not as conversion rate • Common currency conversion mechanism at ETL time

  41. Data Integration – Multi Source Loads • Segregation and Lineage captured in data model • Source data keys, comprised of: INTEGRATION_ID + DATASOURCE_NUM_ID Part no. Plant id ‘MT27907b’ + ‘NW3’ + ‘SAPJapan’ Example: Key_id for Products dimension

  42. Business Process Outsourcing SupportOracle BI Apps supports tenant concept • Unique Enterprise Multi-Source data model • Segregates and maintains data lineage via DATASOURCE_NUM_ID when loading data from several sources • Additional TENANT_ID key allows even more flexibility for data model to support multiple-tenanted source systems • Enables a variety of deployment, data segregation / separation strategies within the same data warehouse

  43. Multi-layered Abstraction Separation of physical, logical and presentation layers Logical modeling builds upon complex physical data structures Logical model independent of physical data sources, i.e. same logical model can be remapped quickly to another data source Metrics / KPIs Multi-pass complex calculated metrics (across multiple fact tables) One Logical Fact can span several table sources including aggregates and real-time partitions Aggregate navigation Federation of queries Prebuilt hierarchy drills and cross dimensional drills Security and visibility Tight integration with Oracle EBS, Siebel CRM and PeopleSoft Enterprise Dashboards by Role Metadata Oracle BI Presentation Services Reports, Analysis / Analytic Workflows Metrics / KPIs Logical Model / Subject Areas Oracle BI Server Physical Map Data Warehouse / Data Model DAC Load Process Staging Area ETL Extraction Process Server Repository Overview Administration Direct Access to Source Data Oracle SAP R/3 Siebel PSFT EDW Federated Data Sources Other

  44. Metrics and Calculations • The applications provide several complex metrics defined logically with an expression builder • Share base (e.g. % share of one product sales over all products sales) • Indexes (e.g. this division’s performance as a ratio of all divisions) • Cross subject area metrics (across Finance and HR, e.g. Revenue per employee) • Variances (e.g. Budget vs Actual) • Time series metrics • MTD/YTD/Running balances/MAVG (e.g. Revenue Chg % YTD) • Period Ago Metrics (e.g. Revenue YAGO) • Snapshot at any point of time support (e.g. # Open Service Requests) All of these are done logically without adding data model/ ETL complexity

  45. Localization / Globalization • Dynamic Language Conversion (translation to 15 languages) • Chinese (Simplified), Chinese (Traditional), Czech, Danish, Dutch, Finnish, French, German, Italian, Japanese, Korean, Portuguese (European), Portuguese (Brazilian), Spanish, Swedish • Codepage / Unicode support • Dynamic Time Zone translations • All timestamps are stored in warehouse stored as UTC • Custom labeling and naming support / Flex Field Naming • Column Names can be dynamically named

  46. Multiple Calendar Support • Supports Gregorian and Fiscal hierarchies out of the box • Configuration to support multiple fiscal hierarchies depending on user profile • Requires Initialization block to read user profile • Dynamically use the appropriate calendar table • CRM OnDemand implementation supports twelve fiscal calendars

  47. Role based dashboards Covering more than 100 roles Navigation Most reports have at least one level of navigation embedded Drill to details from many interactive elements, e.g. chart segments Guided Navigation Conditional navigational links Analytic Workflows Action Links Direct navigation from record to transactional while maintaining context Alerts Scheduled and Conditional iBots Highlighting Conditional highlighting that provides context on metrics (is it good or bad?) Dashboards by Role Metadata Oracle BI Presentation Services Reports, Analysis / Analytic Workflows Data Warehouse / Data Model DAC Load Process Staging Area ETL Extraction Process Web Catalog Overview Administration Metrics / KPIs Logical Model / Subject Areas Oracle BI Server Physical Map Direct Access to Source Data Oracle SAP R/3 Siebel PSFT EDW Federated Data Sources Other

  48. Guided Navigation Enables users to quickly navigate a standard path of analytical discovery specific to their function and role Enhances usability and lowers learning curve for new users Analytic WorkflowsDirected analysis helps inexpert users Conditional Navigation • Appears only when conditions are met and alerts users to potential out of ordinary conditions that require attention • Guides users to next logical step of analytical discovery

  49. Analytic Workflows – Financial Analytics Business Objectives / Issues Maximize Cash Flow • Business Function:Receivables • Role:Director, Credits & Collections • Objectives: 1) Maximize Cash Flow 2) Control Risk of Receivables Portfolio Is DPO on target? Is DSO on target? Is Overdue Balances trending up? Are Payment Terms in compliance? Gain Insights How long is the underlying Overdue Balance pending? What is the aging of Due Balances? Who are the Customers and Collectors? Drill to Due Balances by Region Drill to Overdue Invoice Detail Take Action Target collection efforts to reduce overdue balances

More Related