KB442264 : In-Memory query against Intelligent Cube incorrectly does a cross join instead of joining through available tables using which the attributes could be joined.

• Strategy

This article explains a workflow where customer may experience "intermediate table rows limit reached" error due to cross join in one of the Data Blending queries.

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

SYMPTOM

For a dossier that uses multiple datasets, customers may experience "intermediate table rows limit reached" error due to huge temp tables cross join.

CSI before fix:


************   Individual Step Start   ************
Number of Rows Returned: 3120
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/7/2019 4:14:38 PM
Query Execution End Time: 2/7/2019 4:14:38 PM

Tables Accessed:
Table5	[L_CREATEDDATE]:	Createddate, 		LOOKUP_TABLE	

select	[Createddate]@[CREATEDDATE]
from	#StagesProj
with Table Join Tree: 	[L_CREATEDDATE]
to	Ex144_tempcube4
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 3123
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/7/2019 4:14:38 PM
Query Execution End Time: 2/7/2019 4:14:38 PM

select	[Today Date 2]@[Today_date_2],
	[Createddate]@[CREATEDDATE]
from	RELATION33-<Today Date 2, Createddate, DaysBetweenP>
to	Ex144_tempcube5
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 3120
Time Spent: 0.003 sec(s)
Query Execution Start Time: 2/7/2019 4:14:38 PM
Query Execution End Time: 2/7/2019 4:14:38 PM

select	*	
from	Left Outer Join(
	                Ex144_tempcube4, 
	                Ex144_tempcube5
	               ) on Createddate
to	Ex144_tempcube6
************   Individual Step End     ************
************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/7/2019 4:14:38 PM
Query Execution End Time: 2/7/2019 4:14:38 PM

Drop	Ex144_tempcube4
************   Individual Step End     ************
************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/7/2019 4:14:38 PM
Query Execution End Time: 2/7/2019 4:14:38 PM

Drop	Ex144_tempcube5
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 34649
Time Spent: 0.003 sec(s)
Query Execution Start Time: 2/7/2019 4:14:38 PM
Query Execution End Time: 2/7/2019 4:14:38 PM

Tables Accessed:
Table88	[F_Opportunities80B0FC9011E8A16F2F900080EFC5CD22]:	Todays Date, Today Minus 1 Week, Enterprise Support  C, Needs Completed  C, Plan Completed  C, Approval Status, Opp Stage Short, Lead Source, Psd, Psd Sfid, Territory, Group, Business Unit, Lead Originator, Last Modified By, Owner, Stage Last Changed Date, Created By, Close Date, Date Id, Date, Due Date, Last Modified Date, Next Action, Create Date, Opportunity Name, Opp Link, Account Id, Id, 	Transaction Amount All, Es Opp, Es Opp S6, Es Opp S1 S5, Transactional Amount Es, Gross Licenses, Licensesk Net License, Probability, Consulting, Expense, Opp  Mod Age, Opp Age, Row Count - Opportunities, Days Pending Approval, Services, Transactional Amount Es S1 S5, Transactional Amount Es S6, Net License Es, Net License Es S1 S5, Net License Es S6, Commissionable Amount, # Opp, Tr Am No Exch Rate, 	FACT_TABLE	

select	[Psd]@[PSD],
	[Id]@[ID]
from	AccOppCube
with Table Join Tree: 	[F_Opportunities80B0FC9011E8A16F2F900080EFC5CD22]
to	Ex144_tempcube7
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 
Time Spent: 
Query Execution Start Time: 2/7/2019 4:14:38 PM
Query Execution End Time: 

select	*	
from	Left Outer Join(
	                Left Outer Join(
	                                Ex144_tempcube6, 
	                                RELATION116-<Proj Id, Id>
	                               ) on Proj Id, 
	                Left Outer Join(
	                                Ex144_tempcube7, 
	                                RELATION116-<Proj Id, Id>
	                               ) on Id
	               ) on Proj Id, Id
to	Ex144_tempcube8
************   Individual Step End     ************
...

The above query (in red) produces a cross join query and resulted it too many rows to run into "intermediate table rows limit reached" error.

ACTION

This defect is addressed in Strategy 11.1 release.
If you are upgrading to Strategy 11.1 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.

CSI after fix:


************   Individual Step Start   ************
Number of Rows Returned: 84
Time Spent: 0.001 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
select        [Proj Url]@[Proj_URL]
from          RELATION81-<Proj Stage, Proj Url>
where       [Proj Stage]@[Proj_Stage] in (E5)
to                Ex54_tempcube19
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 84
Time Spent: 0.001 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
select        [Proj Url]@[Proj_URL]
from          RELATION81-<Proj Stage, Proj Url>
where       [Proj Stage]@[Proj_Stage] in (E5)
to                Ex54_tempcube8
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 385
Time Spent: 0.012 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
Tables Accessed:
Table52    [REL_PROJ_STAGE_PROJ_URL]:                Proj Stage, Proj Url,                                        RELATIONSHIP_TABLE,                    ONE_TO_MANY
Table72    [REL_PROJ_URL_PROJ_ID]:     Proj Url, Proj Id,                            RELATIONSHIP_TABLE,              ONE_TO_MANY
 
select        [Proj Stage]@[Proj_Stage],
                    [Proj Id]@[Proj_ID]
from          ESProjectsCube
with Table Join Tree:                  [REL_PROJ_URL_PROJ_ID]
                     Join [REL_PROJ_STAGE_PROJ_URL] with output level Tuple([Proj Stage]@[Proj_Stage], [Proj Id]@[Proj_ID])
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 385
Time Spent: 0.003 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
select        [Proj Stage]@[Proj_Stage],
                    ([Proj Id]@[Proj_ID]) as ([Proj Id]@[PROJ_ID])
from          RELATION112-<Proj Stage, Proj Id>
to                Ex54_tempcube20
************   Individual Step End     ************ 
************   Individual Step Start   ************
Number of Rows Returned: 84
Time Spent: 0.001 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
select        [Proj Id]@[PROJ_ID]
from          Ex54_tempcube20
where       [Proj Stage]@[Proj_Stage] in (E5)
to                Ex54_tempcube0
************   Individual Step End     ************
************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
Drop          Ex54_tempcube20
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 3120
Time Spent: 0.003 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
Tables Accessed:
Table5       [L_CREATEDDATE]:  Createddate,                                 LOOKUP_TABLE        
 
select        [Createddate]@[CREATEDDATE]
from          #StagesProj
with Table Join Tree:                  [L_CREATEDDATE]
to                Ex54_tempcube21
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 3123
Time Spent: 0.003 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
select        [Today Date 2]@[Today_date_2],
                    [Createddate]@[CREATEDDATE]
from          RELATION32-<Today Date 2, Createddate, DaysBetweenP>
to                Ex54_tempcube22
************   Individual Step End     ************ 
************   Individual Step Start   ************
Number of Rows Returned: 3120
Time Spent: 0.003 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
select        *                 
from          Left Outer Join(
                                    Ex54_tempcube21, 
                                    Ex54_tempcube22
                                   ) on Createddate
to                Ex54_tempcube23
************   Individual Step End     ************
************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
Drop          Ex54_tempcube21
************   Individual Step End     ************
************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
Drop          Ex54_tempcube22
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 34649
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:07 PM
 
Tables Accessed:
Table88    [F_Opportunities80B0FC9011E8A16F2F900080EFC5CD22]:   Todays Date, Today Minus 1 Week, Enterprise Support  C, Needs Completed  C, Plan Completed  C, Approval Status, Opp Stage Short, Lead Source, Psd, Psd Sfid, Territory, Group, Business Unit, Lead Originator, Last Modified By, Owner, Stage Last Changed Date, Created By, Close Date, Date Id, Date, Due Date, Last Modified Date, Next Action, Create Date, Opportunity Name, Opp Link, Account Id, Id,           Transaction Amount All, Es Opp, Es Opp S6, Es Opp S1 S5, Transactional Amount Es, Gross Licenses, Licensesk Net License, Probability, Consulting, Expense, Opp  Mod Age, Opp Age, Row Count - Opportunities, Days Pending Approval, Services, Transactional Amount Es S1 S5, Transactional Amount Es S6, Net License Es, Net License Es S1 S5, Net License Es S6, Commissionable Amount, # Opp, Tr Am No Exch Rate,                     FACT_TABLE               
 
select        [Psd]@[PSD],
                    [Id]@[ID]
