Many-to-Many Relationship between two dimensions

View previous topic View next topic Go down

Many-to-Many Relationship between two dimensions

Post  nthumu88 on Thu Feb 12, 2015 12:37 pm

Hi I am modeling a star schema which has a fact table at order level, dimensions material, branch, material branch and time. material branch dimension has both material and branch information.  so now material branch dimension can act as bridge table. Business need reports on all dimensions material, branch and material branch. Can anyone help me in resolving this issue.

nthumu88

Posts : 10
Join date : 2015-01-26

View user profile

Back to top Go down

Re: Many-to-Many Relationship between two dimensions

Post  nick_white on Fri Feb 13, 2015 5:07 am

Hi - I am not clear why you are modelling a material-branch dimension. Aren't the relationships between material and branch defined via the fact table(s)?
Normally attributes exist in only one dimension (ignoring special cases like aggregate dimensions: month, year etc.) so I'm a bit confused when you say "material branch dimension has both material and branch information" as this doesn't sound like a normal dimensional model. Please can you explain a bit more on what these Dims contain and why you have modelled it like this?

Thanks,

nick_white

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

View user profile

Back to top Go down

Re: Many-to-Many Relationship between two dimensions

Post  ngalemmo on Fri Feb 13, 2015 2:11 pm

You simply have a fact table that has dimensional references to the material, branch, and material-branch dimension.

The material-branch dimension is not a bridge and should not be used as one. It is a dimension whose natural key is made up of the material ID and branch ID.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Many-to-Many Relationship between two dimensions

Post  nthumu88 on Thu Feb 19, 2015 4:25 pm

Hi Nick,
this tables contains information about what business unit manufactures what items. one business unit may produce many items and one item is can be produced by many business units. Most of the attributes define the item only attribute which really give info about business unit is its number. And for JDE there is separate item master table but here they are using item branch table as item master. Finally what I did I was I created product dimension from item branch with some fields coming from item master.

nthumu88

Posts : 10
Join date : 2015-01-26

View user profile

Back to top Go down

Re: Many-to-Many Relationship between two dimensions

Post  nthumu88 on Thu Feb 19, 2015 4:26 pm

ngalemmo wrote:You simply have a fact table that has dimensional references to the material, branch, and material-branch dimension.

The material-branch dimension is not a bridge and should not be used as one.  It is a dimension whose natural key is made up of the material ID and branch ID.


yes you are right. but instead of creating two dimensions item and item branch I have created one dimension from two tables.

Thanks

nthumu88

Posts : 10
Join date : 2015-01-26

View user profile

Back to top Go down

Re: Many-to-Many Relationship between two dimensions

Post  nick_white on Fri Feb 20, 2015 7:56 am

Hi - am I correct in thinking that "branch" and "business unit" are the same thing? Assuming I am, you have said that an item could be produced by many branches/BUs so I'm still not clear why you have combined them into a single Dim as they seem to be fundamentally different types of entity.
Until you are considering the specific instance of an item associated to an Order, you don't know which Branch produced it and therefore the relationship between Branch and Item is defined via the Order Fact table using separate dimensions.
If you want to report on which Branches can produce an item then use a factless fact table to relate the two entities.

Hope this helps - apologies if I still haven't correctly understood what you are describing

nick_white

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

View user profile

Back to top Go down

Re: Many-to-Many Relationship between two dimensions

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