KB441491: Different Fact table is used for fact extension between Live and In-Memory Intelligent Cube

• Strategy

This knowledge base article describes an issue where different fact table is being used for fact extension between Live and In-Memory Intelligent Cube.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Symptom
In Strategy 10.x, when there are multiple fact tables available to perform the fact extension, different fact table are used by the analytical engine for the fact extension in Connect Live and In-Memory Intelligent Cube.
Steps To Reproduce
1. Strategy Tutorial is used in this example.
2. Create a Data Import Live Intelligent Cube. Here is the cube definition:

3. Create a Dossier using the above cube. Put "Cust State Id", "Month Id" attributes and "Tot Cost" on visualization.

4. Go to visualization menu and select Query Details and note the SQL that is generated.


...

Tables Accessed:
LU_CUST_CITY    
MIN_LU_CALL_CTR    
CITY_CTR_SLS    
MIN_LU_MONTH    
MIN_FACT_2010    

SQL Statements:

Pass0 -     Query Pass Start Time:        1/29/2019 1:04:29 PM
    Query Pass End Time:        1/29/2019 1:04:29 PM
    Query Execution:    0:00:00.13
    Data Fetching and Processing:    0:00:00.00
      Data Transfer from Datasource(s):    0:00:00.00
    Other Processing:    0:00:00.02
create table T1TW3AGSSOP000 nologging as
select    distinct a12.MONTH_ID  MONTH_ID,
    a12.CALL_CTR_ID  CALL_CTR_ID
from    T177MQWH.MIN_LU_CALL_CTR    a11
    join    T177MQWH.MIN_FACT_2010    a12
      on     (a11.CALL_CTR_ID = a12.CALL_CTR_ID) 

Pass1 -     Query Pass Start Time:        1/29/2019 1:04:29 PM
    Query Pass End Time:        1/29/2019 1:04:29 PM
    Query Execution:    0:00:00.03
    Data Fetching and Processing:    0:00:00.01
      Data Transfer from Datasource(s):    0:00:00.01
    Other Processing:    0:00:00.02
    Rows selected: 576
select    pa12.MONTH_ID  MONTH_ID,
    a14.MONTH_DESC  MONTH_DESC,
    a13.CUST_STATE_ID  CUST_STATE_ID,
    sum(a11.TOT_COST)  WJXBFS1
from    T177MQWH.CITY_CTR_SLS    a11
    join    T1TW3AGSSOP000    pa12
      on     (a11.CALL_CTR_ID = pa12.CALL_CTR_ID)
    join    T177MQWH.LU_CUST_CITY    a13
      on     (a11.CUST_CITY_ID = a13.CUST_CITY_ID)
    join    T177MQWH.MIN_LU_MONTH    a14
      on     (pa12.MONTH_ID = a14.MONTH_ID)
group by    pa12.MONTH_ID,
    a14.MONTH_DESC,
    a13.CUST_STATE_ID

Pass2 -     Query Pass Start Time:        1/29/2019 1:04:29 PM
    Query Pass End Time:        1/29/2019 1:04:29 PM
    Query Execution:    0:00:00.00
    Data Fetching and Processing:    0:00:00.00
      Data Transfer from Datasource(s):    0:00:00.00
    Other Processing:    0:00:00.00
[Populate Report Data]

Pass3 -     Query Pass Start Time:        1/29/2019 1:04:29 PM
    Query Pass End Time:        1/29/2019 1:04:29 PM
    Query Execution:    0:00:00.04
    Data Fetching and Processing:    0:00:00.00
      Data Transfer from Datasource(s):    0:00:00.00
    Other Processing:    0:00:00.02
drop table T1TW3AGSSOP000 purge

[Analytical engine calculation steps:
    1.  Perform cross-tabbing
]


********* End of SQL for DI01.Multi.Fact Table_0 *********
select    [Month Id]@[MONTH_ID],
    [Month Id]@[MONTH_DESC],
    [Cust State Id]@[CUST_STATE_ID],
    [Tot Cost]
from    DI01.Multi.Fact Table_0

