Essbase12c and OBIEE12c - What's New for OBIEE/Essbase Integration

OBIEE 12c has some interesting new features around Essbase integration, including improvements around using Essbase as a query acceleration layer and general improvements around usability and RPD/Outline integration. Come to this session to see what they are, how they affect OBIEE/Essbase integration, and where this initiative may be going over time.

Transcript

1. info@rittmanmead.com www.rittmanmead.com @rittmanmead OBIEE 12c and Essbase: What's New for Integration, and Reporting Against EPM Sources Mark Rittman, CTO, Rittman Mead ODTUG KScope’16, Chicago, June 2016
2. info@rittmanmead.com www.rittmanmead.com @rittmanmead 2 •Oracle BI and DW Gold partner •Winner of five UKOUG Partner of the Year awards in 2013 - including BI •World leading specialist partner for technical excellence, 
 solutions delivery and innovation in Oracle BI •Approximately 80 consultants worldwide •Offices in US (Atlanta), Europe •Skills in broad range of supporting Oracle tools: ‣Essbase, Oracle OLAP ‣GoldenGate ‣Endeca ‣OBIEE, OBIA, ODIEE ‣Big Data, Hadoop, NoSQL & Big Data Discovery About Rittman Mead
3. info@rittmanmead.com www.rittmanmead.com @rittmanmead 3 •Latest Almost latest release of Oracle Business Intelligence Enterprise Edition ‣Visual Analyzer - key new end-user feature ‣Data mashups - allow users to add data to their own subject area ‣Updated look and feel for Answers, Dashboards etc ‣Layered RPD customizations •Simpler configuration + cloning etc •Installer includes option, as per 11g, 
 to install Essbase alongside OBIEE ‣So what does Essbase look like in 12c? OBIEE 12.2.1 - Initial Release of OBIEE 12c
4. let’s go back to 2007
6. info@rittmanmead.com www.rittmanmead.com @rittmanmead 6 •Essbase has been supported a datasource for OBIEE since OBIEE10g ‣BI Server translates Logical SQL queries against RPD into MDX ‣Specific MDX function calls can be passed through (EVALUATE etc) ‣Incremental improvements over years to better support Essbase specifics) •Hyperion Planning & HFM also supported as sources •SmartView ships with OBIEE with
 dual Essbase/OBIEE connectivity Essbase and OBIEE Integration Since OBIEE 10g
7. info@rittmanmead.com www.rittmanmead.com @rittmanmead 7 •Use OBIEE as the Management Reporting platform for both EPM and Essbase •Essbase and Hyperion Planning as datasources ‣HFM via EAL •Replace EPM reporting/dashboard tools ‣WebAnalysis ‣Visual Explorer •Tighter integrations with Financial Reports •Integrate BI into EPM Workspace •Extend SmartView to OBIEE Integration Between The Product Planned from Day 1
8. info@rittmanmead.com www.rittmanmead.com @rittmanmead 8 •Access data in Essbase cube to add as measures / attributes in report •Combine (federate) with other datasets (relational, Hadoop etc) •Include planning data with actuals in BI dashboard •Make use of Essbase calculations and forecasts for BI data Oracle Essbase as a Data Source for OBIEE
9. info@rittmanmead.com www.rittmanmead.com @rittmanmead 9 •OBIEE from version 10g connects to Essbase Server as a data source ‣OBIEE11g min Essbase release 11.1.1.3 ‣OBIEE12c min Essbase release 11.1.2.+ •Optional pass-through of Essbase username/password ‣Leverage Essbase data filters and meta filters for row-level security •Separate OBIEE and Essbase security models ‣SSO possible but one-way only ‣Uses CSS Token generated by BI Server •Minimum integration but max flexibility ‣Standard approach for most use-cases Connecting OBIEE (BI Server) to an Essbase Server Oracle BI Server
 Component Physical
 SQL, MDX BI Presentation Server
 Component Oracle BI Repository
 Logical Business
 Model Read model Logical SQL Results HTTP(S) User requests
 from dashboard, ad-hoc queries etc Results Standalone
 Essbase Server Shared Services
 for User / Roles
10. info@rittmanmead.com www.rittmanmead.com @rittmanmead 10 •As well as being sold separately and as part of Oracle EPM Suite, it can also be licensed as part of a complete Oracle BI Foundation platform ‣Sold as a tightly-integrated OLAP server well-suited to sales reporting-type scenarios •Co-installed with OBIEE and Oracle RTD •Omits features such as Shared Services
 and applications such as Planning, to 
 focus solely on BI-type scenarios •Most of as assumed this was just 
 packaging and cross-selling from Oracle,
 Essbase would still just be Essbase… Essbase Server Packaged with OBIEE - BI Foundation
