Sunday, 29 March 2015

OBIEE 11g query tracking mechanism


We had a requirement of tracking a query fired from OBIEE. This was to help us find out the Analysis that consume the most database resources.
It is possible that some user might be selecting a huge date range resulting in the problem at the database. So the trouble in this case might be the filters put by the user and not the Analysis itself.


The solution to this problem was to introduce the following 2 columns in all analysis:

1. VALUEOF(NQ_SESSION.USER)
2. <Hardcoded name of the analysis>

This will result in the user name of the currently logged in user and the name of the analysis appearing as columns in the physical query.

The DBA can find the notorious query and inform the dev team about the Analysis causing the trouble and the user who is firing the query since the physical query will have these info in it.

Screenshot for your help.














Till next time .. 

2 comments:

Anonymous said...

Vishal,

Did you try applying the same technique to a mutli fact model wherein OBIEE generates 2 different queries for same report and performs the arrangement at server level ?

Vishal Pathak said...

Good question.

Create logical colummns in individual facts with these values and see how it works out