CON9328_Rubin-CON9328 - Using Oracle BI with Oracle Essbase

hurriedtinkleAI and Robotics

Nov 15, 2013 (3 years and 7 months ago)

229 views

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

1

Using Oracle Business
Intelligence with Oracle
Essbase

Gabby Rubin, Oracle

Mitch Campbell, Oracle

Ramke

Ramakrishnan
,
Grant Thornton LLP

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

3

3

Copyright © 2011, Oracle and/or its affiliates. All rights
reserved.

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.

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

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

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

5

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

Oracle Business Intelligence Foundation Suite

Complete Enterprise Business Intelligence

COMMON ENTERPRISE INFORMATION MODEL

FUTURE

PRESENT

PAST

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

6

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


I
mprove business performance by linking
insight to action


Consistent enterprise metrics for improved
productivity


Single analytic view across all data sources



Oracle Business Intelligence Foundation Suite

Single Enterprise
-
Scale Business Analytics Platform

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

7

Oracle Business Intelligence Foundation Suite


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

OBIEE


EPM


Microsoft Office



Oracle
Essbase

Server




Enterprise scale



Optimized multidimensional storage



Over 350 analytic functions



Financial, time and calendar intelligence



Multi user write
-
back



Trickle feed



Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

8



Oracle Essbase Roadmap

Subject to Change


Calc

language 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 Directions

Post 12 month planning cycle

For information purposes only, not a commitment to the
development, release, and timing of any features or functionality.

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

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

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

10

OBI / Essbase Integrated Infrastructure

Logging

Enterprise Manager

Tools

Install

Security

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

11

Enable customers to use BI against
Essbase

without
complex installs and post
-
install configuration.

Essbase and Related Tools Installed with BI


Essbase

installed with BI
using the same security


Financial Reporting, EAS,
Essbase Studio and
Calculation Manager also
installed with BI


Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

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

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

13

BI Server and ESSBASE Interaction


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>

Foundational technology for advance users and PS
future

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

14

BI UI Enhancement for Essbase


Option
to allow how
hierarchies are displayed


New property


Default
is Parent
before/above


Applies to all
axis


Impacts all applicable views


Parent Last
Navigation

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

15

BI
UI Enhancement
for
Essbase


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


Null Suppression
Options

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

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


Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

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


Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

18

BI Publisher Enhancements


GUI
for building MDX queries against
Essbase


E
asily
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

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

19

BI
Publisher With Essbase
MDX Demo

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

20

Future Directions

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

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

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

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

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

23

Analytics By Request

400
450
500
550
600
650
700
750
800
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Sales Forecast

Plan
Forecast
Actual
Create projection

Add Trend lines

Remove


Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

24

Analytics By Request

400
450
500
550
600
650
700
750
800
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Sales Forecast

Plan
Forecast
Actual
Projected
Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

25

Using Essbase with BI
Mobile


Mitch Campbell

Oracle

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

26

Use Case and Lessons
Learned


Ramke

Ramakrishnan

Grant
Thornton
LLP

© Grant Thornton LLP. All rights reserved.

ORACLE BUSINESS
INTELLIGENCE WITH
ORACLE ESSBASE

CASE IN POINT

Ramke Ramakrishnan

Principal, Business Advisory Services, Technology Solutions

27


CUSTOMER CASE STUDY


OBIEE ESSBASE
INTERACTION


LESSONS LEARNED & BEST PRACTICES

© Grant Thornton LLP. All rights reserved.

28

ABOUT GRANT THORNTON

OUR GLOBAL SERVICES

Audit Services

Advisory Services

Tax Services

Financial statement audits


International companies


Privately held businesses


Public companies


Benefit plan audits


Compliance for employee benefit
plans of all sizes


Public finance


Arbitrage rebate compliance


Bond yield monitoring


Loan defeasance


Verification


Corporate Social Responsibility


Sustainability annual reports


Greenhouse gas emissions


Conflict minerals reports


Transaction Advisory

• Corporate

finance

• Buy
-
side / sell side due diligence

• IT due diligence

• Merger integration

• Tax structuring and advisory

• Closing

working capital analysis

• Transaction

cost studies

• Purchase

price allocation


Accounting change implementation


Buy/Sell side due diligence


• Financial

and tax


• Human resources


• IT


• Operations


• FCPA


Business

Consulting

• Business

intelligence and data analytics

• Enterprise

program management

• Finance

operation support

• Finance function optimization

• IT

management

• Technology Solutions

• Health care advisory


Technology

Solutions

Governance, Risk and Compliance

• Internal audit

