Oracle BI EE Integration with Hyperion Sources

55 

Full text

(1)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Open World 2013

Oracle BI EE Integration with Hyperion Sources

(2)

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com

Who Am I?

Venkatakrishnan Janakiraman

Over 10+ Years of Oracle BI & EPM experience

India Managing Director, Rittman Mead Consulting

Blog at http://www.rittmanmead.com/blog

-

Old & defunct blog http://oraclebizint.wordpress.com

Oracle ACE

EPM/BI Specialization

Twitter

-

@krisvenkat

(3)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

About Rittman Mead

Oracle BI, EPM and DW Gold partner

World leading specialist partner for technical excellence, solutions delivery and innovation in Oracle BI

Approximately 100 consultants worldwide

All expert in Oracle BI and DW

UK based

Offices in US, Europe (Belgium), India & Australia

Skills in broad range of supporting Oracle tools:

OBIEE

OBIA

ODIEE

Essbase, Oracle OLAP

GoldenGate

Exadata

EPM

-

HFM

-

Planning

-

Essbase

-

HPCM

Monday, 23 September 13

(4)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Agenda

BI EE Integration with Hyperion Sources

Overview

Integration with Essbase - Indepth

Essbase Outline Features

-

Dimension Types

-

Member Alias

-

Ragged Hierarchies

-

Time Balance Properties

-

UDAs

-

Account Properties (Revenue/Expense

etc)

-

Attributes

-

Varying Attributes

-

Text Measures & Format Strings

-

HOLAP Drills

Essbase Security

MDX Query Structure

Federation

ASO & BSO Difference

Integration with HFM & HCM

Direct Integration using ADM

OFMA

Integration with HPCM

Standard Profitability

Detailed Profitability

Integration with Planning

(5)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Based on Fusion Middleware 11g Platform

Fully-integrated with Oracle Fusion Middleware 11g

Common security (Oracle Identity Management, Oracle Access Manager)

Built on WebLogic Server

Managed through Fusion Middleware

Control (Enterprise Manager)

Built using multiple technologies

including JDeveloper (ADF)

(6)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Based on Fusion Middleware 11g Platform

Essbase HFM HPCM HCM Planning

BI EE Connectivity

Monday, 23 September 13

(7)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Oracle BI EE Integration with Essbase

(8)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Essbase: The Outline

The dimensional model for the Essbase database

Contains all of the dimensions, hierarchies, members

Calculation and aggregation settings

The most important bit of metadata for your

Essbase Database

Stored as a .otl file in the filesystem

Contains entire metadata structure of Essbase

Every property here is important

(9)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline Properties - Dimension Types

Play an important role in Essbase

Accounts

Time

Country

Currency

Accounts

Important Dimension

Like a measure dimension

Primarily used for handling Financial Accounts in Essbase

-

Certain Properties available only for Accounts

No/little significance from Reporting standpoint

(10)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline Properties - Member Names & Aliases

Outline made up of Dimensions

Dimensions made up of Members

Every Member has a unique name

Across dimensions in most Essbase

databases

Duplicate Member enabled databases

-

Unique Member Name in the

dimension

-

Non-Unique across dimensions

Every Member can have Aliases

(decriptions)

Upto 32 alias tables

Default Alias table will be default

description

(11)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline Properties - Member Property

Member Property

Varies by Dimension Type

Standard Properties (Do not affect reports)

Data Storage

Dimensional Storage (BSO)

Comment

Two-Pass Calculation

Time Balance

All these properties

Little Significance from Reporting standpoint

MDX generated will automatically consider

-

these properties

(12)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline Properties - Member Property

Member Property

Varies by Dimension Type

Variance Reporting

Determines whether an Account

-

Expense

-

Revenue

Smartview/FR

-

Flips sign based on Expense/Revenue

(13)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline Properties - Member Property

Member Property

Varies by Dimension Type

Format Strings

New 11.1.2.1 feature

For representing data in different formats

Handled through MDX function in the property

(14)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline Properties - Member Property

Member Property

Varies by Dimension Type

Text Measures

New 11.1.2.1 feature

For representing data in Text Format (True or False)

Similar to Smart lists in Planning

(15)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline - Hierarchies

Hierarchies roll into Dimensions

