Extending Financial Reporting out of Oracle Financials Using Hyperion Essbase

1
Extending Financial Reporting out of Oracle
Financials Using Hyperion Essbase

• George Cooper
• Hackett Technology Solutions

2
Hackett Technology Solutions

• Founded in April 1997
• Traded on NASDAQ as ANSR
• Worlds leading repository of enterprise best
  practices strategies and metrics
• Benchmarking and Advisory Services
• Business Transformation (HR, IT, Planning and
  Performance Measurement, Finance, Supply Chain )
• Over 700 professionals with 12 years experience
  across more than 25 competencies
• Consulting and system integration expertise with
  comprehensive skills in
• Oracle, SAP
• Hyperion

3
Hacketts Oracle Practice

• Certified Oracle Implementation Partner since
  1995
• Completed over 350 Oracle and PeopleSoft
  implementations, upgrades and optimization
  projects
• Implementing Oracle Solutions based on
  Hackett-Certified Practices

• Highly skilled consultants
• Large-scale Oracle implementation experience
• Average 7 years Oracle experience, 12 years
  business experience
• 78 are Hackett-Certified Advisors

• Many of Oracles Leading Accounts Have Already
  Discovered the Benefits of the Answerthink /
  Hackett Group Approach

4
Hacketts Hyperion Analytics Practice

• Strategic and technical implementation consulting
• 1 Hyperion Americas Reseller Award at Solutions
  2006 and 2007
• Member of Hyperion Partner Advisory Council
  Customer Advisory Boards and participant in the
  Partner Development Exchange
• 400 successful Hyperion projects, with well over
  100 in 2006 and 50 of those System 9
• 100 resources dedicated exclusively to Hyperion
• Scores of Hyperion-certified consultants, plus
  Preferred Partner Certification

5
Hyperion Clients
6
Agenda

• Hyperion Essbase Overview
• Essbase Technical Overview
• Typical Implementation
• Case Studies

7
Hyperion Essbase Overview
8
What is Essbase?

• The leading enterprise multi-dimensional database
  engine
• Provides real-time analytic infrastructure for
  business intelligence and enterprise performance
  management (EPM) applications.
• Engineered for scalability, security, and
  rapid-response.
• Through an intuitive interface, business users
  can manipulate large data sets to model complex
  scenarios, forecast outcomes, and perform
  what-if analyses to identify trends and
  optimize business results.
• Oracle Hyperion Essbase Datasheet

9
What is Multi-Dimensional?

• Uses a cube metaphor to describe data storage.
• An Essbase database is considered a cube, with
  each cube axis representing a different
  dimension, or slice of the data (accounts, time,
  products, etc.)
• All possible data intersections are available to
  the user at a click of the mouse.

10
Multi-Dimensional vs. Relational

• Multi-dimensional database are usually queried
  top-down the user starts at the top and drills
  into dimensions of interest.
• Can perform poorly for transactional queries
• Relational databases are usually queried
  bottom-up the user selects the desired low
  level data and aggregates.
• Harder to visualize data can perform poorly for
  high-level queries

Total Products
P01
P02
P03
P01
P02
P03
Total Products
11
Why Use Essbase?

• Rich User Experience users converse with the
  data
• Business and Finance can manage their own
  metadata and reports
• Highly advanced calculation engine
• Easy integration of data sources, including
  manual input
• Large scalability
• Robust, cell-level security
• Many sophisticated reporting tools

12
Rich User Experience

• Sub-second response
• Intuitive interface, especially with Microsoft
  Excel
• Powerful adhoc analysis that allows users to
  query virtually any database intersection in
  seconds
• Visually understand the relationships in the data
• Easily built reports without IT involvement.

13
Rich User Experience

• Excel Essbase Demo

14
Business Metadata Management

• Graphical administration console allows
  authorized administrators to see their data
  structures
• Simple specification of alternate rollups for
  specialized reporting
• Allows the application to evolve as quickly as
  the business

15
Business Metadata Management

• Essbase Administrative Services (EAS) Demo

16
Powerful Calculation Engine

• Over 350 built-in functions, including
• Financial functions, such as net present value,
  rate of return, and compound growth
• Custom multi-dimensional functions such as
  _at_ALLOCATE to drive data to multiple business
  intersections
• Complete time-series support
• Support for summary-level input
• Both run-time and batch calculations
• MDX support

17
Integration of Data Sources

