Oracle Applications Reporting How Can I Get What I Need

1
Oracle Applications Reporting How Can I Get
What I Need?
David Fuston Director, Business
Intelligence Linium, LLC Cell
816.729.1033 Email david.fuston_at_linium.com
2
About David Fuston

• Technical and functional consultant since 1989
  with experience in applications development, IT
  management, and financial controller positions
  for Fortune 500 and World 200 companies.
• Oracle Beta Tester and Early Adopter since 1997
  for numerous programs, such as Applications 11i
  (OFA, DBI, EPF EPB), 9iAS Discoverer and Drake
  10g, Warehouse Builder 9i, and 9i/10g OLAP.
• Officer, speaker, or sponsor in OAUG, BI/DW SIG,
  OracleWorld, AppsWorld, OpenWorld, IOUG, ODTUG,
  Collaborate, Alliance, NCOAUG, RMOUG, SCOUG, KCOUG

3
About Linium

• Linium is a certified consulting firm, with
  practices in Business Intelligence, Oracle
  Applications, Configuration, Service Asset
  Management.
• Linium combines Best-of-Breed products and
  industry best-practices with our talented and
  certified IT consultants. Every Linium engagement
  is managed by our PMI, ITIL, and PRINCE2
  certified project managers.
• With offices in New York, Connecticut, and
  Virginia, we are celebrating 8 years, still
  privately held, with steady growth year over
  year, currently 60 consultants nationwide.
• Partner Status with

4
Agenda

• Reporting and BI definitions
• Requirements Analysis
• IT Reporting Skill Sets
• Reporting Products Marketplace
• Oracles Corporate Performance Management (BI)
• BIS and DBI
• Financial Analyzer and EPB
• Discoverer
• XML and BI Publisher
• OBIEE (formerly Siebel Analytics)
• Summary and Questions

5
Recent Reporting/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
  and transactional systems
