# Many to Many weight factor for all levels Dimension

• ### Question

• Hi,

I have a Fact that has a many to many relationship with a Dimension Organization (Natural Dimension) that has 3 levels (Territory, Region, Country).

I want to define a weight factor not only for the leaf (Territory) but for the 3 levels.

The architecture of fact table and dimension is:

Fact Sales ------------> Dimension Location <------------ Bridge ---------------------> Dimensio Organization (3 Levels: Territory,Region, Country)

So in the bridge table I map the relationship between the Dimension Location and all levels od Dimension Organization and define a Weight factor.   Then Create a measure that is Fact_Sales * Weight.

This measure works fine for the Territory, but for Region or Country thw weight factor is not used .

Thanks

Monday, July 18, 2011 9:18 AM

• Hi Raphael,

I solved this problem I few time ago, but I'm with lack of time so I don't post here my resolution.

But basically I designed a Many to Many relationship between the Fact Table Sales and the Organization dimensions.

In the bridge table that I represented the weight I have a the maping between the Sales values and the organization and I new Measure Called Sales_Organization where I multiply the weight by the sale amount for every organizations levels considered in the structure.

Then with MDX I map this Bridge Measure so the fact table to customize the aggregation I want.

Thanks for the help.

Manuel Meireles

• Marked as answer by Thursday, October 20, 2011 8:41 AM
Wednesday, October 19, 2011 1:58 PM

### All replies

• Hi,

If I understand worrectly your case, since the organization dimension is natural, the bridge table should look like that :

Bridge(Location_ID, Territory_ID, Weight)

Is that correct ?

What you want to achieve is to multiply the fact sales measure by the aggregation of the territories weights either at the region or country level ?

or do you want to have distinct weights (that is not an aggregation) at those levels ?

Regards

Wednesday, October 19, 2011 9:39 AM
• Hi Raphael,

I solved this problem I few time ago, but I'm with lack of time so I don't post here my resolution.

But basically I designed a Many to Many relationship between the Fact Table Sales and the Organization dimensions.

In the bridge table that I represented the weight I have a the maping between the Sales values and the organization and I new Measure Called Sales_Organization where I multiply the weight by the sale amount for every organizations levels considered in the structure.

Then with MDX I map this Bridge Measure so the fact table to customize the aggregation I want.