Comparing materialized views in data servers to data caching in Cognos Analytics
Each instance of an IBM® Cognos® Analytics query service manages its own private cache of reusable result sets from SQL queries. Hence, a cluster of several application tier servers manages its own caches and doesn’t share them. Meanwhile, queries that are sent from any of those instances can benefit from the materialized views a data server might use.
The query service holds result sets in a data cache that reflect the columns a Cognos Analytics user is requesting, with the level of aggregation and filtering applied. The order in which queries are run influences if any cached data can be reused or not. For example, a query projects COUNTRY and SUM(SALES) from T(table) where COUNTRY=UK. The result set of the query holds only the total sales for UK. If a new query is processed where COUNTRY=FR, the first result set cannot be used because the data cache doesn’t cover (include) COUNTRY=FR. Hence, as users change context in a dashboard, or different queries request similar groupings but different measures, the data caches might not be able to avoid requerying the data server.
Contrast this with a materialized view in a data server. The materialized view would be defined to hold the grouped rows for all known countries. Hence, when COUNTRY=UK, it would quickly locate that row, likewise when COUNTRY=FR. It could also be used to compute SELECT SUM(SALES) from T. In effect, the materialized view could span a broader set of dimension categories and measures that service many different needs of the Cognos Analytics users.
The creation of a materialized view requires a database administrator (DBA) to define and deploy the materialized view. Using the data cache in Cognos Analytics doesn’t require a DBA. If a DBA is not available to implement materialized views, relying on Cognos Analytics data caching or data sets, which have prejoined, filtered, and aggregated data, might be your only solution to consider.