OBIEE Presentation variable defaulted by a session variable

Did this one on the OTN forum. Open the filter and add a presentation variable:

Say P_SELECT_DATE

Add a default date well outside the feasible range.

Press add more options and add a session variable LAST_REFRESH_DATE:

The filter will now look like:

Till Next Time

OBIEE VALUELISTOF(NQ_SESSION.VAR_NAME)

On of the lesser known (and documented) features is the use of VALUELISTOF(NQ_SESSION.VAR_NAME) in the RPD.

As far as I know it’s only available in a session init block.

Let’s assume we have a row wise initiate variable called GeoArea:

This one is populated from our authorisation database and tells the system which areas a user is allowed to see.

Since we can only use single row session variables in the rest of the OBIEE system we have to transform it to a single string.

For this we can use the oracle LISTAGG function (see: http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm)

We make a new init block with:

SELECT LISTAGG ( COUNTRY_NAME, '; ')  WITHIN GROUP (ORDER BY AREA)
from (select distinct  AREA , COUNTRY_NAME from  SAMP_ADDRESSES_D ) T
WHERE AREA IN (VALUELISTOF(NQ_SESSION.GeoArea))

If we take a peek in the log we see that BI-server is translating this to:

SELECT LISTAGG ( COUNTRY_NAME, '; ')  WITHIN GROUP (ORDER BY AREA)  from (select distinct  AREA , COUNTRY_NAME from  SAMP_ADDRESSES_D ) T

WHERE AREA IN ('West','North','Central','South','South America','Northern','Middle East','East','North Africa','North America','Africa','Europe','Eastern')

The result we put in a session variable called GEO_COUNTRY_LIST.

Be sure to set the order in which the session variables should be loaded:

This variable we can use for a data restriction on the Logical Table Source of our address table.

Till Next Time

OBIEE Referencing Presentation Variables in Filters

How do you reference a presentation variable in your filter:

Press the filter sign of the column

 

Press Add > Variable > Presentation

Enter the varaible name and default value

Till Next Time

OBIEE NQSSetSessionValue

When playing around with session variables in the repository in the help context you wil find a reverence to NQSSetSessionValue:

But if your looking for how to use it........ (At least I couldn't find anything on the net or in the docs.). Anyhow: after logging an SR I was pointed in a direction to find a solution. The way to use this function is to issue an direct SQL request from a web page by ussing the ?IssueRawSQL?SQL (FI: http://vmobiee:9704/analytics/saw.dll?IssueRawSQL?SQL )

(you have be granted the right permission for this!)

Next you have to format the request like:

call NQSSetSessionValue('DataType VariableName=Value;')

FI: call NQSSetSessionValue('Float MYSESSIONVAR=11;')call NQSSetSessionValue('String MYSESSIONVAR=Test;')

The types allowed are: Integer, String, Float, Date, Time and DateTime.

Till Next Time

OBIEE LOV prompts

The classical way of making a LOV prompt is creating SQL in the form:

SELECT case 1=0 then TABLE.COLUMN else "YES" end from BM_NAME union all SELECT case 1=0 then TABLE.COLUMN else "NO" end from BM_NAME

This always produces costly SQL since OBIEE will execute this as a SELECT DISTINT against the reference table.

Much better is to add a small LOV table to repository with it's own business model. I created a small XLS spreadsheet with a couple of LOV's:

Next I imported it into the repository and create the dim version of the LOV:

Made a very simple business model for it:

And of course a presentation layer:

Now you can create each LOV prompt as a direct SQL request:

SELECT LOV.LOV_VALUE FROM BM_LOV WHERE LOV.LOV_TYPE = 'BOOLEAN' ORDER BY LOV.LOV_SORT

The result can be stored in a presentation or a repository variable for late processing:

Till Next Time

OBIEE the "developersprompt"

When I'm developing a dashboard I often want to play around with some basic settings like cache hit & seed and loglevel. Normally you have go back to answers or your administration panel to change these settings (temporarily). I always advice my developers to make a 'developersprompt'......It's a simple LOV prompt which returns me 3 presentation variables (dp_cache{0,1}, dp_seed{0,1} and dp_log{0,1,2}.

In the prefix of each report we default add:

SET VARIABLE DISABLE_CACHE_HIT = @{dp_cache}{0}, DISABLE_CACHE_SEED = @{dp_seed}{0}, LOGLEVEL = @{dp_log}{0};
(you can find the postfix block in the advanced tab of your report)

When developing your dashboard you can playaround with different settings to find the optimum. Leave the prompt on the dashboard and make in only 'available' for administrators or developers, so that in time of "trouble" your are quickly able to bypass the cache or turn on logging.

Till Next Time

OBIEE Follow the sun / Time driven connection

One of our customers wanted a 'follow the sun' solution to connect to there international databases. Translated to the practice this means that they wanted to set a connectionpool based on the the local time. To achieve this we made a repository initationblock called "REP_IB_TNS" and a repository variable called "LOCAL_TNS_TO_USE".

In the datasource block we made a very simple query which determines which TNS to uses based on the time of the day:

We forced a refresh every 10 minutes by setting the "lifespan" of the block:

In the connection pool you make a reference by using the VALUEOF method:

n theory it takes a maximum of 15 minutes (10 minutes for "REP_IB_TNS", 5 minutes for the connection pool) to refresh all connections

Till Next Time

OBIEE Adding session variables to the report title / using a narrative view

A lot of times we find in the request for reports "please put the value of the used report variables in the title".
But when you try that you quickly run into trouble. By default you are confined to only the predefined ones:

From the OBIEE documentation:
To show your own defined variables (repository/session/presentation) in OBIEE you have to use the narrative view.
First you have to add the variable(s) to your query:

Then alter the column properties, set the column hide property.

Next open a Narrative view, set your descriptive text, the variables are referenced by there a @ and there column number (in this case 2)

Set the rows to display to 1

Reorganize your compound lay-out so that your narrative view is directly below your title

Till Next Time

Sometimes you stand corrected see: http://obiee101.blogspot.com/2008/09/obiee-title-view.html

OBIEE referencing session variables in report title

After spending way to much time figering out the exact lingo, I decided to blog them for futher reference:

• currentTime => @{system.currentTime}
• system.productVersion => @{system.productVersion}
• session.locale => @{session.locale} = en-gb
• session.language => @{session.language} = en
• rtl =>@{session.rtl} = false
• session.loginTime => @{session.loginTime}
• session.logoutTime =>@{session.logoutTime }
• session.lastAccessTime =>@{session.lastAccessTime}
• session.currentUser => @{session.currentUser.id} = administrator
• user.homeDirectory => @{user.homeDirectory} = /users/administrator
• user.id => @{user.id} = administrator
• user.displayName => @{user.displayName} = administrator