Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
SYMPTOM
As of Strategy 10.7, the in-memory query generation engine (aka CSI Engine) can only use explicit many-to-many relationship tables to extend the data after aggregating it at the desired dimension. Starting Strategy 10.8 and 10.4.7, the CSI Engine can leverage the user imported tables for many-to-many relationship to trigger Alternative CSI for better performance. Consider the following example:
STEPS TO REPRODUCE:
1. This example uses Strategy Tutorial project to demonstrate the new CSI optimization.
2. In Strategy Web,create a new Dossier.
3. Click New Data and add ORDER_DETAIL and REL_CAT_ITEM tables map all the attributes to project attributes. Note that Catalog and Item are defined as many-to-many relationship in project schema.
4. This is how the Data Import Intelligent Cube should look. Note that italics means that the attribute is mapped to project attribute.
5. Publish the Cube in In-Memory access mode.
6. Put Catalog attribute and Discount metric on visualization.
7. Go to visualization menu and select Query Details and note that the CSI Engine does not optimize the query to generate the Alternative CSI to aggregate the Discount metric to Item first before joining it to REL_CAT_ITEM table to get to Catalog.
select [Catalog]@[CAT_ID],
sum([[F_ORDER_DETAILF9F6C85E1689662935B1723717195783].Discount])@{[Catalog]} as [Discount]
from ORDER_DETAIL RUSH_ORDER (2 tables)
with Table Join Tree: [F_ORDER_DETAILF9F6C85E1689662935B1723717195783]
Join [F_REL_CAT_ITEMF9F6C85E168966F13E01858029112741] with output level Tuple([Catalog]@[CAT_ID], [Day]@[DAY_DATE], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID], [Promotion]@[PROMOTION_ID])
This causes the join between 2 large tables ([F_ORDER_DETAILF9F6C85E1689662935B1723717195783] and [F_REL_CAT_ITEMF9F6C85E168966F13E01858029112741]) hampering the performance of the query execution.
CAUSE
This is a known limitation until Strategy 10.7 where CSI Engine can only generate the Alternative CSI if there is explicit many-to-many relationship table built for In-Memory Intelligent Cube.
ACTION
This issue has been addressed in Strategy 10.8 and 10.4.7. Additionally, in 11.0.0, the CSI Engine logic was further enhanced due to which the probability of generating the Alternative CSI increased and some reports that were not generating the Alternative CSI would start generating it for better performance.
CSI Engine can now also use fact table to trigger the Alternative CSI. Same example (used above) will now generate the following CSI:
Tables Accessed:
Table8 [F_ORDER_DETAILF9F6C85E1689662935B1723717195783]: Item, Customer, Promotion, Employee, Order, Day, Unit Price, Qty Sold, Unit Cost, Discount, Row Count - ORDER_DETAIL, FACT_TABLE
Table9 [F_REL_CAT_ITEMF9F6C85E168966F13E01858029112741]: Catalog, Item, Row Count - REL_CAT_ITEM, FACT_TABLE
[BEGIN ALTERNATIVE CSI: both alternative and normal CSI are shown and only one will be executed]
Alternative CSI:
select [Item]@[ITEM_ID],
sum([[F_ORDER_DETAILF9F6C85E1689662935B1723717195783].Discount])@{[Item]} as [Discount]
with Table Join Tree: [F_ORDER_DETAILF9F6C85E1689662935B1723717195783]
Save As TempTable10000
select [Catalog]@[CAT_ID],
sum([Table10000.Discount])@{[Catalog]} as [Discount]
with Table Join Tree: TempTable10000
Join [F_REL_CAT_ITEMF9F6C85E168966F13E01858029112741] with output level Tuple([Catalog]@[CAT_ID], [Item]@[ITEM_ID])
Normal CSI:
select [Catalog]@[CAT_ID],
sum([[F_ORDER_DETAILF9F6C85E1689662935B1723717195783].Discount])@{[Catalog]} as [Discount]
from ORDER_DETAIL RUSH_ORDER (2 tables)
with Table Join Tree: [F_ORDER_DETAILF9F6C85E1689662935B1723717195783]
Join [F_REL_CAT_ITEMF9F6C85E168966F13E01858029112741] with output level Tuple([Catalog]@[CAT_ID], [Day]@[DAY_DATE], [Employee]@[EMP_ID], [Item]@[ITEM_ID], [Order]@[ORDER_ID], [Promotion]@[PROMOTION_ID])
[END ALTERNATIVE CSI]
Note how Alternative CSI first aggregates the data from Item, Customer, Promotion, Employee, Order, Day, Item In blue above) before joining the fact to Catalog level. This greatly improves the performance depending on the rows available in the ORDER_DETAIL table. The more the number of rows, the faster the query performance is as compared to 10.7 CSI.
Please also note that the Normal CSI is for display purpose only and whenever Alternative CSI is available, Analytical Engine always executes that and not the Normal CSI.
The Strategy Internal Reference Number for the issue discussed in this technical note is KB442630 and DE89429.
