MicroStrategy ONE
Non-Agg Metric Optimization
Non-Agg Metric 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.
Non-Agg Metric Optimization influences the behavior of non-aggregation metrics by either optimizing for smaller temporary tables or for less fact table access. This property can help improve query performance depending on the fact table size and the potential temporary table size. It may be more effective to create a larger temporary table so that you can avoid using the even larger fact table. If you are short on temporary table space or insert much data from the fact table into the temporary table, it may be better to use the fact table multiple times rather than create temporary tables. Your choice for this property depends on your data and report definitions.
Levels at Which You Can Set This
Database instance, report, and template
Examples
Optimized for less fact table access (default)
The following example first creates a fairly large temporary table, but then never touches the fact table again.
select a11.REGION_NBR REGION_NBR,
a11.REGION_NBR REGION_NBR0,
a12.MONTH_ID MONTH_ID,
a11.DIVISION_NBR DIVISION_NBR,
a11.CUR_TRN_DT CUR_TRN_DT,
a11.TOT_SLS_DLR WJXBFS1
into ZZNB00
from REGION_DIVISION a11
join LOOKUP_DAY a12
on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
select pa1.REGION_NBR REGION_NBR,
pa1.MONTH_ID MONTH_ID,
min(pa1.CUR_TRN_DT) WJXBFS1
into ZZMB01
from ZZNB00 pa1
group by pa1.REGION_NBR,
pa1.MONTH_ID
select pa1.REGION_NBR REGION_NBR,
pa1.MONTH_ID MONTH_ID,
count(pa1.WJXBFS1) WJXBFS1
into ZZNC02
from ZZNB00 pa1
join ZZMB01 pa2
on (pa1.CUR_TRN_DT = pa2.WJXBFS1 and
pa1.MONTH_ID = pa2.MONTH_ID and
pa1.REGION_NBR = pa2.REGION_NBR)
group by pa1.REGION_NBR,
pa1.MONTH_ID
select distinct pa3.REGION_NBR REGION_NBR,
a13.REGION_DESC REGION_DESC,
a12.CUR_TRN_DT CUR_TRN_DT,
pa3.WJXBFS1 COUNTOFSALES
from ZZNC02 pa3
join LOOKUP_DAY a12
on (pa3.MONTH_ID = a12.MONTH_ID)
join LOOKUP_REGION a13
on (pa3.REGION_NBR = a13.REGION_NBR)
Optimized for smaller temp table
The following example does not create the large temporary table but must query the fact table twice.
select a11.REGION_NBR REGION_NBR,
a12.MONTH_ID MONTH_ID,
min(a11.CUR_TRN_DT) WJXBFS1
into ZZOP00
from REGION_DIVISION a11
join LOOKUP_DAY a12
on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
group by a11.REGION_NBR,
a12.MONTH_ID
select a11.REGION_NBR REGION_NBR,
a12.MONTH_ID MONTH_ID,
count(a11.TOT_SLS_DLR) COUNTOFSALES
into ZZMD01
from REGION_DIVISION a11
join LOOKUP_DAY a12
on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
join ZZOP00 pa1
on (a11.CUR_TRN_DT = pa1.WJXBFS1 and
a11.REGION_NBR = pa1.REGION_NBR and
a12.MONTH_ID = pa1.MONTH_ID)
group by a11.REGION_NBR,
a12.MONTH_ID
select distinct pa2.REGION_NBR REGION_NBR,
a13.REGION_DESC REGION_DESC,
a12.CUR_TRN_DT CUR_TRN_DT,
pa2.COUNTOFSALES COUNTOFSALES
from ZZMD01 pa2
join LOOKUP_DAY a12
on (pa2.MONTH_ID = a12.MONTH_ID)
join LOOKUP_REGION a13
on (pa2.REGION_NBR = a13.REGION_NBR)