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 World’s 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. Hackett’s 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 15 + 35% 10 - 15 35% Y E A R S O F E X P E R I E N C E 5 - 10 25% Average 12 years experience 5% • Highly skilled consultants • Large-scale Oracle implementation experience • Average 7 years Oracle experience, 12 years business experience • 78% are Hackett-Certified Advisors • Many of Oracle’s Leading Accounts Have Already Discovered the Benefits of the Answerthink / Hackett Group Approach

4. Hackett’s 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 @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 Hyp Planning Forms Oracle Financials Other Data Sources Actual Balances by Month / GL Segment Excel Templates Hierarchy Metadata Text Files Manual Forecast & Budget Input Essbase Essbase Admin Services Finalized Budgets Adhoc Excel Queries Excel Reports Dashboards & Production Reports Oracle Upload

28. Implementation with Master Data Mgmt Hyp Planning Forms Oracle Financials Other Data Sources Actual Balances by Month / GL Segment Excel Templates Text Files Manual Forecast & Budget Input Essbase Essbase Admin Services Master Data Mgmt (MDM) Finalized Budgets Adhoc Excel Queries Excel Reports Dashboards & Production 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 brand’s 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 B Essbase Brand C 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 Current Essbase Hyperion Planning Web Forms 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 BSO Input Essbase Hyperion Planning Web Forms 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 Planning Essbase Hyperion Planning Web Forms 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 – 415.435.0344 Mcook@thehackettgroup.com George Cooper Manager, BI Northern California Practice Phone – 510.290.9538 gcooper@thehackettgroup.com