Using Oracle Business Intelligence with Oracle Essbase

2. Using Oracle Business Intelligence with Oracle Essbase Gabby Rubin, OracleMitch Campbell, Oracle RamkeRamakrishnan, Grant Thornton LLP

3. THE FOLLOWING IS INTENDED TO OUTLINE OUR GENERAL PRODUCT DIRECTION. IT IS INTENDED FOR INFORMATION PURPOSES ONLY, AND MAY NOT BE INCORPORATED INTO ANY CONTRACT. IT IS NOT A COMMITMENT TO DELIVER ANY MATERIAL, CODE, OR FUNCTIONALITY, AND SHOULD NOT BE RELIED UPON IN MAKING PURCHASING DECISIONS. THE DEVELOPMENT, RELEASE, AND TIMING OF ANY FEATURES OR FUNCTIONALITY DESCRIBED FOR ORACLE'S PRODUCTS REMAINS AT THE SOLE DISCRETION OF ORACLE.

4. Program Agenda • New Essbase/BI Features in BI 11.1.1.7 • Future Direction for Essbase and BI Integration • Using Essbase with BI Mobile • Use Case and Lessons Learned • Q&A

5. Complete Enterprise Business Intelligence Oracle Business Intelligence Foundation Suite Enterprise Business Intelligence Centralize semantic layer and definitions of all metrics, calculations, and measures for data-driven visualizations Increase operational effectiveness from reports to planning that eliminates cost redundancy PAST PRESENT FUTURE COMMON ENTERPRISE INFORMATION MODEL

6. Oracle Business Intelligence Foundation Suite Single Enterprise-Scale Business Analytics Platform • Unique Features • Common Enterprise Information Model with physical, semantic and presentation layers • Market-leading OLAP server for forward-looking analysis • Single set of metrics across the enterprise • Benefits • Improve business performance by linking insight to action • Consistent enterprise metrics for improved productivity • Single analytic view across all data sources

7. Oracle Business Intelligence Foundation Suite Oracle Essbase OBIEE EPM Microsoft Office • Essbase provides a platform to build analytic applications which allows users to model, analyze, and interpret complex business scenarios • Benefits • Enables ‘speed of thought’ modeling of alternative scenarios • Simplifies the forecasting and variance analysis processes • Secure, shared, single version of the truth • Centralized functions, calculations, aggregations • Unique Features • Ease of use for model creation and analysis • Users use familiar tools to analyze – mainly Excel • A centralized enterprise-scale solution • Financial & time intelligence • High performance for complex analysis • Oracle Essbase Server • Enterprise scale • Optimized multidimensional storage • Over 350 analytic functions • Financial, time and calendar intelligence • Multi user write-back • Trickle feed

8. Oracle Essbase Roadmap Subject to Change • Calclanguage enhancements • Calc run-time parameters – sub-variable improvements • New calc functions • @ISMBRUDA, @MEMBERAT, @RANGEHASVAL, @RANGEFIRSTVAL, @RANGELASTVAL, @INTERSECT, @CREATEBLOCK • Performance • JAPI performance for outline editing • Dim Build performance improvements • ASO with multiple attributes • XREF performance enhancements (1/2) • BI Specific (BI Server, OBIEE, Infra) • BI – MDX performance improvements • BI PS – new Essbase related features • Parent last, null suppression control, enhanced member selection • FMW security, BI Shiphome delivery • Resolve naming restrictions • Security filter callback • Dimension member properties • APS/Essbase custom aggregates and lists • Calc language enhancements • Adding @XRANGE support for 30 functions • Parallel Fix • Performance • MDX performance • Sub Select • Aggregate function • JAPI performance for batch outline editing • XREF performance enhancements (2/2) • ASO in BSO Hybrid Mode (1/2) • BI Related • Cube Deployment Services v1 • Null dimension members (via CDS) • Renegade members • BI Java Agent • MOLAP Aggregation • OBIEE PS Write-Back • Cube Deployment Services v2 • Enhanced Financial Logic • Aggregation Weight • EPM Java Agent • Sandboxing • BSO Solve Order • Hot Backup • Active-Active • Essbase ADF controls • Calc language enhancements • New functions • For Loop • Performance • ASO in BSO Hybrid Mode (2/2) • Dim Build performance improvements (BSO) Essbase 11.1.2.3/BI 11.1.1.7 April-June 2013 What’s Next 0-12 month planning cycle Future DirectionsPost 12 month planning cycle For information purposes only, not a commitment to the development, release, and timing of any features or functionality.