11. info@rittmanmead.com www.rittmanmead.com @rittmanmead 11 •Essbase in this scenario is all about BI - making OBIEE faster and more fully-featured •Adds an enterprise-class OLAP server to the stack, deeply integrated ‣Goes one-better than Tableau, Qlikview etc - ASO in-mem OLAP vs. simple query cache •All integration work in this context is about making BI run better,
 not replacing Essbase-specific tools or removing EPM Suite •And the primary use case is query acceleration ‣Faster Analysis and Calculations ‣Business Agility ‣Immediate Visibility ‣Adapt to changes ‣Accurate models Essbase As Query Accelerator for OBIEE
12. info@rittmanmead.com www.rittmanmead.com @rittmanmead 12 •Runs the BI layer on a high-performance, multi-core, 1-2TB server •In-memory cache used to accelerate the BI part of the stack •OBIEE Summary Advisor identifies aggregates based on 
 previous queries, creates in TT or DB12c In-Mem •Uses Oracle TimesTen for Exalytics, or Oracle 12c DB In-Mem Option ‣Consistent response times for queries ‣In-memory caching of aggregates ‣40 cores for high concurrency ‣Re-engineered BI and OLAP software
 that assumes 40 cores and 1TB RAM •Works well, and can be great solution for IT-led projects But … Isn’t This What Exalytics + TimesTen Was For? ERP/Apps DW Oracle BI In-Memory DB/Cache
13. info@rittmanmead.com www.rittmanmead.com @rittmanmead 13 •Selectively creates aggregations that would have made historic queries run faster ‣Approach driven by limits on TimesTen memory on An Exalytics server •Does not pre-compute all aggregations, and needs refining and updating over time •Coupled with TimesTen immaturity, in hindsight not a great solution Limitations with the Summary Advisor / TT Approach
14. info@rittmanmead.com www.rittmanmead.com @rittmanmead 14 •Use Essbase ASO engine to provide the aggregation / calculation features for OBIEE •Potential to aggregate and load the entire RPD dataset into ASO - very space-efficient •No need to continuously identify aggregate candidates - aggregate everything •Fast ASO aggregation times compared to TT - quicker to refresh •Sounds great - why not try this? Another Option : Essbase As Query Accelerator
15. Fast forward to 2012…
16. info@rittmanmead.com www.rittmanmead.com @rittmanmead •OBIEE 11.1.1.6.2 BP1 introduced Aggregate Persistence into Essbase ‣OBIEE’s Administration tool had the 
 ability to define aggregates, and then 
 persist them in an RDBMS ‣BI Server then uses these aggregates 
 to speed up dashboard queries •This OBIEE bundle patch enabled 
 storage of these aggregates in an 
 Essbase ASO database, using 
 headless Essbase Studio OBIEE 11.1.1.6.2 BP1 : Aggregate Persistence in Essbase Oracle BI Server
 Component Physical
 SQL, MDX Physical
 MDX BI Presentation Server
 Component Oracle BI Repository
 Logical Business
 Model Read model Logical SQL Results HTTP(S) User requests
 from dashboard, ad-hoc queries etc Pre-computed aggregated data, stored in an Essbase ASO database Detail-level, and dynamically- calculated
 aggregate data
17. info@rittmanmead.com www.rittmanmead.com @rittmanmead 17 1. Developer/Administrator selects slice of business model to aggregate 2. Aggregate Persistence Wizard then creates a logical SQL script 3. Script is processed by BI Server using nqcmd and the BI Server ODBC client 4. BI Server uses Essbase Studio dmaservlet to create ASO outline, and rules file 5. Detail-level data sourced via BI Server logical model through to source databases How Did Aggregate Persistence in OBIEE11g Work? Oracle BI Repository
 Logical Business
 Model Oracle BI Server
 Component nqcmd script
 containing logical
 DDL and DML for
 creating and populating
 aggregate tables Logical SQL
 via BI Server
 ODBC interface Physical
 SQL, MDX Create outline Read model Add aggregates Essbase Studio dmaservlet XML request SQL queries Data load via
 Essbase rules file Essbase Server Developer uses
 BI Administrator
 tool to select 
 aggregates for 
 creation, with an 
 output of a logical 
 SQL script for DDL 
 and DML 1 2 3 4 5
