Using a row wise initialised session variable in a dashboard prompt

I came across a case today where I wanted to use the contents of a row wise intialised variable in a dashboard prompt.  You can't just use the variable directly as the possible list of values for the prompt as you will get an error message like this:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

SQL Issued: VALUEOF(NQ_SESSION.) 

 

So instead you can use the SQL Results option for the "Show" section of the dashboard prompt to bring in the column you are prompting and then add a where clauses referencing the variable:

 

SELECT Organisation.Organisation FROM Masterplan where Organisation.Organisation=VALUEOF(NQ_SESSION.) 

This will work with no errors and the list of possible results in the dashboard prompt will the same as those held in the session variable.

 

If you then want one of these to be selected by default (if you use report defaults then a blank entry will be added to the dropdown and this will be selected by default) then you can use some SQL again:

SELECT MIN(Organisation.Organisation) FROM Masterplan where Organisation.Organisation=VALUEOF(NQ_SESSION.ORGANIZATION_NAME)

 

This will select the first one in the list of results when ordered alphabetically.

RPD Groups and Siebel Responsibilities

This post explains how responsibilities map to RPD groups in OBIA/Analytics Apps with Siebel as a source of the apps.

First of all, you must either create responsibilities in Siebel with exactly the same name as groups in RPD, or vice versa.  The important point is that there must be groups in the RPD and responsibilities in Siebel with exactly the same name:

 Groups in RPD

 

Responsibilities in Siebel

When a user logs into OBIEE an initialisation block named "Authorization" runs some SQL against the Siebel database and gets the responsibilities for the current user.  It sets the session variable GROUP to the set of responsibilities returned.

If there are groups in the RPD which have identical names to any of the responsibilities held in the GROUP variable after the initialisation block has run then the user will be added to those groups, and will then inherit all the security (object and data level) for that group.

This example is based on Siebel as a source, but there is no reason why this can't work for any source.  The important point is the session initialisation block which runs and gets the groups to add the user to.  In Siebel this is controlled through responsibilities, in another source system it could be through another mechanism; as long as it is possible to retrieve through SQL then the initialisation block can be changed to use this.

Row-wise variables

This is a topic I've seen a few times on the OTN forums, and I'm not surprised because the way it has been built is not exactly intuitive.  With that in mind here are some notes on Row-wise intialisation of varaibles in OBIEE, how it works, how to use them and what they are useful for.

So first of all I am going to set up a table for testing purposes:

CREATE TABLE TEST_ROWWISE(
    ROW_WID NUMBER(10) NOT NULL,
    USER_NAME VARCHAR2(100),
    VAR_VALUE VARCHAR2(100)
);

INSERT INTO TEST_ROWWISE VALUES(1,'matt','Value 1');
INSERT INTO TEST_ROWWISE VALUES(2,'matt','Value 2');
INSERT INTO TEST_ROWWISE VALUES(3,'matt','Value 3');
INSERT INTO TEST_ROWWISE VALUES(4,'sue','Value 1');
INSERT INTO TEST_ROWWISE VALUES(5,'sue','Value 2');

commit;

Then in my repository I created a user matt and a user sue for testing purposes:

Next I created a new session intitialisation block:

With a data source of this:

Then in the data target:

The end result of this is that when a user logs in the Oracle BI server will run the SQL in the initialization block against the database and return the rows from the table TEST_ROWWISE for that user (the parameter :user always passes the username of the currently logged in user).  After it has done that the currently logged in user now has a variable called 'VAR' containing all the values for that user.

I tested it by exposing the TEST_ROWWISE table in an rpd and creating a simple request against that table:

Then to test the variable add a filter to the request:

And the detail of the filter:

When logged on as matt I see:

And when logged in as sue:

So here you can see that the logged in user has a session variable VAR which holds all the values returned from the row-wise initialisation.

One way this technique is used in the Oracle Business Intelligence Applications is to go and fetch the web groups that the user should be added to from the source database.  For instance, if Siebel is the source then an intialisation block gets all the responsibilities for the user from the Siebel DB and assigns these to a variable called GROUPS.  In this way any repsonsiblities that the user is associated to, which have exactly the same name as groups existing in the rpd, will be automatically associated to the user at login.