9. New Integration Capabilities in 11.1.1.7 • Essbase is delivered with BISHIPHOME • Complete installation including the Essbase tools • Support for BI security • Shared services in not needed for BI only use cases • Migrating existing EPM instances with Shared Services will require new security setup • MDX performance enhancements • Presentation Services Essbase oriented features

10. OBI / Essbase Integrated Infrastructure Enterprise Manager Tools Install Security Logging

11. Essbase and Related Tools Installed with BI Enable customers to use BI against Essbase without complex installs and post-install configuration. • Essbase installed with BI using the same security • Financial Reporting, EAS, Essbase Studio and Calculation Manager also installed with BI

12. BI Server and ESSBASE Interaction • New Logical SQL syntax and BI Server ODBC Procedures to support direct interaction with ESSBASE data source • BIS ODBC Procedures to create & execute Essbase Calc and MAXL scripts • Execute MaxL script • List available calc scripts • Create calc script • Execute calc script

13. BI Server and ESSBASE Interaction Foundational technology for advance users and PS future • Support for Essbase as aggregate target for automated Persistent Aggregate scripts • Logical SQL syntaxes for Essbase direct Cube creation (CREATE CUBE, CREATE AGGREGATES) • Logical SQL syntax to updating, deleting inserting single & multiple measures • UPDATE <presentation table> SET <presentation col 1> = <write value>… WHERE <key column 1> = <key value 1> … • DELETE FROM <presentation table> WHERE <key column 1> = <key value 1> • INSERT INTO <presentation table> SET <presentation column 1> = <write value>

14. BI UI Enhancement for Essbase Parent Last Navigation • Option to allow how hierarchies are displayed • New property • Default is Parent before/above • Applies to all axis • Impacts all applicable views

15. BI UI Enhancement for Essbase Null Suppression Options • Controls whether nulls in rows/columns are suppressed in sparse data sets • Default is Suppress • Override at view-level • Applies to row, column edges • Impacts all applicable views

16. Smartview Integration • Smartview as the standard Office integration technology • Mix BI, Essbase and EPM content on a single sheet, document or PowerPoint slide • Fully interactive Essbase content with dynamic BI views

17. EPM Workspace Integration • Consume, Create and Edit BI content within Workspace • BI Catalog • BI Home • BI Interactive Dashboards • SSO enabled • Oracle BI can consume HSS token

18. BI Publisher Enhancements MDX Query Builder • GUI for building MDX queries against Essbase • Easily report against Essbase & Planning cubes without the need to introduce them into the RPD • Combine data coming directly from Essbase on OBI dashboards • Combine Essbase data with data from other sources in the same report • Use BI Publisher for production style reporting against Essbase

19. BI Publisher With Essbase MDX Demo

20. Future Directions

21. MOLAP Aggregation • Using Essbase to accelerate queries in OBIEE • Seamless integration for the BI Admin – no Essbase knowledge is required • No scripts, no database optimization • Cube is automatically introduced into the RPD

22. Enable Complex Analytics for BI • Guided process to introduce metrics and calculated KPIs • Multiple analytics packages • Transparent - No Essbase knowledge required • Automated introduction into the RPD • Sandbox based What If analysis

23. Analytics By Request Create projection Add Trend lines Remove

24. Analytics By Request

