Drill Through Performance

[jguevin]

As an administrator, what ca I do to increase the performance of drill through reports?  We are on 8.4 and have a parent report that has 3 different drill options in it.  One of the 3 has the option to drill 5 different ways, or 5 different children reports.  In testing these one of these 5 we have experienced wait times of 4-5 minutes for the child report to run.  Our data source is a package built from a relational star schema database.  I'm trying to locate the bottleneck by viewing the taskmgr of the app server and web server to see where the processing is taking place.  Does anyone have a suggestion?  My first reaction would be to use a cube as a data source instead so the drill downs levels are already in place.

thanks
jeff

[AussiePete2011]

Hi there,

Is this 8.4 Powerplay or Report Stiudio?
I know that there are performance issues with 8.4 Powerplay with Drill through that have been resolved in a hotsite.  Not sure if that hotsite will roll up into the next fixpack though.  You'd have to log a PMR with IBM to ask.

If its Report Studio, then it will depend on the data source... Is it a powercube, OLAP source or relational source?
You may need to turn on some tracing to see what's happening under the covers.
What type of loads are you seeing on the BI Servers?

Cheers
Peter B

[jguevin]

Thanks Peter,
It was a drillthrough in RS.  The data source is relational.  We did learn a valuable lesson.  The source of the child report query is sql hitting our star schema directly.  However the parameter was applied after the query was run (and the query returned a large set of data).  So we embedded the prompt in the sql itself and changed it from Native sql to Pass Through sql and the report now runs in 30-40 seconds.  A huge improvement.