Different Physical SQL's Generated on Two Systems

Ganim Altiok

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 ?

Joel

The problem could actually lie outside of OBIEE as a matter of fact. Are both OBIEE instances querying the same data source? If not, have you checked that both data sources have the same configurations?

Ganim Altiok

Only the datasources are different but how could OBIEE knows the DB configurations and change the way to generate physical SQL?

On test system OBIEE generates two SQL's and unions them on obi server on prod instance generates only one SQL which has union in it.

Joel

Have you checked and compared the database properties in the physical layer of the rpd:

These could potentially cause such differences.

Ganim Altiok

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.      

Robert Angel

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

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.