KB483281: Aggregate from Base (AFB) metrics based on attributes display incorrect values when Data Engine is set to 11.

Community Admin

• Strategy

When Data Engine is set to 11, the AFB metric defined on an attribute would be aggregated to an incorrect level. This resulted in the grid displaying unexpected values.

Symptoms

The AFB metric defined on an attribute is not aggregated correctly.
Results display incorrect values on the grid.

Glossary

Aggregate from Base metric (AFB)
Metric Level:
A Metric is a calculation of a fact, an attribute or another metric. Simple Metrics are comprised of one or more facts and one or more functions.
Metric Level is the attribute level the metric calculates at. By default, it will be the lowest level on the report.
For example – if you have a Revenue metric and Attributes like Year, Quarter and Month. The Revenue value computed will be different based on which attribute it is calculated against. The lowest level in this case would be Month. It can then be aggregated for the Quarter or for the Year.

Steps to reproduce:

Create a Base Dataset consisting of the following Attributes - Subcategory, Item and Metrics - Cost, Revenue
Create an AFB metric Metric1 based on an attribute ,lets say, Item ID.
Definition of Metric1: Sum(Item ID).
Change the level of Metric1 to attribute Category ID .
Note that Category is an ancestor of the attribute Item , and that Category is not in the base dataset.

3. Create a grid, and drag attribute Category (Cat ID), Item (Item ID) and the metric onto the grid.

The value of the metric seems wrong in the observed results. It is calculated at the level of Item ID, when it should have been aggregated to the level of Cat ID.

CSI:

Deferred Execution
√√√√Folder Browsing√√√√Grid/Graph√√√√Drill√√√√Link drill√√√√UC on dataset√√√√Transaction√√√√Offline Transaction√√√√Smart client-dataset transaction√√√√DDIC√√√√Prompts - All kinds of types√√√√Authentication√√√√Secure Report/Document√√√√Security - Restricted Share & Print√√√√Application Reset√√√√Logout√√√√Push Notification√√√√Badge√√√√Multi-layoutTBC√√√Grid/Graph Quick Switch√√√√Calendar selector√√√√Grid interaction improvement √√√√Retain search history in Filter panel, Prompts√√√√Retain search history in search box selector√√√√3rd party visualizations on iPad√√√√ESRI Map for RSD on iOS√√√√Time-based Calendar on iPad√√√√Graph Drilling on iPad√√√√Map Current Location on iOS√√√√Map Clustering default control√√√√Mobile VI Visualizations – trendline/reference line/formatting/axis origin√√√√3rd party visualization cache and precache√√√√SourceDomainsPortsFacebookgraph.facebook.com
www.facebook.comHTTPS 443Google BigQuerywww.googleapis.com
accounts.google.comHTTPS 443Twitterapi.twitter.comHTTPS 443Dropboxwww.dropbox.com
api.dropbox.comHTTPS 443Salesforcelogin.salesforce.comHTTPS 443Public Dataen.wikipedia.orgHTTPS 443

Get Started ⟩

Prepare Data ⟩

Create Cards ⟩

Share Cards ⟩

Administer

Get Started ⟩

Prepare Data ⟩

Create Cards ⟩

Share Cards ⟩

Administer

Expected CSI:

Tables Accessed:
Table7   [F_DICB02H6807D3CA14AE260A17C1007821A22787]:
           Subcat2 Id, Subcat2 Desc, Item ID, Item DESC,
          Cost, Rev2, Row Count - DICB02H,
          FACT_TABLE
select     ([Subcat2 Id]@[SubCat ID]) as ([Subcat Id]@[SubCat ID]),
                [Item ID]@[Item ID],
                [Item ID]@[Item ID] as [LM2_Sum(Item Id)CTG]
from        DICB02.h_SubCTG(12,42,44)_Item_Rev_Cost_Related(ID,1:M)
with Table Join Tree:           [F_DICB02H6807D3CA14AE260A17C1007821A22787]
to             Ex8_tempcube0

