IBM Support

Problems derived from using emptySet function in Cognos Analytic queries

Troubleshooting


Problem

Extensive use of the emptySet function within an IBM Cognos Analytics query conduce to a poor performance.

Cause

Report authors can create layouts that changed based on conditional logic. One approach being to include a query item in a layout that uses the emptySet function that results in nothing being rendered.

While the emptySet function returns no data, like any other expression, it influences the time required to plan and execute a query. This edge path referencing the item needs to be analyzed and processed just the same as any other edge path. Depending on how many edge paths are present in a query it can have a significant impact on query planning.  Therefore, performance wise the best thing is to avoid the addition of an emptySet function, which doesn't contribute to the result. However, if you must do it then, be aware that this action has a cost.

We are enhancing the query planning starting from Cognos Analytics 11.2.4.0 IF1001 by adding an optimization that removes the empty edge path as early as possible. The goal of this optimization is to eliminate the performance impact of not rendered edge paths over the rest of the planning process.

In theory, if the use of emptySet leaves us with a heavily disabled edge the query planner might find it difficult to process the rest of it. Therefore, although it is unlikely, it might happen that a previously working report fails do to this new emptySet optimization. In that case one can disable the optimization for the impacted report by adding to its query a query hint named removeEmptySets with value false. If you are not familiar with setting advanced query hints, then the following instructions help you set the removeEmptySets query hint. Look for the red arrows and numbered steps.

1- Look for report’s queries.

image-20230329104947-1

2- For each query. Find “Advanced query hints” in the properties of the query.

image-20230329104947-2

3- Add a new query hint named removeEmptySets, set its value to false.

image-20230329104947-3

image-20230329104947-4

.

Resolving The Problem

Avoid adding the emptySet function.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m3p000000hBNFAA2","label":"Troubleshooting"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
29 March 2023

UID

ibm16842563