Note how analytical engine picks MIN_FACT_2010 table to get the distinct tuple for Call Center and Month attribute.
5. Change the data access mode from 'Connect Live' to 'In-Memory'.

6. Notice the visualization data is changed.

7. Look at the query details and note that a different fact table [F_SUBCATEG_MNTH_CTR_SLS19B2BE4A4D29B65AF84601B347419DE0] is being used for fact extension.


Tables Accessed:
Table10    [L_MONTH_ID]:    Month Id,         LOOKUP_TABLE    
Table19    [REL_CUST_STATE_ID_CUST_CITY_ID]:    Cust State Id, Cust City Id,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table35    [F_SUBCATEG_MNTH_CTR_SLS19B2BE4A4D29B65AF84601B347419DE0]:    Call Ctr Id, Subcat Id, Month Id,     Row Count - SUBCATEG_MNTH_CTR_SLS, Tot Unit Sales,     FACT_TABLE    
Table41    [F_CITY_CTR_SLS1FD2FEB047F0A42C15D23E873C3583EF]:    Call Ctr Id, Cust City Id,     Tot Cost, Row Count - CITY_CTR_SLS,     FACT_TABLE    

select    [Month Id]@[MONTH_ID],
    [Month Id]@[MONTH_DESC],
    [Cust State Id]@[CUST_STATE_ID],
    sum([[F_CITY_CTR_SLS1FD2FEB047F0A42C15D23E873C3583EF].Tot Cost])@{[Month Id],[Cust State Id]} as [Tot Cost]
from    IM.DI01.Multi.Fact Table
with Table Join Tree:     [F_CITY_CTR_SLS1FD2FEB047F0A42C15D23E873C3583EF]
     Join [REL_CUST_STATE_ID_CUST_CITY_ID] with output level Tuple([Cust City Id]@[CUST_CITY_ID], [Call Ctr Id]@[CALL_CTR_ID], [Cust State Id]@[CUST_STATE_ID])
     Join (Set of distinct Tuple([Call Ctr Id]@[CALL_CTR_ID], [Month Id]@[MONTH_ID]) where Tuple([Call Ctr Id]@[CALL_CTR_ID], [Month Id]@[MONTH_ID]) in [F_SUBCATEG_MNTH_CTR_SLS19B2BE4A4D29B65AF84601B347419DE0]) with output level Tuple([Cust City Id]@[CUST_CITY_ID], [Call Ctr Id]@[CALL_CTR_ID], [Month Id]@[MONTH_ID], [Cust State Id]@[CUST_STATE_ID])

Cause
This is a known inconsistency in Strategy analytical engine where it may pick different tables for fact extension against In-Memory vs Connect Live Intelligent Cubes.
Action
This issue is fixed 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.
Starting Strategy 11.0, the analytical engine will only use the fact table with smallest logic size containing the needed attribute to create an intermediate fact table, making In-Memory and Connect Live behavior consistent.
SQL against Live intelligent Cube:


Tables Accessed:
CITY_CTR_SLS	
LU_CUST_CITY	
MIN_LU_CALL_CTR	
MIN_FACT_2010	

SQL Statements:

create table TA0I81QTXOP000 nologging as
select	distinct a12.YEAR_ID  YEAR_ID,
	a12.CALL_CTR_ID  CALL_CTR_ID
from	T177MQWH.MIN_LU_CALL_CTR	a11
	join	T177MQWH.MIN_FACT_2010	a12
	  on 	(a11.CALL_CTR_ID = a12.CALL_CTR_ID)

CSI against In-memory Intelligent Cube:


select	[Cust State Id]@[CUST_STATE_ID],
	[Year Id]@[YEAR_ID],
	sum([[F_CITY_CTR_SLSEAE72D1B1651608A22B1919812945917].Tot Cost])@{[Cust State Id],[Year Id]} as [Tot Cost]
