how to avoid double-counting of measures in framework manager

[shaham]

Hi                                                                     
I would like to find out the best practice for applying a dimension to  dimension join in framework manager without it having a negative impact on my measures                                                         
eg:                                                                     
Dimension A           Dimension B                          Fact C                                       Dimension E
Product 1                Product 1- Phone                Product 1 - 100 dollars                Org 1         
                               Product 1 - Online     

** I have determinants defined on Dimension A (for Product 1 - as group by) and product_key as the unique level. This works as long as I only have the above 3 query items. However, when I drag in another query item from another dimension E as an example, the numbers start doubling
                                                                       
Dimension A and Dimension B have a 1 to n relationship as Product 1 can have multiple values in Dimension B.                                   
With this join in place, in Query studio, when I drag in Dimension A  and Dimension B and the measures,the result set is this    (which is expected as I dont want the 100 dollars to be counted twice)             
  Product 1          Product 1 - Phone      100                           
                           Product 1 - Online      100                 
Total Product 1                                      100               
However, when I drag in a value from Dimension E, it starts double counting
Org 1   Product 1          Product 1 - Phone      100                           
                                      Product 1 - Online      100                 
Total Product 1                                                 200
The total value for product 1 in the fact table is only 100 dollars but because of the multiple values in the Dimension B (phone/online),it is duplicating the numbers                                                 
Can you suggest if there is anything I can do on the FM side to avoid   
this?

[Lynn]

The n side of relationship cardinality defines what is treated as a fact. You should only have n around your fact, not on dimension B.

Ideally you should model a star with all dimensions connecting only the central fact.

[charon]

Hi,

lynn is absolutly right. on the one hand, you have the cardinality of the relationsships between fact table and dimension. the best case szenario for working with FM, esp. in case you are going for a dmr modell is the star schema.
http://pic.dhe.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_fm.10.1.0.doc%2Fug_fm_id2352MDA_create_a_model_design.html

What you have shown seems to be a snowflake shema. i would suggest to normalize the dimensions a and b as long as you are sure you have a definite 1:n cardinality and create a new dimension "product", as you can see ibm did in their business case in the demo data with product line > product type > product.

The next thing is, determinants are responsible for the "group by", in the sql, means for the correct aggregation. are there any further aggregations/ relationsships in your modell?

In case you follow the best practise with a pure star schema in FM, you should model two determinants for product, the first for productA which is a group by, and a second one for on the detail level product b.

For best practices in FM (like the multilayer modell with a data import layer, a transformation layer, business layer and so on) just google it. there are a lot of great descriptions out there...(e.g. http://johnscognos10.blogspot.de/2013/04/best-practices-for-cognos-framework.html)
also, cognospaul has a great blog in which you will find some very nice articles considering cognos as well.

GL and cheerz
:P