EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB442142: A dashboard containing metrics defined on attributes fetches unnecessary attributes to Data Blending Engine causing the extra memory to be used and may lead to error condition due to governing limit being reached

Community Admin

• Strategy

This Knowledge Base article describes how when creating a dashboard that has a Metric defined on Attribute that fits several conditions an internal error is encountered.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
SYMPTOM
When a metric is defined on an attribute (For ex: Count of Order) and requires Data Blending Engine to calculate the metric then unnecessary attributes are fetched into Data Blending Engine causing extra memory usage and even hit the governing limit of the Intelligence Server and leads to error.
STEPS TO REPRODUCE
1. Strategy Tutorial project is used to demonstrate the problem.
2. Create a new Dossier and add New Data and add ORDER_DETAIL table and define no relationships as specified below and publish as In-Memory Cube:

ka0PW0000001JSuYAM_0EM44000000gVTD.jpeg

2. Add New Data and add ORDER_FACT table and define following relationship as specified below and publish as In-Memory Cube:

ka0PW0000001JSuYAM_0EM44000000gVTI.jpeg

3. Create a new derived metric as following:

ka0PW0000001JSuYAM_0EM44000000gVTN.jpeg

4. Put "Emp Id" and "Count of Order" on visualization and the Dossier looks like the one following:
 

ka0PW0000001JSuYAM_0EM44000000gVTm.jpeg

5. Go to Query Details and note that all the columns are fetched (in red below) from the table to calculate the metric in Data Blending Engine:


*********   Visualization Summary Start  **********
Time Spent: 0.122 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below.
There are preparation tasks for each step that are not individually measured.
*********   Visualization Summary End    **********



************   Individual Step Start   ************
Number of Rows Returned: 153990
Time Spent: 0.011 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

select	([Order Id]@[ORDER_ID]) as ([Order Id]@[ORDER_ID]),
	[Shipper Id]@[SHIPPER_ID]
from	RELATION0-<Shipper Id, Order Id>
to	Ex4_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3986
Time Spent: 0.010 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

select	[Order Id]@[ORDER_ID]
from	Ex4_tempcube1
where	[Shipper Id]@[SHIPPER_ID] in (2)
to	Ex4_tempcube0
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.001 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

Drop	Ex4_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 27960
Time Spent: 0.049 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

Tables Accessed:
Table6	[F_ORDER_DETAILF9F6C7C31694FE17BD41973219117746]:	Promotion Id, Emp Id, Item Id, Order Date, Customer Id, Order Id, 	Unit Cost, Unit Price, Row Count - ORDER_DETAIL, Discount, Qty Sold, 	FACT_TABLE	

select	distinct	[Customer Id]@[CUSTOMER_ID],
	[Promotion Id]@[PROMOTION_ID],
	([Emp Id]@[EMP_ID]) as ([Emp Id]@[EMP_ID]),
	([Order Id]@[ORDER_ID]) as ([Order Id]@[ORDER_ID]),
	[Order Date]@[ORDER_DATE],
	[Item Id]@[ITEM_ID]
from	ORDER_DETAIL
with Table Join Tree: 	[F_ORDER_DETAILF9F6C7C31694FE17BD41973219117746]<<Order Id> in (Ex4_tempcube0)>
to	Ex4_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3986
Time Spent: 0.008 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

select	[Order Id]@[ORDER_ID],
	[Shipper Id]@[SHIPPER_ID]
from	RELATION0-<Shipper Id, Order Id>
where	[Shipper Id]@[SHIPPER_ID] in (2)
to	Ex4_tempcube3
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 27960
Time Spent: 0.024 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

select	*	
from	Left Outer Join(
	                Ex4_tempcube2, 
	                Ex4_tempcube3
	               ) on Order Id
to	Ex4_tempcube4
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

Drop	Ex4_tempcube0
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

Drop	Ex4_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

Drop	Ex4_tempcube3
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 34
Time Spent: 0.008 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