18. info@rittmanmead.com www.rittmanmead.com @rittmanmead •OBIEE 11.1.1.6, and the 11.1.1.6.2 BP1 bundle patch, included Essbase + administration
 tools as part of the install bundle, but these are not by default enabled •Needs to be patched to 11.1.1.6.2 BP1, then installed using options in a reponse file •Configures Essbase as part of the OBIEE domain, with EAS and Studio also available •Narrow use-case : only for Aggregate Persistence, cannot scale-up, repurpose etc Combined Install of OBIEE and Essbase with 11.1.1.6.2 BP1 [DATAMART_AUTOMATION] ESSBASE_STUDIO_URL = "http://localhost:9080/dma/dmaservlet"; ESSBASE_SERVER = "localhost"; DMA_DATABASE = "DMA_DB";
19. the years pass…
21. info@rittmanmead.com www.rittmanmead.com @rittmanmead 21 •From OBIEE 11.1.1.7, Essbase was now an install option 
 within the OBIEE product installer ‣Has to be licensed separately, or as part of BI Foundation •Installs Essbase Server, Essbase Studio, 
 Financial Reporting and other BI-related/complementary 
 tools alongside OBIEE •Management of Essbase Server, Security, start/stop etc
 all from single Enterprise Manager farm OBIEE 11.1.1.7 : Integration of Essbase into OBIEE Tech Stack
22. info@rittmanmead.com www.rittmanmead.com @rittmanmead 22 •Essbase can be stopped, started, restarted from EM via OPMN •Essbase metrics reported on from EM •Integrated security tools via OPSS 
 application roles and policies Essbase within Oracle BI Domain - EM Management
23. info@rittmanmead.com www.rittmanmead.com @rittmanmead 23 •EPM Workspace integration returns with OBIEE 11.1.1.7! •SSO via shared OPSS (FMW) security •Launch BI content from within Workspace •Store FR reports in Catalog (must launch from
 EPM Workspace though, 
 not from OBIEE dashboard) The Return of EPM Workspace Integration
24. info@rittmanmead.com www.rittmanmead.com @rittmanmead 24 •From OBIEE 11.1.1.7, SmartView can now be used with OBIEE for Office integration •Ability to create new reports as well as analyze Catalog content via Excel, Word etc •Replaces BI Office (and original SmartView from OBIEE 10g) SmartView Compatibility across Essbase and OBIEE
25. info@rittmanmead.com www.rittmanmead.com @rittmanmead 25 •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 Further Integration with BI Server Logical SQL
26. info@rittmanmead.com www.rittmanmead.com @rittmanmead 26 •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> Further Integration with BI Server Logical SQL
27. info@rittmanmead.com www.rittmanmead.com @rittmanmead 27 •Also introduced new Logical SQL features for
 Essbase cube spin-off from RPD ‣Command-line only in this release ‣Created an Essbase cube sourced from RPD
 logical SQL query ‣Wired Essbase cube back into RPD ‣But didn’t deal with any of the Essbase
 member naming incompatibilities OBIEE 11.1.1.7 - Introduction of (Beta) Cube Spin-off
28. info@rittmanmead.com www.rittmanmead.com @rittmanmead 28 •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 
 MDX Query Builder for Essbase Sources
