1 / 69

Oracle Apps and Data Warehousing An Oxymoron

David Fuston ? Short Bio. Technical and functional consultant with over 18 years of experience in applications development, IT management, and financial controller positions for Fortune 500 and World 200 companies.Oracle Beta Tester since 1997 for numerous programs, such as Applications 11i, Financ

Samuel
Télécharger la présentation

Oracle Apps and Data Warehousing An Oxymoron

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. 2003, The Michael Taylor Group and David Fuston, All rights reserved. Oracle Apps and Data Warehousing An Oxymoron? NCOAUG Meeting ? August 15, 2003

    2. David Fuston Short Bio Technical and functional consultant with over 18 years of experience in applications development, IT management, and financial controller positions for Fortune 500 and World 200 companies. Oracle Beta Tester since 1997 for numerous programs, such as Applications 11i, Financial Analyzer 6.x, 9iAS Discoverer, Warehouse Builder 9i, and Oracle 8i/9i Specialize in staff augmentation for Oracle Applications 11i financial (GL, AP, AR, FA, BIS, DBI) and manufacturing (PO, OM, INV) implementations, including data warehousing, business intelligence and reporting Officer, speaker, and sponsor in OAUG, BI/DW SIG, KCOUG, CS-OAUG, IOUG, and BIGSIG. Teaching and training experience started in 1984 as a manufacturing plant zero defects trainer utilizing the Phillip Crosby Quality College methods.

    3. Agenda What are DW and BI the definitions? Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management EDW BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    4. Business Intelligence Definition Business IntelligenceThe processes, technologies, and tools needed to turn data into information, information into knowledge, and knowledge into plans that drive profitable business action. Business intelligence encompasses data warehousing, business analytic tools, and content/knowledge management. THE DATA WAREHOUSING INSTITUTE www.dw-institute.com

    5. BI Analytical Applications Packaged Analytic Application The buy optionA vendor-supplied package that provides domain-specific analytics. It contains an integrated set of analytic tools, data models, ETL mappings, business metrics, predefined reports, and best practice processes that accelerate the deployment of an analytic application in a given domain or across multiple domains. Custom Analytic Application The build optionAn analytic application that is primarily built using tools, code, or customizable templates to provide the exact look, feel, and functionality desired by an organization for its analytic environment.

    6. Recent BI Trends Shift from departmental to enterprise-wide BI Systems pressured to operate in (near) Real-Time Interoperability and integration is key trouble spot BI and DW embedded in Applications BI use in Portals BI dashboards access the DW BI has expanded from the traditional On Line Analytical Processing (OLAP) to now include Query and Reporting, ETL, Data Mining, and Data Visualization. Return on Investment (ROI) for analytic applications range from 17% to more than 2,000% per IDC December 2002 study, document #dg20021202.

    7. Why a Separate BI Tool? Empowers end-users to do own analysis Frees up IS/IT backlog of report requests Ease of useeasy selection of data Drill-down No knowledge of SQL or tables required Exception Analysis Variance Analysis Easy rotation Formula calculations Aggregate data

    8. What is a Data Mart versus a Data Warehouse? Data Warehouse Capture Data That Will Help a Company Answer Questions About the Entire Business Uses Dimensional Modeling To Establish Structure Typically Star Schema or Snowflake Unlike OLTP, Answer Questions About the Process Not the Transaction Data Mart Similar to Data Warehouse But, Focused on One Business Process

    9. Why Consider a Data Warehouse? Standard Oracle Reports dont meet business requirements Visibility and Simplification of Reports Custom reports take too long to produce Too many resources tied up in reporting Data manipulation is required, extensive use of Excel No tools or time to do detailed analysis Data is not accurate Multiple data sources, complex table structures Views are not performing well Multiple versions of the truth in meetings

    10. Buy versus Build in Oracle Data Warehouse (Data Warehouse Infrastructure) Packaged Analytic Applications using Oracle Oracle EDW Decision Point Applications Jaros Analytics Custom Analytic Applications using Oracle Do-it-Yourself with Discoverer & OWB

    11. OLTP versus DW/BI OLAP/BI is iterative in modeling, design, and implementation Frequent exposure of unknown data quality problems Multiple source systems (OLTP) converge into one or more target (DW/OLAP/BI) systems Multiple lines of business use different business rules, assumptions, terminology Quantity of data that will reside in DW/OLAP/BI is typically unknown Difficulties in loading and aggregating data Different challenges in performance tuning

    12. Agenda What BI is and Terminology Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management (BI) EDW BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    13. The Data Warehouse Institute http://www.dw-institute.com/marketplace/index.asp Administration & Operations (127) : Performance & Usage Management, Database Management, Capacity Planning, More Analytic Applications & Development Tools (244) : Development Tools, Visualization Tools, Business Performance Management, More... Business Analytics (199) : Query & Reporting, Production Reporting, OLAP, Business Analytics Suites, More... Business Intelligence Services (96) : Consultants/Systems Integrators, Analytic Service Providers for Data Warehouse, More... Data Integration (168) : Data Quality and Cleansing, DW Mapping & Transformation, Meta Data Management, More... Data Warehouse Design (27) : Data Modeling & Analysis, Data Warehousing Toolsets, More... Information Delivery (91) : Enterprise Information Portals, Broadcasting, Wireless Data Analysis, More... Infrastructure (84) : Relational Database Management Systems, Multidimensional Databases, More...

    14. Market Segment Analysis -- The Olap Report www.olapreport.com/ Based on the many criteria discussed in The OLAP Report, a potential buyer should create a shortlist of OLAP vendors for detailed consideration that fall largely into a single one of the four categories. There is something wrong with a shortlist that includes products from opposite sides of the square. Nigel Pendse OLAP Report

    15. Relational Reporting Advantages Lowest Cost Per Seat Rich Formatting Web deployable Disadvantages No real analysis Not interactive Hard to manipulate for end users Not really BI Major Players Crystal Reports BI/Query IQ/Objects Cognos (Impromptu) Oracle Reports

    16. DOLAP (Desktop) Advantages Low Cost Per Seat Easiest to Deploy End User Friendly Transactional Data Disadvantages Limited Functionality Limited Data Capacity Limited Customization Major Players Cognos (PowerPlay) Business Objects Brio Crystal Decisions Hummingbird Oracle (old c/s Discoverer)

    17. ROLAP (Relational DW) Advantages Deal with Large Data Volumes (Terabytes) Access via SQL Read-Only Reporting Disadvantages Slow Performance Limited Financial Calculations Major Players MicroStrategy (DSS) IBM Informix (MetaCube) MindShare WhiteLight Oracle (web Discoverer, BIS, DBI)

    18. MOLAP (Multidimensional DW) Advantages High Performance Database Best of Breed Solution Sophisticated Functionality Supports Multiple Third Party Tools Supports Gigabytes Disadvantages Proprietary Language Major Players MS OLAP Services (starting with v7.0) Hyperion/Arbor (Essbase) Applix (TM1) Seagate (Holos) Oracle (Express, 9i OLAP option)

    19. Application OLAP/BI Advantages Integrated Application with Database Out-of-Box Complete Toolkit High Functionality Some can be configured as Hybrid OLAP (HOLAP) Disadvantages Complexity Cost Per User Major Players Oracle (OFA & OSA) Hyperion/Arbor (Essbase) Information Builders (WorldMart) SAS

    20. Agenda What BI is and Terminology Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management (BI) EDW BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    21. Assessing BI/DW Readiness Strong Business Management Sponsor Business Vision IS/Business Partnership Current Analytic Culture Feasibility --Ralph Kimball, The Data Warehouse Lifecycle Toolkit

    22. The Process?Key to BI Success Everyone Needs To Be Part of Process End-users, IS/IT, and Executive Management Identify the Business Process That Needs to have Questions Answered Establish Separate Evaluation and Review Teams Two Primary Teams - Decision Team and Management Review Committee Remove Politics Identify a Selection Methodology Design the Solution

    23. Putting it All Together Keys to Success Executive Sponsorship Realistic Expectations * Methodology * Team * Proper technical architecture and tools * Quality data Limited scope changes Fast payback projects *Note: Key areas where DW/ETL tools and OLAP/BI consultants can add value.

    24. Agenda What BI is and Terminology Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management (BI) EDW BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    25. Corporate Performance Management

    26. Performance Management

    27. Oracles CPM Coverage Today

    28. Oracles Phased In Approach to Corporate Performance Management First Step To Improvement: Admit that what were doing is not adequate for the age were in very little integration not enough data inadequate facilities to learn from the past too little time planning for the future too expensive John Schoenherr Vice-President, Analytic Solutions

    29. Corporate Performance Management Continued Step 2 Acquire technology and integrate it into the base server and application architecture Express, OFA, OSA (1995) Financial Services Apps Treasury Services (1998) Activity Based Management Activa (1999) Darwin - Thinking Machines (2000) Balanced Scorecard (2000) Pure Integrate (2000) Step 3 Build a unified data model for CPM Embedded Data Warehouse (EDW)

    30. Corporate Performance Management Continued Step 4 Build a performance management framework and deliver operational reporting Business Intelligence System (BIS - 2001) Daily Business Intelligence (DBI - 2003) Step 5 Release integrated Server (9iR2) and Application Server (9iAS) 2003 Step 6 Release integrated planning, budgeting and analysis (EPB) summer 2003

    31. Agenda What BI is and Terminology Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management (BI) EDW and alternatives BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    32. Embedded Data Warehouse Content: AP, AR, FA, PO, INV, HR, Payroll, Projects, Revenue, Bookings, Backlogs, Shipments Integration of multiple instances / versions of Oracle Apps (10.7, 11.0.x, 11i) via API Leverage 8i/9i data warehousing features, primarily SQL Analytic commands and Materialized Views Conforms to Common Warehouse Metamodel (CWM), specifications adopted by OWG Report only using Discoverer or DBI Support for non-Oracle data sources is difficult via API into Oracle Apps tables Requires 9iR2, 9iAS, OWB 9i, and Discoverer 9iapplicable to 11.5.8 and above

    33. BIS EDW Architecture

    34. Decision Point Applications (www.dpapps.com) First to Market with Packaged Data Warehouse (Sequent) Established in 1996 Oracle Coverage 10.7-11i Financials: GL, AP, AR, FA, PA PO, INV, OE/OM, BOM, WIP HR, Payroll Unique Features Sarbanes-Oxley Financial Compliance Dashboard Cross Industry Approach from Retail to Telecom Fast implementation (8 weeks for entire suite) Proprietary PL/SQL generator for ETL is bundled with product Support Oracle Apps, PeopleSoft, JD Edwards plus custom as data sources Supports Brio, Business Objects, Cognos, Hyperion, etc.

    35. Jaros Analytics (www.jarostech.com) New to Market Jan 03 Industry Focus: Manufacturing, Distribution, Service Oracle Coverage 10.7-11i Financials: GL, AP, AR PO, OE/OM, INV MRP, BOM, WIP Unique Features Near-Real Time Analytics (ODS) Deliver pre-built Discoverer EUL with package 4 week implementation for entire suite Informatica ETL and metadata repository Supports Oracle 8i/9i or SQL Server 2000 as target DB Supports Oracle and non-Oracle data sources Supports Discoverer, Cognos, Hyperion, Crystal, etc.

    36. DW/BI Architecture Comparison Embedded approach (EDW) vs. External (DPA, Jaros) Summary and detail data need to matchevent based or time based, sync modules or whole app Performance considerations for Apps DBraw tables or views, multiple years in OLTP Capture the transient detail (ex: net changes to orders) Ability to create summary or aggregate tables and/or cubes Integration of multiple data sources80% of time EDW OWB and Oracle Interface Tables (performance) DPA proprietary ETL directly into apps Jaros Informatica PowerMart directly into apps Data Warehouses are never done, ultimately becoming the historical repository in lieu of upgrading data in Oracle Apps

    37. ETL tools up to 70% of effort in a DW Infrastructure: Types of data sources (Oracle, non Oracle) Open platform support (NT, Linux, Unix) Data volumes, scalability, and performance Scheduling, Debugging, Concurrent Processing Common metadata shared among applications Business: Complexity of transformation Complexity of integration Resource/Skill Levels: Available resources Skill mix

    38. Agenda What BI is and Terminology Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management (BI) EDW BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    39. Oracle Applications BI Functionality

    40. The Business Intelligence Process

    41. BIS Architecture

    42. BIS Release and Architecture BIS 1.x patch on Apps 11.0 BIS 2.x released with Apps 11i BIS 3.x/4.x Embedded Data Warehouse BIS 5.0 Daily Business Intelligence for 11.5.8+ Version 1-2 Database Views on OLTP Version 3-4 Data Warehouse tables Version 5 Materialized Views

    43. Agenda What BI is and Terminology Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management (BI) EDW BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    44. Oracle Analytic Solutions Products Activity Based Management (ABM) Performance Analyzer (PA) Demand Planner (DP) Sales Analyzer (OSA) Financial Analyzer (OFA) Enterprise Planning and Budgeting (EPB)

    45. OFA/OSA Major Product Releases

    46. Oracle Sales Analyzer Server-centric Approach for the Express Databases Read-only Application Ability for End-users to Create Custom Measures and Aggregates Ability to Deploy in Any OLAP Mode: ROLAP MOLAP HOLAP Not Tightly Integrated with Any of the Modules of the E-Business Suite

    47. Oracle Financial Analyzer Distributed Approach in Using Express Allows Users the Autonomy to Create and Manipulate Own Scenarios of Data Ability to Write Data Back Budgets and Forecasts Ability to Create Asymmetric Reports Integrates OFA with the Oracle General Ledger from the E-Business Suite Custom Facts (FDIs), But Knowledge of Express Language Needed may be needed

    48. OFA Architecture

    49. Oracle GL to OFA Mapping

    50. OFA Integration With Oracle GL Oracle Analysis and Planning Tool of Choice Use the GL Link to Load Data from OGL to OFA Map Structures from OGL Directly to OFA Structures Using Forms in OGL Can Alter Number of Segments Brought Over From OGL Can Combine segments Can Customize OFA and Use Other Non-Oracle Sources

    51. What Happens In Migration to EPB?

    52. OSA/OFA License Migration One-for-One OFA/OSA + Express = EPB + 9iR2

    53. Enterprise Planning and Budgeting ?Integrated Business Processes

    54. Agenda What BI is and Terminology Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management (BI) EDW BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    55. Discoverer Ad-hoc Query Tool Used to Analyze Data on the fly From Oracles Relational Database Tightly integrated With Oracles databases Which Simplifies: Security Scalability Data Access Metadata Creation Tight Integration With Oracle Reports, Oracle Express Products, and Oracle Designer Uses Drill-down and Pivoting Disadvantage - Need For Users to Know Underlying DB and/or SQL

    56. Discoverer Integration Methods Four possible methods: Create EUL against Business Views Oracle BIS/DBI Create Datamart Enterprise Data Warehouse

    57. Create EUL against Business Views Easiest and Fastest DIY approach Captures Flexfield information Up to date information Performance may be an issue

    58. Create Datamart Distill critical Applications tables into a datamart - most difficult DIY approach Requires extensive knowledge of Applications schema Snapshot of data that needs to be refreshed Challenging to preserve flexfields Better performance

    59. Applications Certification DIY approach Discoverer 3i, 4i and 9.0.2 work against Oracle Applications 11i, 11.0 and 10.7 11.5.7 rapid install contains Discoverer 4i (as part of iAS 1.0.2.2.2)

    60. Applications Certification out of the box approach Customers who use BIS/EDW are certified to use 4i against 11i if iAS 1.0.2.2.2 installed on separate machine (metalink note 139516.1, ARU 1834171) Customers who use BIS/EDW and want iAS 1.0.2.2.2 in same Apps Oracle Home can use the 11.5.7 rapid install

    61. OSA, OFA, and Discoverer Share Tables and graphs Drag-and-drop rotation Drill-down/up Easy to use interface Cache data Conditional formatting (color-coding)

    62. Discoverer Advantages Simple SQL custom calculations More flexible reporting (subtotals, etc.) Operates directly against relational data source Transactional view available Record-based selection More intuitive for users that know SQL

    63. Agenda What BI is and Terminology Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management (BI) EDW BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    64. Reports Allows Users to Create Complex Reports from Oracles Relational or Multidimensional Databases Unlimited Data Formatting and High-quality Presentation End-users Usually Only View the Reports Disadvantage - Developers May Need to Create the Complex Reports

    65. Agenda What BI is and Terminology Marketplace Assessing BI/DW Readiness Oracles Corporate Performance Management (BI) EDW BIS and DBI Oracle Sales Analyzer Oracle Financial Analyzer and EPB 9iAS Discoverer Reports Questions

    66. DW and BI ? Cold Hard Facts Consultants, including the Big Five (or Final Four), usually favor products for which they provide implementation services, and the vendors with whom they have partnerships. The OLAP Survey 2 found that implementations by large, general purpose consultants had a much lower success rate than those by smaller specialist BI consulting firms. Nigel Pendse, The OLAP Report, January 09, 2003

    67. DW and BI ? Cold Hard Facts In the past, organizations gave users an analytic tool, provided some training, and hoped for the best. The result was usually failure or lots of underutilized software. A recent report by Nigel Pendse and Survey.com discovered that organizations never deploy an astonishing 39 percent of the OLAP licenses they purchase. THE RISE OF ANALYTIC APPLICATIONS: BUILD OR BUY? TDWI REPORT SERIES by Wayne W. Eckerson

    68. QUESTIONS?

    69. 2003, The Michael Taylor Group and David Fuston, All rights reserved. Oracle Apps and Data Warehousing An Oxymoron? NCOAUG Meeting ? August 15, 2003

More Related