from          AccOppCube
with Table Join Tree:                  [F_Opportunities80B0FC9011E8A16F2F900080EFC5CD22]
to                Ex54_tempcube24
************   Individual Step End     ************
************   Individual Step Start   ************
Number of Rows Returned: 464688
Time Spent: 0.157 sec(s)
Query Execution Start Time: 2/7/2019 3:34:07 PM
Query Execution End Time: 2/7/2019 3:34:08 PM
 
select        *                 
from          Left Outer Join(
                                    Left Outer Join(
                                                    Ex54_tempcube23, 
                                                    RELATION31-<Today Date 2, Id>
                                                   ) on Today Date 2, 
                                    Left Outer Join(
                                                    Ex54_tempcube24, 
                                                    RELATION31-<Today Date 2, Id>
                                                   ) on Id
                                   ) on Id, Today Date 2
to                Ex54_tempcube25
************   Individual Step End     ************
...

CSI is optimized that more passes are used in early stage to eliminate unnecessary data. With temp tables containing more precise data, the final query (above in green) produced is able to execute and resulted into the final visualization.
The Strategy Internal Reference Number for the issue discussed in this technical note is KB442264 and DE116716.

Comment

0 comments

Details

Knowledge Article

Published:

November 19, 2018

Last Updated:

March 21, 2024

************ Individual Step Start ************ Number of Rows Returned: 3120 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/7/2019 4:14:38 PM Query Execution End Time: 2/7/2019 4:14:38 PM Tables Accessed: Table5 [L_CREATEDDATE]: Createddate, LOOKUP_TABLE select [Createddate]@[CREATEDDATE] from #StagesProj with Table Join Tree: [L_CREATEDDATE] to Ex144_tempcube4 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 3123 Time Spent: 0.000 sec(s) Query Execution Start Time: 2/7/2019 4:14:38 PM Query Execution End Time: 2/7/2019 4:14:38 PM select [Today Date 2]@[Today_date_2], [Createddate]@[CREATEDDATE] from RELATION33-<Today Date 2, Createddate, DaysBetweenP> to Ex144_tempcube5 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 3120 Time Spent: 0.003 sec(s) Query Execution Start Time: 2/7/2019 4:14:38 PM Query Execution End Time: 2/7/2019 4:14:38 PM select * from Left Outer Join( Ex144_tempcube4, Ex144_tempcube5 ) on Createddate to Ex144_tempcube6 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/7/2019 4:14:38 PM Query Execution End Time: 2/7/2019 4:14:38 PM Drop Ex144_tempcube4 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/7/2019 4:14:38 PM Query Execution End Time: 2/7/2019 4:14:38 PM Drop Ex144_tempcube5 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 34649 Time Spent: 0.003 sec(s) Query Execution Start Time: 2/7/2019 4:14:38 PM Query Execution End Time: 2/7/2019 4:14:38 PM Tables Accessed: Table88 [F_Opportunities80B0FC9011E8A16F2F900080EFC5CD22]: Todays Date, Today Minus 1 Week, Enterprise Support C, Needs Completed C, Plan Completed C, Approval Status, Opp Stage Short, Lead Source, Psd, Psd Sfid, Territory, Group, Business Unit, Lead Originator, Last Modified By, Owner, Stage Last Changed Date, Created By, Close Date, Date Id, Date, Due Date, Last Modified Date, Next Action, Create Date, Opportunity Name, Opp Link, Account Id, Id, Transaction Amount All, Es Opp, Es Opp S6, Es Opp S1 S5, Transactional Amount Es, Gross Licenses, Licensesk Net License, Probability, Consulting, Expense, Opp Mod Age, Opp Age, Row Count - Opportunities, Days Pending Approval, Services, Transactional Amount Es S1 S5, Transactional Amount Es S6, Net License Es, Net License Es S1 S5, Net License Es S6, Commissionable Amount, # Opp, Tr Am No Exch Rate, FACT_TABLE select [Psd]@[PSD], [Id]@[ID] from AccOppCube with Table Join Tree: [F_Opportunities80B0FC9011E8A16F2F900080EFC5CD22] to Ex144_tempcube7 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: Time Spent: Query Execution Start Time: 2/7/2019 4:14:38 PM Query Execution End Time: select * from Left Outer Join( Left Outer Join( Ex144_tempcube6, RELATION116-<Proj Id, Id> ) on Proj Id, Left Outer Join( Ex144_tempcube7, RELATION116-<Proj Id, Id> ) on Id ) on Proj Id, Id to Ex144_tempcube8 ************ Individual Step End ************ ...

ACTION

