KB442296: For better performance and memory usage of In-Memory queries against Intelligent Cubes, unnecessary attributes from intermediate join tree are removed

• Strategy

This technical article describes an a CSI performance optimization to remove unnecessary columns from the table join tree

This is a performance optimization to remove unnecessary attributes from the output levels of the intermediate branches of the Table Join Tree for CSI. This optimization does not have any impact/change on the final data produced on the visualization.
SYMPTOM:
Sample CSI Before Optimization:


Tables Accessed:
Table38    [L_SUBCATEGORY]:    Subcategory,         LOOKUP_TABLE
Table112    [REL_QUARTER_MONTH]:    Quarter, Month,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table113    [REL_YEAR_QUARTER]:    Year, Quarter,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table115    [REL_CATEGORY_SUBCATEGORY]:    Category, Subcategory,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table116    [REL_MONTH_DAY]:    Month, Day,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table148    [F_TEST_DAY_SUBCAT_COST]:    Subcategory, Day,     Cost,     FACT_TABLE
Table154    [F_TMP_YR_CATEGORY_REVENUE]:    Category, Year,     Revenue1,     FACT_TABLE

select    [Subcategory]@[SUBCAT_ID],
[Subcategory]@[SUBCAT_DESC],
[Day]@[DAY_DATE],
[[F_TEST_DAY_SUBCAT_COST].Cost] as [Cost]
from    Tutorial Cube
with Table Join Tree:     [F_TEST_DAY_SUBCAT_COST]
Join [REL_CATEGORY_SUBCATEGORY] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID])
Join [REL_MONTH_DAY] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Month]@[MONTH_ID], [Subcategory]@[SUBCAT_ID])
Join [REL_QUARTER_MONTH] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Month]@[MONTH_ID], [Quarter]@[QUARTER_ID], [Subcategory]@[SUBCAT_ID])
Join [REL_YEAR_QUARTER] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID])
Join [F_TMP_YR_CATEGORY_REVENUE]<[[F_TMP_YR_CATEGORY_REVENUE].Revenue1] > 1000.0> with output level Tuple([Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID])

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

The Month attribute is not needed in the table join tree as specified in red above.
ACTION:
This optimization is provided in 11.0 release. Please upgrade to 11.0 or later release to take advantage of this optimization:
Sample CSI After Optimization:


Tables Accessed: Table38    [L_SUBCATEGORY]:    Subcategory,         LOOKUP_TABLE
Table112    [REL_QUARTER_MONTH]:    Quarter, Month,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table113    [REL_YEAR_QUARTER]:    Year, Quarter,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table115    [REL_CATEGORY_SUBCATEGORY]:    Category, Subcategory,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table116    [REL_MONTH_DAY]:    Month, Day,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table148    [F_TEST_DAY_SUBCAT_COST]:    Subcategory, Day,     Cost,     FACT_TABLE
Table154    [F_TMP_YR_CATEGORY_REVENUE]:    Category, Year,     Revenue1,     FACT_TABLE

select    [Subcategory]@[SUBCAT_ID],
[Subcategory]@[SUBCAT_DESC],
[Day]@[DAY_DATE],
[[F_TEST_DAY_SUBCAT_COST].Cost] as [Cost]
from    Tutorial Cube
with Table Join Tree:     [F_TEST_DAY_SUBCAT_COST]
Join [REL_CATEGORY_SUBCATEGORY] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID])
Join [REL_MONTH_DAY] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Month]@[MONTH_ID], [Subcategory]@[SUBCAT_ID])
Join [REL_QUARTER_MONTH] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Quarter]@[QUARTER_ID], [Subcategory]@[SUBCAT_ID])
Join [REL_YEAR_QUARTER] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID])
Join [F_TMP_YR_CATEGORY_REVENUE]<[[F_TMP_YR_CATEGORY_REVENUE].Revenue1] > 1000.0> with output level Tuple([Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID])

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

The [Month]@[MONTH_ID] is not required and removed from table join tree.
The Strategy Internal Reference Number for the issue discussed in this technical note is KB442296 and DE91603.

