OBIEE and Essbase Why it makes Sense

Glenn Schwartzberg [email protected] @gschwartzberg1

interRel: Oracle's BI/EPM Partner of the Year       

3-Time Winner of Oracle's BI & EPM Partner of the Year Four Oracle ACE Directors Three Oracle ACE Associate Authors of 10+ of the Best Selling books on Hyperion & Essbase Oracle Platinum Partner One of the 100 fastest growing tech companies in the USA (CRN Magazine) One of the fastest growing companies in USA (Inc. Magazine 2008-present)

Infra structure

Staffing

Consulting

Strategy

Press

Training

Support

Founded in 1997, we are the longest-standing, Oracle EPM/BI-dedicated partner in the world

Reading Material Best Selling Hyperion Books Available:  Planning 11.1.2.2/11.1.2.3: Creating Applications  Planning 11.1.2.2/11.1.2.3: Advanced Planning  Planning 11.1.2.2/11.1.2.3: An End User’s Guide  Smart View 11.1.2.2: End User Guide  Essbase Studio 11.1.2.2  Essbase 11: Admin Guide (Updated for 11.1.2.4)

Play it Forward  Videos to expand on what you've learned here

 Product introductions  What’s New in 11.1.2.4  Expert-level videos for BI/EPM gurus  Technical Reference in video form #PlayItForward

 To experience the education revolution first-hand, join our community at epm.bi/videos

Get a $100 Discount When You Register Using This Code:

IRC

Quick Poll  Who is an Essbase user?  Who uses OBIEE?

Disclaimer These slides represent the work and opinions of the presenter and do not constitute official positions of Oracle or any other organization. This material has not been peer reviewed and is presented here with the permission of the presenter. This material should not be reproduced without the written permission of interRel Consulting.

Essbase

Industry leading OLAP Analysis tool

• Multidimensional cubes • No tables and columns • Data is stored for fast reporting and analysis; • aggregates pre-calculated • Multiple storage types • BSO • ASO • Hybrid • Database definition is defined by the dimensions of data • Dimensions

• Common grouping or • hierarchy of master data

•JAN •JAN •JAN •FEB •FEB

•LA •SF •PHX •LA •SF

•SALES •ACTUAL•500 •SALES •ACTUAL•250 •MARGIN •BUDGET•180 •SALES •BUDGET•300 •MARGIN •ACTUAL•400

•Actuals •Budget •WEST•Sales •Margin•Sales •Margin •L.A. •S.F. •PHX •DEN

Sample Financial Outline

OBIEE

Scalable, efficient query, analysis and reporting server

• Provides integration of many sources • Intelligent caching services • Calculation and integration engine • Intelligent request generation and optimized data access services • Three layers • Physical • Business Model /Mapping • Presentation

Oracle Business Intelligence Foundation Suite 

Source: Oracle

OBIEE Web Client / Analyses       

Front end to the OBIEE suite Ad-hoc reporting interface, pivot, charts Point and click Save organize and share Sources data from the OBI Server Used in Dashboards Also called Answers

Dashboards  Fully interactive graphical dashboards  Real time data across enterprise sources  Point and click to create and use  Guided analytics – intelligent links to aid users in their investigations

Mobile Support  iPhone / iPad  Oracle BI and Oracle Business Approvals  Native apps available via iTunes  Mobile App Designer (11.1.1.7.1.201)  Windows Mobile, WebOS & Android  Standard browser support via HTML and Flash  Alerts via txt message

Why Use OBIEE and Essbase Together  Essbase provides Fast retrieval of data at different levels and drill in capabilities  OBIEE offers a “Federated” data warehouse to bring sources together as if they are one  Can combine data from different sources

 OBIEE provides robust reporting, dashboard and Mobile capabilities

Integrating OBIEE into Essbase

Improving Your Integrations 1. Accounts – to Flatten or not to Flatten 2. Dimension Types 3. Passing Security 4. UDAs 5. Variables 6. ASO Multiple Hierarchies 7. Federated reports(Evaluate is our friend)

Default “Measures” Dimension  By default, measures are imported as measure hierarchies  Cube contains a single measure column that represents all the measures  You will have to bring this measure into every query in order to see data  The same formatting is applied to the measure which can cause problems

 One way to allow us to have the Accounts dim as a hierarchy

Alternative1 – Flattened Measures  Click the cube object and select Convert measure dimension to flat measures

 Why? If you have a smaller, static “flat” list of accounts in Essbase (e.g. Unit, Price, Sales) – this will help in reporting, dashboards and performance

 Not good for large dimensions

Alternative2 – Select Measures Individually  Delete the OBIEE created measures dimension  Click the cube object and New Object -> Physical Cube Column

   

Give the column a name Type is always double External Name MUST be the same as what is in Essbase

