Ambiguous Fact table

[bloggerman]

I have a fact table which has about 10 joins in it. fact table is on the 1 side of the relation for about 4 and on the n side for the other 6. How do you handle modeling for such a scenario.

[MFGF]

When you refer to joins, do you mean joins within embedded SQL retrieving data into the fact query subject, or do you mean relationships linking the fact query subject to other things in the model?

MF.

[bloggerman]

The later. Query Subject joining with other query subjects

[bloggerman]

Anyone?? It is crucial

[Arpitagrawal9]

what kind of realationship the table has with the dimensions is it all one to many

[blom0344]

Very tricky issue.  If you have a model with 2 query subjects that contain both facts and are directly joined then Cognos only manages to generate correct result from the 1 side if a unique determinant is set for that query subject.
Cognos then introduces the following syntax:

Code Select Expand


XSUM(subject1.measure1 at subject1.uniquekey  for subject1.uniquekey)


without the determinant:

Code Select Expand


XSUM(subject1.measure1   for subject1.uniquekey)


Which leads to oversummarizing based on the cardinality with the n-side

When you add other attributes from dimensional tables you will notice these are added to the XSUM syntax

[cognostechie]

Beg to differ Blom !! I had earlier posted screenshots of two query subjects directly joined to each other with 1:Many relationship and the report showed correct aggregation from both query subjects. If you remember, we had an elaborate discussion on this.

[blom0344]

Yes I remember. I therefore build  a little testmodel and my contribution was based on the outcome of the appliance of the determinant. SQL generated is different and results are only correct with determinant in place