select	([Emp Id]@[EMP_ID]) as ([Emp Id]@[EMP_ID]),
	count([Order Id]@[ORDER_ID])@{[Emp Id]} as [Count of Order]
from	Ex4_tempcube4
where	[Shipper Id]@[SHIPPER_ID] in (2)
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 3/5/2019 6:27:00 PM
Query Execution End Time: 3/5/2019 6:27:00 PM

Drop	Ex4_tempcube4
************   Individual Step End     ************

CAUSE
This is a known defect in Strategy 10.11 and earlier releases.
ACTION
This defect is addressed in Strategy 11.0.
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.
Now, the product logic is improved to fetch only necessary columns and perform aggregation as required. Though, this is only a CSI optimization, you may see better data if the underlying table (in this case ORDER_DETAIL) has duplicated rows, as you can see in the screenshot below. Note that Emp Id 1 has 756 rows as opposed to 749 rows previously. This also because the CSI below uses first aggregation as COUNT() and second aggregation is done using SUM() because the Dynamic Aggregation function of COUNT() is SUM() function.

ka0PW0000001JSuYAM_0EM44000000gVTc.jpeg


*********   Visualization Summary Start  **********
Time Spent: 0.129 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below.
There are preparation tasks for each step that are not individually measured.
*********   Visualization Summary End    **********



************   Individual Step Start   ************
Number of Rows Returned: 153990
Time Spent: 0.020 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

Tables Accessed:
Table7	[REL_SHIPPER_ID_ORDER_ID]:	Shipper Id, Order Id, 		RELATIONSHIP_TABLE,	ONE_TO_MANY

select	[Order Id]@[ORDER_ID],
	[Shipper Id]@[SHIPPER_ID]
from	ORDER_FACT
with Table Join Tree: 	[REL_SHIPPER_ID_ORDER_ID]
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3986
Time Spent: 0.015 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

select	[Order Id]@[ORDER_ID]
from	RELATION0-<Shipper Id, Order Id>
where	[Shipper Id]@[SHIPPER_ID] in (2)
to	Ex4_tempcube0
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3986
Time Spent: 0.054 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

Tables Accessed:
Table6	[F_ORDER_DETAILF9F6C7C31694FE17BD41973219117746]:	Promotion Id, Emp Id, Item Id, Order Date, Customer Id, Order Id, 	Unit Cost, Unit Price, Row Count - ORDER_DETAIL, Discount, Qty Sold, 	FACT_TABLE	

select	[Emp Id]@[EMP_ID],
	[Order Id]@[ORDER_ID],
	count([Order Id]@[ORDER_ID])@{[Order Id],[Emp Id]} as [Count of Order]
from	ORDER_DETAIL
with Table Join Tree: 	[F_ORDER_DETAILF9F6C7C31694FE17BD41973219117746]<<Order Id> in (Ex4_tempcube0)>
to	Ex4_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3986
Time Spent: 0.009 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

select	[Order Id]@[ORDER_ID],
	[Shipper Id]@[SHIPPER_ID]
from	RELATION0-<Shipper Id, Order Id>
where	[Shipper Id]@[SHIPPER_ID] in (2)
to	Ex4_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3986
Time Spent: 0.008 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

select	*	
from	Left Outer Join(
	                Ex4_tempcube1, 
	                Ex4_tempcube2
	               ) on Order Id
to	Ex4_tempcube3
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

Drop	Ex4_tempcube0
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

Drop	Ex4_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

Drop	Ex4_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 34
Time Spent: 0.003 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

select	[Emp Id]@[EMP_ID],
	sum([Count of Order])@{[Emp Id]} as [Count of Order]
from	Ex4_tempcube3
where	[Shipper Id]@[SHIPPER_ID] in (2)
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 3/5/2019 6:48:07 PM
Query Execution End Time: 3/5/2019 6:48:07 PM

Drop	Ex4_tempcube3
************   Individual Step End     ************

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

Comment

0 comments

Details

Knowledge Article

Published:

October 18, 2018

Last Updated:

March 21, 2024