Quick and Easy Access to your Oracle Apps Data

1
Quick and Easy Accessto your Oracle Apps Data

• Stan Choflet
• Jaros Technologies
• 3-7-05

2
Proposed Agenda

• Current Reporting Environment
• Alternative Solutions the Market
• Best Practices
• The Data Warehouse
• Packaged Approach Value Proposition
• Best Practices Architecture, Functionality
• Customer Examples
• Technology Demonstration

3
Current Reporting Environment

• Enterprises Implement Oracle for Improved
  Operational Efficiencies
• Data integrated centralized in one modern
  database
• Easier access to data, better decisions
• BUT, Once Implemented, We Find
• Cant use most standard Oracle reports
• Tools are too hard to use
• No capability for analysis
• The Problem is Inadequate Infrastructure
• Complex Table Structures
• Multiple Data Sources
• 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

4
Range of Approaches
Corporate Portals BI Tools Abstraction Cubes D
ata Marts ODS Views Transactional Systems
Plumtree, BEA, Oracle, IBM
Jaros
Jaros Excelerator
MicroStrategy
Jaros
Metadata
Jaros ODS
Jaros Insulation Views
5
BI Infrastructure Options
Oracle-Centric Reporting Strategy

• Oracle Solutions
• Standard Apps Reports
• BIS, DBI
• OFA, OSA ? EPB
• Noetix Database Views
• Custom Data Warehouse
• Decision Point Software
• Jaros Analytics

All dataconvertedto Oracle
Legacy
Legacy
Legacy
Current andhistorical data indata warehouse
Currentdata inOracle
Legacy
Legacy
Archive
Legacy
Enterprise-Centric Reporting Strategy
6
ERP/BI Evolution
Data Warehouse
Standard Reports
ROI
Custom Reports
Data Marts
Views
Effort
Customer Satisfaction
Excel
Time
7
Data Growth/Performance Relationship
Performance
History
Open Transactions
Source OuterBay Technologies
8
Customer Example Run Times
Total Daily Run Time for Manufacturing Release
Space Allocated for SO_LINES_ALL (including
indexes)
Source Customer Study, OuterBay Technologies
9
Typical Scenario

• Executive wants to know revenue and backlog
  (relative to forecast) and margin by reporting
  product line, by customer, month to date, quarter
  to date, year to date

Sources of Data
10
Typical Reporting Process
Staging
PL/SQL
Staging
Reports
Most significant portion of the effort is in
writing PL/SQL
Forecast Product Reporting Line
11
Reporting Simplified
Extraction, Transformation, and Load (ETL)
OE
INV
FND
CST
AR
Forecast,
Legacy
Reporting Product Line
12
Star Schema - Reporting Simplified
Time Dimension
Reporting Product Line Dimension
Revenue Fact
Product Dimension
Customer Dimension
13
Gartners Best Practice BI Foundation

• Key Concepts
• Single source of the truth
• Dont report on transaction sys
• DW/ODS Optimized reporting
• Foundation for analytic apps
• Multiple data sources
• Lowest level of detail

14
Jaros Company Overview

• Background / Experience
• Oracle Applications, Database
• Custom Data Warehouse Development
• Manufacturing, Distribution Industry Experience

Jaros Analytics Customers

• Jaros Analytics Coverage
• GL, AP, AR
• Purchasing
• Orders, Shipments
• Inventory, Costing
• BOM, WIP, Master Scheduling/MRP
• HR, Payroll (Beta)
• Service Contracts (Beta)
• Planned Fixed Assets, Projects, Advanced
  Security Model, MFG PRO

15
Packaged Analytics Value Prop

• EDW Enterprise Visibility, Enterprise Efficiency
• Dramatically TCO, Time, Risk
• 2 day install, 6-8 week base implementation
• Buy vs. Build emerging market
• Pre-built, Focused Content
• 70-80 out of the box (Oracle Fin, OM, PO, Mfg,
  Inv)
• Unique, Modern Approach
• Architecture and Tools for Enterprise Integration
• Near Real-Time Tactical Reporting
• Extensibility of Architecture
• Account Segments, Flexfields, Hierarchies,
  Business Rules
• Custom, Legacy Integration
• Jaros customers get what they want

16
Best Practices Architecture
Any BI Tool
Data Marts Analysis Areas
Material Consumption
Customer Performance
Working Capital
Vendor On- Time
AR Aging
Jaros ETL
Operational Data Store (ODS) Near Real Time
Tactical Reporting
JarosETL
Views / Change Tracking
Generic ERP API
Generic ERP API
MFG PRO
Oracle Apps 10.7
Oracle Apps 11i
Legacy/ Spreadsheets
Other ERP, CRM
17
The Need for BI Integration
Oracle 11i

