Multi-fact query on conformed and non-conformed dimensions

[msn]

I have 3 facts (F1, F2, F3), 2 conformed dimensions (CD1, CD2) and 2 non conformed dimensions (NCD1, NCD2)  in my model.

Fact 1 (F1) has 1.n relationship with  CD1 and CD2.
Fact2 (F2) has 1.n relationship with CD1, CD2 and NCD1
Fact3 (F3) has 1:n relationship with CD1, CD2 and NCD2
All the 3 facts are joined to the 2 conformed dimensions at the same level. This is how the model is in Framework Manager (as in attachment).


In Report Studio
1.   When I bring in data from F1 and F2, correct full outer join is created and I see Cartesian product of the F1 and F2.
2.   When I bring in data from F1 and F3, correct full outer join is created and I see Cartesian product of the F1 and F3.
3.   When I bring in data from  F2 and F3 along with attribute data from CD1, NCD1 and NCD2, the results are weird. I do not see a full outer join between the data in F2 and F3. Each attribute from NCD1 is not outer joined with each attribute from NCD2.

Is there something that I must have missed when modelling in Framework Manager?

[Francis aka khayman]

how about if you bring in data from F2 and F3 only?

[MFGF]

I have 3 facts (F1, F2, F3), 2 conformed dimensions (CD1, CD2) and 2 non conformed dimensions (NCD1, NCD2)  in my model.

Fact 1 (F1) has 1.n relationship with  CD1 and CD2.
Fact2 (F2) has 1.n relationship with CD1, CD2 and NCD1
Fact3 (F3) has 1:n relationship with CD1, CD2 and NCD2
All the 3 facts are joined to the 2 conformed dimensions at the same level. This is how the model is in Framework Manager (as in attachment).


In Report Studio
1.   When I bring in data from F1 and F2, correct full outer join is created and I see Cartesian product of the F1 and F2.
2.   When I bring in data from F1 and F3, correct full outer join is created and I see Cartesian product of the F1 and F3.
3.   When I bring in data from  F2 and F3 along with attribute data from CD1, NCD1 and NCD2, the results are weird. I do not see a full outer join between the data in F2 and F3. Each attribute from NCD1 is not outer joined with each attribute from NCD2.

Is there something that I must have missed when modelling in Framework Manager?

Just to be clear, the fact query subjects are at the 'n' end of each relationship? The post above looks like it implies the dimensions are at the 'n' end?

MF.

[msn]

how about if you bring in data from F2 and F3 only?

When I bring in data only from F2 and F3, the data returned is unrelated. Not an output of full outer join as can be obtained from executing a full outer join sql against the database. B'cos the join is happening on the sc column (in the Cognos SQL generated, the join is on )

on (D2.sc = D3.sc)

[msn]

Just to be clear, the fact query subjects are at the 'n' end of each relationship? The post above looks like it implies the dimensions are at the 'n' end?

MF.

Yes,  the fact query subjects are at the n end of the relationship. The dimensions are at 1 end of the relationship. Sorry if my diagram was not clear on that aspect

[Francis aka khayman]

D2.sc = D3.sc is typo? did you mean F2.sc = F3.sc? if so F2 and F3 has direct relationship?

if not there is a D2 and D2 tables. can you show where those fit in your model diagram?

When I bring in data only from F2 and F3, the data returned is unrelated. Not an output of full outer join as can be obtained from executing a full outer join sql against the database. B'cos the join is happening on the sc column (in the Cognos SQL generated, the join is on )

on (D2.sc = D3.sc)

[msn]

D2.sc = D3.sc is typo? did you mean F2.sc = F3.sc? if so F2 and F3 has direct relationship?

if not there is a D2 and D2 tables. can you show where those fit in your model diagram?

I copied the condition from the cognos sql generated. Yes, I did mean F2.sc = F3.sc.
F2 and F3 have no direct relationship. I have attached the sql. I haven't pulled in any of the columns from the conformed dimension into this query.