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.

Copy

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.

Copy

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)