Different Physical SQL's Generated on Two Systems

Question

Hello All,

We have a test and prod environments (12.2.1.3) which are almost identical systems except connection pool data source names.

So we have the same report on two systems and almost the same NQSConfig.INI (prod has usage tracking).

But these systems generate different SQL's.

Things I checked:

* RPD Database Types 
* Connection Pool Features 
* NQSConfig.INI
* Moved report from test to prod (Archive-Unarchive)

So is there anything else I should check to how OBIEE generates physical SQL ?

Ganim Altiok · Answer

OK, I figured it out.

I disabled caching on our test system as it is on our prod system and now the generated sql's are the same.

In our report pivot table we selected to Show Null Rows, so when I enable cache obiee throws a separated select to the dimension then unions its result on logical level but when I disable cache as there is no cache file, obiee sends an sql with union.

Thank you for your help.

Robert Angel · Answer

Not all databases support the same features, so if, for example one source is MySQL and the other Oracle DB then the results could be very different in terms of the physical sql, OBIEE compensates for limitations of underlying databases.

Ganim Altiok · Answer

Sure thing! Both are Oracle 11g and all set default and the same values.

I forgot to mention that on prod environment caching is disabled. I am going to try to disable caching on test env. too.