• Unique multi-user read/write technology
• Information from many data sources can be easily
  integrated into one database, and thus one set of
  user queries
• In particular, allows budgets and forecasts to be
  fully integrated with actuals

18
Technical Overview
19
Data Storage

• Multi-dimensional, with own proprietary storage
• Two primary data storages
• Block Storage Option (BSO) Record-based
  storage supports write-back and features the
  batch calculation engine.
• Aggregate Storage Option (ASO) Cell-based
  storage supports fast aggregation with a large
  number of dimensions.

20
Block Storage Aggregations

• In general, all members combinations are
  calculated during an aggregation
• Can be optimized for faster performance
• Allows complete control of calculations

21
ASO Storage Aggregations

• Engine decides which level intersections should
  be calculated to minimized retrieval time
• Specific aggregations can be specified to
  optimize particular queries

Smaller Agg. Size
Larger Agg. Size
22
Scalability

• BSO databases generally are impractical with more
  than five or six hierarchical dimensions,
  depending on the number of members, depth of
  hierarchies and structure of the data
• ASO databases have no set limit on number of
  dimensions 20 dimensions are possible
• Both storage types support
• Hundreds of thousands of outline members
• Attribute dimensions, which are based on a
  one-to-many relationship with the base members of
  another dimension
• No additional storage or calculation time is
  needed for Attribute dimensions in BSO
• Drill-through to relational detail

23
Partitions

• Partitions are dimension slices that are shared
  between Essbase database
• Replicated The data is physically transferred
  between the source and target cubes
• Transparent The data in the source cube is
  queried at retrieval time
• Process is seamless to the user all data
  appears to be in the target cube
• ASO and BSO databases can be linked together with
  a transparent partitions

24
Typical Implementation
25
Partnership with Oracle GL

• Users typically use Essbase for
• Historical and forecasted performance
• Budget variances
• Variance and profitability analysis
• Performance trends
• Profitability metrics and Foreign Exchange impact
• Generally any query using non-transactional data
• Users typically use Oracle Reports for
• Viewing individual transactions
• Auditing GL entries
• External reporting
• Generally any query that needs transaction data

26
Typical Support Model

• IT supports core functionality
• Servers and software installation
• Daily extracts and loads from Oracle Financials
  and other data sources
• Core calculation scripts and database
  dimensionality
• Essbase automation
• Central Administration supports
• Security
• Primary hierarchies and metrics
• Line Finance or Business supports
• Most report development
• Alternate hierarchies for specialized reports
• Budget and forecast input and review

27
Basic Implementation
Oracle Financials
Other Data Sources
Hyp Planning Forms
Actual Balances by Month / GL Segment
Hierarchy Metadata
Excel Templates
Text Files
Manual Forecast Budget Input
Essbase
Essbase Admin Services
Finalized Budgets
Dashboards Production Reports
Adhoc Excel Queries
Excel Reports
Oracle Upload
28
Implementation with Master Data Mgmt
Oracle Financials
Other Data Sources
Hyp Planning Forms
Actual Balances by Month / GL Segment
Excel Templates
Text Files
Manual Forecast Budget Input
Essbase
Essbase Admin Services
Master Data Mgmt (MDM)
Finalized Budgets
Dashboards Production Reports
Adhoc Excel Queries
Excel Reports
Oracle Upload
29
Case Study 1

• Budgeting and Forecasting for a Large Retail
  Company

30
Problem

• Build a budgeting and forecasting model that can
  support the needs of the individual brands while
  minimizing both IT support and the technical
  expertise required by the Finance administrators

31
Challenges

• Separate applications could provide the
  flexibility required by the individual brands but
  would increase development time and ongoing
  technical support
• Reporting requirements are significantly
  different in each brand
• A single application would reduce technical
  support but negatively impact planning
  flexibility and possibly reduce performance and
  reliability
• The primary business hierarchies must stay in
  sync with all brands
• Planning administration in each brand must
  require a minimum of training since turn-over is
  relatively high in the finance groups
• A combined corporate view of actuals, budgets and
  forecasts must be supported

32
Solution

• Have separate physical Essbase databases for each
  brand while having a commonly maintained outline,
  calculation scripts, automation procedures and
  Excel utilities
• Keeps each brands data physically separate to
  reduce performance risk while increasing security
• Central outline and core functionality reduces
  maintenance, increases reliability and minimizes
  training for each brand administrator