25. Using Essbase with BI MobileMitch CampbellOracle

26. Use Case and Lessons LearnedRamkeRamakrishnanGrant Thornton LLP

27. ORACLE BUSINESS INTELLIGENCE WITH ORACLE ESSBASE CASE IN POINT • CUSTOMER CASE STUDY • OBIEE ESSBASE INTERACTION • LESSONS LEARNED & BEST PRACTICES Ramke Ramakrishnan Principal, Business Advisory Services, Technology Solutions

28. ABOUT GRANT THORNTON OUR GLOBAL SERVICES

29. ABOUT GRANT THORNTON TECHNOLOGY SOLUTIONS

30. Creating business harmony INTEGRATION OF ERP, BI & EPM Harmony is the integration and access of information across an organization’s ERP, BI and EPM solutions to produce a robust analytical environment that delivers timely business insight. • Harmony turns data into relevant and complete information: • creating multiple perspectives with one-certified and secured view of data (BI) by • connecting transactional detail (ERP) to predictive forecasting content (EPM) and • establishing a flexible end-to-end analytics foundation for future growth and change • Harmony drives the following benefits: • Enables cross-domain analysis of common business subject areas • Provides the ability to develop new analytic models • Improves collaboration across an organization

31. Dedicated bi team FOR FASTER ROI 480 Projects implemented by our team. 160 Number of Oracle specialization certifications. 90 MarketSphere’s dedicated Business Analytics professionals. 13 Average years of experience of team. #1 Awarded Oracle Excellence Award for BI & DW and Fusion BI/Middleware in 2012.

32. CASE STUDY ABOUT IMS HEALTH • A Top provider of information, services and technology to the healthcare industry • Operating in more than 100 countries, it is the world's leading provider of market intelligence to the pharmaceutical and healthcare industries. • Manages 100,000 suppliers, and on insights from more than 40 billion healthcare transactions processed annually • Tracks more than 80% of global pharmaceutical sales activity, 1.4+ million pharmaceutical products; 5,000 databases supporting 16,000 healthcare clients, including pharmaceuticals, biotech, payers and providers • In 2013, the firm was included on IDC Health Insights’ list of top life science Information technology vendors BUSINESS DRIVERS Rapidly develop New Sales Models and Strategies Better visibility to Account Performance, Sales Pipeline and Resources Understand and Provide Improved Analysis to Market Dynamics Integrated Self Service & Ad-hoc Reporting with CRM Suite Internal Alignment on Opportunities between Sales and Finance Sales Force Effectiveness

33. CASE STUDY BI MATURITY ASSESSMENT Business Intelligence Technologies BI Maturity with BI Technologies help our clients understand current state and improvement opportunities Complexity / Competitive Advantage • What Business Value does Business Intelligence brings? • Where in the Organization is most of the reporting and analysis is done today? • Who is using Business Reports, Analysis and Indicators? • What drives Business Intelligence in the Organization? • What Strategies for developing Business Intelligence are in use today? LOW Business Value HIGH

34. CASE STUDY KEY BUSINESS DRIVERS Improved Visibility for Performance Management

35. CASE STUDY BI FOUNDATION SUITE • The latest version of OBIEE Installation now includes Essbase as option and install several products including Calculation Manager, Essbase Studio, Financial Reporting and Workspace for broader integrated reporting and intelligence • All of the core components of Oracle BI Foundation Suite can be deployed on Oracle Exalytics Platform OBIEE (Oracle BI Server) and Essbase are now packaged together as a single product and are the core server components of Oracle BI Foundation Suite. Oracle Business Intelligence Foundation Suite consists of Oracle Business Intelligence Enterprise Edition (OBIEE) 11g, Oracle BI Publisher, Oracle Essbase, Oracle Scorecard and Strategy Management, and Oracle Essbase Analytics Link (EAL).