29. info@rittmanmead.com www.rittmanmead.com @rittmanmead 29 •Technical Enhancements driven by product direction Essbase Product Drivers 2013-2015 • Calc language enhancements • Facilitate goal seeking, tgt setting • Incremental financial logic • Data in varying time granularities • Financial aggregation logic • Transparent introduction into the semantic model • Query performance • MDX enhancements • BI query acceleration • Rapid scenario modeling • Smaller footprint • Reduce downtime • Self-tuning • Auto-recovery BI Cloud EPM
30. info@rittmanmead.com www.rittmanmead.com @rittmanmead 30 •Essbase12c release out soon, will be delivered initially as part of Essbase Cloud Service ‣Part of Oracle PaaS, alongside DBaaS, BICS, PBCS etc ‣New Essbase database web based administration tool ‣Quick database creation by uploading an Excel spreadsheet ‣Scenario management - Lightweight workflow •Sandboxing - Create a personal slice of the database •New Java based architecture optimised for Oracle Cloud ‣Designed to support higher concurrency ‣Elimination of SEC file ‣Managed within Weblogic console ‣Part of either EPM or BI Domain Essbase 12c - Re-Engineered for the Cloud CDS Scenario Management Thin Client Editor Catalog Java Agent Dynamic Filters DBX Drill Through Scripting: R, Groovy, JACL Data Source Grid UI APS Java API, REST and Web Services Unified Engine (ASO/BSO) Background Write In-Place Write …
31. info@rittmanmead.com www.rittmanmead.com @rittmanmead 31 •Combines the flexibility of BSO models with ASO performance •BSO traditionally used for r/w planning-type applications, typically now in-memory •ASO used for rack-and-stack BI-type applications, level 0-writes only •Hybrid takes ASO engine and layers ASO aggs over BSO level-0 blocks •100% backward compatible with existing BSO databases •Translates BSO calc scripts to MDX functions •Can run in-memory •Part of a move towards a single Essbase engine (over time…) BSO/ASO Hybrid Aggregation Mode
32. info@rittmanmead.com www.rittmanmead.com @rittmanmead •A selected member which will collect the data from rejected records of the dimension •Ensures that totals presented by Essbase after data load match source system totals ‣Previously, Essbase totals would reflect just the members that loaded OK ‣Renegate member feature ensures all values are loaded, if only into new “others” bucket New In Essbase 12c - Renegade Members Prod (G3,L0) P1 P2 Geog (G3,L0) G1 G2 Prod Geog Measure P1 G1 1 P2 G1 3 P1 G2 5 P2 G3 7 P2 G4 2 P3 G4 1 P3 G2 2 Query RDBMS Essbase Essbase with RM Select Prod, SUM(Data)
 From Fact, ProdDim
 Where Fact.Prod = ProdDim.Prod
 Group by Prod P1, 6
 P2, 12 P1, 6
 P2, 3 P1, 6
 P2, 12 Select Geog, SUM(Data)
 From Fact, GeogDim
 Where Fact.Geog = GeogDim.Geog
 Group by Geog G1, 4
 G2, 7 G1, 4
 G2, 5 G1, 4
 G2, 7 Geog/Prod P1 P2 _ProdRenegade G1 1 3 #Missing G2 5 #Missing 2 _GeogRenegade #Missing 9 1
33. info@rittmanmead.com www.rittmanmead.com @rittmanmead 33 •Remove most of the naming restrictions for Essbase ‣Object name length ‣Reserved words ‣Restrictions on characters •Initial support will be restricted to JAPI and data 
 loaded from SQL sources •Significant change in behavior, currently aimed at 
 BI Query Acceleration use-cases New in Essbase 12c - Relaxed Naming Restrictions
34. info@rittmanmead.com www.rittmanmead.com @rittmanmead 34 •Early access to Essbase 12c, running co-located alongside OBIEE12c in FMW12c ‣Essbase 12c Java agent running in WLS Managed Server •Aimed solely at BI query acceleration - for other use-cases use standalone Essbase 11g •Hybrid Agg Mode for faster dynamic calculations •Improvements to BSO performance •Ease of integration with 3rd party tools 
 (Cube Deployment Services) •Up coming 12c enhancements ‣BI oriented outlines ‣BI specific features Further Integration of Essbase into OBIEE12c Stack 11 Database: RCU Schemas Admin Server Managed Server Node Manager BI System Components BI Server BI Scheduler BI Presentation Server BI Java Host Cluster Controller Oracle Platform Security Services End Point Registry WLST DMS ODL Identity Store Essbase Studio Metadata Configuration Service Instance
35. info@rittmanmead.com www.rittmanmead.com @rittmanmead 35 •Component that is deployed with Essbase 12c •Essbase BI acceleration wizard uses its API to create Essbase cubes ‣BI user creates Essbase cube based on BI business model metadata ‣CDS provides integration between the BI repository and Essbase Server -Creates the Essbase cube definition in XML metadata format. -Validates the cube definition -Generates a rules file for each dimension -Creates the cube outline -Loads metadata and data -Wires back metadata mapping information to the BI repository Cube Deployment Services
36. info@rittmanmead.com www.rittmanmead.com @rittmanmead 36 •UI for creating spin-off Essbase12c cubes from RPD business model •Accessed via http://machine_name:port/cds/view ‣Same port number as /analytics •Define cube, select dimensions and levels •Some limited scope for customising cube •Load, rebuild, drop cubes •Cubes built using wizard can only be used as
 aggregate LTS sources in RPD, not general use BI Acceleration Wizard
