OBI Server Splits Queries

A Grand Total or Measures at different grains in an analysis is quite a common requirement for BI reports. If we are using Oracle Business Intelligence tool, it is quite smart and intelligent to combine multiple sub-queries for each request into a single query and fire it to the database. The database being robust (and supposed to be) and just because database guys have always been my best friends, I have been pretty happy to see this kind of intelligent query design, and all the query processing load being transferred to the database. However, we might have seen this might not be the case always (yes, even after enabling WITH clause in db features of the database).I have seen a case myself recently,when the BI Server thought of splitting the single request to multiple and joining the record sets in its engine.
We can force the BI Server to always try to keep it as a single query by adding the following parameter in your NQSConfig.ini  
DISABLE_SUBREQUEST_CACHING = YES 
So if you notice from this parameter, we can understand that the BI Server has a tendency to split queries and cache them individually and combine on demand, which I think is again another smart way of working on a request. So, it’s upto us to choose on this processing.
NOTE: This parameter is only to direct the BI Server not to split queries. So it means, you could still see some queries split up if the Server does not think if it can combine.
Hope you found this useful and till I catch up again, Adios.

Advertisements