• SOX compliance

• Special attestation reports

• Enterprise

risk management

• Quality

assessment review


Cybersecurity


• Contract compliance


Global

Public Sector


• Organizational

improvement


• Information

technology


• Financial

management


• Infrastructure

advisory


Forensic & valuation


• Anti
-
corruption


• Fraud &

investigation


• Forensic technology




Litigation & dispute



• Valuation




Restructuring and Turnaround

• Interim and crisis management

• Distressed mergers and acquisitions



Cash management & forecasting

• Financial forecast

review

• Strategic alternatives

assessment

• Performance

improvement

• Organizational

& strategic assessment

• Debt

negotiation & refinancing

• Distressed

M&A

• Bankruptcy

Compliance


Federal, multistate and foreign
tax returns




Loan staff and outsourcing
services




Tax accrual preparation under
SFAS 109

Federal tax


Accounting methods




Cost segregation




Interest and penalty services




LIFO and other inventory
services



Personal goodwill


Research tax credits




Section 199


Domestic
Production


Activities Deduction




Therapeutic discovery tax credit




Transaction planning

State and local tax


Controversy




Credits and incentives


Income and franchise tax




Real and personal property
tax






Reorganizations, mergers and
acquisitions



Sales, use and other transactional
tax




Unclaimed property (escheat)

International tax




Global tax compliance



International tax advisory




Regional specialty consulting




Transfer pricing


Compensation and benefits




Benefits




Compensation



Employment tax




Human resource services


Expatriate tax services




Tax accounting and risk advisory


Accounting for income taxes


Tax function optimization


© Grant Thornton LLP. All rights reserved.

ABOUT GRANT THORNTON

TECHNOLOGY SOLUTIONS

29

© Grant Thornton LLP. All rights reserved.

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




30

CREATING BUSINESS HARMONY

© Grant Thornton LLP. All rights reserved.

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.

© Grant Thornton LLP. All rights reserved.

32


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

CASE STUDY

ABOUT IMS HEALTH

BUSINESS DRIVERS


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

© Grant Thornton LLP. All rights reserved.

33

Reporting

What
Happened?

Analysis

Why did it
happen?

Monitoring

What is
happening
Now?

Prediction

What might
happen?

CASE STUDY

BI MATURITY ASSESSMENT

Business Value

Complexity / Competitive Advantage

Business
Intelligence
Technologies

Predictive
Analytics

Dashboards,
Scorecards

OLAP and
Visualization Tools

Query, Reporting
and Search Tools

BI Maturity with BI Technologies help our clients understand current state and improvement opportunities

LOW

HIGH


What Business Value does Business Intelligence
b
rings?


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?

© Grant Thornton LLP. All rights reserved.


Improved
Visibility for
Performance
Management


Standardized reporting environment


One
-
stop
-
shop for analytical reporting


Ability for decision
-
makers at all levels to more easily access, understand, analyze,
collaborate, and act on information


Transition into an enterprise
-
wide scalable BI foundation by leveraging common BI
tools

Establish Data
Management Strategy


Ability to react more quickly via enhanced visibility


Yardstick for measuring performance and results


Ability to access and analyze information anytime, anywhere


Centralized location to monitor KPIs and reporting

Drive Automation
through KPIs


Common business layer to increase the re
-
use of established software assets


One system to satisfy all global reporting requirements


The right information at the right time


New insights into customers & markets

Drive Internal &
External
Responsiveness


‘User
-
centric’ IT environment that allows business users to get the information through a
self
-
service interface


Easy
-
to
-
use, robust tools with slice & dice, dimensional analysis, data drill
-
down, data
integration

Improve Productivity
Through Self
-
Service

34

CASE STUDY

KEY BUSINESS DRIVERS

© Grant Thornton LLP. All rights reserved.

35


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)
.

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

© Grant Thornton LLP. All rights reserved.

APPLICATION TOPOLOGY

CASE STUDY

Global Data Warehouse

Financial Planning & Budgeting

Financial Management

Source
Systems

Data Quality / Business Definitions

SAP Financials

Oracle

Data Integrator

Oracle EPM Suite

Oracle BI Foundation Suite

Essbase Studio

ASO

BSO

Financial Data

Quality
Management

Informatica

Enterprise Data
Management

Business
Analytics

36

© Grant Thornton LLP. All rights reserved.

37

CASE STUDY

USER EXPERIENCE

User Interface &
Interactions


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

Scorecard & Strategy
Management


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

Actionable
Intelligence & Alerts


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

BI Publisher


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

