Relationship to fact from dimension is not unique BK

View previous topic View next topic Go down

Relationship to fact from dimension is not unique BK

Post  bobby2929 on Fri Jan 02, 2015 3:37 pm

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

bobby2929

Posts : 4
Join date : 2014-12-15

View user profile

Back to top Go down

Re: Relationship to fact from dimension is not unique BK

Post  ngalemmo on Sat Jan 03, 2015 1:56 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Relationship to fact from dimension is not unique BK

Post  bobby2929 on Sat Jan 03, 2015 9:33 am

This is what you suggesting right

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

bobby2929

Posts : 4
Join date : 2014-12-15

View user profile

Back to top Go down

Re: Relationship to fact from dimension is not unique BK

Post  ngalemmo on Sat Jan 03, 2015 2:55 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Relationship to fact from dimension is not unique BK

Post  bobby2929 on Sat Jan 03, 2015 3:17 pm

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




bobby2929

Posts : 4
Join date : 2014-12-15

View user profile

Back to top Go down

Re: Relationship to fact from dimension is not unique BK

Post  nick_white on Mon Jan 05, 2015 8:40 am

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,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Relationship to fact from dimension is not unique BK

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum