An Essbase OBIEE integration walk through with tips and tricks

Best Practices or Tips & Tricks?

It’s probably best practices for myself but tips and tricks for you.

During the past few months, I have been going through theories and logics of KPIs, Dashboards and Strategy management. There are still a lot to go through but I’ve decided to go technical again for those who are missing out on Essbase & OBIEE. I’ve been doing Essbase and OBIEE integration since 2009, accumulated more than 30 Essbase OBIEE projects and there are lots of improvements on the tools since then. Unfortunately, Hyperion experts usually don’t like OBIEE and vice versa. I know that feeling since I came from the Hyperion side and always was an Essbase fan, that was back in 2004. The integration is actually not as difficult as it seems nowadays. You just need some tips and tricks for Essbase OBIEE integration.

Essbase Pre-requisites

This demonstration will be based Essbase “Sample_U” Application using “Basic” DB. You will need:

  1. Essbase installed…
  2. EAS install…
  3. Sample_U application & Basic db
  4. Elementary EAS & Essbase knowledge such as naming Generations and Creating Substitution Variables
  5. Know your server name and ports

OBIEE Pre-requisites

I am on OBIEE Version 11.1.1.7.140715 (Build 140622.1831 64-bit) with Essbase installed. If you installed this Essbase on EPM, then your default EAS port will be 10080 and default Essbase port will be 1423. It will be different from my setup. However, I chose to install OBIEE with Essbase is purely because I’m installing on a Windows 7 environment which is impossible with Hyperion install. Moreover, it takes less RAM!!! Anyway, you will need the following:

  1. OBIEE installed obviously!
  2. Oracle DB with rights to create tables.
  3. General BI Administration knowledge.
  4. Variable Manager knowledge

Essbase Setup

We will be using Sample_U which is in Unicode Mode.

034-Essbase-OBIEE

032-Essbase-OBIEE

Essbase OBIEE Tip 1 – Give the hierarchies generation name

Naming the generations in Essbase is much easier then handling them in the RPD. Moreover, naming convention will be the same later on in the RPD and Essbase Outline. In general, name your Generations starting with “Gn” so it might be G1: Market Dim. This Gn stands for Generation n and by adding this, it is unlikely you will have duplicate member names as well as being clearly defined while doing Answers or working in the RPD.

In the outline, right click on the Year Dimension and edit the Generation. For Year, since it’s DTS, we won’t be able to modify the Generation Name for History and Quarter. But let’s add G3: Month.

033-Essbase-OBIEE

Next, let’s do the same for Market Dimension so you get the below result.

031-Essbase-OBIEE

 

I’d do the same for all other dimensions except Measures! I usually treat measure or account a bit different. But if you’ve done it, there’s no harm.

For other dimensions, I’ve named mine:

G1: Product Total

G2: Product Group

G3: Product

G1: Scenario

G2: Scenario

G1: Caffeinated Attr.

G2: Caff. Boolean

G1: Ounces Attr.

G2: Ounces

G1: Pkg Type Attr.

G2: Pkg Type

G1: Population Attr.

G2: Population Group

G3: Population

G1: Intro Date Attr.

G2: Intro Date

You’ll notice that all the Attribute dimensions are named with Attr. in G1. This is to let users that use “Answers” know that this is an Attribute Dimension. You can have your own naming if you want. The idea is to let people know that we don’t actually need this Dimension for retrieval and that it’ll hinder performance when used due to the on the fly calculations.

Essbase OBIEE Tip 2 – Use Essbase for Substitution Variable

We can always use within EAS. It’s easy to manage and guess what, there’s a free tool for managing it thanks to Tim Tow and AppliedOLAP for maintaining the tools. If you want your copy, go here: http://www.appliedolap.com/free-tools/subvar-manager

Let’s create a Current Month variable for this basic application and I’ve called mine CurMonth with Aug as the value.

007-Essbase-OBIEE

You’ll see how this will work in OBIEE later.

OBIEE Setup

First things first, turn off Skip Gen1 if you think you need Generation 1, which we do for this demonstration. By default mine was checked for some reason.

029-Essbase-OBIEE

 

Uncheck “Skip Gen 1 levels in Essbase drag and drop actions”. Who ever made this option didn’t come from Hyperion. Generations and Levels are very different concepts in Essbase. It should have just been Skip Gen 1 in Essbase drag and drop actions even though it’s obvious but it’s to skip any uncertainties.

Secondly, we’ll move on to importing the Essbase. I’m sure majority of you would know how to do this so I won’t go through the details.

030-Essbase-OBIEE

 

You should have results like this.

028-Essbase-OBIEE

 

 

Essbase OBIEE Tip 3 – Renaming Measure to Fact and Never Flatten Anything

Rename the “Measure” to “Fact” because we have a dimension called Measure. Because we’re on Essbase, this measure is the actual intersection cell which gives the number. There’s no actual meta-data to this. I never flatten my dimensions purely because that’s not how Essbase natively works.
014-Essbase-OBIEE

Essbase OBIEE Tip 4 – Double Check Your Dimension & Hierarchy Types

There are a few rules to Dimension & Hierarchy Types.

Rule Number 1: Have a Time dimension. This is usually tagged once imported.

027-Essbase-OBIEE

Rule Number 2: Have your Account or Measure dimension tagged as Measure Dimension and Value as Hierarchy Type.

