Base Table Join for Template

The Base Table Join for Template is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

When reports contain metrics from different fact tables or a compound metric made up of data from different fact tables, then the Base Table Join for Template property can be used to choose between intermediate table joins and base tables joins. The property is mainly performance-related. If intermediate table join is chosen, then the type of intermediate table is governed by the Intermediate Table Type VLDB property (see Intermediate Table Type in the Table Properties section).

Caution must be taken when changing this setting since the results can be different depending on the types of metrics on the report.

Levels at Which You Can Set This

Database instance, report, and template

Example

Use Temp Table Join (default)

select a11.MARKET_NBR MARKET_NBR,

sum(a11.CLE_SLS_DLR)

 CLEARANCESAL

into #ZZTIS00H5D3SP000

from HARI_MARKET_DIVISION a11

group by a11.MARKET_NBR

select a11.MARKET_NBR MARKET_NBR,

sum(a11.COST_AMT)

 COSTAMOUNT

into #ZZTIS00H5D3SP001

from HARI_COST_MARKET_DIV a11

group by a11.MARKET_NBR

select pa1.MARKET_NBR MARKET_NBR, 

 a11.MARKET_DESC MARKET_DESC, 

 pa1.CLEARANCESAL WJXBFS1,

 pa2.COSTAMOUNT WJXBFS2

from #ZZTIS00H5D3SP000 pa1

 left outer join #ZZTIS00H5D3SP001 pa2

 on (pa1.MARKET_NBR = pa2.MARKET_NBR)

 left outer join HARI_LOOKUP_MARKET a11

 on (pa1.MARKET_NBR = a11.MARKET_NBR)

Use Fact Table Join

select a11.MARKET_NBR MARKET_NBR,

 max(a13.MARKET_DESC) MARKET_DESC,

 sum(a12.CLE_SLS_DLR) CLEARANCESAL,

 sum(a11.COST_AMT) COSTAMOUNT

from HARI_COST_MARKET_DIV a11

 join HARI_MARKET_DIVISION a12

 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT 

 and a11.DIVISION_NBR = a12.DIVISION_NBR 

 and a11.MARKET_NBR = a12.MARKET_NBR)

 join HARI_LOOKUP_MARKET a13

 on (a11.MARKET_NBR = a13.MARKET_NBR)

group by a11.MARKET_NBR