This week, I took on optimising a cube report which I had authored recently. This was a not too complex burst report working off an SSAS 2008 cube. The report was taking around 1 minute per burst, with around 500 bursts required = 500 minutes (over 8 hours).

The issue in my report was the use of Detail and Summary filters. Whilst they do work, the effect they have on an OLAP data source is significant!

Inspecting the MDX generated by Report Studio, showed lots of UNIONS, AND CROSS JOINS. What was occurring – Report Studio was performing full cross joins and then applying the detail and summary filters to the returned record set.
In this case, the cross joins resulted in a record set that could easily surpass 100 million records!!! Even though the end result was correct, this was adding a significant overhead to the report.

Remember; COGNOS firstly performs a cross join between all query members and then applies the summary and detail filters

How to optimise this – SIMPLE, reduce the resultant cross join result set, which would require the use dimensional functions within the Query members, e.g.
previously whilst I had the entire member level, now I had
FILTER ( {set}, {condition})

using the above, I was able to limit my query members to the select few records, which also meant my record sets were significantly smaller.
In the end, I was able to totally remove the Detail and Summary filters and the bursting now takes around 5 seconds each!! not a bad outcome, from over 8 hours to 40 minutes!

As a simple rule, avoid using Detail and Summary filters on dimensional reports.