When reports or analyses are run we expect them to execute in a reasonable amount of time - usually a few seconds. As data volumes
continue to increase it becomes ever more challenging to ensure good query performance.
Measure First
When a report is running very slowly it is a temptation to leap to conclusions about the cause and the solution. After many years
of experience with these situations we've come to recognize the importance of taking the time to measure performance.
- Quantify the current performance. Take a few minutes to run the report in question and time its execution. That becomes the
baseline for improvement. Also take the time to discuss what is reasonable performance. A report that is used many times each
day needs to respond in seconds. Another report that is only used once a month can be scheduled to run overnight and could take
hours to run and still be acceptable. Make sure to set the user expectations carefully in this area.
- Understand the performance variability. Does the report perform at the same pace all the time? If not, this can indicate
some kind of environmental factor is impacting it's performance.
- Break down the performance. With both IBM Cognos and Microsoft busines intelligence tools there is a strong auditing component
that can tell you how much of the execution time was in retrieving data from the database and how much was formatting and presenting
the information. You'll want to understand this so that you can tune the appropriate area.
Push Performance Upstream
With a well designed business intelligence system the performance issues will always come back to the data mart.
This is a good thing
because it means that any performance improvements in the mart will be centralized and reusable. In a not so well designed system the
opportunity is to push the heavy lifting back towards the mart.
Read more about Why a Data Mart?
- We worked with a manufacturing firm whose reports including some very complex case statements embedded in the reports in order
to group products into groupings by packaging size that were not possible in their source ERP system. Because these statements
were embedded in the reports they caused some complex SQL WHERE clauses to be generated that did not make use of the available
indexes. There were also inconsistencies between reports related to how different developers had categorized the packaging sizes.
We moved this categorization to the data mart. This made the reports much simpler and easier to maintain and the performance increased
greatly.
- We once diagnosed slow performance on a report for a government agency that was generating a single SQL query to the database
of over 50,000 characters in length. By simply revising the IBM Cognos Framework Manager metadata layer we were able to improve the
report execution time by over 10x and reduce the generated SQL to about two paragraphs in length.
- One of our retail clients was encountering performance challenges with a custom application that needed to summarize nearly
a billion rows of historical transactions to display sales and inventory trending history over a ten year period. By analyzing
the generated SQL queries and clarifying their performance objectives we realized that it would be very difficult to meet those
performance objectives with the current architecture. Instead we developed an OLAP
cube that was build nightly from the data mart. This successfully met their performance requirements and easily accomodated future
expansion and enhancement.
- An easy technique in IBM Cognos or Microsoft SQL Server Reporting Services (SSRS) to many performance issues is to cache
a report or set of reports. For one financial services client we used the IBM Cognos Software Development Kit to built a simple
application that could generate hundreds of pre-parameterized reports and automatically add them to schedules so that every night
after the data mart was refreshed these reports would automatically be refreshed and pre-rendered into HTML for instantaneous
delivery the next morning.