Hierarchies can be Ragged

BSO

Parent Dimension Considered as root node of Hierarchy

ASO

Depends on the Storage Type

Stored Hierarchies - Similar to BSO

Multiple Hierarchies Enabled (each behave like a dimension)

-

We cannot add data across hierarchies

-

MDX rollups across hierarchies will not work

-

Parent Dimension becomes Label Only

(16)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline - Hierarchies

BSO ASO

(17)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline Properties - User Defined Attributes (UDAs)

Every Member in Essbase outline can be tagged with UDA

Very useful for grouping related members

Example

Population Groups

Product Characteristics

Color Attributes etc

Very helpful to choose related members

All Major Market Cities

All Major Market Cities

-

with Pop < 9000000 etc

(18)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline Properties - Attribute Dimensions

Similar to UDAs

But defined as Dimensions

Tagged to a member

Can have a hierarchy of attributes

Always retrieved at run time

Similar to SCD-1s in DW

(19)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Outline Properties - Varying Attributes

Equivalent to SCD-2s in DW

Introduced in 11.1.2.1

Has some known issues/bugs

Very good concept

MDX Query PERSPECTIVE Clause

(20)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

BI EE Support as of Today

Essbase Feature

BI EE Support

Alias & Alias Tables

Full Support

Standard Outline Props (Dim Type, Two pass etc)

Full Support

Variance Reporting

No Support

Format Strings

No Support

Text Measures

No Support

User Defined Attributes

Full Support

Attributes

Full Support

Multiple Hierarchy Support

Full Support

Varying Attributes

No Support

(21)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Oracle BI EE Integration with Essbase

(22)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Importing an Essbase Cube Step 1 : Select Metadata Import

Using BI Administration tool, select File > Import Metadata

Enter connection details for Essbase Server (requires Essbase Client to be

installed on server and desktop)

Select cube for import, check Import UDAs if UDAs are present in cube

(23)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Importing an Essbase Cube Step 2 : Review Import

Review imported cube metadata

Accounts dimension is now a hierarchy

Single measure dimensioned by all

dimensions

Columns still based on outline

dimension generations

(24)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Importing an Essbase Cube Step 3 : Convert To Parent-Child

Imported level-based hierarchies can be converted to value-based

Protects against outline evolution, new levels (generations) being added

Right-click on dimension, select Properties > Levels,

then change Hierarchy Type to Value

(25)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Importing an Essbase Cube Step 4 : Add Alias Columns

Where aliases are present in the outline, these can be brought through to OBIEE

Right-click on dimension, select Create Columns for Alias Table

Select alias to create column for; alias then appears in physical database

(26)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Importing an Essbase Cube Step 5 : Create Remainder of Model

Once physical model is created, drag and drop over to business model and

mapping layer, and then presentation layer

Essbase data is then ready

for reporting on using Answers

(27)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Essbase Reporting Features in OBIEE 11g

OBIEE 11g comes with a number of improvements for reporting against Essbase

Hierarchical column provide OLAP-style

drilling against Essbase cubes

Selection steps and hierarchical prompts

provided member-based selections

UDAs can be used in filters

Value-based hierarchies remove need

to query specific levels to select

members for analysis

(28)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Essbase and Hierarchical Columns

Essbase cubes now use Hierarchical Columns by default

Full drill into detail (symmetric / asymmetric drilling)

Combine with regular attribute columns

Works across both

relational and OLAP sources

MOLAP and ROLAP

analysis combined

(29)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Selection Steps and Essbase

The OBIEE 11g Selection Steps feature can be used to select members

based on a hierarchy

Select individual members, drill into hierarchy

Select members based on

position in a hierarchy

Select Children Of

Select Ancestors of

Keep/Remove members

(30)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Conditional Selection Steps

Selection steps can be added conditionally

Select condition type, action, measure, operator and value

Allows you to add/remove/keep members based on measure values

(31)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Review Selection Steps

Selection Steps can be reviewed using

the Results tab > Selection Steps

Shows steps applied sequentially

to the dataset

Can be used in conjunction with regular

criteria column filters

(32)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Filtering against UDAs

User-Defined Attributes can be accessed via filters

UDAs are not displayable like columns, they instead act as filters

Add filter based on UDA = 1 (true) or false (0)

