OBIEE Query LOGLEVEL

To analyze or diagnose OBIEE report performance we need to see its log files.These diagnostics will be shown to us depends on the LOGLEVEL we set for the user.

The following Oracle note gives us a clear understanding of each LOGLEVEL we set for the users.

Query Logging Levels:

Logging Level	Information That Is Logged
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. This matches the TOTAL_TIME_SEC that is logged in usage tracking. 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. Do not select this level without the assistance of Oracle Support Services.
Level 4	Logs everything logged in Level 3.   Additionally, logs the query execution plan. Do not select this level without the assistance of Oracle Support Services.
Level 5	Logs everything logged in Level 4.  Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Oracle Support Services.
Level 6 and 7	Not used.

Reasons Why a Query is Not Added to the Cache

Sometimes even though we enabled cache in all areas, still few reports doesn’t hit Cache.I have noticed some valuable information related to Cache management in Oracle documentation.

Using logging facilities we can diagnose common reasons for getting a cache miss, where the logical SQL query that was supposed to seed the cache did not get inserted into the cache.

The following describes some situations when this might occur.

• Non-cacheable SQL element. If a SQL request contains CURRENT_TIMESTAMP, CURRENT_TIME,RAND, POPULATE, or a parameter marker, then it is not added to the cache.
• Non-cacheable table. Physical tables in the Oracle BI Server repository can be marked ‘non-cacheable.’ If a query references any non-cacheable table, then the query results are not added to the cache.
• Cache hit. In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache.The exception is query hits that are aggregate roll-up hits. These are added to the cache if the NQSConfig.INI parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to YES.
• Result set is too big. This situation occurs when you exceed the size set in DATA_STORAGE_PATHS, or if you have rows in excess of the number set in MAX_ROWS_PER_CACHE_ENTRY.
• Query is canceled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through timeout.
• Oracle BI Server is clustered. Queries that fall into the ‘cache seeding’ family are propagated throughout the cluster. Other queries continue to be stored locally. Therefore, even though a query might be put into the cache on Oracle BI Server node 1, it might not be on Oracle BI Server node 2.

Level 4 of query logging is the best tool to diagnose whether the Oracle BI Server compiler intended to add the entry into the query result cache.

Varchar2 greater than 4000 in OBIEE

I came across one requirement where one column having approximately 7000 char length when we trying to convert that CLOB field into varchar.But as we know oracle has limitation on varchar2 length upto 4000 chars only.

So the workaround I have approached is:

Split the 7000 char clob column into two varchar columns in RPD using DBMS_LOB.substr() function

Syntax for DBMS_LOB.substr() is:

DBMS_LOB.substr(<table.columnname>,<length of characters>,<start position>)

Column1: This brings first 3500 chars

CAST ( EVALUATE(‘DBMS_LOB.substr(%1,3500,1)’, Customers.”Customer Segment”) AS VARCHAR ( 3500 ))

Column2:  This brings Next 3500 chars

CAST ( EVALUATE(‘DBMS_LOB.substr(%1,3500,3501)’, Customers.”Customer Segment”) AS VARCHAR ( 3500 ))

Now in Answers drag these two columns into criteria and one more column:

In 3^rd column-> go to column properties-> data format and select Treat Text As – Custom Text Format.

And in Custom Text Format give the text liek this:@[html]@1@2

Where:

@1 and @2 are column positions in answers criteria.

In this case BI server doesn’t include the 3^rd column in query to DB as we are Camouflage the concatenation  of columns based on browser capabilities i.e. using HTML which will show the exactly how we do string concatenation.

All choices for Edit box control in OBIEE Dashboard prompt

I have seen so many questions regarding Edit box control in Dashboard prompt for getting ‘All Choices’ like drop down list control when you have a presentation variable to carry the prompt value to a report.

To achieve this we have had enough techniques from our vast OTN and BI blogs.

As usual apart from that I want to contribute my 2 cents worth solution to our BI world.

When you set the edit box prompt value to a presentation variable in dashboard prompts you don’t have any option to set to “All choices” by default, So at this stage as we don’t have any value for prompt our report will return No results message until and unless if you have set to any default value.

So in this scenarios if we want to show the report for all values just put % symbol in Default to ->Specific value box.

Examples for Dashboard prompt:

Here % means “All values” for Edit box in OBIEE language 

For eg: I have an answers Report with Year and Revenue columns and having a filter on Year column with Presentation variable var_year.

Now I placed these prompt and report in a dashboard page. By default my report is showing full data without filtering i.e. our prompt is giving all values to report.

That’s it by default our prompt is sending all values to filter