OBIEE Variable
Variables used to hold a value at any point of time.
In OBIEE, there are 4 different types of variables. They are
1. Repository Variables
2. Session Variables
3. Presentation Variables
4. Request Variables
Initialization block: Initialization block is used for assigning a value to a variable.With the help of initialization block,value of the variable refreshed dynamically.
To create a new initialization block, Select Manage and then choose variables
Once initialization block is selected, following screen appears:
Initialization block is scheduled for a time which is 10:08:54 AM daily. The frequency for this refresh may be after evey hour, so that after every hour the queries will be executed and variables will be refreshed with the value that a query returns.
Edit data source: Edit data source has the queries of the initialization block and the connection pool details through which connection to database is established. With the help of connection pool, queries in an initialization block will be executed against the database.After clicking Edit data source, the following screen appears:
Edit data target: Using Edit data target , variables can be created for the initialization blocks. To create a vairable, click New then the variable dialog bog will appear
Following Variable dialog box appears, Name, type, initialization block and default initialization should be given.
Repository Variable: Repository Variable holds a value that will be same for all users. There are two types of repository variables:
1) Static Variables
2) Dynamic Variables
Static Variables : Static variables holds a constant value which will not be changed frequently or any point of time. Its value can be changed by administrator.
Dynamic Variables : Dynamic Variables hold a value that can be refreshed dynamically. The values populated to these dynamic variables using initialization block query.
Creation of Repository Variables:
To Create a variables, Click Manage and Select Variables
Then the following dialog box appears. To create variables, Select Action and Choose New -> Repository-> Variable
How to set the variable type:
Once the variable is created then the variable dialog box appears, then we can set the variable type, can select static for static variable or dynamic for dynamic variable
For a dynamic variable, initialization block has to be assigned, which will have queries that is executed at different intervals of time.Static variables will hold constant values rather than values that would go for a change frequently.
How to uses repository variable in repository
How to use repository variable in report column formula:
 |
How to use variables in prompts
How to use repository variable in report filter:
In analysis, we can filter the report with advanced option. In advanced we have three types of variables: Presentation, Repository and Session Variable. Variable name should be provided.
How to use a repository variables in Views
In views like static/Narrative view, repository variable can be accessed with the following syntax:
@{biServer.Variable[‘variable name’]}
Session Variables:
Session Variables holds different values for different user. Its value may vary across user sessions. Session variables generally used for security purpose. There are two types of session variables:
1) System Session Variable
2) Non system Session Variable
Initialization block : Initialization block populates the value for the variable. Click Manage and Select Variables. Then the following screen appears, Click Action ->New->Session->Initialization Block
System Session Variables: System session variables holds some reserved names. It cannot be used for other purposes.Values populated to these variables using initialization blocks. Each and every active BI Server will generate session variables and initialize them.
Example : USER, LOGLEVEL,PROXY etc
These variables used by BI Server and BI Presentation Server for specific purpose.
Non System Session Variables: Non system session variables generally used for security purpose since it holds different values for different user. Values populated to these variables with initialization blocks.
Example : Let us consider a geography authorization is implemented. In this case lets assume there are
Geography :Pakistan & Singapore.
Users : User1 & User2
And in this case User 1 should be able to see sales related to Pakistan and User 2 should be able to see only the sales happening at singapore.
When user logins into the application, we can retrieve the groups associated with the user using session variables. With this session variable value (Pakistan), filter can be applied to the facts and dimensions. Therefore both dimension and fact will only display Pakistan data for the user.
Creation Of Session Variables:
Row wise initialization :
If a variable is expected to hold more than one value or array of values then row wise initialization option is enabled in initialization. With this row-wise initialization, a variable can hold array of values.
Use Caching
if cache is enabled, then the request of the query will be stored in the server.
Presentation Variables :
Presentation variables is created in the OBIEE Application and it is not created at the repository level.
Presentation variable is created using Column prompt or Variable Prompt. Presentation variable holds more than one value. It holds values that a user selects from the prompt.
Example : A column prompt is created for Year. And here Sel_Year presentation variable is created using set a variable option. And it holds the year that users selects from the prompt.
Example : A variable prompt is created for presentation variable Sel_Year.By default, it hold the year values. And it will hold the year that users selects from the prompt.
Presentation variable can be used in report filter:
Presentation variable can be used in static/narrative views as shown below:
Presentation variable can be used in a report column formula as shown below:
Presentation variable can be used in a report column heading as shown below:
Request Variable: Request variable is created with the help of the column prompt. With Set variable option in column prompt, request variable is created and initialized. Request variables override the values of session variable.
Let us consider we have a session variable type which holds the value ‘1’. And we have created a request variable with the name ‘type’. This type will hold a value that is selected by the user from the prompt.
A report is filtered with this type session variable. But prompt will have this request variable, which holds the value that is selected by the user from prompt.
When dashboard is executed, the report will be filtered with the type variable. but the type will hold the value that is set by request variable rather than session variable.
In general Request variable is used to override the value of session variable.
No Comments