At every release, Cognos Analytics prioritizes performance improvements to ensure a seamless user experience and faster insights delivery. In this blog, we will be introduced to few additional key techniques that serve as the initial touchpoints when aiming to enhance Cognos responsiveness ourselves.
We will take a closer look at the following capabilities:
1. Data Caching:
Explore the benefits of both Server Caching and Browser Caching in optimizing data retrieval and dashboard rendering speeds.
2. Data Sets:
Understand how leveraging Data Sets can streamline data access and manipulation, facilitating smoother report generation and dashboard interactivity.
3. Cascading Filters:
Learn how to utilize Cascading Filters effectively to refine data views and enhance user-driven data exploration experiences.
4. SQL Tips and Guidelines:
Gain insights into best practices and guidelines for writing efficient SQL queries when interacting with relational databases in Cognos Analytics.
5. JVM heap size:
Enhance dashboard visualization loading by increasing the JVM heap size for the query service, ensuring smoother performance and faster data rendering.
Data Caching
Data caching comes in two flavours: server-side and browser-side. In Cognos Analytics, server-side caching stores commonly used data to speed up dashboard loading, while browser-side caching acts like a backup, making your favourite dashboards load super quick.
A couple of things to consider are firstly, when using relational database sources, JDBC driver must be configured.
Secondly, when dashboards present real-time data updates, both server-side and browser-side caching must be turned off. This will ensure users are always getting the freshest data straight from the source.
Overall, data caching helps make dashboards run smoother and stay on top of the latest data trends.
Server Caching
Server caching acts like a repository, storing result sets from dashboard queries directly on the Cognos Analytics server and are available to be used by different users. However, a couple of conditions must be met for this functionality to occur.
Firstly, users executing queries must match the same or compatible query patterns. Secondly, the security profile and data source connection profile must align perfectly. Essentially, cached results from one user's request can be utilized for another user's request only if both users share identical data security profiles. This approach not only significantly enhances performance but also ensures robust data security.
Additionally, server caching, synchronizes with the data cache configuration set in a Framework Manager package or data module.
Finally, although new dashboards default to “Automatic cache” behaviour, this configuration can be customized to meet specific requirements.
Browser Caching
Browser-side caching, designed to enhance dashboard performance, introduced in version Cognos Analytics 12.0. It operates independently from server caching and aims to optimize client-side interactions with relational data. With browser caching, query data is stored directly on the device's web browser. Consequently, when engaging with dashboards, there's no need for constant data source or server-side cache queries. Instead, each time a user accesses their dashboard, it retrieves the latest data from Cognos Analytics and caches it locally in their browser. As they navigate through their dashboard, this cache is leveraged, reducing unnecessary network traffic and server processing. Finally, the cache remains active solely during a dashboard session for enhanced security.
Data Caching Documentation
Demo Recording
Data Sets
Imagine your data warehouse as a vast library filled with valuable information. Data Sets empower you to navigate this library with precision, enabling you to select only the tables and fields relevant to your work. Whether you're in sales, finance, or marketing, Data Sets offer the flexibility to tailor your data selection to suit your specific needs.
Since there were introduced, Data Sets have evolved significantly, offering advanced functionality to refine data selection further. With Data Sets, you can eliminate irrelevant information, creating lean and efficient datasets customized for your reports or dashboards.
Furthermore, Data Sets also enable you to leverage existing queries from Cognos Analytics Reports, allowing you to seamlessly incorporate them into your datasets. This means you can import complex report specifications, including multiple queries, into the Data Set editor, facilitating the creation of new Data Sets with identical queries. Subsequently, these datasets can be utilized to generate dashboards, stories, and more. Ultimately, this translates to improved speed, efficiency, and user experience.
However, it's important to note that while you can create dashboards directly from a data set, the same cannot be done from a report. To utilize data sets in reports, you must first create a data module from the data set and then use the data module as a source for your report.
Data Sets Documentation
Demo Recording
Cascading Filters
Another effective technique for optimizing dashboard loading performance are the Cascading filters.
Whilst exploring approaches to streamline dashboard performance, we often encounter challenges posed by large datasets that tend to slow down our dashboards, particularly when applying filters. Recognizing this issue, Cognos Analytics introduced a ground-breaking feature in version 11.2.3: Cascading filters.
With Cascading filters enabled, dashboards offer a diverse method to filter selection, allowing filters to impact the available filter options in other columns. Essentially, this feature ensures that any column utilized as a filter in the "This tab" area or as a local filter on individual visualizations will display only those values unaffected by filters in the "All tabs" area.
Moreover, filters within the same filter tab can be organized into a cascade set, providing further flexibility. Filters grouped within a cascade set can influence selections for other filters within the set through two distinct modes: "First to last" or "Multidirectional."
Cascading Filters Documentation
Demo Recording
SQL Tips and Guidelines
1. Number of rows returned should be equal to the number of values displayed.
2. Data cache and Data sets may be good way to avoid waiting for large amounts of data to be transferred.
3. Materialised views, stored on the data server, can provide summarised data improving query performance and reduce the workload on the data server.
4. Reduce data type conversions.
5. Avoid complex join and filter expressions to enable the data server to plan queries efficiently and speed them up.
6. Avoid unnecessary outer joins to improve efficiency of queries presented by reports or dashboards.
7. Use constraints on tables in data servers so the data server query engine considers them for strategies.
Optimizing Query Performance Documentation
Increase JVM heap size for query service:
1. From the Welcome page, navigate to the Hamburger Menu, select Manage and click Administration Console.
2. Click on the System tab and select the arrow to the right of All servers. Hover over the Services option and click Query.
3. Against your running ‘QueryService’, click Set properties.
4. Under the Settings tab, identify the tuning options and adjust the following values:
Initial JVM heap size for the query service (MB) (Requires QueryService restart): an ideal value to enable the initial size is 4 GB.
JVM heap size limit for the query service (MB) (Requires QueryService restart): an ideal value to enable the maximum or size limit is 16 GB.
5. Ensure that you restart the QueryService which you just adjusted the JVM heap size for so the changes can be implemented.
Increase JVM heap size Documentation