from	CITY_CTR_SLS LU_CALL_CTR LU_SU... (21 tables)_Multi-table Fact
with Table Join Tree: 	[F_CITY_CTR_SLSEAE72D1B1651608A22B1919812945917]
	 Join [REL_CUST_STATE_ID_CUST_CITY_ID] with output level Tuple([Cust City Id]@[CUST_CITY_ID], [Cust State Id]@[CUST_STATE_ID], [Call Ctr Id]@[CALL_CTR_ID])
	 Join (Set of distinct Tuple([Call Ctr Id]@[CALL_CTR_ID], [Quarter Id]@[QUARTER_ID], [Year Id]@[YEAR_ID]) where Tuple([Call Ctr Id]@[CALL_CTR_ID], [Quarter Id]@[QUARTER_ID], [Year Id]@[YEAR_ID]) 
in [F_MIN_FACT_2010EAE72D1B1651608A22B197231C173745]) 
with output level Tuple([Cust City Id]@[CUST_CITY_ID], [Cust State Id]@[CUST_STATE_ID], [Call Ctr Id]@[CALL_CTR_ID], [Year Id]@[YEAR_ID])

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

Comment

0 comments

Details

Knowledge Article

Published:

August 7, 2018

Last Updated:

March 21, 2024

... Tables Accessed: LU_CUST_CITY MIN_LU_CALL_CTR CITY_CTR_SLS MIN_LU_MONTH MIN_FACT_2010 SQL Statements: Pass0 - Query Pass Start Time: 1/29/2019 1:04:29 PM Query Pass End Time: 1/29/2019 1:04:29 PM Query Execution: 0:00:00.13 Data Fetching and Processing: 0:00:00.00 Data Transfer from Datasource(s): 0:00:00.00 Other Processing: 0:00:00.02 create table T1TW3AGSSOP000 nologging as select distinct a12.MONTH_ID MONTH_ID, a12.CALL_CTR_ID CALL_CTR_ID from T177MQWH.MIN_LU_CALL_CTR a11 join T177MQWH.MIN_FACT_2010 a12 on (a11.CALL_CTR_ID = a12.CALL_CTR_ID) Pass1 - Query Pass Start Time: 1/29/2019 1:04:29 PM Query Pass End Time: 1/29/2019 1:04:29 PM Query Execution: 0:00:00.03 Data Fetching and Processing: 0:00:00.01 Data Transfer from Datasource(s): 0:00:00.01 Other Processing: 0:00:00.02 Rows selected: 576 select pa12.MONTH_ID MONTH_ID, a14.MONTH_DESC MONTH_DESC, a13.CUST_STATE_ID CUST_STATE_ID, sum(a11.TOT_COST) WJXBFS1 from T177MQWH.CITY_CTR_SLS a11 join T1TW3AGSSOP000 pa12 on (a11.CALL_CTR_ID = pa12.CALL_CTR_ID) join T177MQWH.LU_CUST_CITY a13 on (a11.CUST_CITY_ID = a13.CUST_CITY_ID) join T177MQWH.MIN_LU_MONTH a14 on (pa12.MONTH_ID = a14.MONTH_ID) group by pa12.MONTH_ID, a14.MONTH_DESC, a13.CUST_STATE_ID Pass2 - Query Pass Start Time: 1/29/2019 1:04:29 PM Query Pass End Time: 1/29/2019 1:04:29 PM Query Execution: 0:00:00.00 Data Fetching and Processing: 0:00:00.00 Data Transfer from Datasource(s): 0:00:00.00 Other Processing: 0:00:00.00 [Populate Report Data] Pass3 - Query Pass Start Time: 1/29/2019 1:04:29 PM Query Pass End Time: 1/29/2019 1:04:29 PM Query Execution: 0:00:00.04 Data Fetching and Processing: 0:00:00.00 Data Transfer from Datasource(s): 0:00:00.00 Other Processing: 0:00:00.02 drop table T1TW3AGSSOP000 purge [Analytical engine calculation steps: 1. Perform cross-tabbing ] ********* End of SQL for DI01.Multi.Fact Table_0 ********* select [Month Id]@[MONTH_ID], [Month Id]@[MONTH_DESC], [Cust State Id]@[CUST_STATE_ID], [Tot Cost] from DI01.Multi.Fact Table_0