36. CASE STUDY APPLICATION TOPOLOGY Data Quality / Business Definitions Business Analytics Oracle BI Foundation Suite Oracle EPM Suite ASO BSO Essbase Studio Financial Planning & Budgeting Financial Management Enterprise Data Management Global Data Warehouse Financial Data Quality Management Oracle Data Integrator Informatica Source Systems SAP Financials

37. CASE STUDY USER EXPERIENCE User Interface & Interactions Scorecard & Strategy Management Actionable Intelligence & Alerts BI Publisher • Advanced Visualizations • Presentation hierarchical columns • Support for ragged, skip and unbalanced hierarchies • Map view integration • Simple navigation • EPM and Endeca Integration • Full text search capability • Mobile Access • Create objectives and initiatives to form strategy • Assign KPI's to objectives and initiatives for measuring progress and performance • Empowers user to articulate, monitor and communicate integrated strategy roadmap • Improves users ability invoke processes and trigger workflows based on events identified through reports, dashboards and scorecards • Delivers functionality of 'Guided Analytics' • Provides for actionable insights • Extends BI to integrate business processes, services and applications • Incorporates customer UI and interactions • New architecture and functionality with 11g • Provides same ADF DVT visualizations as Answers • Reusable across different reports with segregated data model • Allows for online creation and editing of reports and templates • Removes dependency on traditional editors • Includes rich UI features and Improves federated queries

38. CASE STUDY ANALYTICS ENGINE ORACLE ESSBASE • Richest business user experience: Delivers "speed-of-thought" query response time so users can quickly understand the metrics that influence business performance, ask questions, and make informed decisions. what drives the business. • Highly advanced calculation engine: It is the most advanced, high performance calculation engine on the market. It ships with over 350 pre-built, out-of-the-box functions, accelerating the development of complex business models. • Best custom analytic environment: It enables line-of-business personnel to drive application design and rapid iterations. The highly graphical "outline" technology makes it easy to build dimensions, hierarchies, metrics, and scenarios. Provides environment for rapidly developing custom analytic & EPM applications Enables business to quickly model complex business scenarios Easy for line-of-business personnel to develop & manage analytic applications that model complex scenarios, forecast the business and perform "what-if" analyses Supports extremely fast query response times for vast numbers of users, large data sets, & complex business models Hot-pluggable across any data source

39. CASE STUDY INTEGRATED BUSINESS ANALYTICS • Company Overview & Situation • Operating in more than 100 countries, the world's leading provider of market intelligence to the pharmaceutical and healthcare industries; Tracks more than 80% of global pharmaceutical sales activity, 1.4+ million pharmaceutical products; 5,000 databases supporting 16,000 healthcare clients, including pharmaceuticals, biotech, payers and providers • Lack of visibility to Pipeline, Sales opportunities, Improved and Integrated Revenue Reporting and Sales force Effectiveness • MarketSphere/Grant Thornton Applied Expertise • Enabled Finance and Sales Executives globally to gain deeper insights and better visibility to Financial metrics including pipelines, revenue and resource utilization delivered through BI Foundation Suite (OBIEE and Essbase) • Implemented OBIEE Sales/Finance Dashboard(s) delivered within Salesforce.com cloud portal utilizing Oracle FMW with integrated Single-SignOn (SSO) using WNA/Kerberos • Using Agile approach, delivered the full solution in sprints, including: • Establish developmental best practices on administration and maintenance of OBIEE to support a broader reporting and analytics platform for the future state • Integrate reporting from SQL Server DW, Essbase and OBIEE. • Deployed Mobile Analytics for business users that constantly travel across the globe • Established MarketSphere’s Foundation Analytics Methodology and leverage the full potential of the BI Foundation Suite • Benefits & Value Achieved • Moved from fragmented reporting to automated and integrated reporting • Fast access to financials through ad-hoc analysis capabilities and Performance Dashboards • Ability to access BI/Essbase content within SFDC portal with greater visibility to both hard and soft backlogs on projects; Remote decision makers access key data on the mobile devices Foundation Analytics Methodology™ Solution Delivered in Sprints “My Dashboards” delivered through SFDC portal