© Grant Thornton LLP. All rights reserved.


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

CASE STUDY

ANALYTICS ENGINE


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.

ORACLE ESSBASE

38

© Grant Thornton LLP. All rights reserved.

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:

o
Establish
developmental best practices on administration and maintenance of
OBIEE to support
a broader
reporting and analytics platform for the future state

o
Integrate
reporting from
SQL Server DW, Essbase and OBIEE.

o
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

CASE STUDY

INTEGRATED BUSINESS ANALYTICS

Foundation Analytics Methodology™

Solution Delivered in Sprints

“My Dashboards” delivered through SFDC portal

39

© Grant Thornton LLP. All rights reserved.

40


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




ORACLE BI FOUNDATION SUITE

BENEFITS

© Grant Thornton LLP. All rights reserved.

41


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

OBIEE AND ESSBASE INTERACTION

HIGHLIGHTS

© Grant Thornton LLP. All rights reserved.

42


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

OBIEE AND ESSBASE INTERACTION

STAR SCHEMA MODEL

Physical

Logical

Presentation

© Grant Thornton LLP. All rights reserved.

43


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

OBIEE AND ESSBASE INTERACTION

MULTIPLE MEASURES

© Grant Thornton LLP. All rights reserved.

44


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


OBIEE AND ESSBASE INTERACTION

HIERARCHY ELEMENTS

© Grant Thornton LLP. All rights reserved.

45


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


OBIEE AND ESSBASE INTERACTION

DIMENSION TYPES

All
Dimensions and Hierarchies
are not always the same when imported into OBIEE


© Grant Thornton LLP. All rights reserved.

46


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


OBIEE AND ESSBASE INTERACTION

ALT HIERARCHIES

© Grant Thornton LLP. All rights reserved.

47


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



OBIEE AND ESSBASE INTERACTION

MULTIPLE DIMENSIONS

© Grant Thornton LLP. All rights reserved.

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])

© Grant Thornton LLP. All rights reserved.

49


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:

1)
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

2)
Source Blending where different measures and/or same measures sourced
from Essbase and other data sources

3)
Navigate to details through the passing of parameter values from one report
to another report

4)
Relational lookup functions to query some additional attributes for reporting


OBIEE AND ESSBASE INTERACTION

FEDERATION

© Grant Thornton LLP. All rights reserved.

50

1)
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


OBIEE AND ESSBASE INTERACTION

FEDERATION

© Grant Thornton LLP. All rights reserved.

51

OBIEE AND ESSBASE INTERACTION

FEDERATION

2)
Provides a blended analysis by combining data from multiple data sources and present in the same
report. The business logical model is setup such that based on the request, BI Server will determine
the optimal and/or required data sources to execute the query and return the results

© Grant Thornton LLP. All rights reserved.

52

3)
Navigation allows to propagate context of a report into another, different report, using
prompted filters. The initial report can be sourced from a system (
essbase
)

and
the target
report can be sourced from a a different system (relational)
. The setup
leverages same
object names in each Subject Area for prompted columns. The navigation is setup
directly in the column interaction tab.

OBIEE AND ESSBASE INTERACTION

FEDERATION

© Grant Thornton LLP. All rights reserved.

53

4)
OBIEE repository can be setup to run Calculations
on a report
sourced
from Essbase,
but, after Essbase aggregation a lookup to a relational table is performed to retrieve
values of member attribute names.

OBIEE AND ESSBASE INTERACTION

FEDERATION

© Grant Thornton LLP. All rights reserved.

54


Selection Steps are the best way to take advantage of
the reporting from Essbase Outline.


This feature provides ability to select individual
members based on the position in the hierarchy such
as selecting children, ancestors and keep/remove
members and can be leveraged both from ad
-
hoc
analysis as well as from Dashboard view


Additional considerations when using selection steps
are


Avoid using filters at the generation level as in most cases
reimport or changes to the Essbase outline will affect the
generation level


Ensure that the Time Series Aggregation is setup up correctly
in the logical layer to avoid issues

OBIEE AND ESSBASE INTERACTION

SELECTION STEPS

© Grant Thornton LLP. All rights reserved.

55


Essbase Studio creates SQL rule file to load data and metadata into Essbase


Streaming vs.
Non
-
Streaming Mode


In
non
-
streaming mode, Essbase Studio uses JDBC for browsing the tables and building the
metadata, and it uses the ODBC driver on the Essbase Server for dimension building and
data load.
This mode is much faster than the other one.


In
streaming mode only the Studio JDBC driver is used to get the data into Essbase
.

