Joining issue in FM

[PRIT AMRIT]

Hi all,

I have 2 Dimension Tables:

TRN_TIME_CATEGORY TRN
WE_CSTM_COMM_VISIT CV

The Relationship between these two dimension tables:

CV.WE_VISIT_COMP_DATETIME >= TRN.START_DATE AND CV.WE_VISIT_COMP_DATETIME < TRN.END_DATE

I have 1 fact table: TDS_WE_ISELL_VISIT_SURVEY TVS . And it has only link to WE_CSTM_COMM_VISIT table, the relationship is;

CV.OBJECT_ID = TVS.WE_COMM_VISIT_OID

My SQL in DB looks like;

Select TRN.DAY_KEY,TRN.START_DATE,TRN.END_DATE,TRN.CATEGORY,SUM(TVS.WE_CNT_OF_COMM_ACTY)
from
       TDS_WE_ISELL_VISIT_SURVEY TVS,
       WE_CSTM_COMM_VISIT CV,
       TRN_TIME_CATEGORY TRN
where (CV.WE_VISIT_COMP_DATETIME >= TRN.START_DATE AND CV.WE_VISIT_COMP_DATETIME < TRN.END_DATE)
AND CV.OBJECT_ID = TVS.WE_COMM_VISIT_OID
AND TRN.DAY_CODE='2012-08-15'
GROUP BY TRN.DAY_KEY,TRN.START_DATE,TRN.END_DATE,TRN.CATEGORY

When I am trying to impliment the same in FM and defining the join as above SQL, when I do the reporting on TRN.DAY_KEY AND SUM(TVS.WE_CNT_OF_COMM_ACTY), I have noticed Cognos is writing two separted SQL instead of joining through the Common/shared dimension i.e. CV.OBJECT_ID = TVS.WE_COMM_VISIT_OID

select TRN_TIME_CATEGORY.DAY_KEY C0, TRN_TIME_CATEGORY.DAY_CODE C1
from TRN_TIME_CATEGORY TRN_TIME_CATEGORY
where TRN_TIME_CATEGORY.DAY_CODE='2012-08-15' order by C0 asc nulls last

select T1.WE_CNT_OF_COMM_ACTY C0 from TDS_WE_ISELL_VISIT_SURVEY T1
where T1.IN_USE_FLAG<>N'd' or T1.IN_USE_FLAG is null order by C0 asc nulls last

Could you please suggest if I am missing something? Thanks

Br,
Prit

[CognosPaul]

Check determinants on the tables and cardinality on the joins.

[PRIT AMRIT]

Got it. Had to play with the cardinality to get this right. Thanks PaulM