Oracle Database Express Edition (XE)

Excellent article!!!! Disk IO is always very hard to tackle and explain even to IT.

Hi Everyone,I meet the performance problem from obiee 11g.
I was using a complex view in RPD from EBS, this is a gl account balance,about 40 thousands rows.
I seperately query the view in client tool is very fast(1s),but run the report in web cost almost (47s) and I using the sql that biee generated in client is also fast.this report pass through three layer from rpd to request db.I used to try using hints to change the query optimizer execution plan but didn't work.

please provide me your advise. thanks so much.I'm looking forward to your reply!

is there any a good idea to realize a realtime report using biee rpd.

Hi Everyone,I meet the performance problem from obiee 11g.
I was using a complex view in RPD from EBS, this is a gl account balance,about 40 thousands rows.
I seperately query the view in client tool is very fast(1s),but run the report in web cost almost (47s) and I using the sql that biee generated in client is also fast.this report pass through three layer from rpd to request db.I used to try using hints to change the query optimizer execution plan but didn't work.

please provide me your advise. thanks so much.I'm looking forward to your reply!

is there any a good idea to realize a realtime report using biee rpd.

Hi Carl,

This kind of question is best posted to the forum.

thanks, Robin.

Very informant and detailed, many thanks for this.

One question, and an issue which every OBIEE developer runs into all too often - you allude to 'running very large volumes of data only to export to excel' - what would be your best practise recommendation on this, given that 'Do not do it' is rarely listened to by the client?

Many thanks,

Robert.

Very informant and detailed, many thanks for this.

One question, and an issue which every OBIEE developer runs into all too often - you allude to 'running very large volumes of data only to export to excel' - what would be your best practise recommendation on this, given that 'Do not do it' is rarely listened to by the client?

Many thanks,

Robert.

"Best Practice" is "do it with the right tool for the job and don't drag things DB -> network -> BI Server -> BI Presentation Server -> network -> browser -> desktop -> Excel".

Very informant and detailed, many thanks for this.

One question, and an issue which every OBIEE developer runs into all too often - you allude to 'running very large volumes of data only to export to excel' - what would be your best practise recommendation on this, given that 'Do not do it' is rarely listened to by the client?

Many thanks,

Robert.

Hi @"Robert Angel",

I cover some of this in my No Silver Bullets talk. Generally my advice is:

1. Don't do it - but rather than just prohibiting users, understanding what it is they're doing with the data once it's in Excel and helping them maybe do that in OBIEE instead
2. Do it smarter. The very worst thing for performance is to as the Presentation Services to generate the XLSX, because of the overhead. Use CSV instead, or use BI Publisher for larger volumes. See 1558070.1 p.13 for more details
3. You can pull data directly from the BI Server into Excel using the ODBC interface (see this blog by @"Andrew Fomin." for more info).
4. If you don't mind bypassing OBIEE entirely (and losing the benefit of your common enterprise information model, the RPD) then just hit the DB directly for the data.

It comes down to what the data is, the size of it, how frequently you need the extract, and so on.

On 2 I have advised clients to do this very thing myself, but curiously on one site ran into a problem where excel exports could handle more data than csv, which given the overhead you mention I did find very odd.

Thanks for 3 - I had not heard of this previously!

What would your comment be on 5 -  get them to use Smart View?

On 2 I have advised clients to do this very thing myself, but curiously on one site ran into a problem where excel exports could handle more data than csv, which given the overhead you mention I did find very odd.

Thanks for 3 - I had not heard of this previously!

What would your comment be on 5 -  get them to use Smart View?

Good point - SmartView can be useful, if they've already got it installed and are using it. It does have it's own limitations though, that can sometimes defeat the point of using it. But - certainly worth a try.