What is Initialization Block and session variable in OBIEE +?

How to use row level security in OBIEE ?

If you a familiar with the web development you should be aware of the concept call constructor. Constructor is automatically called when object is initiated. And object initiated means; object is assigned to the memory. Destructor is does the exactly opposite work by cleaning/freeing the memory used by object.

And object here is Session.
So long story short. You can use initialized the session variable under Initialization Block in OBIEE +.

Session variables are like a global variable. They are available across the repository.

Types of Session Variable

System: Session variable

System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and no system session variables).
Examples of the System session variable

USER Holds the value the user enters as his or her logon name.
EMAIL Contains the user’s default email address for use with Answers

Non System: Session variable

The procedure for defining non system session variables is the same as for system session variables.

A common use for non system session variables is setting user filters. For example, you could define a non system variable called   USERNAME in UsageTracking.rpd repository. So that each user can just see their usage not every one else’s Usage.

Below are the steps on how to achieve this.

Step 1: Create an initialize block called setUser

Initialization Block

Variable Manager

Step 2: Create session variable called: LOGIN

Variable Creation

Step 3: Set the filter in Presentation Layer for the Column.

Variable Filter

Step 4: Run the report in Answer to verify that filter and all is working.

Answer's Report

Wow…. We just got the data related to customer’s only

Step 5: Check the sessions

Now when report is running check in the OBIEE Administrator
go to Manage – -> Sessions

Session Manager

Great we can check the Session Variable LOGIN and its Value is Administrator which is user who is currently logged in.
It gives lots of other information about the session like
Session id which is unique for each Session
Variable contains the name of the session etc

Step 6: Check logs

Make sure that for Administrator or the user for which you want to see the logs , proper log level has been set. Logging level 5 is good enough to see the query being sent to database.

Logging level 0 Means no Logging
Logging level 7 Max logging

Please note: On production system Logging is overhead unless and until it is absolutely required.

Check Log Level

Go to \server\Log\ NQQuery.log

You will see the screen shot as attached below

Log file

Requirement : Often time its really a pain to create users in bulk.So below script will be helpful to create users with default password.

Step 1: Create the script as shown below.
This script basically creates a declaration syntex for OBIEE.

Run the SQL Command like to Generate the File Called : userImport.udml (or you may use excel or something else )

SELECT
‘DECLARE USER “‘|| Sales_person ||’” AS “‘||Sales_person ||’” UPGRADE ID 1121 FULL NAME {’||Sales_Person_Name||’} PASSWORD ”’Welcome”’ PRIVILEGES ( READ);’ FROM tbl_sales_person
Modify the script according to the usage.

save the output of the above sql command to a file called
userImport.udml

Step 2: Run the command to load the users
Once above file is ready locate the exe file called nqudmlexec.exe

(History : nqudmlexec.exe – check the name, it starts with nq. Siebel had acquired nQuire Software in 2001. Still lots of logs, exes still have prefix nq)

Run the below command to
C:\OracleBI\server\Bin>nqudmlexec -U -P
I userImport.udml -B Security.rpd -O Security1.rpd

Importing OBIEE Users to repository