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

One thought on “OBI Server Splits Queries

  1. Dhar,

    I was wondering if you can help with this issue. Here is a picture of my RPD model in Oracle OBIEE 11g BI Administrator:

    http://scottysols.files.wordpress.com/2013/09/multi_fact_model.png?w=908

    It is in the format D1 < -- F1 --> D2 < -- F2 --> D3.

    In the picture you can also see the Logical Level settings for one of the Logical Table Sources and one of the Measure columns.

    When I try to create a report in OBIEE, OBIEE throws a SQL error. It doesn't understand how to relate D1 to D3. My requirement is to build an RPD that supports a report like this:

    D1 Column | F1 Measure Aggregated | D2 Column | F2 Measure Aggregated | D3 Column

    minus any cartesians. I haven't even gotten to the cartesian error (which would be a good sign I am on the right track), i just get the error:

    None of the fact tables are compatible with the query request AWARD_SUMMARY_DIM.AWARD_NUMBER

    We have also tried smashing F1 and F2 together into a single Fact within Business Model and Mapping with the same results.

    Thanks,

    S.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s