KB489390: Support reusable count distinct metrics in Intelligent Cubes (requires Data Engine 14 and registry setting)

Strategy enhanced the Analytical Engine logic for OLAP cubes to correctly handle Count Distinct metrics for dynamic aggregation. The enhancement ensures that when a metric is defined to count distinct attribute values (for example, counting distinct Customer IDs), the engine generates a proper Count Distinct expression during dynamic aggregation. This fix targets metrics using Count Distinct on an attribute, with all metric components available in the cube at the time of publishing, so that dynamic aggregation yields accurate distinct count results.

Before the Update

Dynamic Null Function([[F_MAIN_INDEX].M01.CountDistinctAttr+Smart+AFB])@{[Category],[Region],[Year]}

In this expression, the engine inserted a placeholder "Dynamic Null Function" at the level of Category, Region, and Year to avoid incorrect aggregation results. (AFB means Aggregate-From-Base for dynamic aggregation.)

After the Update (Corrected)

count(distinct [Customer]@[CUSTOMER_ID])@{[Category]

The engine generates a proper 

Count Distinct

In the Before scenario, the lack of a real aggregation function meant the metric did not compute a distinct count at all, leading to incorrect results such as as each row showing the raw attribute or null values. In the After scenario, the metric correctly calculates the number of unique attribute values within the grouping, even when all attributes are present on the report.

Cause

The incorrect behavior occurred because the Analytical Engine did not generate a proper aggregation function for the Count Distinct metric. Historically, certain aggregation functions (like distinct counts) are not dynamically aggregated by default because rolling them up on the fly can produce errors or double counting. In fact, the default dynamic aggregation function for metrics using Count Distinct was effectively none (set to no aggregation). Strategy documentation notes that metrics defined with functions such as Count Distinct are recalculated at higher levels using in-memory aggregation. They return null or placeholder values when dynamic aggregation is attempted.

Resolution

The engine logic for Intelligent Cube metrics of the affected type was updated. The Analytical Engine now correctly handles Count Distinct aggregation for smart metrics when certain conditions are met. The enhancement applies to metrics that satisfy all of the following criteria:

• The metric is a smart metric using AFB: The metric is configured as a smart metric using the Aggregate-From-Base (AFB) dynamic aggregation setting. In practice, this means that the metric is intended to re-aggregate at different levels in the cube without querying the warehouse.
• All metric components exist in the cube: All metric components (that is, the attribute being counted distinctly) are available at the time of publishing the cube. In other words, the metric is based on an attribute or a fact present in the cube dataset, not an object computed outside the cube.
• The Count Distinct function applies to an attribute: The dynamic aggregation is a Count Distinct of an attribute (such as counting unique attribute IDs). The improved logic specifically supports distinct counts at the attribute level, not of a fact or other complex expressions.
• Data Engine version 2025: The enhancement works for Data Engine version 2025 introduced in the May 2025 release.

When an OLAP cube metric meets all the criteria listed above, the engine's dynamic aggregation generates a true Count Distinct expression instead of a null function. To illustrate the improvement in formatting, the table below compares the dynamic aggregation formula before and after the engine update:

Dynamic Null Function([...].CountDistinctAttr+Smart+AFB)@{[Category],[Region],[Year]}

COUNT(DISTINCT [Attribute]@ID)@{[Category],[Region],[Year]} 

In summary, the engine fix ensures that Count Distinct metrics in OLAP cubes using smart metrics with AFB behave as expected. The dynamic aggregation now properly calculates the distinct count of an attribute at any level of detail, yielding correct and consistent results even when the report includes all attributes. This enhancement allows users to confidently employ distinct count smart metrics in cubes without encountering the null or incorrect results from before.