KB484238: Improve performance for Transformations by adjusting Join Order in Google BigQuery

Description 

When compiling, traditional databases reorder the Join and Where clauses to efficiently execute the SQL that is passed from the applications layer. Teradata, SQL Server, and DB2 are examples of strong SQL compilers. Modern data warehouses, like Google BigQuery, have yet to rely on the SQL compiler, and so far relied on distributed scale-up computing to address performance issues. 
Strategy users, especially retailers, run into performance issues with the reports when performing a Lift and Shift from traditional on-prem data warehouses (e.g. Teradata) to Google BigQuery. The delayed performance becomes especially problematic in reports leveraging Year To Day, Quarter To Day, and Month To Day time transformations. These rely heavily on table based transformation where Google BigQuery runs into performance challenges. When the Time Transformation table joins with the Fact table, it can drastically interrupt the data order.
 

Steps to reproduce

• In Strategy Developer, created the metric YTD:
  

• Create the report with attribute Year, Day, Category; metric YTD; and filter Month in {201601, 201701}.
  
• View the SQL:
   
  

SQL Statements:
select              a15.`YEAR_ID`  YEAR_ID,
        a15.`QUARTER_ID`  QUARTER_ID,
        max(a17.`QUARTER_DESC`)  QUARTER_DESC,
        a12.`DAY_DATE`  DAY_DATE,
        a14.`CATEGORY_ID`  CATEGORY_ID,
        max(a16.`CATEGORY_DESC`)  CATEGORY_DESC,
        sum((a11.`QTY_SOLD` * a11.`UNIT_COST`))  WJXBFS1
from `engine_tutorial`.`ORDER_DETAIL`                     a11
        join          `engine_tutorial`.`YTD_DAY`                 a12
          on          (a11.`ORDER_DATE` = a12.`YTD_DAY_DATE`)
        join          `engine_tutorial`.`LU_ITEM`                  a13
          on          (a11.`ITEM_ID` = a13.`ITEM_ID`)
        join          `engine_tutorial`.`LU_SUBCATEG`                       a14
          on          (a13.`SUBCAT_ID` = a14.`SUBCAT_ID`)
        join          `engine_tutorial`.`LU_DAY`                   a15
          on          (a12.`DAY_DATE` = a15.`DAY_DATE`)
        join          `engine_tutorial`.`LU_CATEGORY`                      a16
          on          (a14.`CATEGORY_ID` = a16.`CATEGORY_ID`)
        join          `engine_tutorial`.`LU_QUARTER`                         a17
          on          (a15.`QUARTER_ID` = a17.`QUARTER_ID`)
where              a15.`MONTH_ID` in (201701, 201601)
group by          1,
        2,
        4,
        5
 

Solution

To improve performance between Google BigQuery and Strategy, Strategy changed the way in which SQL is generated by Strategy to first filter the Time Transformation table, followed by joins to the Fact and other lookup tables. 
This feature is addressed in Strategy 2020 Update 2 and Strategy 2021. To take advantage of this fix, upgrade to Strategy 2020 Update 2 or Strategy 2021 or above and enable the project level setting, Data Engine Version, to 2020. For more details on enabling this setting, refer to Change Data Engine Version. 
After applying the fix, the above SQL statement changes to: 

 
Strategy internal reference number for this technical note is KB484238 and F28952.