Alternative 3 – Make Another Dimension Measures  There is nothing that says the Essbase accounts dimension has to be the measures dimension in OBIEE  Choose another dimension that makes more sense like scenario and flatten it

 Typically small  You are going to select based on it anyway

 Can’t decide what is best or you need different methods for different analysis, Clone the data source

Dimension Types

Hierarchy Types  Types  Unbalanced – Dimensions branches have varying number of levels (default setting in OBIEE)  Balanced – all dimensions branches have the same number of levels (an oddity in the Essbase world)  Ragged – Dimensions branches have varying depths with some branches having children and grandchildren while others may have no children or fewer levels. (normal in Essbase)  Value – “parent/child” list  Does it Matter? Performance and Ease of Use 23

Balanced Hierarchies  Most optimal for integration with OBIEE especially if Federated reports are desired with relational (other ways to do it)  Most unnatural for typical Essbase users  Balanced Hierarchies most often require dummy levels to be built in  Can add significantly to drilling

24

Should You Use the Value Hierarchy Type? To “Value”

To Not “Value”

Generations change frequently

Generations do not change frequently

Member Browser control meets requirements for dashboard control

Need to use radio, check or other dashboard controls for member selection

Easier to find a member – Financial Cubes with large Account dimension

Need to Federate Data

Set the Hierarchy Type Right-click the hierarchy and select Properties for the hierarchy to set the hierarchy type.

Value Hierarchy

For Value Hierarchy to Refresh …  You should turn off the “Cacheable” setting under the cube properties in the Physical layer or changes won’t propagate

Keeping it Secure

OBIEE: Security  MSAD used to authenticate users – left responsibility to set up initial security with IT  Create groups and assign users to groups  Group assignment remains responsibility of IT

 OBIEE – Assign roles to MSAD groups  OBIEE – Assign catalog permissions to MSAD groups

30

Connection Pool Setup  OBIEE with Essbase – use Essbase security (filters) then set the connection pool to :USER/:PASSWORD  Allows OBIEE to pass though the signed in user and password from Shared Services  Allows Essbase filters to be used  User still needs to be provisioned in both OBIEE and Shared Services  Don’t use for Substitution Variable connection  Caching needs to be turned off or this security does not work

31

Substitution Variables  Depending on the scope of the Essbase variable, the naming convention for the BI Server variable is as described below.  Server instance scope

 :  Application scope

 ::  Cube scope

 :::  Add the application:database name for substitution variables specified at those levels.

Substitution Variables  Essbase substitution variables are defined and maintained in EAS and then imported into OBIEE.  Rename variables with generic names to make migration between environments easier and it makes it easier for analysis developers, too.  Global variables can be managed using one connection pool and database in the Physical Layer of the RPD.  Need to have a connection with an Admin Connection Pool (no :USER) 33

Substitution Variables  Uses the Variable Manager

 The refresh interval should be set appropriately to reflect anticipated update cycles for Essbase variables.

ASO and BSO in RPD BSO – Alternate Rollups show as generations

ASO – alternate rollups (multiple hierarchies enabled) Generations are within the separate hierarchies.

Convert Multiple Hierarchies to Single Hierarchy  You can leave the hierarchies separate  You can’t select members from both hierarchies in the same report  In the Presentation layer you can create a logical table and merge the hierarchies together

Convert Multiple Hierarchies to Single Hierarchy  Select Convert to single hierarchy view  To return to the multi-hierarchy view, right-click the dimension object again and select Convert to multi-hierarchy view

Renaming Generations  Can be done in Essbase by naming Generations  Most Essbase developers don’t do this  More often done in the Business Model Mapping layer or Presentation layer  Makes navigation easier for users  Really only viable where hierarchies are balanced  Or  To make value hierarchies easier to understand

