KB483227: Job canceled after adding a subtotal to the template. The template uses a Connect Live Dataset.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Symptoms

• Data Engine Version set to 11 (Strategy 11.0) or  "Defect fixes that may impact Data, SQL, MDX, CSI" Feature flag is enabled (Strategy 10.11)
• Dossier using a dataset in "Connect Live" mode
• Derived metric defined as Count() on an attribute with "Subtotals From Base" enabled
• Error encountered when adding subtotals to a Grid visualization with the derived metric:

QueryEngine cannot cancel job with ID: . Because 24536.[the ID cannot be found in the job queue of Query Engine.]
 

Glossary

• Template - the Strategy report or visualization that attributes and metrics are placed on
• Connect Live - mode of data access that retrieves results directly from the data source
• Derived Attribute Push Down - feature that evaluates Derived Attributes at the source when all required columns come from the same table; serves to optimize performance
  
• also known as the "Always calculate Derived Attribute at source for Connect Live Intelligent Cube Datasets" feature

Steps to Reproduce

Note: The error message only appears when the data contains a large number of rows. The tables in Strategy Tutorial are not large enough to trigger this error and will correctly return subtotals. We provide an example below to show the structure of a template that will be affected by the fix for this error:
 

• Create a new dossier.
  
• Add new data.
  
1. Add the "ITEM_EMP_SLS" table.
2. Save the dataset in "Connect Live" mode.
• Create a derived metric Count(Item Id) with the following definition: 

• Add Emp Id and Count(Item Id) to a Grid visualization.
• Show totals.

Depending on the amount of data, users may experience job cancellation or errors when running the dossier.
 

Cause

• A new feature, "Derived Attribute Push Down", was introduced in Strategy 10.11, that forces subtotals to be calculated at the source instead of In-Memory.
  
• In Strategy 10.11, it is enabled when the "Defect fixes that may impact Data, SQL, MDX, CSI" Feature flag is enabled.
• In Strategy 11.0, it is enabled when Data Engine Version is set to 11.
• In this workflow of calculating subtotals, every column is retrieved during SQL execution to calculate the total, and for a dataset with many more rows of data, this retrieval can exceed the capacity of the Intelligence Server.
  
• To see the SQL executed, inspect "Query Details" of the visualization.

Solution

• Upgrade to Strategy 2019 or above. We recommend the latest version.
• Set Data Engine Version setting to 11 or above.
  
• The project level setting Data Engine Version can only be enabled through Workstation. For more details on enabling the setting, see Change Data Engine Version.
• Set "Derived Attribute Push Down setting" Feature Flag to ON. (This is OFF by default in Strategy 2019.)
  
• Open Strategy Web, and open "Preferences" > Project Defaults. Set feature status from OFF to ON.

After the new fix, calculation of subtotals with Connect Live datasets is disabled completely. Only the columns that are used by the metric are retrieved during SQL execution, but this is insufficient to calculate the total, so the value on the template will be blank.
As of now, the "Subtotals From Base" option for metrics will not be supported for "Connect Live" datasets when "Always calculate Derived Attribute at source for Connect Live Intelligent Cube Datasets" is enabled. This fix will return blank values for subtotals, and may return no data where there were previously. This change is to be expected. Please bring the datasets In-Memory or disable subtotals when using these metrics.
Strategy Internal Reference number for this technical note is KB483227 and DE112741.