Obiee 11g: After upgrade from 10g, 11g report generated UNION ALL in SQL
OBIEE CPU Patches — Where To Find OBIEE 10g and 11g Critical Patch Updates (CPU) Patches And/Or Security Alerts
June 17, 2012Upgrading OBIEE 10g Repository To 11g Using The Upgrade Assistant ( UA ) Fails To Start The BI Server
June 29, 2012Applies to:
Business Intelligence Server Enterprise Edition – Version: 11.1.1.3.0 [1905] and later [Release: 11g and later ]
Information in this document applies to any platform.
Symptoms
1) Customer upgraded 10.1.3.4.1 RPD and web catalog to 11.1.1.3.
2) Its noticed that in 11.1.1.3 the same reports take a lot longer to execute:
Eg: 1 min 30 sec in 11G vs. only 30 sec in 10G.
3) RPD doesn’t have any metadata inconsistencies, connection pool settings are good.
4) The memory usage spikes up when reports are run, and BI Server is taking too long compiling these reports.
5) Customer notices that the XML copied from 10g to 11g creates a more complicated logical query in 11g compared to 10g.
6) When customer copies the logical query from 10g and runs in 11g it gives the same performance as 10g.
Cause
Basically this is caused by many column with the empty string formula: ”, causing it to have 2nd UNION leaf to calculate the subtotal in 11.1.1.3.
Solution
These columns with a column formula ” where used as separators in the report.
Replaced all column separators with “duplicate measure” columns with a column length of 0.
This ensures the subtotals are NOT trying to use the separators as dimensions and create extra queries with UNION ALL.
