KB218299: Lookup table is not used in the filtering condition in the last join pass

SYMPTOM
In Strategy 9.x-10.x, it is noticed that after enabling the VLDB property:  Query Optimization > Apply Filter Options > Apply filter to passes touching warehouse tables and last join pass, filter to the last join pass is using fact table instead of lookup table.
In the warehouse touching pass, filter is using  lookup table table_2; in the last join pass, filter is using temp table result ZZMD00 pa11 and ZZMD01 pa12 instead of the lookup table.

insert into ZZMD00 
select   a14.A_DESC  A,
            Sum(a11.B)  WJXBFS1
from     table_1 a11
            join       table_2 a14
              on      (a11.B_ID = a14.B_ID)
where    (a14.A_ID in (3, 2, 4, 1)

…omit other passes…

select   coalesce (pa11. A_DESC  , pa12. A_DESC  )  A,
            pa11.WJXBFS1  WJXBFS1,
            pa12.WJXBFS1  WJXBFS2
from     ZZMD00           pa11
            full outer join     ZZMD01           pa12
              on      (pa11.CD_MD_MERCADO = pa12.CD_MD_MERCADO and 
where    (coalesce(pa11.A_ID, pa12.A_ID) in (3, 2, 4, 1)

 
CAUSE
This is working as designed. By default, fact table will be used to function as the WHERE clause driving table.
 
ACTION
Change the VLDB property: Query Optimizations > Where Clause Driving Table to the first option “Use Lookup table” (by default, it’s set to “use Fact table”).
 

insert into ZZMD00 
select   a14.A_DESC  A,
            Sum(a11.B)  WJXBFS1
from     table_1 a11
            join       table_2 a14
              on      (a11.B_ID = a14.B_ID)
where    (a14.A_ID in (3, 2, 4, 1)

…omit other passes…

select   coalesce (pa11. A_DESC  , pa12. A_DESC  )  A,
            pa11.WJXBFS1  WJXBFS1,
            pa12.WJXBFS1  WJXBFS2
from     ZZMD00           pa11
            full outer join     ZZMD01           pa12
              on      pa11.CD_MD_MERCADO = pa12.CD_MD_MERCADO
             join      table_2 a14
              on      (a11.B_ID = a14.B_ID)
where    a14.A_ID in (3, 2, 4, 1)

 
Note: In the above example, in the first pass of SQL touching the warehouse table, the filter is applied to the lookup table. In this case, it is applied to the lookup table as the filtering condition cannot be applied on the fact table. Refer to the following Strategy Knowledge Base document for more information on the VLDB setting "Where clause driving table":
KB7980 : Definition of the 'Where clause driving table’ VLDB property in Strategy Engine 9.x