Optimizing query performance
Cognos® Analytics is designed to take full advantage of your data infrastructure. The primary data access strategy is to delegate data processing, as much as possible, to a data server.
Therefore, in typical scenarios the volume of data is bounded by your data server capacity to respond to analytical queries within the threshold of your users’ wait time tolerance. Typically, users don't like waiting more than a few seconds for a request when interacting with data.
Cognos Analytics generates Structured Query Language (SQL) queries to retrieve data from relational data servers. Users must wait while the data server responds to such queries. For example, when connecting to the SQL interface of a data server, Cognos Analytics generates SQL that is tailored for the type and version of the data server technology, and optimized to minimize the user wait time.
Usually, the number of rows that need to be transferred from the data server to Cognos Analytics is equal to the number of values to be displayed within Cognos Analytics. Even if your data server stores billions of records, if a bar chart in a Cognos Analytics dashboard displays five bars, only five rows of data should be retrieved. The data server computes all joins, aggregations, calculations, filters, and so on, that result in the five values that get displayed in the Cognos Analytics visualization.
It's possible to make requests that cannot be processed by an underlying data server. These types of requests might require processing by the Cognos Analytics query service instead. While this is not always possible, Cognos Analytics is designed to avoid generating SQL statements that return a large number of rows where only a small percentage of the row data is presented to users. Although such SQL statements might not be complex or expensive for the data server to process, they can result in large amounts of data that is transferred to the Cognos Analytics server for local processing, which might increase the wait times.
Query times can be greatly impacted by aggregations, calculations, joins and other such operations that require data processing either by Cognos Analytics or a data server. Keep in mind that dashboards are typically presenting summary perspectives of data, which could be more detailed (fine grained) at the source. A good first step in dashboard performance troubleshooting is identifying widgets that are accessing millions of rows of stored data, which then undergoes processing to reduce the number of rows to a small number of values to be displayed in a visualization. Most of that processing time could be avoided if the final result of data to be presented to the user were preprocessed. To address this issue, many data server technologies offer a concept known as a materialized view, which denotes a precomputed result of one query that is accessible to other queries. The same concept might be known under different names.
Materialized views can reduce user wait times in Cognos Analytics by offering data that is preaggregated at the key, frequently requested intersections of logical aggregation. For more information, see Comparing materialized views in data servers to data caching in Cognos Analytics.