KB483317: Incorrect values for metric defined from attribute when filter is applied in live connect mode

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

Description

With the Data Engine version set to 11 in Strategy 2019, you may notice that metrics defined by attributes (e.g. Count[Employee ID]) are calculated at the grand total level, and not the correct level. 

Steps to reproduce

The following example uses Strategy Tutorial attributes and metrics. 

• Create a new dossier. 
• Click New Data > Databases.
• From the Tutorial Warehouse, import the "CITY_MNTH_SLS" and "LU_MONTH" tables.
• Select Connect Live.
• Add a link on [Month ID].
• Using the Metric Editor, create a count metric with the "Month Id" attribute in the LU_MONTH dataset, using the formula: Count([Month Id]){~+}
  

• In the Display Name field, enter Count (Month Id)
• Create a derived attribute in the CITY_MNTH_SLS table using the formula: [Cust City Id]@ID
  

• In the Attribute Name field, enter City.
• In your first visualization, drag the "Cust City Id" and "Year Id" attributes, and "Count(Month Id)" metric onto your grid. 
• In your second visualization , drag the "Cust City Id," "Year Id," and "Month Id" attributes onto your grid. 
• Add a filter on the derived attribute, "City," and check "1."
  Notice that for every year, "Count(Month Id)" returns 48, instead of 12. 
  

Why is this happening?

This is a known defect in Strategy 2019 with the Data Engine version set to 11. In Connect Live mode, if the filter cannot be pushed down, the metrics defined by attributes are calculated by SQL statements using the grand total.
 
When the Data Engine version is set to 10, the metrics defined by attributes are calculated by the CSI Engine, and will return the correct data.

Solution

You should set the Data Engine version to 10. The project level setting of the Data Engine version can only be enabled via Strategy Workstation.
You can also upgrade to Strategy 2020 or later versions, and set the Data Engine version to 12.
The metrics defined by attributes should calculate at the correct level.

Glossary

Filter Push-Down A way to apply filter directly in the SQL generation, often in earlier calculation passes. This reduces the amount of calculations done in the CSI Engine, improving execution performance.
Connect Live See Connect Live to datasource (Direct Data Access) in Data Import.
Data Engine version See Change Data Engine Version.
The Strategy reference numbers for this KB article are KB483317 and DE133745.