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.

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.

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

 

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.

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.

 

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.

 

You should have results like this.

 

 

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.

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.

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.

 

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

 

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

Rename it to Measures instead.

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.

 

By default, Nullable is checked.

 

Uncheck the Nullable.

 

Essbase OBIEE Tip 5 – Importing Essbase Substitution Variables

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

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

 

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.

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

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

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

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

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

Then import this table into the RPD.

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

Choose the connection pool where you hold your OBIEE_AUTH table.

 

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.

 

 

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

 

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.

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.