Urgent : cardinality relationship (1:1 or 1:n)

[davidcognos]

Hi,

I am presently working on Framework manager in cognos.We have to import the tables from database and then do the joins. Can anybody please tell me the cardinality to be given to the tables in OLTP. I know that the fact table should be given as 1:n and the dimension tables as 1:1. But here my requirement is OLTP.  I have seen some FM Models cpf files where  the cardinality is given as 1:1 to tables where the facts are present. On what basis is this cardinality is given.


Thanks in Advance!!!!!

Cognag31

[blom0344]

Basically if all your measures are coming from the same OLTP table the 1:1 or 1:n is academic. However if you want to be able to report on multiple facts (joined by a common dimension) then Cognos needs the proper cardinality settings to decide which table (Query subject) are facts and how to split the query and stitch it back again.

In OLTP you may have measures coming from 2 tables (Query subjects) that are directly joined in a 1:1 <--> 1:n relation. We observed that by setting determinants we still get the proper result (???)  In other cases we simply set the aggregation to min/max for the measure from the 1:1 side instead of sum and get the correct result.

[davidcognos]

Hi,

Thanks a lot for the reply....

The other doubt is that if I give only 1:1<--> 1:1  cardinality for all the tables, though they contain the facts or not, will it work or will it give the wrong output. Please clarify my doubt.

Could you explain why and how your sentence works. 'In other cases we simply set the aggregation to min/max for the measure from the 1:1 side'.


Thanks in Advance!!!!

Cognag31

[blom0344]

The answer would be: "It depends"

Setting cardinalities is primarily an aid for Cognos. This is due to the fact that Cognos expects you to remodel into fact and dimension objects.

Consider joining a order header table to a order lines table. One order will have 1 line in the header and n lines in the order lines table.

The order header may store the total discount for that order as a measure, say 100 dollar. With 10 order lines you risk having the discount summarized to 10*100 = 1000 dollar. This is due to the fact that the join precedes the summarizing. Using aggregate 'sum' for the discount may lead to faulty figures.

This is why you need to figure out where you draw the measures from the OLTP schema