This discussion is archived
4 Replies Latest reply: Aug 9, 2012 3:16 AM by skulll.r RSS

Many to many relationship

skulll.r Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points