OBIEE11g: How to set Logging Levels for all the Users using a System Session Variable (or) Creating or Configuring or Setting LOGLEVEL system session variable in OBIEE or LOGLEVEL Variable in OBIEE

Recently i got a requirement to add the logging level as 5 for all the users. We can get this done by creating a 'LOGLEVEL' session variable. In this post we will see how to set a common logging level for all the users using LOGLEVEL session variable.

We know that Logging level can be defined for each and every user in the RPD separately. To set the logging level for an individual user we should open the RPD in online mode--> click on Manage-->Identity-->select the particular user and double click on the user then we can see the Logging levels in the General tab-->we can set the logging levels from 0 to 7. The description of the each and every logging level is given below

Logging Levels:

Level 0
No logging.
Level 1
Logs the SQL statement issued from the client application. Also logs the following:

• Physical Query Response Time — The time for a query to be processed in the back-end database.
• Number of physical queries — The number of queries that are processed by the back-end database.
• Cumulative time — The sum of time for all physical queries for a request (that is, the sum of all back-end database processing times and DB-connect times).
• DB-Connect time — The time taken to connect to the back-end database.
• Query cache processing — The time taken to process the logical query from the cache.
• Elapsed time — The time that has elapsed from when the logical query is presented to the BI Server until the result is returned to the user. Elapsed time can never be less than response time, because elapsed time takes into account the small extra time between the logical query being presented to the BI Server to the start of preparation of the query. In cases where this delta time is negligible, the elapsed time equals the response time.
• Response time — The time taken for the logical query to prepare, execute, and fetch the last record.
• Compilation time — The time taken to compile the logical query.
• For each query, logs the query status (success, failure, termination, or timeout), and the user ID, session ID, and request ID.

Level 2
Logs everything logged in Level 1.
Additionally, for each query, logs the repository name, business model name, subject area name, SQL statement issued against the physical database, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application. Level 3
Logs everything logged in Level 2.
Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails. Level 4
Logs everything logged in Level 3.
Additionally, logs the query execution plan. Level 5
Logs everything logged in Level 4.
Additionally, logs intermediate row counts at various points in the execution plan. Level 6 and 7
For future purpose

About Session Variables:

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 non-system session variables).
System session variables obtain their values from initialization blocks.
Every active BI Server session generates session variables and initializes them. Each session variable instance can be initialized to a different value.

LOGLEVEL Session Variable:

The value of LOGLEVEL (a number between 0 and 5) determines the Logging level that the Oracle BI Server will use for user queries. This system session variable overrides a variable defined in the Users object. If the Administrators Users object has a Logging level defined as 4 and the session variable LOGLEVEL defined in the repository has a value of 0 (zero), the value of 0 applies.

Creation of LOGLEVEL Session Variable:

Step 1: Open the RPD in Online or Offline mode (I have done this complete exercise in online mode only)

Go to Manage-->Variables-->Click on System in the left side--> Just do right click on the right side empty space--> from the list of the session variables select 'New LOGLEVEL' as shown in the below screenshot..

Step 2: In the Name enter as LOGLEVEL (case sensitive) and check in the option Enable any user to set the value as shown in the below screenshot

Click on New button of the initialization block as shown below

Give the initialization block name as you like in my case it is LOG_LEVEL and click on Edit Data Source button as shown below

Now select the radio button which says Use OBIEE Server as shown below

Now write the sql query (Select 5 from Sales) as shown in the below screenshot. As i am using the OBIEE server i referred the table from the RPD only . The table (Sales) which i used in the query is in the RPD. You can use any table from the RPD to create this sql query.

Click on Test. You can see LOGLEVEL variable value as 5 as shown in the below screenshot

Again click on Test button as shown in the below screenshot. You can get the below screenshot.

As it is a system defined (System reserved) variable one pop up will be displayed with a warning message as shown in the below screenshot, Just click on Yes

Now set the default value as 5 as shown in the below screenshot and click on Ok

Step 3: for testing purpose select few users and look at their logging level (It can be done only in the online mode) . Manage -->Identity-->Action-->Set Online User Filter--> give the value as * and hit Enter or click on Ok.

In the below screenshot we can see the logging level for the weblogic user is set to 0.

 In the below screenshot we can see the logging level for the Nagarjuna user is set to 0.

Now do the consistency check of the RPD and Save it. If any errors are there then we need to fix then before saving the RPD.

 Step 4:Now log in to the analytics using weblogic user and create one sample report or add any columns to the criteria tab and click on Results. You can see the results of the columns selected as shown in the below screenshot

Click on Administration -->Manage Sessions--> Click on View log of any session query as shown in the below screenshot.

Now you can see the query log file even though the logging level of the particular user is set to 0.How it is getting it means the default logging level (0)of the user is overwritten by the LOGLEVEL variable value (5).

Do sign out once and Now log in to the analytics using Nagarjuna user and create one sample report or add any columns to the criteria tab and click on Results. You can see the results of the columns selected as shown in the below screenshot

Now you can see the query log file even though the logging level of the particular user is set to 0.How it is getting it means the default logging level (0)of the user is overwritten by the LOGLEVEL variable value (5).

Hope this helps you........

Thanks,

Nagarjuna

OBIEE11g: How to Use Prompt as Report Selector or Using a Prompt as a Report Selector in OBIEE11g

Hello Everyone...

Recently many people started asking me to write a post on Using a Prompt as a report selector in OBIEE11g. We all know it is not a new requirement, It is most common requirement in many of the project. This is a bit tricky in 10g version but in 11g this is a simple task. In 10g version we are using "Guided Navigation Link" to get this done. In 11g version we can do it very simply using the "Section --> Condition" option in the dashboard. In this post we will see how to do it....

