Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
SYMPTOM:
This technical article describes an issue when adding a derived metric to a Visual Insight Dashboard resulting in a cross join. Attempting to change dashboard properties option "Allow joins across datasets based on unrelated common attributes" or changing the derived metric parameters "Use lookup for attributes" cannot resolve the issue. This only affects the SQL and Data remains same with cross join. Cross join may have performance implication on the Database side.
STEPS TO REPRODUCE:
1. In Strategy Tutorial Web, create a New Dossier.
2. Click "Existing Objects" and create a Connect Live dataset like this:
2. Create a derived metric called Countitem with this expression: Count<Distinct=True>(Item){~+}
3. Add Category attribute and Revenue, Countitem metric to visualization.
4. Add following filters to visualization using 'Edit Filter..." option.
5. Use Query Details option to obtain the SQL of the visualization. Note that while calculating the Countitem metric, there is a cross join (in red below) just to apply the Date filter, which is unnecessary.
...
SQL Statements:
Pass0 - Query Pass Start Time: 12/20/2018 4:25:09 PM
Query Pass End Time: 12/20/2018 4:25:09 PM
Query Execution: 0:00:00.07
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 TI7MVSC7PMD000 nologging as
select a13.CATEGORY_ID CATEGORY_ID_2,
count(distinct a11.ITEM_ID) WJXBFS1
from LU_ITEM a11
cross join LU_DAY a12
join LU_SUBCATEG a13
on (a11.SUBCAT_ID = a13.SUBCAT_ID)
where (a13.CATEGORY_ID in (1)
and a12.DAY_DATE in (To_Date('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), To_Date('2008-01-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')))
group by a13.CATEGORY_ID
Pass1 - Query Pass Start Time: 12/20/2018 4:25:09 PM
Query Pass End Time: 12/20/2018 4:25:09 PM
Query Execution: 0:00:00.18
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 TARPNNBXHMD001 nologging as
select a13.CATEGORY_ID CATEGORY_ID_2,
sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) WJXBFS1
from ORDER_DETAIL a11
join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)
join LU_SUBCATEG a13
on (a12.SUBCAT_ID = a13.SUBCAT_ID)
where (a13.CATEGORY_ID in (1)
and a11.ORDER_DATE in (To_Date('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), To_Date('2008-01-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')))
group by a13.CATEGORY_ID
Pass2 - Query Pass Start Time: 12/20/2018 4:25:09 PM
Query Pass End Time: 12/20/2018 4:25:09 PM
Query Execution: 0:00:00.01
Data Fetching and Processing: 0:00:00.00
Data Transfer from Datasource(s): 0:00:00.00
Other Processing: 0:00:00.02
Rows selected: 1
select pa11.CATEGORY_ID_2 CATEGORY_ID_2,
a13.CATEGORY_DESC CATEGORY_DESC,
pa11.WJXBFS1 WJXBFS1,
pa12.WJXBFS1 WJXBFS2
from TI7MVSC7PMD000 pa11
left outer join TARPNNBXHMD001 pa12
on (pa11.CATEGORY_ID_2 = pa12.CATEGORY_ID_2)
join LU_CATEGORY a13
on (pa11.CATEGORY_ID_2 = a13.CATEGORY_ID)
...
CAUSE:
This is a known defect in releases earlier than Strategy 10.11.
ACTION:
This issue has been addressed in Strategy 10.11.
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.
If you are upgrading to Strategy 10.9 / 10.10 / 10.11: To pick up the fix, enable the Feature Flag "Defect fixes that may impact Data, SQL, MDX, CSI" within the Web Preferences -> Project Defaults. For more details on enabling Feature Flags refer to What are the Feature Flag options and what do they accomplish in MicroStrategy Web 10.8 and up. Starting Strategy 11.0, this Feature Flag is removed and replaced with project level setting called "Data Engine Version".
Now, the SQL is fixed to remove the cross join (see Pass1 below). Here is the SQL for the same dossier used above.
...
SQL Statements:
Pass0 - Query Pass Start Time: 12/20/2018 4:27:33 PM
Query Pass End Time: 12/20/2018 4:27:33 PM
Query Execution: 0:00:00.31
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 TLM9Y1DNMMD000 nologging as
select a13.CATEGORY_ID CATEGORY_ID_2,
sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) WJXBFS1
from ORDER_DETAIL a11
join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)
join LU_SUBCATEG a13
on (a12.SUBCAT_ID = a13.SUBCAT_ID)
where (a13.CATEGORY_ID in (1)
and a11.ORDER_DATE in (To_Date('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), To_Date('2008-01-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')))
group by a13.CATEGORY_ID
Pass1 - Query Pass Start Time: 12/20/2018 4:27:33 PM
Query Pass End Time: 12/20/2018 4:27:33 PM
Query Execution: 0:00:00.06
Data Fetching and Processing: 0:00:00.00
Data Transfer from Datasource(s): 0:00:00.00
Other Processing: 0:00:00.03
create table TP3J7X8HUMD001 nologging as
select a12.CATEGORY_ID CATEGORY_ID_2,
count(distinct a11.ITEM_ID) WJXBFS1
from LU_ITEM a11
join LU_SUBCATEG a12
on (a11.SUBCAT_ID = a12.SUBCAT_ID)
where a12.CATEGORY_ID in (1)
group by a12.CATEGORY_ID
Pass2 - Query Pass Start Time: 12/20/2018 4:27:33 PM
Query Pass End Time: 12/20/2018 4:27:33 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.04
Rows selected: 1
select pa11.CATEGORY_ID_2 CATEGORY_ID_2,
a13.CATEGORY_DESC CATEGORY_DESC,
pa12.WJXBFS1 WJXBFS1,
pa11.WJXBFS1 WJXBFS2
from TP3J7X8HUMD001 pa11
left outer join TLM9Y1DNMMD000 pa12
on (pa11.CATEGORY_ID_2 = pa12.CATEGORY_ID_2)
join LU_CATEGORY a13
on (pa11.CATEGORY_ID_2 = a13.CATEGORY_ID)
...
The Strategy Internal Reference Number for the issue discussed in this technical note is KB440641 and DE72285.
