I am writing a report that combines metrics from two fact tables, but both are on the same level:
[li]From SDW_Exam_Fact I am counting contact_lu_id's at the program level[/li]
[li]From SDW_Completer_Fact I am counting Fact_ID's at the program level (the fact table itself is the unique
combination of programs and contacts who have completed the programs).[/li]
The filter on Year is not working the same, even though both fact tables have the same type of relationships
with the time dimension, and the attributes and filters are built the same way. Here is the code:
The second pass is filtering differently, and ineffectually (it is not filtering at all).
Any ideas??
Thanks.
Dave
[li]From SDW_Exam_Fact I am counting contact_lu_id's at the program level[/li]
[li]From SDW_Completer_Fact I am counting Fact_ID's at the program level (the fact table itself is the unique
combination of programs and contacts who have completed the programs).[/li]
The filter on Year is not working the same, even though both fact tables have the same type of relationships
with the time dimension, and the attributes and filters are built the same way. Here is the code:
Code:
create table ZZSP00 nologging as
select a11.PROGRAM_DIM_ID PROGRAM_DIM_ID,
a12.YEAR_LU_ID YEAR_LU_ID,
count(a11.CONTACT_LU_ID) WJXBFS1
from SDW_EXAM_FACT a11,
SDW_EXAM_TIME_DIM_VW a12
where a11.EXAM_TIME_DIM_ID = a12.EXAM_TIME_DIM_ID
and a12.YEAR_LU_ID in (55)
group by a11.PROGRAM_DIM_ID,
a12.YEAR_LU_ID
create table ZZSP01 nologging as
select a11.PROGRAM_DIM_ID PROGRAM_DIM_ID,
a12.YEAR_LU_ID YEAR_LU_ID,
count(distinct a11.COMPLETER_FACT_ID) WJXBFS1
from SDW_COMPLETER_FACT a11,
SDW_YEAR_LU a12
where (a12.YEAR_LU_ID in (55)
and a12.YEAR_LU_ID in (55))
group by a11.PROGRAM_DIM_ID,
a12.YEAR_LU_ID
select distinct pa1.PROGRAM_DIM_ID PROGRAM_DIM_ID,
a12.PROGRAM_SHORT_LABEL PROGRAM_SHORT_LABEL,
a12.PROGRAM_LONG_LABEL PROGRAM_LONG_LABEL,
pa1.YEAR_LU_ID YEAR_LU_ID,
a11.YEAR YEAR,
pa1.WJXBFS1 WJXBFS1,
pa2.WJXBFS1 WJXBFS2
from ZZSP00 pa1,
ZZSP01 pa2,
SDW_YEAR_LU a11,
SDW_PROGRAM_DIM a12
where pa1.PROGRAM_DIM_ID = pa2.PROGRAM_DIM_ID and
pa1.YEAR_LU_ID = pa2.YEAR_LU_ID and
pa1.YEAR_LU_ID = a11.YEAR_LU_ID and
pa1.PROGRAM_DIM_ID = a12.PROGRAM_DIM_ID
drop table ZZSP00
drop table ZZSP01
Any ideas??
Thanks.
Dave