Variables Part1: Static and Dynamic Repository variable

Variables are used to store a value. They contain value in the memory that is used by Oracle BI server during its processing.

Types of Variables in OBIEE:

1) Repository Variables
2) Session Variables
3) Presentation Variables
4) Request Variables

Repository and session variables can be used in both web catalog (Analytics) and Repository. These are defined inside the Administration tool under variable manager.
However, Presentation and session variables can be used in  web catalog (Analytics) only.

1) Repository Variables

• Open the repository in Online mode
• Sub-types:

1. Static
2. Dynamic
1. Static Variables

•               Using the Static variable at catalog (Analytics) level

  These variable are used when we want to pass a constant value multiple times at different places. Open the repository in online mode --> Manage --> Variables... ---> Repository -->Variables -->Static ---> Rigth click on blank section-->New static variable -->

Check in changes & save.

Go to analytics, reload server metadata.

Test the static variable:

Go to analytics-->New-->Analysis --> sales--> select Year, region, revenue.

Add filter on Year -->Add more options-->Repository variable --> Type : "V_CURR_YEAR" in repository variable textbox-->ok.

Go to results tab --> the data will be displayed for only year 1999 as per the value of static variable in the repository.

                      

•               Using the Static variable at repository level

In BMM layer , the customer data comes from D1_CUST_PART1 and D1_CUST_PART2 tables.

Part1 table has all customers whose first name is less than 'N' and Part2 table has all customers whose names is greater than or equal to 'N'. 

This 'N' may change in future. instead of hard coding the value as 'N' we will use  static variable whose value is 'N'.

Now create a static variable as shown below:

Now go to BMM, double click on D1_CUST_PART1 in sales-->Content tab--> replace 'N' by variable V_VAR.

Do the same for D1_CUST_PART2 table.

Check in changes & save.

Go to analytics-->Develop a new report with Name & Revenue columns.

Add filter on Name column -->Value less than 'K'.

Go to results tab--> the data is fetched from D1_CUST_PART1 since K is less than 'N'.

2. Dynamic Variables

Dynamic variable get their values from initialization blocks. This initialization block is nothing but a variable that holds results of a SQL query and passes it to the dynamic variable. The Oracle BI server has to refreshed in order to assign value to Initialization block.

To create a dynamic variable do the following:

Go to Manage--> Variables...-->Repository-->Variables-->Dynamic

Now right click on the blank page-->New dynamic variable-->

Name: V_PF; 

Initialization Block: Click on New... button

(Repository Variable initialization Block window appears)

Name: IB_PV

Default Initializer: '&' (To avoid errors in report when the SQL query fetches null values)

Now click on Edit data source:

Data source type: Database as shown below

Default Initialization string: select min(substr(name,1,1)) from D1_CUST_PART2
(This fetches first letter of name in CUST_PART2 table i.e.'N')

Connection pool: Orcl.connection pool

Upon clicking "Test" the value of  'N' for V_PF.

click ok and exit from Repository Variable initialization Block window.

Click ok again. 

From the drop down select "IB_PV" as Initialization block.
Check in changes and save.

Assigning dynamic variable to dimension tables:
Now that we have created the V_PF dynamic variable, we will assign this to D1_CUST_PART1 table as shown below:

Do the same for D1_CUST_PART2 as shown below:

Now, go to Analytics,create a new report with a filter as follows:

Go to results tab and you will see the data with name only starting from Letter 'A' and 'B'. Now, click on Administration--> Under session management click on Manage sessions-->Go to the recent query generated-->View log-->

 The query generated shows us that the data was fetched from D1_CUST_PART1.
This is how the dynamic variables are used.
We can also use Dynamic repository variables in filter as shown below: