As a software architect for IBM Cognos Dynamic Cubes, I frequently get the question, "I have Cognos Dynamic Cubes but my reports and dashboards aren't fast enough. What can I do?" And, sometimes the question is more of a plea, "Help! I need to improve performance of the reports!" Often, what I've seen with situations like these is that by understanding where the time is being spent and following a few preferred practices to address those bottlenecks, report performance can be improved tremendously.
Query performance is mostly determined by how much data is fetched from the caches and the database, and how much information the report is trying to show the user. So, if we get down into what contributes to the time spent and performance of reports with Cognos Dynamic Cubes, there are 3 main components:
- Data retrieval time
- Query processing time
- Report rendering time
To reduce data retrieval time, this is where leveraging the extensive in-memory caching and aggregate awareness of Cognos Dynamic Cubes can really shine. The expectation is that a vast majority of OLAP-style reports and analyses that run against Cognos Dynamic Cubes show data at high levels of hierarchies, for example, report on the Gross Profit for Region and Product Line. Use of aggregates can significantly improve performance of queries by providing data aggregated at levels higher than the grain of the fact. By using both in-memory aggregates and database aggregates, data warehouses of all sizes, but especially medium, large, and extra large warehouses, can benefit from the greatly decreased data retrieval query times.
The easiest way to get aggregates that will significantly improve query performance is to run the Aggregate Advisor. The Aggregate Advisor provides in-memory aggregates for the aggregate cache, and recommendations for database aggregates. To get better, more relevant aggregate recommendations, a representative workload should be captured and used by the Aggregate Advisor.
It is worth noting that Cognos Dynamic Cubes maintains an active connection to the underlying database to get data that it doesn't have cached yet. So, the better optimized the underlying data warehouse is, the better the performance of any initial load queries and SQL queries that require values from the database will be.
Speaking of SQL queries to the underlying database, another important and often overlooked area of cube performance is optimizing the cube model. The cube model is the basis for all SQL queries generated, so consider optimizing your model before looking into database tuning or creating summary tables. When modeling in IBM Cognos Cube Designer you may see entries under the Performance Issues tab. These warnings are intended to alert you to modeling issues that may have room for improvement.
To reduce query processing time, the caches certainly help, but reportauthors can get faster report results by understanding how to construct an efficient OLAP report and how these reporting constructs affect the multidimensional queries sent to and processed by query service. The two areas of report optimization that have the greatest impact are: suppression and filters.
Large, sparse result sets consume a lot of memory in the engine and take a long time to execute. Suppression is almost always a necessity for these large, sparse reports. It is generally a preferred practice to follow for reports so that the engine does not have to process a bunch of empty data points that do not exist.
Reporting and analysis users need to be aware of the performance implications if reports are not filtered or scoped appropriately. As mentioned earlier, the expectation is that a vast majority of OLAP-style reports and analyses are filtered and scoped to show data at high levels of aggregation. Any deeper analysis should have filters to highly constrain the resulting data. For example, showing data for customers within a region, as opposed to including all leaf-level customers of an entire dimension.
Use of different report filtering constructs can have different performance characteristics because of the corresponding multidimensional functions used in the queries.
For different approaches to focusing or filtering dimensional data in a crosstab, see the document IBM Cognos Report Studio User Guide, Version 10.2.2, Chapter 11 Dimensional Reporting Style, section Limitations When Filtering Dimensional Data Sources. There are important limitations and possible alternative approaches to consider when using context filters described in the document. See also Appendix D, Limitations when using dimensional data sources section Limitations When Filtering Dimensional Data Sources for other limitations to be aware of when filtering dimensional data sources.
Just like Cognos Cube Designer provides warnings in the Performance Issues tab for potential modeling issues that may impact performance, Cognos BI studio report validation also provides warnings of report constructs that may impact performance. Validate your report and resolve any warnings after you understand and follow the guidelines for dimensional reporting. This approach is especially important for warnings related to the aforementioned limitations, as resolving these issues will likely result in improved performance.
To reduce report rendering time, how dimensional reports are written plays a role, and there may be some report server tuning configurations related to resources and concurrency. But, a basic concept to keep in mind if you have very large reports is that these reports may take more resources to render. Large result sets mean large amounts of data passed around the query service and report service. So, when working with Cognos Dynamic Cubes,it is important to estimate the hardware requirements for your cube. IBM Cognos Cube Designer provides a hardware sizing calculator, to help you estimate memory, CPU, and disk requirements depending on the cube size and the expected load.
There you have it, some highlights of the approaches you can use to optimize Cognos Dynamic Cubes - use the features available in Cognos to help you: hardware sizing calculator in Cognos Cube Designer, Aggregate Advisor in Cognos Dynamic Query Analyzer, Performance Issues tab in Cube Designer, report validation and suppression in the Cognos BI studios.
For more information about the capabilities and preferred practices related to optimizing your dynamic cube for performance, check out the new, second edition of the IBM Redbooks publicationIBM Cognos Dynamic Cubes, SG24-8064-01. This new edition reflects Cognos Business Intelligence Version 10.2.2 Fixpack 1, IBM Cognos Dynamic Cubes V10.2.2 Fixpack 1. This book includes many new chapters and new information captured from lessons learned in the field. For a high level overview of IBM Cognos Dynamic Cubes see the IBM Redbooks Solution Guide Big Data Analytics with IBM Cognos Dynamic Cubes, REDP-5265
To learn about IBM Cognos Dynamic Cubes architecture, take a look at the blog post by MaryAlice Campbell Lifting the covers on IBM Cognos Dynamic Cubes.
Avery Hagleitner is a Software Architect for IBM Cognos Dynamic Cubes in IBM Analytics Solutions at the IBM Silicon Valley Laboratory. Avery has over 14 years of software development experience at IBM. Her interests range from high-performance Java server applications to engaging graphical user interfaces. Her areas of expertise include business intelligence, data warehousing, and online analytical processing (OLAP). Avery holds a master's degree in Software Engineering from San Jose State University, California, USA, a Bachelor of Science degree in Computer Science and a minor in Psychology from the University of California, San Diego. Avery is one of the authors of the IBM Redbooks publication IBM Cognos Dynamic Cubes, SG24-8064-01 and the IBM Redbooks Solution Guide Big Data Analytics with IBM Cognos Dynamic Cubes, REDP-5265.
Likes before 03/04/2016 - 1
Views before 03/04/2016 - 5588