select     *
from        Left Outer Join(
                                Ex8_tempcube0,
                                RELATION1-<Cat Id, Subcat Id>
                               ) on Subcat Id
to             Ex8_tempcube1

select     [Cat Id]@[Cat ID],
                [Subcat Id]@[SubCat ID],
                [Item ID]@[Item ID],
                sum([LM2_Sum(Item Id)CTG]) @{[Cat Id]} as [LM2_Sum(Item Id)CTG]
from        Ex8_tempcube1

Observed CSI

Tables Accessed:
Table7   [F_DICB02H6807D3CA14AE260A17C1007821A22787]:
           Subcat2 Id, Subcat2 Desc, Item ID, Item DESC,
          Cost, Rev2, Row Count - DICB02H,
          FACT_TABLE
select     ([Subcat2 Id]@[SubCat ID]) as ([Subcat Id]@[SubCat ID]),
                [Item ID]@[Item ID],
                [Item ID]@[Item ID] as [LM2_Sum(Item Id)CTG]
from        DICB02.h_SubCTG(12,42,44)_Item_Rev_Cost_Related(ID,1:M)
with Table Join Tree:           [F_DICB02H6807D3CA14AE260A17C1007821A22787]
to             Ex8_tempcube0

select     *
from        Left Outer Join(
                                Ex8_tempcube0,
                                RELATION1-<Cat Id, Subcat Id>
                               ) on Subcat Id
to             Ex8_tempcube1
select     [Cat Id]@[Cat ID],
                [Subcat Id]@[SubCat ID],
                [Item ID]@[Item ID],
                [LM2_Sum(Item Id)CTG] as [LM2_Sum(Item Id)CTG]
from        Ex8_tempcube1

Note: In the observed results, the metric [LM2_Sum(Item Id)CTG] has not been aggregated to Cat Id level (in red above) and hence the value of this metric cannot be calculated correctly.
Note: In the expected results, the metric [LM2_Sum(Item Id)CTG] has been aggregated to Cat Id level (in red above) and hence can be calculated correctly.

Cause:

For AFB metrics on attributes that aggregate to a level that is not in base dataset the AFB flag is set as false when selecting its components; including itself.
As a result, such an AFB metric would not be aggregated to the right level, and the values of this metric would seem incorrect.

Action:

This is a known defect and has been addressed in Strategy 11.1
Please upgrade to Strategy 11.1 or above to obtain this fix.
If you are upgrading to Strategy 11.0 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11. This property can only be enabled through Workstation.
For more details on enabling the setting, refer to Change Data Engine Version .

The Strategy Internal Reference Number for the issue discussed in this technical note is KB483281 and DE115506.

Comment

0 comments

Details

Knowledge Article

Published:

May 8, 2019

Last Updated:

May 31, 2019

Glossary

Aggregate from Base metric (AFB)

Metric Level:
A Metric is a calculation of a fact, an attribute or another metric. Simple Metrics are comprised of one or more facts and one or more functions.
Metric Level is the attribute level the metric calculates at. By default, it will be the lowest level on the report.
For example – if you have a Revenue metric and Attributes like Year, Quarter and Month. The Revenue value computed will be different based on which attribute it is calculated against. The lowest level in this case would be Month. It can then be aggregated for the Quarter or for the Year.

Steps to reproduce:

Create a Base Dataset consisting of the following Attributes - Subcategory, Item and Metrics - Cost, Revenue

Create an AFB metric Metric1 based on an attribute ,lets say, Item ID.
Definition of Metric1: Sum(Item ID).

Change the level of Metric1 to attribute Category ID .
Note that Category is an ancestor of the attribute Item , and that Category is not in the base dataset.

CSI:

Action:

This is a known defect and has been addressed in Strategy 11.1
Please upgrade to Strategy 11.1 or above to obtain this fix.

If you are upgrading to Strategy 11.0 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11. This property can only be enabled through Workstation.

For more details on enabling the setting, refer to Change Data Engine Version .

The Strategy Internal Reference Number for the issue discussed in this technical note is KB483281 and DE115506.