Session variables are used in the OBIEE repository for (amongst other things) setting user context information for use in analytics and dashboards. These can either be standard variables, or row-wise variables, the difference being that a row-wise variable acts as an array, i.e. it can hold multiple values.
A standard variable may be used to hold a single value, populated by an initialization block – such as the example below which populates the user_id value from E-Business Suite.
A typical use for a row-wise variable is to hold a list of accessible elements for use in security data filters, as can be seen in the following example from Oracle BI Applications (11.1.1.8.1) where the variable BUSINESS_GROUP holds the lists of business groups that a user has access to according to their responsibility set up in E-Business Suite.
Initialization Block
Data Filter
In this example the variable BUSINESS_GROUP can take on multiple value and the data filter then expands this to an In clause in a fashion similar to the following:
Select Sum(headcount) From wc_headcount_f whf Where -- Some predicates And whf.business_group_id In (1,2,...);
I.e. the value of the variable is explicitly inserted into the SQL statement. As a side note, this can lead to performance issues (no bind variables used, in-list with many elements etc) however that is a topic that I will probably cover in a separate blog.
Anyway, onto what I actually want to show in this blog – that is the manipulation of row-wise variables to give a set of single-element variables which can be used as if they were defined individually. One might ask “Why would you want to do this?”. Well, for a start I might not know how many elements a data set might return. Let’s say I have a manager > employee relationship and I wish to populates a variable for each level in the management hierarchy when a user logs in. That user could sit anywhere within the hierarchy so I would have to define a fixed “upper” number of variables up-front. Not very scalable and also might look confusing to someone picking up your work in the future (why have you got a hundred variables defined when only 3 get populated??). So let’s take a look at that SQL statement again which is used to populate the business group row-wise variable:
SELECT DISTINCT 'BUSINESS_GROUP', TO_CHAR(FND_PROFILE.VALUE_SPECIFIC('PER_BUSINESS_GROUP_ID', USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID)) FROM (SELECT USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID FROM FND_USER_RESP_GROUPS WHERE START_DATE < SYSDATE AND (CASE WHEN END_DATE IS NULL THEN SYSDATE ELSE TO_DATE(END_DATE) END) >= SYSDATE AND USER_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_USER_ID) ELSE (SELECT USER_ID FROM FND_USER WHERE USER_NAME = ':USER') END) AND RESPONSIBILITY_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) ELSE RESPONSIBILITY_ID END) AND RESPONSIBILITY_APPLICATION_ID = (CASE WHEN 'VALUEOF(NQ_SESSION.EBS_SSO_INTEGRATION_MODE)' = 'Integrated' THEN VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) ELSE RESPONSIBILITY_APPLICATION_ID END)) See what it’s doing? The first column BUSINESS_GROUP is the name of the variable and the second is the value. So what if we were to generate the name dynamically? I.e. :
Select 'XXMY_VARIABLE' || To_Char(Level) var, 'Value ' || To_Char(Level) val From dual Connect By Level <=5;
What the above gives us isn’t one variable with a set of values, but a set of variables named XXMY_VARIABLE1 to XXMY_VARIABLE5 with a single value each.
So why might this be useful? Well, in the first instance for the situation I’ve already described where we have an unknown number of hierarchical levels, this technique provides a scalable solution with no need to make changes to the RPD if that number increases in the future, but secondly, it allows for a mechanism to be created for storing fixed value constants outside of the RPD whose values can be maintained independently of an RPD deployment. Let’s assume a simple structure like this, created in the E-Business Suite ERP source system and maintained using a custom form:
Create Table xxobiee_parameters ( param_name Varchar2(30) Not Null, param_value Varchar2(240), -- Usual audit columns etc );
Now a simple initblock as follows will load any parameter entered into that table as a single-value variable.
Select 'XXGENPARAM_'||param_name param_name, param_value val From xxobiee_parameters;
I’ve prefixed with XXGENPARAM_ purely so that we can be sure we don’t also create a variable within the RPD directly with a name that conflicts with one created here. You could of course still do this in theory, however it simply requires an agreed standard to not use that prefix for variable names.
One may also wish to take this a little further and allow for user-specific values. However if that is the case, my suggestion would be to make use of E-Business Suite functionality that exists already – profiles values. This would provide a generic mechanism for surfacing user profile values without having to make a single change in the RPD after the initial setup. Ideally we would flag the profiles to extract so as to avoid loading unnecessary data, however due to the lack of a descriptive flexfield on profile options an alternative solution must be used. This can be anything you wish, an application lookup, value set, custom table etc. For the purpose of this example I will use a common lookup:
Select 'XXPROFILE_' || fpo.profile_option_name, fnd_profile.value(fpo.profile_option_name) profile_val From fnd_profile_options fpo, fnd_lookup_values flv Where flv.lookup_code = fpo.profile_option_name And flv.lookup_type = 'XXOBIEE_PROFILE_VALUES' -- View application, security group etc. ;
Remember to ensure you set the execution precedence of this initblock to run after the E-Business Suite authentication initblocks.
You’ll need to decide for yourself how useful this may or may not be to your organization. It is of course true that quite often profile values need further processing on them to be useful (such as the extrapolation of ledgers from a data access set), however as you can see this is very quick to set up and even easier to maintain for those odd occasions where such functionality is needed.