• Central maintenance of core hierarchies and
  metrics keeps one version of the truth
• Include functionality in the database to allow
  any account to either have direct input or be
  calculated as a percentage of a selected driver
  (revenue, headcount, etc.)
• Allows the brands to decide how each account is
  calculated

33
Solution (cont)

• Task each brand administrator with building their
  own reports, input templates and alternate
  hierarchies
• Gives each brand the flexibility to internally
  report their business in the manner their
  management desires to see it
• Brands can share reports and templates where
  appropriate
• Build a central administration console to
  automate common administrative functions
• Calculating input data, maintaining scenarios and
  controlling the budget process in a single, easy
  to use interface
• Build a separate corporate database with a
  replicated partition to the individual brand
  databases
• Supports a combined view of the corporation

34
Architecture
Central Essbase Outline
Oracle Financials
Essbase Admin Services
Hierarchy Metadata
Actual Balances by Month / GL Segment
Common Automation Outline and Data Updates
Replicated Partition
Brand A Essbase
Brand C Essbase
Brand B Essbase
Corp Essbase
Excel Templates
Manual Forecast Budget Input
35
Case Study 2

• Budgeting and Forecasting for a Large Trade Show
  Management Company

36
Problem

• Build a budgeting and forecasting model that can
  support very detailed forecasts and budgets while
  providing quick and transparent access to all data

37
Challenges

• Large number of business dimensions potentially
  increase database size and calculation time
• Planners are spread over a wide geographic area
  with little technical expertise
• A very large number of individual projects need
  to be forecasted

38
Solution

• Use Hyperion Planning as the overall planning
  engine
• Web-based planning input
• Finance administrator can easily define forms
• Little training needed for budget / forecast
  input
• Keep current data in the Planning BSO database
  but move actuals and historical plans into a
  separate ASO database
• BSO database allows write-back for maintaining
  the current information
• ASO database allows fast loading and aggregation
  times for the much larger volume of historical
  and actual data
• Optional Link the two database via a transparent
  partition to give the users one view of the data

39
Architecture
Oracle Financials
Hierarchy Metadata
Hyperion Planning Admin Console
Hyperion Planning
Actual Balances by Month / GL Segment
Hyperion Planning Web Forms
Current Essbase
Level0 Export
ASO History Essbase
Excel Reports
40
Architecture (Alternative 1)
Oracle Financials
Hierarchy Metadata
Hyperion Planning Admin Console
Hyperion Planning
Actual Balances by Month / GL Segment
Hyperion Planning Web Forms
BSO Input Essbase
Level0 Export
ASO Report Essbase
Excel Reports
41
Architecture (Alternative 2)
Oracle Financials
Hierarchy Metadata
Hyperion Planning Admin Console
Hyperion Planning
Actual Balances by Month / GL Segment
Hyperion Planning Web Forms
Planning Essbase
ASO Actual / History Essbase
Transparent Partition
Excel Reports
42
Case Study 3

• ERP Reporting at a Large Semiconductor Company

43
Problem

• Build a suite of reports from various ERP
  modules during a new Oracle implementation under
  a very aggressive time frame

44
Challenges

• Development time frame was short and business
  involvement constrained during the ERP
  implementation
• Users required the flexibility to design and
  build reports and dashboards over time but see
  the data immediately at go-live
• Some modules (such as quality assurance) had a
  large volume of data and a large number of
  dimensions
• Data had to be refreshed nightly

45
Solution

• Build separate Essbase databases for each ERP
  module with the dimensionality and level of
  detail necessary to manage the business
• Task the business and finance staff to develop
  their reporting through Excel SmartView
• For each application combine a BSO Essbase
  database for core calculations and an ASO Essbase
  database for user reporting
• Supports a large number of dimensions and
  extremely large data volumes
• Develop formatted reports in Hyperion Financial
  Reports and Hyperion Web Analysis where
  appropriate
• Use Hyperion System9 Interactive Reporting for
  transaction-level reports

46
Architecture
Oracle ERP
Hierarchy Metadata
Excel Reports
Text Extracts
BSO Level 0 Essbase
ASO Report Essbase
Level0 Export
Dashboards Production Reports
Calculations
47
Contact Information

• Michael Cook
• Director of Business Development
• Phone [phone]
• Mcook_at_thehackettgroup.com
• George Cooper
• Manager, BI Northern California Practice
• Phone [phone]
• gcooper_at_thehackettgroup.com