Referencing GROUP session variable in initialization blocks for different types of authentication

I was looking and reading some threads on OTN obiee forum about GROUP session variable on how to reference it in initialization blocks when using internal authentication. When I said internal authentication I mean that we don't use external table (normal or row-wise data in table) or any other form of authentication, just define users and groups internal in RPD and assign those users to one or many groups internally, in RPD.

In this post I'll show how to reference GROUP session variable in initialization blocks for different types of authentication and whether it is even possible and if not what is the workaround for that. The idea is to use GROUP session variable in the target initialization block that returns index Yes or No if the user belongs to special group.

You may read my previous post http://108obiee.blogspot.com/2009/03/external-table-authentication-and-row.html about external table authentication and row-wise initialization in OBIEE.

Example one - users are assigned to groups internal (in the RPD)

Power user is assigned to Special Group in RPD:



Now, make one initialization block that we'll use in all cases. It will give us Yes or No indicator if the user is the member of the Special Group and will populate session variable SPECIAL_GROUP:



select
case when INSTR('VALUEOF(NQ_SESSION.GROUP)', 'Special Group')>0 then 'Yes'
else 'No'
end
from dual

We need to use INSTR because the user can be a member of many groups. In this example user Power is the member of three groups. In normal condition we could retreive this session variable and receive data in this format:

Users (A-M);Users (N-Z);Special Group

Groups are separeted by semicolons.

Log on to Answers with Power user:



Use the simple report to retrieve session variables from our initialization block:



The result is:



SPECIAL_GROUP takes default value Yes/No. This brings me the question why we cannot use the fact that we assign user to group(s) internal (in RPD) and use this information in VALUEOF(NQ_SESSION.GROUP) in initialization blocks.

We see that VALUEOF(NQ_SESSION.GROUP) is working in Answers but not in initialization blocks when we used internal (RPD) authentication, to assing user to group(s).

And now if we try to copy/paste code from initialization block for retreiving SPECIAL_GROUP variable into Answers, just replace INSTR with obiee LOCATE function:



case
when LOCATE('Special Group', VALUEOF(NQ_SESSION.GROUP))>0 then 'Yes'
else 'No'
end

This code is equal to the one from the initialization block:

case
when INSTR('VALUEOF(NQ_SESSION.GROUP)', 'Special Group')>0 then 'Yes'
else 'No'
end

And the results is:



Conclusion is that if at the end of all initialization blocks GROUP session variable is not filled (what is our example case) it will be filled with the groups that we have defined internal (in the RPD) for current user so therefore we couldn't reference it in the initialization block because it is not yet filled.

So in case that we want to use GROUP session variable for this case in initialization block and if we need this SPECIAL_GROUP session variable indicator and we don't want to have external table authentication because we use internal user - group(s) authentication the workaround is:

1. Make one initialization block for re-filling internal groups into GROUP session variable, just rewrite information from RPD for each user. This block will be execute before the target:



select
case
when ':USER'='Power' then 'Special Group;Users (A-M);Users (N-Z)'
end
from dual



Now, go to Answers and use the same report to retrieve SPECIAL_GROUP session variable from our initialization block:



The result is:



This works fine, but the VALUEOF(NQ_SESSION.GROUP) is taking groups from the RDP and the VALUEOF(NQ_SESSION.SPECIAL_GROUP) use groups filled from the initialization block b_int_rpb_user_groups.

Note that if you assign user to groups in the RPD and do the same in initialization block the first one takes priority, and you can see this if you retrieve VALUEOF(NQ_SESSION.GROUP) in Answers.

We don't want to have the same information twice or different information so we checkout groups for Power user in RPD:



Now we use groups only from the initialization block b_int_rpb_user_groups:



Example two - users are assigned to groups from external table

Assign Power user to multiple groups in external table:



Disable block b_int_rpb_user_groups from the previous example and use b_ext_tab_auth_all explained in http://108obiee.blogspot.com/2009/03/external-table-authentication-and-row.html.

This block takes information from external table and re-fill session variables USER, GROUP, DISPLAYNAME and LOGLEVEL:

select username, groupname, displayname, to_number(loglevel)
from OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'



Use this block as execution precedence in the b_int_special_group_indicator block:



Test in Answers and result:



The SPECIAL_GROUP session variable is filled correct because we used external table autentication for re-filling GROUP session variable in initialization block before.

Example three - users are assigned to groups from external table using row-wise initialization

USER session variable is authenticated (and re-filled) from normal external table:



select username
from OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'

Other session variables we re-filled from row-wise initialization:



select
session_variable_name,
session_variable_value
from obiee_row_wise_init
where username=':USER'

Row-wise external table:



The block for SPECIAL_GROUP session variable is the same like in previous examples:



Test in Answers and result:



The SPECIAL_GROUP session variable is filled correct even when we used external table autentication for USER session variable and row-wise initialization for re-filling GROUP and other session variables in initialization block before.