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.
