4 Replies Latest reply: Aug 9, 2012 5:16 AM by skulll.r RSS

    Many to many relationship

    skulll.r
      Hi everybody!

      I need to do many to many relationship in Oracle BI (11.1.1.6.1)

      Only one valuable article that I have found is this
      http://gerardnico.com/wiki/dat/obiee/join_in_lts

      I have main fact table FUEL PRICE couning 57000 rows
      I have dimension table ORG couning 1000 rows
      Also I have ORG parent child table counting 2900 rows

      Befo integration many to many relation, percormance was perfect!

      Afte I add many-to-many fact table ORG2Bussines_type counting 100 rows
      and dictionary Bussines_type counting 27 rows

      Many to many works fine using mentioned technology from article, but the performance in 3 times poorer.
        • 1. Re: Many to many relationship
          Luko
          Hello,

          Look at http://www.rittmanmead.com/2008/08/the-mystery-of-obiee-bridge-tables/

          Regards,
          Luko
          • 2. Re: Many to many relationship
            skulll.r
            Thanks for reply,

            But it's for OBIEE 10, The bridge table option is not available anymore with OBIEE 11g.
            • 3. Re: Many to many relationship
              Luko
              Hello,

              I forgot about this. Look at:http://docs.oracle.com/cd/E14571_01/bi.1111/e10540/busmodlayer.htm#BGBJGJIB

              Regards,
              Luko
              • 4. Re: Many to many relationship
                skulll.r
                I tried both of the ways metioned in this link.
                Both of them work, but in 10000 times slower than before.
                My problem is in the performance.

                /*This script is generated by BI*/
                select distinct T44374.decade_desc as c1,
                T44383.MONTH_OF_YEAR_NAME as c2,
                T78234.NAME as c3,
                T78221.display_name_full as c4,
                T44374.ID_MONTH as c6,
                T78221.ID as c7,
                T78221.ID_PARENT as c8,
                T44374.id_decade as c9,
                T81737.id as c10,
                T44374.ID as c11
                from DWH.D_MONTH T44383,
                DWH.D_DAY T44374,
                DWH.D_BUSINESS_TYPE_V T81737
                left outer join(((DWH.D_PRODUCT2_V T78234
                inner join DWH.F_AZS_PRICE2 T78252
                On T78234.ID = T78252.ID_PROD)
                left outer join(DWH.D_ORGANIZATION2 T78221
                inner join DWH.D_ORGANIZATION2_PC T78965
                On T78221.ID = T78965.ANCESTOR_KEY) On T78252.ID_COMPANY = T78965.MEMBER_KEY)
                left outer join DWH.F_ORG_2_BUSINESS_TYPE_V T81742
                On T78965.MEMBER_KEY = T81742.ID_ORG) On T81737.id = T81742.ID_BUSINESS_TYPE
                where (T44374.ID = T78252.ID_DAY and T44374.ID_MONTH = T44383.MONTH_CODE);

                /* This script is generated by me, and it's equal to upper one. But my script in many many times faster*/
                select distinct T44374.decade_desc as c1,
                T44383.MONTH_OF_YEAR_NAME as c2,
                T78234.NAME as c3,
                T78221.display_name_full as c4,
                T44374.ID_MONTH as c6,
                T78221.ID as c7,
                T78221.ID_PARENT as c8,
                T44374.id_decade as c9,
                T81737.id as c10,
                T44374.ID as c11
                from DWH.D_MONTH T44383
                left join DWH.D_DAY T44374
                on T44374.ID_MONTH = T44383.MONTH_CODE
                left join DWH.F_AZS_PRICE2 T78252
                on T78252.id_day = T44374.id
                left join DWH.D_PRODUCT2_V T78234
                on T78234.id = T78252.ID_PROD
                left join DWH.D_ORGANIZATION2_PC T78965
                on T78252.ID_COMPANY = T78965.MEMBER_KEY
                left join DWH.D_ORGANIZATION2 T78221
                on T78221.ID = T78965.ANCESTOR_KEY
                left join DWH.F_ORG_2_BUSINESS_TYPE_V T81742
                on T81742.id_org = T78965.Member_Key
                left join DWH.D_BUSINESS_TYPE_V T81737
                on T81737.id = T81742.id_business_type;

                I tired to use hints such as a REWRITE, STAR_TRANSFORMATION, but it doesn't work.