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.