3 Replies Latest reply on Oct 22, 2012 11:02 AM by 934476

    N:N relationship between 2 dimensions

      Hi Guru,

      I've got a modeling issue and I need your wise help.

      I've got a fact table and two dimensions. Elements of the first one are grouped in the second one.
      The first dimension is Country : Belgium, France, UK ...
      The second one is GroupCountry : (Belgium + Netherlands + Luxembourg), (Belgium + France), (All the country in UE), ...

      As you can see, a country can be in several GroupCountry.

      How is it possible to design that in OBIEE ?

      My only idea was a bridge table but it usually stand between facts and dim, not in a snowflake (between two dim).
      Fact <--> Dim Country <--> Bridge Table <--> Dim GroupCountry.

      Any idea ?

        • 1. Re: N:N relationship between 2 dimensions

          Add a one to many physical join between Country and Group country tables.In BMM , you can model them as single logical dimension.

          1 person found this helpful
          • 2. Re: N:N relationship between 2 dimensions

            I just say, you could design the hierarchy simply with "GroupCountry" as the parent and "Country" as the child. Now, with this design you would have to answer yourself some questions though

            1. What would you like to see when in a report you have two "Group Countries" with same participating "Country"? As per this design, the country information would be duplicated across "Group Countries".

            2. For any report, if you are trying to get a grand total by "Group Countries", the country information might get duplicated again if it belongs to different groups, thereby making the grand total incorrect. Is it again ok? Of course, there are ways to mitigate this too...

            And again as per me, yes, you could certainly create something like bridge table here to make it work.

            Hope this sheds some light.

            Thank you,
            1 person found this helpful
            • 3. Re: N:N relationship between 2 dimensions
              Thanks for your answers !

              @Dpka : The problem is that this is a many to many relationship.

              @Dhar :
              1. Duplication is fine.
              2. We won't use Group Countries that intersects in the same report so it should be ok.

              So a bridge table between two dimensions could be ok ? I'm gonna try.

              Thanks a lot