40. ORACLE BI FOUNDATION SUITE BENEFITS Delivers common framework, presentation layer and reporting experience for end-users. Best Mechanism for unifying multiple data sources and metadata into a single common BI platform With data federation capabilities in OBIEE, achieve robust performance to Dashboards/Reports by leveraging Essbase Aggregation and MDX queries with seamless drill-through capabilities to transactions Enhance reporting experience with certain dashboard functionality that cannot be achieved through native Essbase/Hyperion tools (column selectors, dimension level switching and measure selection) Eliminate complex Essbase design and dynamic calculation that will require specific data and dimension intersections and managing some of those in the BI Server and OBIEE abstract layer Control user inputs and what if scenarios in Essbase using OBIEE action framework Oracle BI EE can also easily integrate with other applications on the front-end, so BI EE content can be exposed through Web Services and accessed in various operational applications and portals

41. OBIEE AND ESSBASE interaction HIGHLIGHTS OBIEE supports value-based hierarchies, alias tables and UDA for Essbase Hierarchical Column support in OBIEE 11g provides enhanced OLAP style reporting in OBIEE for Essbase UDA columns can be used in filters Ability to perform "Selection Steps" such as "Select children of", "keep/remove members" etc., in the filters Provides advanced visualization, graphics , geo spatial integration capabilities for Dashboards BI Mobile, Scorecards, KPI Watch list, Web Service and application integration Support reporting on alternate hierarchies from Essbase ASO application (not supported for BSO) Perform federation (horizontal and vertical) capabilities between Essbase and Relational sources OBIEE can use Essbase as an aggregate persistence target where the aggregates are stored in Essbase (This will require installing Essbase, EAS and Studio on the same MW home as OBIEE. Available in 11.1.1.7 Power to combine native OBIEE analytics functions and Essbase native functions

42. OBIEE AND ESSBASE INTERACTION STAR SCHEMA MODEL Presentation Logical Physical • In the OBIEE repository, Essbase is translated into a relational star schema metadata model • Leveraging the star schema surrounded by dimensions and fact measures, • During import the first imported member is labeled as ‘Measure’ Dimension type . • Time dimension are typically identified automatically. • Create/Import Additional Columns for UDA and Alias Table(s) for better reporting • Flattening of Measure dimension makes it easy to build extra Logical Measured columns and also eases up the process to build analysis

43. OBIEE AND ESSBASE INTERACTION MULTIPLE MEASURES To make an effective use of the BI reporting from Essbase, it is necessary to have multiple version of the measures If all measures are treated the same, flatten of measure may not be required. However, if the measure contains different type such as volumes and dollars, we need them as separate measures with different formatting options The goal is to primarily support drill-down for the hierarchical measures as well as static flattened measures for dashboard type reporting

44. OBIEE AND ESSBASE INTERACTION HIERARCHY ELEMENTS • Keys that are inherent to an Essbase cube such as Memnor, Member Key, Parent Key are brought into BI repository as elements of the hierarchies. • “Member Number Ranking” (Memnor) column is automatically created for every Generation in each dimension and used for sort order • UDAs and Aliases can be seen only in logical layer and are typically used for display purposes as well as alternate columns in hierarchies for meaningful reporting • In case of using Alias tables the default outline in BI is sorted alphabetically hence users find the outline completely different compared to Smartview, work around is to actively assign the sort key (memnor) of that dimension to the Alias column

45. OBIEE AND ESSBASE INTERACTION DIMENSION TYPES All Dimensions and Hierarchies are not always the same when imported into OBIEE • Dimension Type is designated in the physical layer after import process. • Time Dimension • Measure Dimension • Attribute Dimension • Other or Unknown • Type of hierarchy is designated in the physical layer after import process. • Balanced or Unbalanced • Ragged • Value based

46. OBIEE AND ESSBASE INTERACTION ALT HIERARCHIES • Alternate Hierarchies are most common in Essbase. As an example, the period dimension can have Year/Qtr/Mth and YTD rollups and month variances • In OBIEE repository, it can be modeled to keep as single hierarchy view or as multiple dimensions for each • The key consideration to use multiple hierarchies in OBIEE is • Multiple hierarchies enabled in Essbase outline • only one can be used at a given time in a report

47. OBIEE AND ESSBASE INTERACTION MULTIPLE DIMENSIONS The most common best practices in Essbase is to have separate dimension for Year and Period especially for Planning Application. In certain reporting scenarios where we have to show range of periods or requirements where data has to be shown till the selected period (Example: Show the financial data as of Aug-2011) multiple dimension of same type may be required

48. OBIEE AND ESSBASE INTERACTION LARGE OUTLINES • With the Essbase data retrievals are manifested through the OBIEE generated MDX queries there are some clear limitations on large outlines as well as large set of members in a given Essbase outline. The typical errors are: • ESSBASE ERROR: SET IS TOO LARGE TO BE PROCESSED. SET SIZE EXCEEDS 2^64 TUPLES • Internal error: Query is allocated too large memory (> 4GB) and cannot be executed • Essbase Error: Internal error: Query is too large and cannot be executed. The product of member counts across all dimensions in the query exceeds 2^64 • The common ways to work around these limitations are: • Reduce the number of members and/or dimensions to a single MDX query • Design your report to logically break apart to multiple MDX queries With   set [_Customer7]  as 'Generate([Customer].Generations(2).members, Descendants([Customer].currentmember, [Customer].Generations(7), leaves))'   set [_Cycle0]  as '{Distinct({[Cycle].[1 - Identify Opportunity], [Cycle].[Stage 1-5]})}'   set [_Opportunity ID3]  as 'Filter([Opportunity ID].Generations(3).members, ( NOT ([Opportunity ID].CurrentMember IS [Opportunity ID].[0_OP])))'   set [_Period0]  as '{Distinct({[Period].[Q1]})}'   set [_Profit Center0]  as '{Distinct({[Profit Center].[United States]})}' select   { [Attribute Calculations]   } on columns,   NON EMPTY {crossjoin({[_Customer7]},crossjoin({[_Cycle0]},crossjoin({[_Opportunity ID3]},crossjoin({[_Period0]},{[_Profit Center0]}))))} properties MEMBER_NAME, GEN_NUMBER, [Customer].[Default], [Cycle].[Default], [Opportunity ID].[MEMBER_UNIQUE_NAME], [Opportunity ID].[Memnor], [Customer].[MEMBER_UNIQUE_NAME], [Customer].[Memnor], [Opportunity ID].[Default], [Period].[Default], [Profit Center].[Default] on rows from [RevV1u.RevV1u] where ([Currency].[United States Dollar (RPT)], [Scenario].[Opportunity (Gross)], [Year].[FY12])

49. OBIEE AND ESSBASE INTERACTION FEDERATION • Data Federation is commonly referenced to the linking of data between difference data sources. With OBIEE, federation of Essbase data with other data sources such as relational, can be accomplished by the following: • Drill to Details as part of the hierarchical drill where certain level of data will be maintained in Essbase and the details in relational mapped through the common dimension levels • Source Blending where different measures and/or same measures sourced from Essbase and other data sources • Navigate to details through the passing of parameter values from one report to another report • Relational lookup functions to query some additional attributes for reporting

50. OBIEE AND ESSBASE INTERACTION FEDERATION A report can show information sourced from Essbase and as users drills down a logical hierarchy, transparently switch to relational source. When a level of detail is reached in the hierarchy drill, that is not supported by Essbase aggregated source, OBIEE server automatically switches to relational model to source the corresponding detailed data