Recommendation - Drilling Down in Very Large Data Sources

When you drill down in a report that uses a very large data sources query results can be much larger than what can be held in memory. For example, if you insert the lowest level of four hierarchies that each contain 1000 members, the report can contain 1,000,000,000,000 cells. At best, this query will take a very long time to run. While this large query executes, all other queries for the same server process will likely be blocked. For most data sources, the query will likely fail due to insufficient memory or timing out. Then the memory is released and normal services resume.

However, when using a Microsoft SQL Server 2005 Analysis Services (SSAS) cube, the memory consumed by the SSAS client is not always released in a way that other queries can effectively re-use. The server continues to run with insufficient memory, causing new queries to either run very slowly or fail completely. You may encounter an error, and, to continue, the system administrator must manually stop and restart the IBM® Cognos® BI service.

To avoid these problems, consider the size of hierarchy levels and sets when creating reports and avoid combining them in ways that create large queries. To determine the size of a set, create and run a very small report that includes only a count of the members within that set. You can also use filtering techniques to focus your data.