37. info@rittmanmead.com www.rittmanmead.com @rittmanmead 37 •Either click on Launch Essbase Acceleration Wizard link •Or select Actions > New from Deployments Browser BI Accelerator Wizard Step 1 : Launch Screen
38. info@rittmanmead.com www.rittmanmead.com @rittmanmead 38 •Select from BI Business Models that Essbase Server is co-located with •Either type in a name for the new Essbase application, or select existing one to reload BI Accelerator Wizard Step 2 : Select BM and Target App
39. info@rittmanmead.com www.rittmanmead.com @rittmanmead 39 •Select measures to include in Essbase cube ‣SUM, COUNT, and COUNT DISTINCT 
 aggregation rules are supported ‣Derived measures are not supported,
 i.e. based on logical formula in RPD BI Accelerator Wizard Step 3 : Select Measures
40. info@rittmanmead.com www.rittmanmead.com @rittmanmead 40 •Select dimensions to include in cube ‣Ragged hierarchies not supported BI Accelerator Wizard Step 4 : Select Dimensions
41. info@rittmanmead.com www.rittmanmead.com @rittmanmead 41 •Select levels to include in aggregation ‣Include all, or just subset for skip-level aggregation ‣Just include top-most levels 
 to accelerate totals BI Accelerator Wizard Step 5 : Select Levels
42. info@rittmanmead.com www.rittmanmead.com @rittmanmead 42 •Review choices made in cube design •Some limited opportunity to customise - add more logical columns •See underlying storage settings •Deploy cube, deploy in background BI Accelerator Wizard Step 6 : Review and Deploy BI Accelerator Wizard Step 4 : Select Dimensions
43. info@rittmanmead.com www.rittmanmead.com @rittmanmead 43 •As with all OBIEE aggregate persistence utilities, aggregates then wired back into RPD •BI Server will then use Essbase source for query acceleration as appropriate •Reload or redeploy from BI Acceleration Wizard BI Accelerator Wizard Step 7 : Review Deployment 1 2 3
44. info@rittmanmead.com www.rittmanmead.com @rittmanmead 44 •MaxL can still be used to create and populate Essbase 12c in this scenario ‣MaxL Shell included with OBIEE12c install •Supported approach, will work •But BI Acceleration Wizard is how 
 you’re supposed to build cubes for now But … Can We Still Build Essbase Cubes Manually?
45. info@rittmanmead.com www.rittmanmead.com @rittmanmead 45 •A separate install of Essbase Administration Services can be connected to this Essbase 12c •Allows you (in an unsupported, workaround way) to use EAS with co-located Essbase 12c But … Can We Still Build Essbase Cubes Manually?
46. info@rittmanmead.com www.rittmanmead.com @rittmanmead 46 •Yes - and this is the recommended approach for showing Essbase data in OBIEE12c ‣Query accelerator is “behind the scenes” and invisible to end users •Minimum supported version of Essbase is 11.1.2+ … And Can We Still Access Standalone Essbase?
47. info@rittmanmead.com www.rittmanmead.com @rittmanmead 47 •Latest version available as an on-premise, standalone install ‣As shipping with Exalytics, EPM Suite etc •Recent enhancements to core Essbase server include ‣In-Place write reduces database fragmentation ‣Continue to enrich the calc language ‣Background write deliver high performance for NFS with Exalytics ‣Anonymous data export ‣Controlled shutdown, eliminates most DB corruption issues ‣Smartview Calc Script launcher with context-aware substitution variables ‣Continues to be invested in and available as standalone OBIEE datasource ‣Minimum version supported by OBIEE12c is 11.1.2.x Core Essbase Server 11.1.2.4 Recent New Features
48. info@rittmanmead.com www.rittmanmead.com @rittmanmead 48 •Does not handle more complex, and larger, RPDs - use with caution •Limited ability to customise storage settings for Essbase cube •Not a solution for Essbase reporting or custom cubes - it’s all about query acceleration •Treat as experimental and early access - it’s “supported” by •In general, we recommend standalone Essbase 11g (11.1.2.+) for general Essbase reporting •But … a taste of the future Limitations in Initial Release
49. info@rittmanmead.com www.rittmanmead.com @rittmanmead OBIEE 12c and Essbase: What's New for Integration, and Reporting Against EPM Sources Mark Rittman, CTO, Rittman Mead ODTUG KScope’16, Chicago, June 2016