KB483545: Filtering on an Attribute Causes High Memory Usage Because of Bad Join Path Logic in Data Engine

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

Symptoms

• Dossier or Document takes an extensive period of time to execute or never finishes executing after filtering on an attribute.
  
• High memory usage on the Intelligence Server, sometimes leading to memory depletion

Glossary

• Lowest-level attribute - the attribute in a hierarchy that is at the bottom or not the parent of another attribute (e.g. Year parents Quarter parents Month). When there are multiple hierarchies involved, the lowest-level attributes refers to the set of the lowest-level attribute from each hierarchy.

Cause

This is a known issue in Strategy 10.5 and earlier versions.

• When there are an excessive number of attributes involved in generating the relation between datasets, it leads to costly joins, as seen by the long execution time and high memory usage.
  
• This stems from the formula Data Engine uses to calculate the cost of joining two datasets.

Solution

• Upgrade to Strategy 10.6 or above. We recommend the latest version.
  
• If upgrading to Strategy 2019 Update 3 or later, 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.

In the new fix, Data Engine changes how the cost of joining datasets is calculated and consequently, the join path that is generated. The new improved estimation of cost is a more accurate heuristic prediction and helps Data Engine avoid costly joins.
 

Impact

This fix affects any situation where two datasets are joined in execution, and there are more than two lowest level attributes.

• May change CSI and data
  
• When joining datasets with more than one attribute in common, the join path generated from this new logic narrows the amount of data returned, sometimes leading to no data at all.
• You may encounter an error message like: 
  

  Yes IDZipCode10880228802CSI before fix select [Call Ctr Id]@[CALL_CTR_ID], sum([[F_CITY_CTR_SLS47D2F7934AA01B2579FF23BD2359A940].Tot Cost])@{[Call Ctr Id]} as [Tot Cost] from DS2.CITY_CTR_SLS CITY_MNTH_SLS CITY_SUBCATEG_SLS (3 tables) with Table Join Tree: [F_CITY_CTR_SLS47D2F7934AA01B2579FF23BD2359A940] <[[F_CITY_CTR_SLS47D2F7934AA01B2579FF23BD2359A940].Tot Cost] > 1000000> CSI after fix select [Call Ctr Id]@[CALL_CTR_ID], sum([[F_CITY_CTR_SLS47D2F7934AA01B2579FF23BD2359A940].Tot Cost])@{[Call Ctr Id]} as [Tot Cost] from DS2.CITY_CTR_SLS CITY_MNTH_SLS CITY_SUBCATEG_SLS (3 tables) with Table Join Tree: [F_CITY_CTR_SLS47D2F7934AA01B2579FF23BD2359A940] having [Tot Cost] > 1000000.0 CategoryCountryProfitRevenueBooksUnited States453652ElectronicsCanada3547ElectronicsUnited States634245MusicCanada73134MusicUnited States654326LTrim([Subcategory@DESC])[Row Count]/Sum([Row Count])"No data returned for this view. The filter applied may exclude all data"

  
  

To opt out of this fix, upgrade to Strategy 2019 Update 3 or later and set Data Engine Version to 10.
Strategy Internal Reference number for this technical note is KB483545 and DE39953.