Saturday, January 30, 2016

OBIEE & Essbase Value Hierarchy Bug & Workaround


To begin with this article is based on a bug I submitted.


Bug 22302005 - LOGICAL LEVEL KEYS GET MESSED UP WHEN DIMENSION IS SET TO VALUE BASED HIERARCHY


When OBIEE 11.1.9 was released it took a while for me to actually start ‘Net New’ with my RPD development.  I just upgraded and kept working.  Well the day came that I needed to bring a new cube into my OBIEE 11.1.9 environment.  When I attempted to query a hierarchy dimension that I had set to Value Based / Parent-Child I got the following error.










So I looked at the Log to see the Logical SQL.



I know I had used this cube on my 11.1.1.7 instance and I had no issues so I went and looked at the RPD in my 11.1.1.9 instance.








Physical layer there is the Measures Member Key…

So I went to my 11.1.1.7 instance and re-ran the same analysis





Then I looked at my Business Model








And the Physical Layer. Hmmm… no Measures Member Key
 

A Side by Side view often helps to see the difference.

 

Using my OBIEE 11.1.1.7 instance as my reference 



I edited the Detail Level properties of the Measures Hierarchy.



Then added the Gen3,Measures as a new Logical Key



Next I edited the properties of the Measures Dimension to set the Primary Key - DetailKey to the Gen3,Measures.


Finally I deleted the the offending column.
 
The last thing to do was to save and check in my changes then test the hierarchy in an OBIEE Analysis and now no errors.


As a point of reference this bug is in current release of OBIEE 12c as well.

So until Oracle fixes Bug 22302005 this is a simple work around.

Saturday, January 23, 2016

OBIEE Repository Variable as Column Header

We had a client with a business requirement to produce a columnar dataset with the column headers of a table for the measure to display the value of an Essbase Substitution Variable which was being stored as a dynamic repository variable.  In a previous post I showed how I manage Essbase Substitution Variable in the repository.
 
I know I can use Presentation variables to change column headers, but my Essbase Substitution Variables are Repository Variables and that just doesn’t seem to work. 
  
Now the first thing one might ask is why not use a pivot with periods as columns.  Well like most clients there is a particular view they wanted and there were other columns needed that are not shown.

So after some noodling and playing around I came up with an option.  Use a Dashboard Prompt to set a Presentation Variable and use the Repository Variable to set the default value of the prompt.
 
 
 

I used the Presentation Variable to set the Custom Heading for the column. 
 
 

This little analysis was then embedded in a dashboard and working with Dashboard Properties add the dashboard prompt is hidden. 
 
 
 
 
The dashboard looks like this.  Notice that the 3rd column has the proper month name showing.
 
 
Thanks and until next time.