OBIEE 11.1.1.7.141014 generate huge sql-query

Question

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!

John_K · Answer

have you checked the SQL that is being generated is actually meaningful? For example, is it using tables you don't expect it to? Sometimes if you get your logical model wrong then OBIEE will go "around the houses" to join two tables together, which usualyl manifests itself as you seeing a bunch of tables that aren't related to your analytic in the SQL and dire performance.

[Deleted User] · Answer

@3257772 Thanks for never closing your threads and never deigning to answer to the people spending time investigating your issue and responding. Much appreciated.

[Deleted User] · Answer

3257772 wrote: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.

Basically if you put OIEE on top of anything - database, cube, flat file, Hadoop, XML - with rubbish physical structure you will get exactly that: rubbish.

It's easy to blame a metadata number-crunching engine that does NOT store data physically again.

999 out of 1000 you'll have a very nice and well-performing system if you adjust the source to actually be a usable structure OR you put massive machines in place. Analytics isn't magic. You either have to put intelligence in place or power.