Scenario: We have three reports, for example 1) Yearly Revenue Report 2) Quarterly Revenue Report 

3) Monthly Revenue Report

The reports should be displayed on the dashboard as per the user selection i.e there should be a prompt which displays the three reports names depending on the report name that the user selected in the prompt the respective has to display. If the user selects Year value in the prompt then only year report has to display, If the user selects Quarter then Quarter report has to display and so on...

Here the user can have two choices either he can able to see only one report at a time using Radio button prompt or he can able to see multiple reports at a time using choices list prompt. In this post we will see the second option (Selecting multiple reports or Single report at a time). This is easy in 11g because of the Presentation Variable property, like i discussed in my earlier post the Presentation variable in 11g can capture multiple values. Please follow the below steps to do get this requirement....

Step 1: Creating the Reports

I am going to create three reports now Yearly Sales, Quarterly Sales and Monthly Sales as follows

Step 2: Creating the Dashboard Prompt

Create a new dashboard prompt by clicking on 'New" and then by selecting "Dashboard Prompt" as shown in the below screenshot

Now select the Subject Area from which you want to create the prompt. In my case i have used "Sample Sales Lite"

Click on the Add (+) icon and select Column Prompt

Now select any column from any of the tables because we not going to use any column values here we will create one dummy column and Hard code the report names as values.

I have selected the Per Name Year column and renamed it to "Select the Report" as shown in the below screenshots. Select the User Input as "Choice List" (By default it would be Choice List only)

Click on the Edit formula icon of the prompt and in the column formula.

Just enter any string value in the single quotes and click on Ok. In my case it is 'Dummy'

Now expand the Options ans select the "Choices List Values" as Specific Column Values as shown in the below screenshot.

Now Click on Edit icon and enter your report names as values of the prompt as per the requirement.

In my case i have given the report names as Year, Quarter and Month in the prompt. Here the report names are nothing but values of a column.

After entering the values click on Ok

Depending on your requirements select the options "Enable user to select multiple values", "Enable user to type values" and "Require user input".

Here very important thing is defining a "Presentation Variable" . In Select a Variable section select "Presentation Variable" then give one name in the below box. In my case i have created a variable called "Select" . All the presentation variable names are case sensitive so we need to be careful while creating a filter with presentation variable. You can see i have used the same name of the variable everywhere (Select) with the same case how ever i created it.

Enable user to select multiple values : If you need to enable multi values select option for the user you must select this option. In my case i would like to give the option to the end user to select multiple values so i enabled it by clicking on the check box.

Enable user to type values: Some times the user would like to type in the values like a text box field so this check box enables that feature so that the user can directly type the values with out selecting it from the prompt drop down.

Require user input: If you would like to make this prompt as a mandatory selection the you can select this check box. This option will disable the "Apply" button if the user is not selecting any value from the prompt.

After selecting all the required options save the prompt by clicking on Save icon as shown in the below screenshot.

Step 3: Creating the dummy reports for section condition validation.

Now create 3 dummy reports one for Yearly report , one for quarterly report and one for monthly report as shown in the below screenshots.

We have pull any column from any table to the criteria tab and go to Edit Formula and just give the Report name what ever you have given in the Prompt. The Report name that we have given in the Prompt and the value we are giving in this dummy column should be the same.

Remove the existing name give the new name as per the prompt value

 In my case i have given "Year" as a value in the prompt and the same i have given in the edit formula in the single quote as it is a string.

Now click on the filter of the column properties.

Select the operator as "Is equal to / Is in" then click on "Add More Options" and select "Presentation Variable" 

Type the presentation variable name that we have created. In my case it is "Select" (Case sensitive) and click on Ok.

After doing this you can see the filter as follows. Then save the report. In my case i have saved this report as Year Dummy.

Repeat the same process for creating Quarter Dummy report and Month dummy report as shown in the following screenshots.

By the end of this step you will have three dummy reports created Year , Quarter and Moth.

Step 4: Creating a New Dashboard to Place these reports.

Click on the New icon and select the "Dashboard" option.

Give the dashboard name. Here i have given the dashboard name as Report Selector. You can see the same on the following screenshots

Now add the content to the dashboard as shown below

Add four sections one is for Prompt , one is for Year report, one is for quarter report and one is for month report.

Now drag and drop the prompt and reports in the respected sections. Better to follow the order either ascending or descending because if you dont follow the order then the reports will display in jumbling manner when the user is selecting all the values in the prompts. I followed the descending order here so if we select all the values in the prompt then first Year, next Quarter and last Month Report will come in the order.

Now click on the section properties of the Year report section and select "Condtion"

Now click on Filter icon.

select Analysis and  click on Browse

select the Year Dummy report that we created in the previous step and click on Ok.

Click on Ok

Click on Ok

Repeat the same process for Quarter report section and Month Report Section

After adding the section conditions to the three report sections save the dashboard by clicking on the Save Icon and click on Run.

Now on the dashboard we can see only the prompt with three report names as check boxes as follows.

Now select the Year option and click on Apply ......

You can see only Year report on the dashboard

Now select the Quarter option and click on Apply ......

You can see only Quarter report on the dashboard

Now select the Month option and click on Apply ......

You can see only Month report on the dashboard

Now select all the three options and click on Apply ......

You can see all the three reports now ..... Year report, Quarter Report and Month Report on the dashboard in the same order how we kept the sections in the dashboards.

This is the continuation of the above screenshot.....

Hope this helps you.............

Thanks,

Nagarjuna