KB442655: Default value changed for "Separate Count Distinct" VLDB setting for Impala 2.x database and leads to different SQL

Prerequisites

• Using data from an Impala 2.x database
• Query generated contains many temporary tables
• VLDB setting "Separate COUNT DISTINCT" is set to default.

Glossary

• Separate COUNT DISTINCT - VLDB setting that indicates how to handle COUNT (and other aggregation functions) when DISTINCT is present in the SQL

Change in the Default VLDB Value

• Open Strategy Developer.
• Log into your project source as a user with administrative privileges (e.g. Administrator).
• Navigate to Administration > Configuration Managers > Database Instances.
• Locate an Impala 2.x database instance, right-click, and view "VLDB Properties".
• Enable "Advanced Settings" under "Tools" on the top toolbar.
• View "Separate COUNT DISTINCT" under the Metrics folder. 
• The default inherited value is, before the upgrade, for "Separate COUNT DISTINCT" is set to "One pass"; after the upgrade, it is set to "Multiple count distinct, only one count distinct per pass":

Change in the SQL View

Before the upgrade, when multiple count(distinct) metrics exist on a template, the following error message is observed: 
 

"AnalysisException: all DISTINCT aggregate functions need to have the same set of parameters as count(DISTINCT ..."
 
After the upgrade, such error should be fixed.
When running any report that uses data from an Impala 2.x database, looking at SQL View, users may notice more temporary tables being generated than in previous versions of Strategy. 
 

Summary

This is an expected change when upgrading from Strategy 10.11 or earlier version to a newer version. No further action is required.

• The VLDB setting "Separate COUNT DISTINCT" default value was changed from "One pass" to "Multiple count distinct, only one count distinct per pass".
  
• Why? Impala does not support multiple count(distinct) calculations in a single query, so with the old default value, the user may encounter an error message like: "AnalysisException: all DISTINCT aggregate functions need to have the same set of parameters as count(DISTINCT ..."
• The new default value forces Data Engine to generate multiple passes for every count distinct calculation and avoids this error.
  
• Although the SQL has changed, the data itself should remain unchanged.

Note: The VLDB setting can always be changed from its default setting to a desired value.
 
Strategy Internal Reference number for this technical note is KB442655 and DE121600 / DE105342.