OBIEE Repository Variables, Filtering and Conditional Formatting
September 3rd, 2008 by Mark Rittman
I’m currently sitting in Galway airport coming back from a few days work, and as I’ve got an hour or so free I thought I’d take a look at server variables in OBIEE. Repository and Session server variables are something you tend to encounter first when working with LDAP security, but they can also be used to create dynamic row-level filters, provide the filter values for conditional formatting and many other tasks, and it’s worth taking a few minutes to understand how they work.
Variables in the Oracle BI Server can be either repository or session variables, and are defined using the Variable Manager within the Oracle BI Administrator (Manage > Variables).
This brings up the Variable Manager, that divides variables out into repository and session variables, with a further subdivision into static and dynamic repository ones and system and non-system session ones. You can also define variables at the Presentation Server level, these are scoped to individual dashboards and are used amongst other things for setting dashboard prompt values and integrating the dashboard with BI Publisher parameters. Here’s a screenshot of a typical Variable Manager screen.
So what are server (repository) variables used for? Well if you’ve done any programming, either in languages like Visual Basic or on the web, variables these platforms use are the same as the ones that the BI Server uses. As with web development, you can either have server variables that hold the same value across all sessions (in BI Server terms, a “repository” variable), or variables that hold a value specific to each user session (”session” variables in the BI Server). Furthermore, repository variables can either be constants (”static repository” variables) or can have values that vary over time (”dynamic repository” variables), with the dynamic ones refreshed to a schedule via SQL calls and session variables usually set at the time of logon by reference to an LDAP server or an SQL statement. So now that’s all clear, in what sort of situation would they be used? Here’s a scenario that makes use of all these types of BI Server variable.
We have a reporting requirement where salespeople are responsible for a number of states, and these states change on a constant basis. Some of these states are shared with other salespeople and it’s not really practical to define set groupings of these states, hence we have a lookup table on our database that lists out the states each salesperson is responsible for, together with a marker against one particular state that is their home state. We want to use these lists of states as dynamic filters on the reports our salespeople run, and have reports default to their home state when they view their data in a dashboard. We also want to only show data for the current month in their reports, and hold the state in which our headquarters is based in a constant so that we can highlight the sales that are made near to it. In other words, we’ve got a good example of where variables in various shapes and forms can be used to make this process a whole lot easier to implement.
To start off, I use the Variable Manager to define a new static repository variable that I call HEAD_OFFICE_STATE and set to the value ‘NY’. This initial value, that stays constant for static repository variables, is called the “default initializer” and can either be typed in or set via an expression. The only way this variable value can change is if I go back into the Variable Manager and change it there.
Next I create the second repository variable, this time a dynamic one, that is used to return the current month name and year to any query that requires it. Unlike static repository variables this variable’s values change over time, using an SQL statement executed to a schedule to update its values.
As well as having a default initializer, dynamic variables get their values from initialization blocks that provide the mechanism to execute the SQL via a schedule. To define the initialization block I press “New” and enter the SQL, refresh frequency and variable target for the block. Notice in this example that I’m running the SQL against an Oracle database connection pool, and the truncated month and year name is generated through some Oracle function names and a reference to sysdate.
So now I’ve got my two repository variables defined. Before I create my my session variable, I first have to locate a table in my database that lists out the selection of states that each salesperson is working with. Notice how there’s sometimes more than one row per salesperson.
I now move on to creating the session variable. To use the data from the table above to provide the values for my dynamic filter, I first of all make sure that this table is accessible through a connection pool in the BI Server physical layer, and then go back to the Variable Manager to define my session variable. Now if this variable was going to hold a single, scalar value, I could define it as a regular non-system session variable, but as it need to hold data from more than one database row, I instead define it using an Initialization Block and row-wise variable initialization, which defines one or more variables based on a select statement, dynamically creating the variables as needed and concatenating the results of multiple rows into the variable.
To perform this task I first define a the new Initialization Block and call it STATE_SECURITY. I then define my SELECT statement as the following:
select 'STATE_FILTER',state from variable_example.salesperson where salesperson_name = ':USER'
This will return one or more rows with STATE_FILTER, the new dynamically-created variable name, as the first column, and the set of allowed salesman states as the second value, filtered on the logged-in user ID. My initialization block now looks like this:
To assign the results of this SELECT statement to a variable, the STATE_FILTER variable, I then click on the “Edit Data Target” button and select Row-Wise Initialization, rather than try and assign the results to individual variables. The initialization block will then take all the rows that have STATE_FILTER as the first column and create a single concatenated, comma-separated list out of the second column, so that I can then apply this value to a filter.
Now that the session variable definition is complete, I move over to the Security Manager application, create a group for all of my report users and then define a filter for that group against the SALES table, the one I want to restrict access to. The filter references the session variable I just created, using an equality (”=”) operator rather than the “in” operator you’d have expected, this is a requirement for row-wise variables and OBIEE handles the translation properly in the background.
Now if one of the affected users logs in an runs a report against that table, the results are filtered down without any intervention on their part.
Conditionally formatting the State column based on whether the value each row contains is also fairly straightforward. When you create a conditional format condition you can specify that that value tested against is a variable; to access one of the repository variables you put biServer.variables[''] around the variable name so that, in my example, the variable name becomes biServer.variables['HEAD_OFFICE_STATE'].
Displaying the report now shows all instances of “NY” highlighted in red, based on my conditional formatting rule and the value of the HOME_OFFICE_STATE static repository variable.
So there you have it. Moving on from here, filtering the report again based on the dynamic repository variable is just a case of referencing the CURRENT_MONTH variable in my filter, and adding another session variable to hold the salesperson’s home state involves creating another initialization block that this time provides a value for a regular (i.e. not row-wise) HOME_STATE session variable.
September 4th, 2008 at 10:26 am
Thank you for the explanation, but this means that you should maintain a table with the salespersons and the STATE, but is it also possible to do this for a group in stead of user?
September 5th, 2008 at 7:28 am
@Osman
Do you mean, is it possible to set up a dynamic filter based on groups rather than user? Yes is the answer, obviously groups don’t have sessions and therefore their own session variables, what I’d probably do is set up a dynamic system variable that refreshes, say, every day or every hour, have that hold the list of states assigned to each group, and then set up group filters that reference these dynamic system variables.
regards, Mark
September 8th, 2008 at 6:38 pm
Hi Mark,
I am conditionally formatting negative values in a column based on a simple filter test for column values less than zero. However, I do have total (summary) rows in the table (and pivot table) and could not find any way to conditionally format the values in them. So, while the negative values in the column display in red, the toal displays in black even though it is negative!
Do you know of any workaround to this?
Thanks,
Sarosh
September 9th, 2008 at 6:47 pm
Hi Mark,
I have an OBIEE Dashboard request that has 7 pivot tables and depending on the day of the week, we need to automatically default to a specific pivot table. Is there any way in OBIEE to dynamically default to a specific pivot table based on a condition?
Will appreciate any input on this issue.
Vijaya
September 9th, 2008 at 11:26 pm
Hi Mark,
I encountered an issue with Oracle BI EE Session Variables that I’d appreciate your opinion on. I have two sources from where I could get the Company information for a user - LDAP or an external SQL Server table. SourceA is the default from an LDAP source, and SourceB may exist in the external table, and if it does exist then it should override SourceA.
So I set up SourceA as a Session variable to get the company LDAP attribute in an initialization block - works just fine. Set up SourceB as SELECT COMPANY FROM USER_TABLE WHERE USERID = ‘:USER’ in another initialization block. Works fine by itself.
To get the values conditionally, I tried the following for SourceB: SELECT ISNULL((SELECT COMPANY FROM USER_TABLE WHERE USERID = ‘:USER’), ‘:SourceA’). This works fine so long as SourceB is present, but if it returns NULL from the external table, then the value of ‘:SourceA’ is not assigned to SourceB. It seems that ‘:User’ and ‘:Password’ are the only session variables that can be referred to in initialization blocks.
This seems to be a perplexing limitation.
Thanks,
Sarosh
September 10th, 2008 at 1:28 pm
@Sarosh
Have you tried to prefix the name sourceA with “NQ_SESSION.” eg NQ_SESSION.SourceA?
Also if you are using multiple initialization blocks make sure that the blocks execute in the correct dependency order.
September 11th, 2008 at 1:17 pm
Hi everyone, i have an issue with one of the requests
The Subject Area Sales has Columns Report Date and Sales Facts
Sales (Subject Area)
Report Date (Column1)
Date
Day Ago Date
Week Ago Date
Month Ago Date
Sales Facts (Column2)
Gross Sales
Day Ago Gross Sales
Week Ago Gross Sales
Month Ago Gross Sales
The Request is to see the Date, Gross Sales, any one Historic Gross Sales (Day Ago or Week Ago or Month Ago) and the Historic Date.
I created the answer request with a column selector on the Day Ago Gross Sales and added Week Ago Gross Sales and Month Ago Agross Sales as items in the selector
Request:
——————————————————————————–
Column Selector: Prior PeriodDay Ago Gross Sales
——————————————————————————–
Date——————Gross Sales————– Day Ago Gross Sales———–Historic Date
——————————————————————————–
9/1/2008————$100————————–$150————————————????????
9/2/2008————$99—————————-$89————————————-????????
9/3/2008————$35—————————-$22————————————-????????
The Column Selector works great.(AKA, when I select Prior Period as Week Ago Gross Sales, I get the Date, Gross Sales and Week Ago Gross Sales as expected.
Issue:
How would I get the Historic Date, dynamically based on the Column Selector?
ex: 1. When the column Selector, Prior Period = Day Ago Gross Sales Then I would like to see the Day Ago Dates under the Historic Date bucket
2. When the column Selector, Prior Period = Week Ago Gross Sales Then I would like to see the Week Ago Dates under the Historic Date bucket. and so on……
Can any one please help me how do i link the Historic Gross Sales with the Historic Dates
Please let me know if the above situation is not explained well and I will try to give more inputs.
Any help is greatly appreciated.
Thanks
Sai
September 11th, 2008 at 4:57 pm
Hi Peter,
Tried every conceivable way to reference the session variable, but it just does not seem to work. Yes, the initialization blocks are in the correct order - SourceA is initialized before SourceB.
Thanks,
Sarosh
September 11th, 2008 at 7:15 pm
@Sarosh
Try using ‘VALUEOF(NQ_SESSION.SourceA)’
September 12th, 2008 at 4:53 pm
Hi Peter,
Tried VALUEOF as well. I think all these expressions (Valueof, NQ_SESSION) are meant to be used in Answers only, not in the repository. I tried substituting ‘:USER’ and ‘:PASSWORD’ in the ISNULL statement in the Init Block and they are the only repository variables that could be properly substituted in the ISNULL statement. Very strange!
Anyway, we did a workaround through external database tables, creating one table for each Company, and populating each table with the users from their respective companies. Klunky, but works.
Thanks for all your help.
September 12th, 2008 at 5:26 pm
You should be able to use session variables in the RPD - it is documented… or at least quoted in an example see also http://blogs.oracle.com/siebelessentials/2008/09/#005980 - which was posted earlier today
where there is a handy guide to variable use - which shows me that I should have used double quotes; but that differs from the manual page that has single quotes around the whole ‘ValueOf(…)’ expression
But also remember that you can only define a session variable in one block - you can’t redefine in a second block
October 30th, 2008 at 8:56 pm
Hi
Thanks for this blog.
Would request your help to resolve my issue.
I have to call a function with varchar parameters. parameter are nothing but my table column values.I’ve created session variables which holds value from my table column values.
for eg:
*EVALUATE(’REP1_FIRSTMONTH_F(%1)’ AS CHARACTER ( 30 ), VALUEOF (NQ_SESSION.”month”))*
so whenever i call function using EVALUATE in OBIEE. i hve to pass ‘ ‘ (single quotes) for my varchar values. without this , i’m facing error stating :
*State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 59015] The requested column PERIOD0 does not exist in this table. (HY000)*
If i’ll give parameter within single quotes in my dashboard prompt( by entering manually) it is working fine, but fails without the quotes.
I tried giving ‘VALUEOF(NQ_SESSION.sess_Var)’ wthin quotes, but couldnt get resolved.
kindly help me in solving this issue.
Thanks for your time
Regards - Vijay
November 4th, 2008 at 8:43 pm
Maybe a simple questions but has anyone gotten Dynamic Variables refreshed on Unix and if so how?
Admin Guide says…
You will also set up a schedule that the Oracle BI Server will follow to execute the query and periodically refresh the value of the variable.
November 6th, 2008 at 9:14 pm
@Kelly
Dynamic repository variables require the Oracle BI Scheduler - there is some documentation on how to set this up on Unix in the Infrastructure installation and configuration guide.
You will need to create some tables (using the scripts supplied) for the scheduler repository - see the systems requirements and support platforms guide for acceptable databases for this - Oracle is OK though
When installed make sure that the schedule administrator user has administrator rights to the RPD file
If you have any more questions just ask
December 4th, 2008 at 9:49 am
Can anyone suggest the usage of “Request Variable” in OBIEE. I couldn’t find any documentation on this.