************ Individual Step Start ************ Number of Rows Returned: 84 Time Spent: 0.001 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM select [Proj Url]@[Proj_URL] from RELATION81-<Proj Stage, Proj Url> where [Proj Stage]@[Proj_Stage] in (E5) to Ex54_tempcube19 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 84 Time Spent: 0.001 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM select [Proj Url]@[Proj_URL] from RELATION81-<Proj Stage, Proj Url> where [Proj Stage]@[Proj_Stage] in (E5) to Ex54_tempcube8 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 385 Time Spent: 0.012 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM Tables Accessed: Table52 [REL_PROJ_STAGE_PROJ_URL]: Proj Stage, Proj Url, RELATIONSHIP_TABLE, ONE_TO_MANY Table72 [REL_PROJ_URL_PROJ_ID]: Proj Url, Proj Id, RELATIONSHIP_TABLE, ONE_TO_MANY select [Proj Stage]@[Proj_Stage], [Proj Id]@[Proj_ID] from ESProjectsCube with Table Join Tree: [REL_PROJ_URL_PROJ_ID] Join [REL_PROJ_STAGE_PROJ_URL] with output level Tuple([Proj Stage]@[Proj_Stage], [Proj Id]@[Proj_ID]) ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 385 Time Spent: 0.003 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM select [Proj Stage]@[Proj_Stage], ([Proj Id]@[Proj_ID]) as ([Proj Id]@[PROJ_ID]) from RELATION112-<Proj Stage, Proj Id> to Ex54_tempcube20 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 84 Time Spent: 0.001 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM select [Proj Id]@[PROJ_ID] from Ex54_tempcube20 where [Proj Stage]@[Proj_Stage] in (E5) to Ex54_tempcube0 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM Drop Ex54_tempcube20 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 3120 Time Spent: 0.003 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM Tables Accessed: Table5 [L_CREATEDDATE]: Createddate, LOOKUP_TABLE select [Createddate]@[CREATEDDATE] from #StagesProj with Table Join Tree: [L_CREATEDDATE] to Ex54_tempcube21 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 3123 Time Spent: 0.003 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM select [Today Date 2]@[Today_date_2], [Createddate]@[CREATEDDATE] from RELATION32-<Today Date 2, Createddate, DaysBetweenP> to Ex54_tempcube22 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 3120 Time Spent: 0.003 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM select * from Left Outer Join( Ex54_tempcube21, Ex54_tempcube22 ) on Createddate to Ex54_tempcube23 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM Drop Ex54_tempcube21 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM Drop Ex54_tempcube22 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 34649 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:07 PM Tables Accessed: Table88 [F_Opportunities80B0FC9011E8A16F2F900080EFC5CD22]: Todays Date, Today Minus 1 Week, Enterprise Support C, Needs Completed C, Plan Completed C, Approval Status, Opp Stage Short, Lead Source, Psd, Psd Sfid, Territory, Group, Business Unit, Lead Originator, Last Modified By, Owner, Stage Last Changed Date, Created By, Close Date, Date Id, Date, Due Date, Last Modified Date, Next Action, Create Date, Opportunity Name, Opp Link, Account Id, Id, Transaction Amount All, Es Opp, Es Opp S6, Es Opp S1 S5, Transactional Amount Es, Gross Licenses, Licensesk Net License, Probability, Consulting, Expense, Opp Mod Age, Opp Age, Row Count - Opportunities, Days Pending Approval, Services, Transactional Amount Es S1 S5, Transactional Amount Es S6, Net License Es, Net License Es S1 S5, Net License Es S6, Commissionable Amount, # Opp, Tr Am No Exch Rate, FACT_TABLE select [Psd]@[PSD], [Id]@[ID] from AccOppCube with Table Join Tree: [F_Opportunities80B0FC9011E8A16F2F900080EFC5CD22] to Ex54_tempcube24 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 464688 Time Spent: 0.157 sec(s) Query Execution Start Time: 2/7/2019 3:34:07 PM Query Execution End Time: 2/7/2019 3:34:08 PM select * from Left Outer Join( Left Outer Join( Ex54_tempcube23, RELATION31-<Today Date 2, Id> ) on Today Date 2, Left Outer Join( Ex54_tempcube24, RELATION31-<Today Date 2, Id> ) on Id ) on Id, Today Date 2 to Ex54_tempcube25 ************ Individual Step End ************ ...