OBIEE AND ESSBASE INTERACTION

ESSBASE STUDIO


Export and Import of model/cube schema as an XML
file between environments makes easier for migration.


Creating tailored tables/views for each dimension in the
relational source gives more flexibility and easier to
develop models/cube schemas in Studio.


Drill Through Capability


MaxL Deploy command for automation process


Oracle BI Repository can be a source for Essbase Studio


© Grant Thornton LLP. All rights reserved.


To Maximize the power of Essbase functions in OBIEE, the native MDX functions such
as
EVALUATE
,
EVALUATE_AGGR

and
EVALUATE_PREDICATE

functions
can
be invoked. These
functions provide a way to pass through a request to the underlying
Essbase data source for
an analysis to use a particular
function.


OBIEE AND ESSBASE INTERACTION


INVOKE MDX FUNCTIONS

EVALUATE
(‘RANK(%1.dimension.currentmember,%2.members)’ AS
INTEGER,CHANNELS.”Gen4,CHANNELS”,CHANNELS.”Gen4,CHANNELS”
)



EVALUATE_AGGR
((%1,Customers.dimension.currentmember).value/Count(
Customers.dimension.currentmember.chi
ldren
)’,


EVALUATE_PREDICATE
('length(%1)>6',"A
-

Sample Sales"."Products"."P4 Brand")

56

© Grant Thornton LLP. All rights reserved.

57


OBIEE provides the ability to update the Essbase data directly using an action link and
scripts


OBIEE AND ESSBASE INTERACTION

ESSBASE WRITEBACK

UPDATE "F
-

Essbase Interaction"."Basic" SET "Total
Expenses" = @{p1} WHERE "F
-

Essbase
Interaction"."Product"."Category" = '@{p2}' AND "F
-

Essbase Interaction"."Scenario"."Scenarios" = '@{p3}'
AND "F
-

Essbase Interaction"."Year"."Quarter" =
'@{p4
}’

© Grant Thornton LLP. All rights reserved.

58


Essbase Calculations scripts can also be invoked from OBIEE using Actions framework

OBIEE AND ESSBASE INTERACTION

INVOKE CALC SCRIPTS

© Grant Thornton LLP. All rights reserved.

59


The best approach is to
use filters which are prompted rather than filters based on presentation
variables.
BI Server does not pass the filters to the
MDX
queries and there by the entire
data set is
fetched and
filters gets applied on the BI Server.
This may affect performance especially when the
report uses dynamic calculations
.


OBIEE supports Generation based reporting. Level information is not extracted from the Cube.


Evaluate function can be used to directly call MDX functions
.


Cube needs to be re
-
imported if there is a change in the measure hierarchy or the dimension structure
is changed like adding or deleting levels
.


No control on Essbase substitution variables coming in as Repository Variables and can hold only one
value (a text value).


When
we import any member from Essbase which is a shared member, the children of that member
cannot be drilled through
in OBIEE. The work around is to build
Shared member starting from the
level where users do not have to drill
further


Essbase
import into BI has only four data types Double,
Int
,
var
,
Unkown
. So a requirement to use
‘date’ in Calendar form (like travel websites) will require lots of Cast and Recast of Date from Essbase.


OBIEE AND ESSBASE INTERACTION

OTHER OBSERVATIONS

© Grant Thornton LLP. All rights reserved.

60


Pervasive Interface for Reporting, Analysis and Dashboard with comprehensive delivery options


Deliver BI through Common Foundation and Platform services


Maximizing the EPM applications with enablement of Oracle Business Intelligence tools and technologies


Powerful Analysis with Essbase Aggregation for Reporting, and building what
-
if models Financial Planning
and Budgeting


Business Intelligence encompass Any Source/Data including Essbase utilizing Smart View, Financial
Reports, OBIEE Analysis, Dashboards and BI Publisher


Business Intelligence relational reporting with BI Apps pre
-
built subject area and content with Essbase for
advanced Analytics


Leverage and share data between OBI pre
-
build/custom analytical applications, Oracle Essbase and EPM
applications


Manage and Share Enterprise Dimensions to ERP, BI and EPM leveraging Oracle DRM


Financial Management reporting through Essbase Analytical Link (EAL) and Hyperion Financial
Management Analytics (HFMA)


Deliver High Performance structured and un
-
structured data Analytics through Exalytics

CLOSING COMMENTS

BI FRAMEWORK

© Grant Thornton LLP. All rights reserved.

QUESTIONS?

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

62

Copyright

©

2013, Oracle and/or its affiliates. All rights reserved.

63