Extreme run times on a crosstab report

[beerguy82]

All -

I've been working on the development of a large, multiple crosstab report that provides an overview of my company's sales.  Each crosstab has 3 different levels (from different hierarchies) nested on the left edge and is 16 columns wide (2 of which are calculated).  Overall, there ends up being around 430 distinct crosstabs with up to 16 columns and 250 rows. 

I've filtered as much as I can, but the performance of this report is still bad.  It takes about 3 hours to run through and spit out in Excel 2007 (when it doesn't take down the server of 8 cores and 8gb ram).

Does this seem right for a report this size?  Any strategies to make it run quicker?  I've read and applied all I can from the 'Efficient OLAP Reporting' document in the KB.  Are there any utilities that can be used to see where the bottlenecks are in the report?

Thanks.

Tim

[blom0344]

You may be stretching the limit of what can be accomplished by webbased reporting. Your example is very similar to a case I ran into with a customer who tried to rebuild Business Objects full client crosstab reports into Cognos 8 report studio. It was just too much for the server to render..

Is there any chance that you can condense the data before generating the report? If granularity of the base data is very low compared with the level of the output (lots of aggregation) that it may be beneficial to create an aggregate first (assuming you can work this in through an ETL proces)

The number of 430 puzzles me though. any chance you can divide the load into batches. Are you generating output in one pass for the entire company?

[beerguy82]

That's what I was afraid of.  I took a look at the MDX and it was hideously complex, with a lot of crossjoins.  Right now I think the granularity is correct.  I'm reporting sales by month for a bunch of different products and our sales data only goes down to the month level from this cube.  I work at an beer company and I'm reporting on between 75-100 distinct SKUs across 2 years.  The left edge is as so:

Style, Package, Year

and the columns are as follows

Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov   Dec   Yr Total   % Chg M / Y      YTD    Inv

The 430 distinct crosstabs is necessary as the organizational structure I am reporting on is as follows:

Division (5) --> Region (16)  --> District (72) --> Distributor (340)


with each lower level nested below it's parent so the sales hierarchy is intact in the report. 

Another problem, it seems, is with the way Cognos bursts the reports.  If I run the report from the region level down, it might take 2 hours, but if I burst it, it generates 16 distict reports and takes MUCH longer. 

Any other ideas would be greatly appreciated, but I'm beginning to think this might be something I just have to deal with...
     
Tim