locked
Many to Many weight factor for all levels Dimension RRS feed

  • 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

Answers

  • 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.

     

    In summary was this.

     

    Thanks for the help.

    Manuel Meireles

    • Marked as answer by MJGomes 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.

     

    In summary was this.

     

    Thanks for the help.

    Manuel Meireles

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