Using context variables in OBIEE

Tuesday, December 8, 2015

Using context variables in OBIEE

One of our clients used a report which posed a real challenge to achieve in OBIEE. We use OBIEE 11.1.1.7.14 for those curious about it. The data at hand was of the following sort; consider one order, shown below. It reflects one order and the changes the client made to that order. An order is closed when the product is shipped.

 

timestamp

order type

# products

01/04/2015

open

5

03/04/2015

open

7

04/04/2015

open

4

06/04/2015

closed

4

 

Next, consider a timeframe in which the data is analyzed. If it is analyzed for only the first of April, they will see one order for 5 products. So far, so good.

But an analysis of one order only says so much. Expanding the range until the fourth of April reveals the first obstacle; what should we do with the changing number of products? We can’t summarize them because the number is related to one order, it only changed over time because of the nature of the product (food for example). The client wanted to see the last number of products that were set for that order. This could be done in OBIEE by using the ‘last’ aggregation measure. But it became tricky when they also wanted to see the last order type if the range extended so that at least one record of that order was included. So for instance;

 

timeframe used

results expected

01-05/04/2015

open; 4 products

03-06/04/2015

closed; 4 products

03-10/04/2015

closed; 4 products

07-10/04/2015

nuttin’

 

 

No matter how hard we tried, we didn’t succeed in modelling this in OBIEE. This forced us to think outside the box, to look for possibilities to achieve this outside the boundaries of OBIEE. After a constructive brainstorming session, we came up with a solution! An application context! This is a set of (name-value|variable-value) pairs that the Oracle Database stores in memory during a session. We can try to use these variables in OBIEE and pass them through to our database session so we can use its values in a view for example where can add the needed sql code to achieve our goal.


 

To enable this approach, follow these steps;

  1. create a context and don’t worry, you can reference a package that doesn’t exist yet!

CREATE CONTEXT MY_CONTEXT USING demo.pck_my_context_api;

       

  1. create a package that simply sets the value of the variable in the context

CREATE OR REPLACE package pck_my_context_api
is
    procedure set_myvar (my_var_context in varchar2);
end;
/

CREATE OR REPLACE package body pck_my_context_api
is
    context_name varchar2(30) := 'MY_CONTEXT';

    procedure set_myvar (my_var_context in varchar2) is
    begin
            dbms_session.set_context(context_name, 'MYVAR',
        my_var_context);
    end;
end;
/

 

  1. add something like this to the database view that is used to retrieve the data. In it, you can use the value of the variable which you set in a dashboard prompt fox example.

… SYS_CONTEXT('MY_CONTEXT', 'MYVAR') …

Be sure to use the correct context and variable name!

 

  1. create a session variable in the administration tool, remember it’s name!

 

  1. add the call to the created procedure as a script to execute before the query in the connection scripts tab of the connection pool that is used.

call pck_my_context_api.set_myvar('VALUEOF(NQ_SESSION.MYVAR)');

 

 

  1. add the following in the prefix field of the advanced sql clauses in the advanced tab of your OBIEE report. You can recognize the standard notation when using a presentation variable with a default value.

SET VARIABLE  MYVAR=’@{PV_MYVAR}{0123456789}’;

 

 

This should do the trick!

 

To test this, I created a dashboard prompt in which I set the value of a presentation variable (PV_MYVAR) through the use of a text field.

 

I also created a report which is based on a database view that just shows the value of the application context variable.

CREATE VIEW V_MYVAR as
    select SYS_CONTEXT('MY_CONTEXT', 'MYVAR') as MYVAR from dual;

The report looks like this;
 

For now, it just shows the value that I used in the SET VARIABLE MYVAR=’@{PV_MYVAR} {0123456789}’; command that I entered in the prefix field of the advanced sql clauses since there was no value present for the PV_MYVAR variable.

When we combine all of this in a dashboard, the value of the presentation variable that is set in the prompt will be passed through to the application context variable in the session that was created when I clicked ‘Apply’ and the BI server launched its query towards the database. The execute before query script will launch and it will set the value of the variable

 

Entering a value in the prompt and hitting ‘Apply’ yields;

In the session log we can see that the value is being passed through the connection pool and passed down to the database session, where it is shown in the view which I created and which is shown in the report! That way, you can use values from your dashboard on a database level.

Sending query to database named localhost (id: <<5740>>), connection pool named demo, logical request hash d900fb23, physical request hash 55ecc6a5: [[
call pck_my_context_api.set_myvar('I am the greatest!');

 

Good luck!

PVE