NQQuery.log File

We can find the file in the following path - InstalledDrive\OracleBI\server\Log.

NQQuery.log file holds information about the user requests.

It holds information about the request like-

1.The name of the user who sent request

2. Time at which the query issued

3. Session id, Request id.

4. SQL Request generated by the Presentation Services.

5. General Query Info such as Repository type (star or snowflake), name of Subject Area, Presentation catalog name.

6. Equivalent physical query generated by the BI server to send the underlying Data source.

7. Query status (success or failure)

8. Number of rows retrieved from database.

9. Physical query response time in seconds.

10. Physical Query Summary Stats: Number of physical queries generated, Cumulative time , DB-connect time in seconds.

11. Number of rows returned to the client.

12. Logical Query Summary Stats: Elapsed time , Response time , Compilation time in seconds etc.

 The amount of information to be stored in NQQuery.log file decides by the Logging Level of the user who issued the request.

Query Logging level can be set from 1 to 7. But the logging level 2 is

recommended as the BI server logs sufficient information for this logging level in NQQuery.log file.

As the user logging level is higher, the amount of information

stored in the NQQuery.log file will be more for each query. This decreases the

performance of server and memory space.

The amount of information stored in the NQQuery.log file about query requests

varies, that depends on the logging level of the user who has issued that

query.

For Logging Level 1, the BI server logs the following information in NQQuery.log file.

For Logging Level 2, the BI server logs the following information in NQQuery.log file.

+++jaga:2f0000:2f0002:----2012/04/09 11:58:15 ############################################## -------------------- SQL Request: SET VARIABLE QUERY_SRC_CD='Report';SELECT PROD_TYPE.ptid saw_0, PROD_TYPE.ptname saw_1, PROD_TYPE.description saw_2 FROM dbo ORDER BY saw_0, saw_1, saw_2 +++jaga:2f0000:2f0002:----2012/04/09 11:58:15 -------- General Query Info: Repository: Star, Subject Area: dbo, Presentation: dbo +++jaga:2f0000:2f0002:----2012/04/09 11:58:15 --------- Sending query to database named Prac (id: <<1150>>): select distinct D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from (select T58."ptid" as c1, T58."ptname" as c2, T58."description" as c3 from "product_type" T58 ) D1 +++jaga:2f0000:2f0002:----2012/04/09 11:58:15 ------------ Query Status: Successful Completion +++jaga:2f0000:2f0002:----2012/04/09 11:58:15 ------------Rows 3, bytes 960 retrieved from database query id: <<1150>> +++jaga:2f0000:2f0002:----2012/04/09 11:58:16 ------------- Physical query response time 0 (seconds), id <<1150>> +++jaga:2f0000:2f0002:----2012/04/09 11:58:16 ------------ Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) +++jaga:2f0000:2f0002:----2012/04/09 11:58:16 ------------- Rows returned to Client 3 +++jaga:2f0000:2f0002:----2012/04/09 11:58:16 ------------- Logical Query Summary Stats: Elapsed time 0, Response time 0, Compilation time 0 (seconds)

If the same request is issued again by any user at any time, the BI Server will not generate and send the physical query to the database. Instead, the BI Server first do the match between incoming request and the requests which are stored at Cache (called as Query Cache). If match is found, then the BI server will get the result from that cache itself and send  it to the user.This improves the query response time.

Example : Assume that, a request and its result is stored in Cache (in my case the user jaga has issued that request). Now any user, lets say Jay, issue the same request. Then the BI server will do a match with cache. Definitely server will find the match as both the request are same. So, the BI server will not generate any physical queries. Server will just retrieve the data from cache and returns to user.

For Logging Level 3, the BI server logs the following information in NQQuery.log file.

+++jaga:340000:340004:----2012/04/06 15:14:38 ############################################## -------------------- SQL Request: SET VARIABLE QUERY_SRC_CD='Report';SELECT PROD_TYPE.ptid saw_0, PROD_TYPE.ptname saw_1 FROM dbo ORDER BY saw_0, saw_1 +++jaga:340000:340004:----2012/04/06 15:14:38 -------------------- General Query Info: Repository: Star, Subject Area: dbo, Presentation: dbo +++jaga:340000:340004:----2012/04/06 15:14:38 -------------------- Logical Request (before navigation): RqList distinct product_type.ptid as c1 GB, product_type.ptname as c2 GB OrderBy: c1 asc, c2 asc +++jaga:340000:340004:----2012/04/06 15:14:38 -------------------- Sending query to database named Prac (id: <<945>>): select distinct D1.c1 as c1, D1.c2 as c2 from (select T58."ptid" as c1, T58."ptname" as c2 from "product_type" T58 ) D1 +++jaga:340000:340004:----2012/04/06 15:14:38 -------------------- Query Result Cache: [59124] The query for user 'jaga' was inserted into the query result cache. The filename is 'C:\OracleBIData\cache\NQS_NWS1018_734601_54878_00000002.TBL'. +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Query Status: Successful Completion +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Rows 3, bytes 336 retrieved from database query id: <<945>> +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Physical query response time 0 (seconds), id <<945>> +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Rows returned to Client 3 +++jaga:340000:340004:----2012/04/06 15:14:39 -------------------- Logical Query Summary Stats: Elapsed time 1, Response time 0, Compilation time 0 (seconds)