• BI has expanded from the traditional On Line
  Analytical Processing (OLAP) to now include Query
  and Reporting, Extract-Transform-Load (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.

6
Agenda

• Reporting and BI definitions
• Requirements Analysis (5 W(s) and H)
• IT Reporting Skill Sets
• Reporting Products Marketplace
• Oracles Corporate Performance Management (BI)
• BIS and DBI
• Financial Analyzer and EPB
• Discoverer
• Summary and Questions

7
Common Oracle ERP Reporting Scenario

• ERP team has belief that basic reporting will be
  provided by standard and FSG reports
  out-of-the-box
• ERP team believes that Discoverer, OFA, DBI, etc.
  could be installed later for more complex
  reporting
• Reality is that neither assumption is correct
• Reality is that putting in ERP system without
  planning for reporting is like building a house
  without considering plumbing or electrical
  connections

8
Typical Oracle Reporting Scenario
9
User Type Categories (Who? Why?)
Executive Perspective "What will happen?"
This is the business owner's perspective,
encompassing the full enterprise, adapting to
changes, both internal and external, over time.
The thought pattern is one of process control,
strategic direction, and execution
effectiveness. Middle Management Perspective
"Why did it happen?" This is the architect's
perspective, encompassing their department or
area, and the workflow process for which they are
responsible. The thought pattern is one of
internal controls, quality checkpoints, and
workflow efficiency. End-User Perspective
"What happened?" This is typically the
worker's perspective dealing with end-customer
commitments and internal company relationships.
The thought pattern here is workload scheduling,
in-and-out baskets, daily tasks completed on time
and under budget.
10
Report Level Categories (What? Where?)
Strategic Enterprise-Wide reports Line of
business/division/Region quarterly and yearly
reporting, dashboards, balanced scorecards,
reports in finance/sales/operations, industry
statistics and market share, regulatory and audit
compliance, summary and aggregate
data. Tactical Line of Business/Division/Regio
n Monthly reporting, actual versus budget
comparisons with commentary/notations/graphs,
packaged analytical apps (BIS/DBI), OLAP
(OFA/EPB/TM1/data mining), Key Performance
Indicators (KPIs), Portal red/yellow/green
stoplights and gauges, three or more dimensions
in ad hoc query and reporting Operational
Daily and weekly reporting, Detail data from
GL/AP/FA/PO, two dimensions in reporting,
scheduled reporting, Departmental/cost center
measurements against objectives, account and
inter-company reconciliations
11
Frequency of Update (When?)
12
Process for Apps Reporting Success (How?)

• Establish the Vision
• Evangelize the Vision
• Prioritize the Portfolio
• Allocate Sufficient Resources
• Align Business and IT for Long Haul
• Build Trust in System

13
Agenda

• Reporting and BI definitions
• Requirements Analysis
• IT Reporting Skill Sets
• Reporting Products Marketplace
• Oracles Corporate Performance Management (BI)
• BIS and DBI
• Financial Analyzer and EPB
• Discoverer
• Summary and Questions

14
Reporting/BI Skills Required
15
Agenda

• Reporting and BI definitions
• Requirements Analysis
• IT Reporting Skill Sets
• Reporting Products Marketplace
• Oracles Corporate Performance Management (BI)
• BIS and DBI
• Financial Analyzer and EPB
• Discoverer
• Summary and Questions

16
Agenda
17
(No Transcript)
18
Match User Types to BI Functionality
19
End-User Tool Categories IT Perspective

• Organizes end-user tools into four categories.
• Relational reporting simple two-dimensional
  non-interactive reports, typically with rich
  formatting and low cost deployment
• Desktop reporting more end-user friendly,
  transaction data oriented, limited data capacity,
  limited functionality, with limited customization
  supported reporting
• Analytic reporting distinguished by access to
  large volumes of data (terabytes), read-only SQL
  based access, cube or multi-dimensional
  presentation layer, and more advanced financial
  calculations, such as formulas using solves (IRR)
• Analytic reporting with "write-back" capability
  have a data write-back capability (such as for
  forecasts or activity based budgets), deal with
  summarized or aggregate levels of data
  (gigabytes), fast query performance due to
  proprietary languages, and higher cost to go
  along with higher functionality

20
End-User Tool Categories IT Perspective

• Type 1 Relational Reporting tools
• BO/Crystal Reports
• Oracle Reports
• Oracle BI Publisher
• Cognos Impromptu
• Type 2 Desktop Reporting tools
• Cognos PowerPlay
• Business Objects
• MS Excel
• Hyperion/Brio
• Old client server Discoverer 3i

• Type 3 Analytic Reporting tools
• MicroStrategy
• Oracle Web Discoverer
• Whitelight
• OBIEE (formerly Siebel Analytics)
• Type 4 Analytic Write-Back Reporting tools
• MS Analytic services
• Oracle Express/9i 10g OLAP Analytic Workspace
• Hyperion
• Applix TM1
• SAS
• SPSS

21
Agenda

• Reporting and BI definitions
• Requirements Analysis
• IT Reporting Skill Sets
• Reporting Products Marketplace
• Oracles Corporate Performance Management (BI)
• BIS and DBI
• Financial Analyzer and EPB
• Discoverer
• Summary and Questions

22
Financial Statement Generator (FSG)

• Report Writer for GL (only) Type 1 tool
• New in 11.5.10, can publish FSG reports in XML
  format
• Has two required components created by an IT
  developer
• Row set typically contains accounts and
• Column set often contains time periods for
  trend reports.
• (Optional) third component is content set, which
  allows you to limit your data, for example, to a
  single cost center, business line, division etc.
  
• Using ADI to publish reports allows you to
  present the content set data as separate pages in
  an excel workbook so each cost center would have
  its own sheet for instance.
• Re-Use same components or combine components in
  ad hoc reports or with multiple reports
• Able to combine several books into a single
  report prior to consolidation

23
Oracle Reports

• Type 1 Tool -- Supports all Oracle Apps modules
• Standard or static reports come pre-defined with
  Oracle Apps
• New in 6i, can create reports from Oracles
  Relational or Multidimensional Databases from
  same tool, and can publish reports in XML or PRT
  formats
• Can allow Super Users to Create Basic Reports
• Unlimited Data Formatting and High-quality
  Presentation
• Disadvantage IT Developers Will Need to Create
  the Complex Reports, such as reports involving
  cross-module reporting, multiple database
  schemas, or using multiple data sources.

24
BI Publisher (formerly XML Publisher)

• Type 1 Tool -- Supports all Oracle Apps modules
• Available to early adopters in 11.5.9, standard
  in 11.5.10 and version 12
• Can process data from both relational and
  multidimensional (analytic workspaces via SQL
  API) as well as flat file, MS Excel, images, etc
  to make a consolidated view of data in
  presentation layer.
• Can allow Super Users to Create Basic Reports via
  MS Word templates still requires IT support to
  register the template
• Practically unlimited Data Formatting and
  High-quality Presentation
• Disadvantage IT Developers Will Need to Create
  the Complex Reports, such as reports involving
  cross-module reporting, multiple database
  schemas, using multiple data sources, or
  dashboard graphs.

25
Oracle EDW/BIS/DBI Architecture
Oracle Business Intelligence
Ad hoc analysis reporting
BIS PHPTables, Graphs,KPIs
DBI ManagementReporting
Discoverer Workbooks
BIS Views
End User Layer
BI PortalTables, Graphs, KPIs
EDW Facts,Dimensions
BIS
Custom Facts,Dimensions
Materialized Views
Business ViewGenerator
Base Summary
CustomBusinessViews
GeneratedBusinessViews
DBI
EDW
e-Business Suite Transaction Tables
Other Data Sources
26
Business Intelligence System (BIS)

• Type 2 or 3 tool--First shipped in 1998,
  currently in maintenance mode, last enhancements
  made in Oracle Apps 11.5.4
• Predefined views and predefined discoverer
  workbooks using five common dimensions
  (Organization, Product, Geography, Sales Channel,
  and Time)
• A Flexfield Mapping form is provided to handle
  the mapping of customer-specific descriptive
  Flexfield or key Flexfield segments to any of the
  common dimensions
• Uses standard Oracle Apps security profiles
• Covers GL, AP, AR, HR, PO, Projects, CRM,
  Manufacturingprocess and discrete modules
• Standard advice on performance problemssetup BIS
  on a separate reporting instance
• Uses Discoverer technology stack and Oracle
  Applications schema containing the Oracle
  Applications Foundation (FND) tables under the
  covers

27
Daily Business Intelligence (DBI)

• Type 3 tool--First shipped as Early Adopter in
  February 2003, had roughly 10 Early Adopters
  using versions 5.0, 5.1, and 6.0 in beta testing.
  Now in GA status with 11.5.10 with version 7.0
• Uses same HTML server as Performance Analyzer and
  Enterprise Planning and Budgeting, not same as
  Discoverer
• Covers GL, AP, AR, OM, HR, discrete and process
  mfg, INV, ASCP, PO, Projects, Sales, Marketing,
  contracts
• Uses same security model as Oracle Apps if
  installed in same instance
• Designed to be intuitive, and therefore no end
  user training should be necessary

28
DBI Limitations

• Since materialized views must be synchronized on
  a schedule (time or event driven), each html page
  in viewer must have all data updated at same
  time.
• Now allowed to extend or customize views or
  viewer DBI Designer was released as part of DBI
  7.0.1 for 11i.10 on Jan 21, and as part of DBI
  6.0 Rollup 5 for 11i.9 on Feb 25.  You can
  download documentation for the Designer from
  Metalink note 295677.1. In addition, you can
  create EUL on top of materialized views and use
  Discoverer for customizations.
• Requires 9.2.0.4, 11.5.10 and FP D, Apps SSO,
  BIBeans 2.7.5, PMV and PMF 4.0.7 prefer RAC for
  performance reasons if do not have RAC, then can
  use a bigger box.
• Expect 3-6 weeks to get up and running for each
  module
• If need to incorporate external data sources, you
  are currently outside of DBI support, unless
  loaded into FEM Schema via APIs
• If want to customize MV in FEM schema, then copy
  and make changes ONLY to copy for ongoing
  support, or can use NoetixViews.

29
Underlying required modules for DBI

• DBI for Financials
• GL, iExpenses, AR, AP, OM, HR for the Profit
  and Loss and Expense Management pages
• AP for Payables Management and Payables Status
  pages
• DBI for Supply Chain
• OM, AR for Management pages
• Discrete and/or Process Manufacturing for
  Manufacturing Management page
• Inventory for Inventory Management page
• ASCP for Planning Management page
• DBI for Procurement
• Purchasing, iProcurement, Payables, HR
• DBI for PLM all of the above
• DBI for Projects
• Project Costing, Project Billing, Project
  Resource Management, HR, Financials
• DBI for HR Human Resources
• DBI for Sales Sales Online, Marketing
• DBI for Quoting Quoting
• DBI for Service Contracts Service Contracts
• DBI for Interaction Center Advanced Inbound
  Telephony, eMail Center

30
Customizing versus Extending DBI
Remove Measures, Portlets, Table columns
() Change Region Layout on Overview Pages
() Change Report Labels / Column
Headings Rearrange table columns (within
limits) Customize-------------------------------
--------------------------------------------------
--------------------- Extend Create Measures,
KPIs, Dimensions () Create Scorecards, KPI
Reports () Create DBI Overview Pages and
Reports -- Upcoming
() Via Business Intelligence Administrator
responsibility () Via Oracle Balanced Scorecard
and/or DBI Designer Tools
31
Oracle Financial Analyzer

• Type 4 Tool--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
• OFA integrates with the Oracle General Ledger via
  GL Link, or can customize to use non-Oracle data
  sources
• Custom Facts (FDIs), But Knowledge of Express
  Language Needed may be needed
• Needs more administrative time for support and
  maintenance than other tools

32
OFA Architecture
33
Enterprise Planning and Budgeting (EPB)

• Type 4 tool--Delivered as module of Oracle
  Applications, but can be run standalone
• Delivered as a patch to Oracle Applications
  11.5.9 -- Included as part of the rapid install
  for 11.5.10
• Backports of EPB to 11.5.8 or earlier are not
  technically feasible
• Requires Oracle9i Database 9.2.0.4 plus OLAP
  Option or above
• If a customer has 10.7 or 11.0.x or no Oracle
  Applications, then the product can be a
  standalone solution
• More Planned Sources
• Projects
• Fixed Assets
• Human Resources
• Grants
• Supports consolidated views and scenarios of
  Oracle and non-Oracle data via API(s) in FEM
  Schema, same as Enterprise Performance Foundation
  used by DBI, BSC, and PM

34
Migration From OFA to EPB

• OFA and OSA 6.4 will be supported until mid 2008
  with error correction support until end of 2006
• OSA ROLAP customers will need to convert their
  warehouse schema into EPB format as part of
  migration
• Migration paper located at http//www.oracle.com/a
  pplications/cpm. These are migration tools, not
  a one-click upgrade
• Should I migrate or re-implement?
• OFA to EPB migration toolkit can be downloaded
  from Metalink Patch 3922422. It is geared for
  standard OFA objects ONLY and applicable to
  version ZPB.A only.
• Migration and OFA vs. EPB Feature Comparison
  whitepapers
• http//www.oracle.com/applications/cpm/epb.html
• License Migration
• OFA/OSA licenses can migrate to either
• Full user EPB licenses or Oracle BI licenses
  (Discoverer)
• Express licenses migrate to Database Enterprise
  Edition plus OLAP Option run time only licenses

35
Oracle EPB vs Oracle OFA
36
Buy vs Build In Oracle (Reporting Infrastructure)

• Packaged Analytic Applications using Oracle
• Oracle EDW
• Teradata Decision Experts (Formerly DecisionPoint
  Applications)
• Jaros Analytics
• Noetix NETS
• Cognos Performance Management
• Business Intelligence System (BIS)
• Enterprise Planning and Budgeting (replaces
  Financial Analyzer)
• Custom Analytic Applications using Oracle
• Do-it-Yourself with Discoverer OWB
• DBI (with DBI Designer, see Metalink note
  295677.1 )
• OBIEE (formerly Siebel Analytics)
• these products are in maintenance mode
• Acquired by Teradata (NCR) in 11/2005

37
Discoverer

• Ad-hoc Query Tool used to analyze transaction
  data on the fly from Oracle relational (or AW
  databases starting with 10g)
• Integration With Oracle Warehouse Builder, Oracle
  Reports, and Oracle Designer
• Disadvantage - Need For Users to Know Underlying
  DB structures and/or SQL
• Weak security, either public or private, unless
  installed as part of Oracle Apps
• Discoverer Manager defines the following 4 types
  of drills
• Item to item drills (item hierarchies)
• Date to date drills (date hierarchies)
• Drill to detail item class (hyperdrills)
• External application drills (hyperdrill plug-ins)

38
Discovered Integration Methods

• Four possible methods
• Custom EUL against Business Views
• Oracle BIS, or EUL against DBI Foundation
• Create Datamart
• Enterprise Data Warehouse

39
Applications Certification (DIY Approach)

• Discoverer 3i, 4i, 9.0.2, and 10g work against
  Oracle Applications 11i, 11.0 and 10.7
• 11.5.7 rapid install contains Discoverer 4i
  (4.1.48 as part of iAS 1.0.2.2.2)

40
Applications Certification (Out-Of-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 (metalink note 146468.1)
• Be sure to check these Debugging and
  Troubleshooting technotes (Metalink notes
  186981.1, 130091.1, 216208.1, 165195.1, 175287.1)

41
OBIEE (formerly Siebel Analytics)

• Oracle Business Intelligence Enterprise Edition
  (OBIEE) is a stand-alone version of the Siebel
  Analytics product, but it can be implemented in
  conjunction with both PeopleSoft and Oracle
  Applications.
• This same technology stack is being incorporated
  into version 12 of Oracle Applications sitting on
  top of DBI Enterprise Performance Foundation, and
  incorporating the existing DBI content.
• There is a migration path for Discoverer and DBI
  for Oracle Applications to OBIEE
• Oracle supports OBIEE dashboards for the
  PeopleSoft Enterprise Performance Management
  (EPM) Warehouse, including integration points for
  single sign-on (SSO), security synchronization
  and bi-directional drills between the dashboard
  and the PeopleSoft HCM applications.
• Oracle supports building dashboards on top of the
  Oracle Daily Business Intelligence (DBI) summary
  tables using OBIEE. All three components of OBIEE
  (Repository, Answers, and Dashboard) will be
  required along with a technical understanding of
  the EPF data model and schemas.
• technical session at ODTUG Kaleidoscope 2007
  conference in June at Daytona Beach, FL

42
Agenda

• Reporting and BI definitions
• Requirements Analysis
• IT Reporting Skill Sets
• Reporting Products Marketplace
• Oracles Corporate Performance Management (BI)
• BIS and DBI
• Financial Analyzer and EPB
• Discoverer
• Summary and Questions

43
BI Strategic Maturity Where Are You?
44
Typical Environments Reporting Tools
45
More Questions?
46
Oracle Applications Reporting How Can I Get
What I Need?
David Fuston Director, Business
Intelligence Linium, LLC Cell
816.729.1033 Email david.fuston_at_linium.com