Double counting issue

[M]

Hi All,

I creating a framework model with 5 tables for an engineering company . Please look at the attachment.

business unit  [ lookup table for business unit description]
division  [ lookup table for division description]
supplier master [ lookup table for supplier details ]
supplier Tran [ Have measure : order total, division , business unit and supplier]
purchase order

I have joined all the tables except the purchase order and works fine. 

When i join purchase order table to business/division/suppliermaster to get the description . FM complains about the double counting.  I need the business unit, division description and supplier details for the purchase order table.

How can i avoid double counting in this scenario..pls advise?

Thanks
M

[M]

Please find the attachment here..

[New_Guy]

Hi,
Did you check the concept of determinants in FM? if not try it and do a group by by the Business and division name by adding them as determinants.
Good luck
New guy

[Lynn]

You should take a look at the FM user guide, particularly chapter 5 and chapter 9. The cardinality you show in your diagram is backwards. The query engine will identify facts as those that are on the "n" side of all relationships. Does one row of your supplier_tran table really join to more than one row in any of the lookup tables? No, a single transaction has a key that matches to one and only one record in your supplier dimension.

You'd then include purchase order also on the "n" side of all relationships so it too will be treated as a fact table. At that point you'll have a multi-fact model that will produce stitch queries. Setting determinants can avoid double counting in multi-grain situations, but it appears you'll be joining to all dimensions at the same level of granularity. Multi-fact and multi-grain are also covered in the user guide.

As a side note, I'm not clear on the relationship between division and business unit. If it is a hierarchy of multiple business units making up each division then a single dimension table would handle that perfectly well.