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:
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 ..