By default, your Account or Measure dimension Hierarchy Type is unbalanced. It works but in the future, when you add another Generation to it, you’ll need to modify the RPD again. What to do to avoid this is to have it as Value dimension. This way, it’s a Parent Child relationship. Remember to double check that it’s a Measure Dimension.

Originally, you’ll have Measure Dimension starting with Gen1,Measures like this.

022-Essbase-OBIEE

 

Double click “Measures” and change Hierarchy type to Value and Dimension Type as Measure Dimension.

021-Essbase-OBIEE

 

After modifying, expand the hierarchy and you’ll see Gen4,Measures.

020-Essbase-OBIEE

Rename it to Measures instead.

019-Essbase-OBIEE

Rule Number 3: Uncheck Nullable

By default, Nullable is checked. I usually uncheck it so that prompts in the dashboard will not have a null value to choose from.

So for every generation where you see the green key, double click it.

026-Essbase-OBIEE

 

By default, Nullable is checked.

025-Essbase-OBIEE

 

Uncheck the Nullable.

024-Essbase-OBIEE

 

Essbase OBIEE Tip 5 – Importing Essbase Substitution Variables

In variables manager, go to Initialization Blocks. Right click and choose New Initialization Block.

001-Essbase-OBIEE

Then Edit the Data Source and choose your Essbase connection pool. Setup the Refresh interval if needed but I’ll leave it as default.

003-Essbase-OBIEE

 

Next step is to edit the data target. Click New and for Variable add the Substitution Variable by “hostname:application_name:database_name:variable_name”. Then set the default initializer if you like. Click ok when finished.

004-Essbase-OBIEE

Let’s test it out by clicking test and you should get Aug as results.

002-Essbase-OBIEE

Essbase OBIEE Tip 6 – Making use of Alias Tables & Uncheck Cacheable

If you click on the cube, this is what you should get.

018-Essbase-OBIEE

Please uncheck Cacheable.

In your Oracle DB, create a table called OBIEE_AUTH with columns:

USER_ID, DISPLAY_NAME, USER_GROUP, GROUP_NAME, WEB_LANGUAGE, LOGLEVEL, STATUS, SERVER_CONN, CUBE_1, CUBE_2, CUBE_3, CUBE_4, USER_DIRECTORY

CREATE TABLE “LOCAL_OBIEE”.”OBIEE_AUTH”
( “USER_ID” VARCHAR2(240),
“DISPLAY_NAME” NVARCHAR2(240),
“USER_GROUP” NVARCHAR2(240),
“GROUP_NAME” NVARCHAR2(240),
“WEB_LANGUAGE” NVARCHAR2(240),
“LOGLEVEL” NVARCHAR2(240),
“STATUS” NVARCHAR2(240),
“SERVER_CONN” NVARCHAR2(240),
“CUBE_1” NVARCHAR2(240),
“CUBE_2” NVARCHAR2(240),
“CUBE_3” NVARCHAR2(240),
“CUBE_4” NVARCHAR2(240),
“USER_DIRECTORY” VARCHAR2(240)
) ;

017-Essbase-OBIEE

Then fill in the table with admin, admin, BIAdministrator, , Default, 1, Enabled, localhost, Basic, , , Native Directory

016-Essbase-OBIEE

Then import this table into the RPD.

Next through Variable Manager, go to Initialization Blocks. Create a new Initialization Block called OBIEE_INIT.

011-Essbase-OBIEE

Choose the connection pool where you hold your OBIEE_AUTH table.

010-Essbase-OBIEE

 

Also enter the following select statement:

SELECT DISPLAY_NAME, USER_GROUP, WEB_LANGUAGE, LOGLEVEL, SERVER_CONN, CUBE_1, CUBE_2, CUBE_3,  CUBE_4 FROM OBIEE_AUTH WHERE lower(USER_ID) = lower(‘:USER’) and STATUS = ‘Enable’

So it’ll look like this.

 

009-Essbase-OBIEE

 

Next step is to Edit Data Target and add new variables as follows:

008-Essbase-OBIEE

 

Go back to the Basic Cube and select Variable and type VALUEOF(NQ_SESSION.LANG). This will pick up the Web_Language set in the OBIEE_AUTH table when the user logs in.

012-Essbase-OBIEE

What we just did is to use the table to define which Alias table from Essbase to use. If you modify the Web_Language to “ChineseNames”, then Chinese Alias from Essbase will be displayed as results in OBIEE.

Final Steps

Drag Basic from Physical to BMM then to Presentation.

Let’s go through Answers and prove everything works tomorrow. A bit long and tired now.

Advertisements

Author: Steve Yeung

Being in the EPM & BI field for more than 8 years, it's about time I contribute to newcomers! As a founder of MondayBI.com I wish to give you all the help I can. Feel free to give any suggestions or questions. Hope you will all enjoy this blog! William Wong Essbase Certified Specialist OBIEE Certified Specialist

10 thoughts on “An Essbase OBIEE integration walk through with tips and tricks”

  1. Hi Steve.

    As per your suggestion, I am posting my question here as well.

    I am integrating Essbase and OBIEE. I have few measures (accounts) as date in Essbase, such as Receipt Date etc.

    Now when I fetch this in OBIEE, they appear as numbers.

    How to get date measures from Essbase to OBIEE exactly the way they are sitting in Essbase?

    Thanks for your reply. Much appreciated.

    Regards

    Liked by 1 person

  2. Hi, I have imprted same cube twice with different names. Problem is entity hierarchy I can see child nodes in one cube . In next cube same entity in a different structure like gen1 , gen2. what is the issue.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s