Slow report performance

[gdmort]

Hi Everyone,

I'm fairly new to Cognos and so I'm hoping the solution to my problem is an obvious one. We have a Cognos PowerCube which contains the following hierarchy: Section -> Sub-section -> Book -> Trade. At a trade level there are quite a number of records being returned (10s of thousands). I decided to create a report which shows the measure aggregated to book level and have a drill-through when the user clicks on a specific book that will run a sub-report showing the trades in that specific book. To achieve this I created the following slicer on the sub-report and pass the selected book through the the pBook parameter:

Code Select Expand

[PnL Cube].[Headings].[Headings].[Book] -> ?pBook?

It works fine, but it runs a lot longer than I would expect. It takes about 40 seconds to generate a cross-tab containing only 8 rows. When I build the same crosstab in Analysis Studio and drill down to the same book, the 8 trades come back almost instantaneously. To me this suggests I'm doing something wrong in Report Studio rather than a there being an issue with the cube. One theory I have is that the full data set is pulled from the cube and then only filtered based on the slicer when the report runs (as opposed to only pulling the data for the selected book from the cube). This would explain the slow performance, but I haven't found any documentation to confirm that this is in fact what is happening.

Any ideas what I'm doing wrong?

EDIT:  I just removed the slicer completely and the report still runs 40 seconds (pulling the full data set) so it seems as though my suspicions about it pulling the full set before slicing might be correct. Any way around this?

Thanks,
Grant

[MFGF]

Hi,

A Slicer will change the context of the measures but will leave all the Trade members in the set. I assume you were doing zero-suppression to then show only those Trade members with measure values?

I would do this a different way:

1. Get rid of the slicer in the target report
2. Instead of bringing the Trade level into the report, add a Query Calculation with the expression

children([PnL Cube].[Headings].[Headings].[Book] -> ?pBook?)

Cheers!

MF.

[gdmort]

That's done the trick! Thanks.

Thanks for the explanation about the slicer too - makes sense now.