Only for some advanced troubleshooting, higher log level may be set.

Syntax for Repository Variables in different places

We can use Repository variables in Title view, Narrative view, Fx of Answers, Text box in Dashboard and SQL statements.

We can  refer Repository variables in two ways :
1. VALUEOF(VARIABLE_NAME)
2. @{biServer.variables['VARIABLE_NAME']}

VALUEOF(VARIABLE_NAME) - We can use this syntax in fx of Answers, SQL statements in Administration Tool and Direct Database Requests.

We can not use this syntax in any other places. If you this syntax in title view or Narrative view you will get abnormal results.

@{biServer.variables['VARIABLE_NAME']} - We can use this syntax in all places (fx of report, title view, narrative view, text box of dashboard and in SQL statements).

In fx of Answers : 

In Title View :

In Narrative View :

In Text Box of Dashboard :

Merge two different RPDs in OBIEE

We know that OBIEE supports two online repository (ie. two repositories can be online). But in this case one RPD will lose Oracle Product's support. For this, the solution is, Merging two repositories into one and making it online. Follow the below steps : Assume that there are two RPDs called RPD1 and RPD2 and the requirement is RPD1 should be merged with RPD2. To do this follow the below steps. Step 1 : Create one dummy Repository with a name called Dummy.rpd and close it.

Step 2 : Open RPD2 in offline mode. Make sure that, RPD2 is not being loaded into OBI server. Because we can not do any changes in offline to the RPD which is bieng loaded on OBI sever.

 Step 3 : Go to File -> Merge and select the RPD Dummy.rpd.

Step 4 : In Repository Properties,

Select the Modified Repository by clicking on the select button. In our case select RPD1 is the                                                                                                   modified RPD. Next, select the RPD name where you want to same the merged repositories. Then select the correct decision from Decision drop down. See the below image to do these things.

Step 5 : Click on the Merge button.

OBIEE Usage Tracking – Install in < 20 minutes

Thanks to Christian Screen, he has documented very good information on the Usage Tracking in this video. I found this video in his blog and posted here. Please watch the following video and ask me if you come across any doubt.

<iframe align="left" allowfullscreen="" frameborder="0" height="300" mozallowfullscreen="" src="http://player.vimeo.com/video/7547905" webkitallowfullscreen="" width="400"></iframe></div>
 

A Potted History of Oracle BI Suite Enterprise Edition

To read about history of Oracle BI Suite Enterprise Edition click on the below link.
http://www.rittmanmead.com/2007/09/a-potted-history-of-oracle-bi-suite-enterprise-edition/#comments

Using Presentation Variable in OBIEE

To use the Presentation variables in Title,Subtitle,Narratives,fx of any reports the syntax  is - @{variable_name}.

Presentation Variable in Title view : 

 Presentation Variable in Narrative view : 

                                         

But we should consider two things while using the variable in fx of request.

                                        

The Presentation Server replaces the variable name with the value associated with that variable (value selected in prompt).

So, when the variable is returning character type value, then the variable name must be enclosed in single quote. When the variable is returning numeric type value, then the variable name must not be enclosed in single quote.

When the variable is returning CHAR type value -

                                         

When the variable is returning Numeric type value - 

                                     Note :   

  Make sure that when a presentation variable is returning char type value, we should enclose it in single quote, otherwise the presentation services treats its value as logical  column name. If you have any logical column with that name then there would no issue. Otherwise you will get an error like -  Unresolved column.

Multiple values for presentation variable.

In OBIEE 10g, Presentation Variables can hold only one value even if we select more than one value from the Multi Box in Dashboard Prompts.

But11 G supports multiple values for presentation variable. That is one can send multiple values through Presentation variable in dashboard prompt. For more info go through this link - http://www.varanasisaichand.com/2011/07/multiple-values-in-presentation.html