Comment

0 comments

Details

Knowledge Article

Published:

November 20, 2018

Last Updated:

December 31, 2018

Tables Accessed: Table38 [L_SUBCATEGORY]: Subcategory, LOOKUP_TABLE Table112 [REL_QUARTER_MONTH]: Quarter, Month, RELATIONSHIP_TABLE, ONE_TO_MANY Table113 [REL_YEAR_QUARTER]: Year, Quarter, RELATIONSHIP_TABLE, ONE_TO_MANY Table115 [REL_CATEGORY_SUBCATEGORY]: Category, Subcategory, RELATIONSHIP_TABLE, ONE_TO_MANY Table116 [REL_MONTH_DAY]: Month, Day, RELATIONSHIP_TABLE, ONE_TO_MANY Table148 [F_TEST_DAY_SUBCAT_COST]: Subcategory, Day, Cost, FACT_TABLE Table154 [F_TMP_YR_CATEGORY_REVENUE]: Category, Year, Revenue1, FACT_TABLE select [Subcategory]@[SUBCAT_ID], [Subcategory]@[SUBCAT_DESC], [Day]@[DAY_DATE], [[F_TEST_DAY_SUBCAT_COST].Cost] as [Cost] from Tutorial Cube with Table Join Tree: [F_TEST_DAY_SUBCAT_COST] Join [REL_CATEGORY_SUBCATEGORY] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID]) Join [REL_MONTH_DAY] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Month]@[MONTH_ID], [Subcategory]@[SUBCAT_ID]) Join [REL_QUARTER_MONTH] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Month]@[MONTH_ID], [Quarter]@[QUARTER_ID], [Subcategory]@[SUBCAT_ID]) Join [REL_YEAR_QUARTER] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID]) Join [F_TMP_YR_CATEGORY_REVENUE]<[[F_TMP_YR_CATEGORY_REVENUE].Revenue1] > 1000.0> with output level Tuple([Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID]) [Analytical engine calculation steps: 1. Perform cross-tabbing ]

Tables Accessed: Table38 [L_SUBCATEGORY]: Subcategory, LOOKUP_TABLE Table112 [REL_QUARTER_MONTH]: Quarter, Month, RELATIONSHIP_TABLE, ONE_TO_MANY Table113 [REL_YEAR_QUARTER]: Year, Quarter, RELATIONSHIP_TABLE, ONE_TO_MANY Table115 [REL_CATEGORY_SUBCATEGORY]: Category, Subcategory, RELATIONSHIP_TABLE, ONE_TO_MANY Table116 [REL_MONTH_DAY]: Month, Day, RELATIONSHIP_TABLE, ONE_TO_MANY Table148 [F_TEST_DAY_SUBCAT_COST]: Subcategory, Day, Cost, FACT_TABLE Table154 [F_TMP_YR_CATEGORY_REVENUE]: Category, Year, Revenue1, FACT_TABLE select [Subcategory]@[SUBCAT_ID], [Subcategory]@[SUBCAT_DESC], [Day]@[DAY_DATE], [[F_TEST_DAY_SUBCAT_COST].Cost] as [Cost] from Tutorial Cube with Table Join Tree: [F_TEST_DAY_SUBCAT_COST] Join [REL_CATEGORY_SUBCATEGORY] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID]) Join [REL_MONTH_DAY] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Month]@[MONTH_ID], [Subcategory]@[SUBCAT_ID]) Join [REL_QUARTER_MONTH] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Quarter]@[QUARTER_ID], [Subcategory]@[SUBCAT_ID]) Join [REL_YEAR_QUARTER] with output level Tuple([Category]@[CATEGORY_ID], [Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID], [Year]@[YEAR_ID]) Join [F_TMP_YR_CATEGORY_REVENUE]<[[F_TMP_YR_CATEGORY_REVENUE].Revenue1] > 1000.0> with output level Tuple([Day]@[DAY_DATE], [Subcategory]@[SUBCAT_ID]) [Analytical engine calculation steps: 1. Perform cross-tabbing ]