• Large Corporations Today
• Many ERP Systems, Many Divisions
• Legacy wide variety, long path to integration
• MA Activity, Growth
• Complex reporting and analysis requirements

Oracle 10.7
JDE
4th Shift
MANMAN
MAPICS
Custom
18
Active Change Detection

• Required to keep the DW in sync with the Apps
• Missing in most 1st Generation Solutions
• Common Problems w/o change tracking
• Consistent read during replication/ETL process
• Deletes in Oracle dont get deleted in DW/ODS
• Change history file
• Resynchronize DW/ODS in case of process failure
• Event based approach is superior to time based
• Triggers on tables is the recommended approach
• Jaros no-impact change detection (log sniffing)

19
Data Marts Analysis Areas

• Financials
• Account Margins
• Costs, margins by COGS, revenue, and receivables
  accounts
• AP Invoices Summary
• AR Aging Detail with configurable buckets
• AR DSO Snapshot
• AR Sales (Summary with YTD, QTD, MTD growth vs.
  Goal, Plan)
• GL, Drill to AP, AR subledgers
• Purchasing
• Variance Analysis (PPV. IPV) at PO receipt time
• To sub element cost level by vendor, inventory
  org, account segment, etc.
• PO Vendor On-Time Performance Summary
• By request date and promise date
• PO Vendor Outstanding Balances Summary
• PO Vendor Payment History Summary

20
Analysis Areas (Cont)

• Sales, Shipments, Customers
• Net Bookings
• Customer, Sales Rep, Product Analysis
• List Price, Selling Price, COGS, Gross Margin,
  Discount Analysis
• Open Orders including costing, margins
• OM Customer Service Summary (on-time by
  customer, item)
• OM Lead Times Summary
• Outstanding Work Orders (ability to deliver on
  time)
• Supports ATO, PTO, kits, standard items Flow and
  Discrete
• Production and Efficiency
• INV On-hand Snapshot (units w/ sub element costs)
  
• INV Item Turns Snapshot with configurable Turns
  calculation
• INV Obsolete Inventory Analysis Summary
• MFG Usage (WIP, Sales Demand)
• MFG Forecast vs. Actual Summary
• WIP Analysis, Operational Variance Analysis, std
  vs. actual
• BOM with Cost
• Detailed BOM Analysis with Cost
• Unit, Elemental, Sub-Element Cost

21
Features

• Multi-Currency Support
• Global, Transacted, and Set of Books Currencies
  throughout
• Ex Drill from consolidated set of books to
  detail in USD
• Cross Functional Analysis
• Filter by 30 conformed dimension tables
• Drill Across Subject Areas
• Near Real Time Updates
• Historical Trending and Snapshots

22
BI User Profiles
Strategic Planning
Executives
Data Warehouse
Enterprise data Consistent GUI Industry
drivers Enterprise KPIs
Analysts
Functional Managers
Enterprise and LOB data Scenario and
simulation History and forecasts Domain-specific
KPIs
Tactical Analysis
LOB Managers
LOB data Drill down option Business Trends LOB
KPIs
Operational Managers
Operational Data Store
Process data Real time Feedback loops Operational
metrics
Operational Decisions
Summarized
Detailed
Data Granularity
23
Best Practices
of user population 85 15
Dashboards, Reports, Ad-hoc Query, Drill Down
Static Docs
ROLAP or MOLAP
DW/ODS Single Source of the Truth
Multiple Data Sources
24
Enterprise Visibility
Sales Finance Procurement Manufacturing Inventory
JarosGlobal DW
Oracle US
JarosEUR DW
MFG PRO - US
Oracle 1 EUR
Cullinet - US
Oracle 2 EUR
MFG PRO - EUR
JarosSo AM DW
OracleBrazil
25
Enterprise Sales Visibility - 13 ERP Systems
JarosGlobal Sales DW
Oracle1
MAPICS
Legacy
MAN-MAN
4th Shift
Oracle2
Legacy 2
26
Jaros Fastest Time to Results

• Single Repository for Management Information
• Company-wide, comprehensive
• Integrate Oracle and non-Oracle data
• Fin, OM, Mfg, Inv, PO out of the box
• Easily Integrate non Oracle data
• Portal for docs, pubs, links
• Near real time and daily updates
• Enterprise Portal/Dashboard
• Tables, graphs, metrics, trends, drill down
• Role-based security
• Web, intra/extranet access
• User friendly, customizable
• Enterprise-class system
• Highly available, high performance
• User documentation for Oracle, extendable to
  others

27
Jaros Demo Environment
IE 6.0
Jaros Dashboard
Discoverer 9iR2 Jaros EUL
BEA WebLogic Server
Oracle 9i, Jaros Data Marts
ETL
Oracle 11.5.8 Vision Data