Many-to-Many Relationship between two dimensions

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.

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,

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.

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.

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

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