Tables Accessed: Table10 [L_MONTH_ID]: Month Id, LOOKUP_TABLE Table19 [REL_CUST_STATE_ID_CUST_CITY_ID]: Cust State Id, Cust City Id, RELATIONSHIP_TABLE, ONE_TO_MANY Table35 [F_SUBCATEG_MNTH_CTR_SLS19B2BE4A4D29B65AF84601B347419DE0]: Call Ctr Id, Subcat Id, Month Id, Row Count - SUBCATEG_MNTH_CTR_SLS, Tot Unit Sales, FACT_TABLE Table41 [F_CITY_CTR_SLS1FD2FEB047F0A42C15D23E873C3583EF]: Call Ctr Id, Cust City Id, Tot Cost, Row Count - CITY_CTR_SLS, FACT_TABLE select [Month Id]@[MONTH_ID], [Month Id]@[MONTH_DESC], [Cust State Id]@[CUST_STATE_ID], sum([[F_CITY_CTR_SLS1FD2FEB047F0A42C15D23E873C3583EF].Tot Cost])@{[Month Id],[Cust State Id]} as [Tot Cost] from IM.DI01.Multi.Fact Table with Table Join Tree: [F_CITY_CTR_SLS1FD2FEB047F0A42C15D23E873C3583EF] Join [REL_CUST_STATE_ID_CUST_CITY_ID] with output level Tuple([Cust City Id]@[CUST_CITY_ID], [Call Ctr Id]@[CALL_CTR_ID], [Cust State Id]@[CUST_STATE_ID]) Join (Set of distinct Tuple([Call Ctr Id]@[CALL_CTR_ID], [Month Id]@[MONTH_ID]) where Tuple([Call Ctr Id]@[CALL_CTR_ID], [Month Id]@[MONTH_ID]) in [F_SUBCATEG_MNTH_CTR_SLS19B2BE4A4D29B65AF84601B347419DE0]) with output level Tuple([Cust City Id]@[CUST_CITY_ID], [Call Ctr Id]@[CALL_CTR_ID], [Month Id]@[MONTH_ID], [Cust State Id]@[CUST_STATE_ID])

Tables Accessed: CITY_CTR_SLS LU_CUST_CITY MIN_LU_CALL_CTR MIN_FACT_2010 SQL Statements: create table TA0I81QTXOP000 nologging as select distinct a12.YEAR_ID YEAR_ID, a12.CALL_CTR_ID CALL_CTR_ID from T177MQWH.MIN_LU_CALL_CTR a11 join T177MQWH.MIN_FACT_2010 a12 on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)

select [Cust State Id]@[CUST_STATE_ID], [Year Id]@[YEAR_ID], sum([[F_CITY_CTR_SLSEAE72D1B1651608A22B1919812945917].Tot Cost])@{[Cust State Id],[Year Id]} as [Tot Cost] from CITY_CTR_SLS LU_CALL_CTR LU_SU... (21 tables)_Multi-table Fact with Table Join Tree: [F_CITY_CTR_SLSEAE72D1B1651608A22B1919812945917] Join [REL_CUST_STATE_ID_CUST_CITY_ID] with output level Tuple([Cust City Id]@[CUST_CITY_ID], [Cust State Id]@[CUST_STATE_ID], [Call Ctr Id]@[CALL_CTR_ID]) Join (Set of distinct Tuple([Call Ctr Id]@[CALL_CTR_ID], [Quarter Id]@[QUARTER_ID], [Year Id]@[YEAR_ID]) where Tuple([Call Ctr Id]@[CALL_CTR_ID], [Quarter Id]@[QUARTER_ID], [Year Id]@[YEAR_ID]) in [F_MIN_FACT_2010EAE72D1B1651608A22B197231C173745]) with output level Tuple([Cust City Id]@[CUST_CITY_ID], [Cust State Id]@[CUST_STATE_ID], [Call Ctr Id]@[CALL_CTR_ID], [Year Id]@[YEAR_ID])