Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Symptom
Incorrect data might be returned for a Nested Metric in a data blended dossier. The nested metric will contain a parent metric based on an OLAP functions like RunningSum and a nested child metric based on an aggregation function.
A sample nested metric that can meet this issue will have a formula like M1=RunningSum<BreakBy={[Year Id], [Region Id]}, SortBy=([Month Id]@[Month Id])>(M2), where the metric M2 is a Conditional Metric having a formula like sum([Tot Dollar Sales])<[Month Id]@[Month Id] in {200603, 200501, 200604, 200502}>, Tot Dollar sales is a metric existing in one of the datasets for the dossier, and the attributes Year, Region should come from different datasets. When incorrect data is returned for these nested metrics, BreakBy and SortBy attributes specified for the OLAP function are not considered as needed during the calculation of the child metric M2. For instance, if a visualization of a data blended dossier has Month, Region, and M1 on its grid, the metric calculation pass for M2 will not include the attribute Year as the following sample pass shows, and thus BreakBy Year can’t be applied as needed when M1 (=RunningSum(M2)) is calculated.
select [Month Id]@[MONTH_ID],
[Month Id]@[MONTH_DESC],
[Region Id]@[REGION_ID],
sum([Tot Dollar Sales])@{[Month Id],[Region Id]} as [M2]
from Ex8_tempcube1
where [Month Id]@[MONTH_ID] in (200603, 200501, 200604, 200502)
to Ex8_tempcube2
Why is this happening?
This is a known issue starting from Strategy 11.0 with Data Engine Version 2019 or above.
Solution
To fix the data issue, upgrade to Strategy 2021 Update 4 and set the Data Engine Version to be 2021. To set Data Engine Version, refer to How to set Date Engine Version.
After the fix, the data for nested metrics like M1 can be calculated correctly with BreakBy and SortBy attributes being considered during the calculation of nested child metric M2 and applied successfully for the further calculation of M1 (=RunningSum(M2)). The metric calculation pass of M2 for the visualization having Month, Region, M1 will include Year as the sample pass shows below:
select [Month Id]@[MONTH_ID],
[Month Id]@[MONTH_DESC],
[Year Id]@[YEAR_ID],
[Region Id]@[REGION_ID],
sum([Tot Dollar Sales])@{[Month Id],[Region Id]} as [M2]
from Ex2_tempcube1
where [Month Id]@[MONTH_ID] in (200603, 200501, 200604, 200502)
to Ex2_tempcube2
Strategy Internal Notes:
Strategy Internal Reference number for this technical note is DE215490.
