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.