Relationship to fact from dimension is not unique BK

Hi,

I have

Commodity(S_PROD_INT)

Whic has one to many relationship with
 
1)Commodity Unit(S_PROD_INT_XM)
2)Commodity Conversion Factor(S_PROD_INT_XM)
3)Commodity(S_PROD_INT) recursive  Parent child relationship


I am doing below steps to build S_Commodity

S_Commodity

Commodity(S_PROD_INT) ROW_ID
Commodity Unit(S_PROD_INT_XM) UNIT_CD  Where S_PROD_INT.ROW_ID=S_PROD_INT_XM.PAR_ROW_ID and TYPE='01'
Commodity Conversion Factor(S_PROD_INT_XM) CNVRSN_LOC_TYPE_CD Where S_PROD_INT.ROW_ID=S_PROD_INT_XM.PAR_ROW_ID and TYPE='02'


Now i get multiple ROW_ID so i cant make this as business key so i am making
combination of Commodity+Commodity Unit+Commodity Conversion Factor ROW_ID as BK


Now i am trying to link this to Premit_Items(which will be fact) which has one to many relation with Commodity(S_PROD_INT) which will be
S_Commodity now but S_PROD_INT ROW_ID IN S_Commodity is not unique any more.


Please tell me what should i do.

Thanks

The unit should be a dimension of the fact, not an attribute of commodity.

As far as the fact goes, it is not a one to many relationship.  A measure on the row is expressed in a particular unit.  That's it.

The issue is converting among units, which is a query/reporting issue, not a dimension design issue.  Use a bridge table to calculate unit conversions. As a bridge there is no issue with business keys. The bridge would contain the commodity key, the unit key and a conversion factor. The conversion factor is expressed against an assumed standard unit. An alternate design is to store two mutually exclusive values, one containing a multiplier and the other a divisor. Whichever is not null is used to multiply or divide the quantity. This is commonly used for currency as it avoids precision problems for currencies with extraordinary exchange rates. You would join to the bridge to calculate a common unit.

Another method is to store the unit key, target unit key and conversion factor to convert from one unit to another without adjusting to a common unit.

This is what you suggesting right

http://www.kimballgroup.com/2011/06/design-tip-136-adding-a-mini-dimension-to-a-bridge-table/

Basically, but its primary use would be between a fact and the desired unit dimension.

Now i am thinking why i need bridge cant i just snow flake Commodity Unit and Commodity Conversion Factor to S_PROD_INT

Hi - looking through this thread it reads as though you are trying to convert a relational model directly into a dimensional model i.e. replicating all the relationships, cardinality, etc. - but just trying to fit it into a dimensional model. IMO this approach won't work - you need to define your dimensional model based on your reporting requirements not on the structure of your relational DB.

The steps would be:
- define your reporting requirements
- define the measures that you want to report on
- group these measures into fact tables and declare the grain of each fact table, ensuring all measures correspond to this grain
- associate the required dimensions to the fact, again ensuring that the Dimensions are compatible to the grain

While you have 1:m relationships between Commodity and Commodity Unit/Commodity Conversion Factor in your source system, isn't this just reference data? When Commodity is assigned to a particular transaction/event (whatever you are recording in Premit_Items) doesn't this then resolve to a single Unit and Conversion factor that is being applied?
If not then can you explain further what the measures are in Premit_Items and what your reporting requirements are?

Regards,