OBIEE query performance issue

1415129

Dear all,

          We have serval report of day granularity. 

          Because data volumn is becoming bigger, we have efficiency problem.

          We tried to seeding caching query data, it seems not working.

          We have all join field indexed.

         Is there any other way to solve the performance issue except using aggregation table?

Thanks and Regards,

Gianni Ceresa

Hi,

I would say the first thing to do is to find out where "issues" are and quantify the performance issue (often the subjective feeling of a performance issue is a lot worst than factual measures of the performances).

Once you know how much and where things happen you will find out what part would need to be improved to get the best result (the 80% - 20% rule, identify the 20% of work giving you 80% improvements instead of touching the 80% to only gain 20%).

Have a look at https://www.youtube.com/watch?v=0W34l2O9GMo

(the slides https://speakerdeck.com/rmoff/no-silver-bullets-obiee-performance-in-the-real-world )

[Deleted User]

Gianni beat me to it, but in general:

1415129 wrote: We have all join field indexed. Is there any other way to solve the performance issue except using aggregation table?

Yes about 500 different ways. And this is to 95% a database issue and not an OBI issue.

What's your precise source? DB type? Version? Etc?

have you already talked to your DBAs? THEY know - or at least are supposed to know - how to best handle performance.

1415129

@Gianni Ceresa

@Christian Berg,

     Thank you all.

     Actually, we have a very wired situation, we can not rely on our DBA team.

     I am trying to find some way to optimize our biee model, like joins, indexs etc by checking the SQL execution plan.

Alvin

[Deleted User]

1415129 wrote: Actually, we have a very wired situation, we can not rely on our DBA team.

^--- So basically your BI initiative is more or less bound to die?

1415129 wrote: I am trying to find some way to optimize our biee model, like joins, indexs etc by checking the SQL execution plan.

^--- Indexes are not "in the BIEE model". They are in the database.

"SQL execution plan" <--- mainly depends on the database. Not OBIEE.

Performance problems are not solved in a tool which is a dynamic query generator and which does not store data on its own. They are solved at the source.

Or do you want to load your whole source data into cache? .....

Thomas Dodds

1415129 wrote:I am trying to find some way to optimize our biee model, like joins, indexs etc by checking the SQL execution plan.Alvin

I get the feeling you are heading in a path that you will think of trying to force hint the use of indexes in the physical layer of the RPD or you have already done so ... this is NOT the way to go.  @Gianni Ceresa and @Christian Berg are spot on in their comments