Performence and tuning of a drill thru impromptu report from a cube

[rk2003]

Hi all,

I have an Impromptu DrillThru report from a cube, runs for very long time say more than an hour, if i run the report on the impromptu client it is taking only 4 mins to execute.
So i am trying to figure out the performence bottlenecks...
can any one sujjest me what areas I have to check to find out the problem with this.

Can any one help me out in this

Thanks in Advance.

RK

 

[rustycage]

Some tips:

Try to run an explain plan on the query from the client tool
and then from the drill-through. See if you can recognize how the query is being processed differently.

Make sure that the columns you are filtering on in the cube are properly indexed in the database.

Watch alternate drill-downs(especially dates). I have seen problems occur when a user tried to drill-through on an alternate drill down for a date dimension. Rather than doing a "BETWEEN" funtion, the SQL generated was an enormous series of DATE = 'SPECIFIC DATE' OR DATE = 'NEXT SPECIFIC DATE' and so on for every date in the range. Depending on how far down the user drilled into the date dimension, those queries may run for extended periods of time.

Hope that helps