BusinessObjects Board

Adding a universe filter into report cause time blowout

Hi all,

I have a report which runs across an Oracle Financials universe that I recently created.
The universe is a Linked universe with the master and 6 slave universes. The slaves are topic specific, GL, AP, FA etc and are linked together with joins via the master.

The problem is, and I don’t believe that it is the design, is that I have a requirement in a report to drill down to Cost Center, Function and Natural, no big deal, done in many other universes in the past without a problem.

Except in this universe. I have a derived table that is created from a schema called APPS (those familiar with Oracle Financials should know this one.) . The table is aliased in three times, once for each field, no bid deal. I can run a query on the table and put a field on say Natural = 1100 and the query returns in an instant.
However I am using XI R2 and when I add a prompt, it obviously needs to be populated, and I need to be able to select one, many or all of the three fields mentioned.
Herein lies the problem. I have tried two different ways of create a prompt that uses the ‘*’ as the wild card, both of which work perfectly in every other universe I have, but in this universe, because the filter has the ‘OR’ operator, for some reason the run time is blown out from milliseconds to around 9.5 minutes.

I am at a loss, I have never experienced this before and hope that someone may be able to answer my question as to what may be causing this.

Thanks


plessiusa :netherlands: (BOB member since 2004-03-22)

just throwing this out there since I had a similar problem years ago, OR clauses can negate any indexes on that column (possibly other types of optimizations e.g. partitioning)

check with your DBA. get them to run an explain plan with and without the ‘OR’

if it is the ‘OR’, what can you do? if you have a good DBA, they should be able to help you rewrite the SQL. I know thats not always possible in BO

alternatively post the SQL with some description and let us geeks have a go at it


caledonia :us: (BOB member since 2009-09-10)

Hi thanks for the reply.

Thankfully for me its no longer a problem as I don’t work for that organisation anymore.

It now becomes a problem for them to work through should they deem it necessary.


plessiusa :netherlands: (BOB member since 2004-03-22)

Crickey, I hope one wasn’t related with the other? :blue:


ErikR :new_zealand: (BOB member since 2007-01-10)

Haha,

No, my contract came to end and wasn’t renewed, but I guess after three years on one site, its probably good to move on as it was becoming a little stale.

Hows life in Wellington. I here that you’ve had some mighty frosts in the last week.


plessiusa :netherlands: (BOB member since 2004-03-22)