(33)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Displaying Aliases

Aliases appear in the subject area just like other columns

Have to be imported into the RPD

Can be used just as any other column

(34)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Displaying Aliases

We can make Members to reflect Aliases name directly

Setting up the Display Column as the Alias

New feature - This can be driven through a Session Variable

(35)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Handling Essbase Alternate Hierarchies

Alternate hierarchies can be imported from Essbase ASO cubes (not BSO)

Dimension must be multiple hierarchy enabled in the outline

Multiple hierarchies are then imported into the RPD and created separately, based

off of the same logical dimension table

1

2

3

(36)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Handling Attribute Dimensions

Attribute dimensions are imported into the RPD just like regular Essbase

dimensions

Can be queried in analyses alongside regular dimension members/logical columns

Typically attribute dimensions are nested within the “parent” dimension

within the presentation layer of the RPD

Can be queried separate from the “parent” dimension, or alongside it

1

2

3

(37)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Essbase Security and Filters

Data filters and Meta Filters are respected by OBIEE

SSO integration between Workspace and OBIEE Presentation Server removed in

11g

SSO still possible but requires external solution (until 11.1.1.6+?)

Shared Services and OBIEE both connecting to same external LDAP server

External SSO over both OBIEE and EPM products

(38)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Security Design - BI EE & Essbase

Essbase Security

Completely different from BI EE/relational Security

Experience of Essbase filters will be different from BI EE relational data filters

(39)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Security - Example - Traditional

Base Level Data

Rollup Hierarchies

(40)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Security Example

Admin User A User B User C Monday, 23 September 13

(41)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Security Example

Essbase has only one cell

Essbase Security - Either 6000 visible or not

(42)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Security Example

Essbase has only one cell

BI EE Security - Level 0 Data Filters in RPD

Poorly generated MDX

(43)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Drill-Across and Drill-Through To Relational Data

One of the most powerful features in the OBIEE it is ability to combine data

sources

Multiple data sources presented as a single logical dimensional model

In terms of Essbase and HFM integration there are three main use-cases:

1.

Providing drill-down from an Essbase-based model to detail-level relational

data

2.

Displaying relational attributes and descriptive data alongside Essbase

measures

3.

Combining Essbase and relational measures into a single business model

(44)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Oracle BI EE Integration with HFM

(45)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Integration with HFM

2 Options

Custom integration like Essbase

-

Everything is done manually

Using OFMA

-

Oracle Financial Management Analytics

(46)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Integration with HFM - Manual

Integration with HFM

Through ADM Drivers

Very similar to Essbase Integration

Entire metadata structure imported

Queries fired are ADM queries

-

Not as flexible as MDX

(47)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Integration Details - Import Metadata

Very Similar to Essbase

Provide connection details to HFM Server

Choose the Application

Import Metadata

(48)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Integration Details - Build RPD

Define BMM & Presentation Layers

Similar to Essbase

Entire model gets created automatically

(49)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

HFM Integration - Build Reports

All queries generated are ADM Queries

Very limited control from front end

Unlike MDX, we cannot function ship custom MDX functions

Works well for basic reports

(50)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

HFM Integration - OFMA

Separate licensable Product from Oracle

Comes with a custom Configurator

Can pick and choose BS/PL accounts

Splits the Accounts dimension and configures reports to point to BS/PL

Accounts

(51)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

HFM Integration - OFMA

Pre-configures all Prompts with selected values in Configurator

Very handy for larger FM databases

One time setup

(52)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

HFM Integration - OFMA

All reports/dashboards available out of the box

Requires trial and error to understand how the Configurator works

(53)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Oracle BI EE Integration with HFM

(54)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Standard Profitability - Reporting

HPCM Standard Profitability

Uses Essbase as the backend

All Allocated data stored in Essbase

All Contribution data stored in

Essbase

ASO Cube for reporting

Recommended to use Reporting

Queries feature in Standard

Profitability

-

Builds Smartview reports using a

Wizard interface

-

Replicate reports in BI EE

(55)

T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com

Detailed Profitability - Reporting

All data stored in relational tables

Explicit modeling required

Source Table

Target Table

Contribution Table

Dimension Tables

Standard Relational modeling techniques apply

Figure

Updating...

Related subjects :