Hi Team,

Customer has reported that his OBIEE report is generating huge SQL for which he is facing performance issues both in OBIEE and in Database. Later on he has informed that he changed his report column filter formula which has resolved the issue as of now. But is not satisfied with the OBIEE behavior and is keeping on claiming that it's a bug.

Then I have suggested below -

===================

OBIEE generate huge sql-query that lead performance problems on database server.

There may be various reasons for this issue and are as below (which are relevant to Customer's case)-

(1) Customer's report is fetching huge amount of Data.

(2) Customer is using complex queries in column formula for which OBIEE is taking long time to compute that dynamically.

(3) Caching is not enabled.

(4) Target tables in DB are having huge data and are not indexed.

So, considering that I had suggested below -

(1) In Big reports (which are fetching huge amount of Data), Customer can apply some filters to reduce the output Data volume. Which will generate comparatively smaller SQL's and will reduce burden in DB.

(2) Don't use complex formula in column filters in report level, instead of that Customer can compute/transform those columns in ETL level and their OBIEE will get pre calculated data reducing the overhead.

    => Part of this workaround has been already verified by customer as he confirmed in the SR -

"

We found out that changes of parameters in one column filter formula of the one column of bad report resolve the issue with generation of huge sql-query.

Setting default values in column formula resolve the issue (generation of huge sql-query to DB level):

>>> cut <<<<

AND (' Balance. Calendar." "Date "BETWEEN date '@ {balance_date1}

@{2017-01-01}' and date '@ {balance_date2} {2017-01-31}'

>>> cut <<<<

Changes:

- adding {2017-01-01}

- adding {2017-01-31}

etc.

"

(3) Try to enable caching (if not done yet). Reference: How to Disable/Enable Cache in OBIEE 11g (Doc ID 1352462.1)

(4) Try to create indexes using 1 or set of unique columns (that are used in report) in DB level for faster query output. Explain plan of the physical query after column filter formula modification is available here :

https://orioniss.us.oracle.com/iss/faces/issviewer/home?context=SR&contextNum=3-14273184721&file=plan_after.html

=================

Is there anything else which we can suggest to Customer? Please advise!

Thanks in advance!