MicroStrategy ONE

Transformation Formula Optimization

Transformation Formula Optimization is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Transformation Formula Optimization VLDB property allows you to improve the performance of expression-based transformations. Performance can be improved for reports that include expression-based transformations and meet the following requirements:

  • No attributes on the report grid or the Report Objects of the report are related to the transformation's member attribute. For example, if a transformation is defined on the attribute Year of the Time hierarchy, no attributes in the Time hierarchy can be included on the report grid or Report Objects.
  • The filter of the report does contain attributes that are related to the transformation's member attribute. For example, if a transformation is defined on the attribute Year of the Time hierarchy, a filter on another attribute in the Time hierarchy is included on the report.

For information on expression-based transformations and how to create them, see the Project Design Help.

If your report includes an expression-based transformation This VLDB property has the following options:

  • Always join with transformation table to perform transformation: A join with the transformation table is used to perform the transformation. This option supports backwards compatibility and also serves as a fallback if optimization cannot be applied for the transformation.
  • Use transformation formula instead of join with transformation table when possible (default): If the transformation is an expression-based transformation and the report meets the requirements listed above, the expression is used rather than using a join with the transformation table.

    This can improve performance of expression-based transformations by eliminating the requirement to join with the transformation table. If the transformation is included on a report that cannot support this optimization, then a join with the transformation table is automatically used to support the transformation. An example of this optimization is shown below.

Levels at Which You Can Set This

Database instance, report, and template

The SQL statements shown below display a SQL statement before (Statement 1) and after (Statement 2) applying the transformation optimization.

Statement 1 

select a14.CATEGORY_ID CATEGORY_ID,
max(a15.CATEGORY_DESC) CATEGORY_DESC,
sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)))
WJXBFS1
from ORDER_DETAIL a11
 join LU_DAY a12
 on (a11.ORDER_DATE = a12.DAY_DATE - 1 YEAR)
 join LU_ITEM a13
 on (a11.ITEM_ID = a13.ITEM_ID)
 join LU_SUBCATEG a14
 on (a13.SUBCAT_ID = a14.SUBCAT_ID)
 join LU_CATEGORY a15
 on (a14.CATEGORY_ID = a15.CATEGORY_ID)
where a12.DAY_DATE = '08/31/2001'
group by a14.CATEGORY_ID

Statement 2 

select a14.CATEGORY_ID CATEGORY_ID,
 max(a15.CATEGORY_DESC) CATEGORY_DESC,
 sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)))
WJXBFS1
from ORDER_DETAIL a11
 join LU_ITEM a13
 on (a11.ITEM_ID = a13.ITEM_ID)
 join LU_SUBCATEG a14
 on (a13.SUBCAT_ID = a14.SUBCAT_ID)
 join LU_CATEGORY a15
 on (a14.CATEGORY_ID = a15.CATEGORY_ID)
where a11.ORDER_DATE = DATE('08/31/2001') - 1 YEAR
group by a14.CATEGORY_ID