Logical Columns - Calculations  Adding calculations with logical columns makes creating analyses easier.  Time functions like AGO are among the standard time functions  Use Evaluate function to make custom member lists Evaluate('Descendants([Products],levels([Product],0)) /*%1*/,"ASOSamp"."All Merchandise"."Gen3,"All Merchandise")  Where possible have the calculations in Essbase

39

Federated Reporting

Options  Add Balanced Hierarchies into your cube  ASO – multiple hierarchies where hierarchies are balanced within themselves or across them  BSO – Balanced across entire hierarchy

 Write complex formulas with hardcoding and IF statements  Clone your Essbase cube  Have an analysis cube with asymmetrical hierarchy  Have a reporting cube with symmetrical hierarchies  Export level 0 data from one and import to the other

 Use Evaluate and driver prompts

Evaluate and more  Level 0 drill through can be accomplished using the Go URL in the Action Framework passing the level 0 members  For more advanced drill through, using a combination of evaluate, Prompt variables, Dashboards, Dashboard prompts and Action links can provide the most versatile drill through.  Information on this method is too long to detail in this presentation.(It is a whole presentation in itself.  Information on this method is available in the book “Developing Essbase Applications – Hybrid Techniques” in a chapter by Mike Nader (also available as an Oracle Open World 2015 presentation)

42

Other Optimizations

Faster Performance  Enable caching  But…. Careful with changes to underlying Essbase outline structure as it can cause the analyses and dashboards to fail.  Don’t enable if you are using Value dimensions

 Optimize the cube!  Optimize the cube! Yes, it’s that important and has a huge impact on overall performance.

UDA as a Physical Column  You can choose whether to import UDAs in the Import Metadata Wizard. If you choose to import UDAs, then by default, each UDA is modeled as a dimension property in the Physical layer of the repository  To model all UDAs in a cube as separate physical columns, right-click the cube table and select Create columns for UDA.  To model all UDAs in a dimension as separate physical columns, right-click the dimension object and select Create columns for UDA, then select All UDAs.  UDAs will return a 0 or 1 value so you need to use it as a filter.

Skip Gen 1 Levels  Skip Gen 1 levels in Essbase drag and drop actions  When selected, excludes Gen 1 levels when you drag and drop Essbase cubes or dimensions from the Physical layer to the Business Model and Mapping layer  Often, Gen 1 levels are not needed for analysis, so they can be excluded from the business model.

Faster Performance for Essbase  For each hierarchy, right click on the hierarchy and select Properties.  Check the box for “Use Unqualified member name for better performance”

Going Mobile

Mobile Access  At many companies mobile access was one of the top requirements.  Performance is key to successful mobile delivery  There are options for going mobile  Mobile App  Mobile App Designer (MAD)  Mobile App (HD) – uses same dashboards as PC users – only works with iPhones and iPads.  MAD – create apps for use on any mobile device 49

Mobile App      

The Mobile app supports both i-devices and android. Free to download, but not free to use. Included with BIFS Answers and dashboards displayable Must have secure connection or your OBIEE instance has to be open(not recommended)

50

Mobile App Designer  Allows business users to make dashboards as easily as making a new PowerPoint presentation  4 easy steps 1. Select a device type  Phone  Tablet  Great to display on desktop as well 2. Select a data source  OBIEE RPD  BI Publisher  Microsoft Excel 3. Drag-and-drop items 4. Preview on a PC or via mobile using a QR code

MAD

Integrating OBIEE into Essbase

Integrating OBIEE into Essbase  Essbase 12c allows for “renegade” members so data is not lost  Restrictions on member names has been removed in Essbase 12c  We can persist data using the OBIEE persistence aggregation Cube Deployment Services (CDS) to spin off Essbase ASO cubes BUT:  These cubes are OBIEE only (not exposed for users to query directly)  Creating a cube makes it usable across the enterprise and to different tools allowing reporting consistency

 The following information on how to use CDS is courtesy of Mark Rittman  RittmanMead Consulting

BI Acceleration Wizard

Wizard – Launch Screen

Wizard – Select BM and Target

Wizard – Select Measures

Wizard – Select Dimensions

Wizard – Select Levels

Wizard – Review and Deploy

Wizard – Review and Deploy

Comments  This is pretty new (first introduced in 11.1.1.6 but changed with each version).  Seems to work well for simple small RPDs but can have problems with more complex implementations  Test to see if it works for your case  Treat this as experimental  Expect more improvements to come in the future but be careful with it right now.

Summary

Considerations with Essbase Sources  Workspace and OBIEE Dashboard integration came back with release 11.1.1.7  Can’t federate value based hierarchies (join relational and Essbase data using hierarchy tagged as Value)  Dynamic dashboards using “Children of” functionality, time functionality (limited compared to FR)  Limitations with controls on value based hierarchies  Push calculations back to Essbase  Performance – Less than efficient BI Server SQL processing against MDX result set  Will always be slower than Smart View  Complex and fragmented OBIEE SQL when using hierarchical columns  Expensive OBIEE SQL on selection steps  Better with each new release

Lessons Learned from Clients  Legacy reports do not make good dashboards  Expect that the initial dashboards will be changed as users become familiar with the tool  Performance varies widely and testing is needed for multiple situations: Wireless vs wired, PC vs iPads and iPhones  If you have a web team, take advantage of their expertise before creating dashboards – talk to them about color, layout, etc.  Allow plenty of time for data validation

 Executive backing is key to successful rollout! 66

Lessons Learned

   

Essbase is simpler than Relational to import Do reporting as an iterative process Phased approach Understand that Essbase is different than relational  Simpler to set up  Performance  Design can be different  Know the limitations and importance of Value dimensions

Questions? Glenn Schwartzberg [email protected] @gschwartzberg1