Generating multiple physical queries for single Report having single fact. — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Generating multiple physical queries for single Report having single fact.

Received Response
11
Views
3
Comments
3027402
3027402 Rank 1 - Community Starter
edited Aug 13, 2024 7:05PM in Oracle Analytics Cloud and Server

Hi

I am facing a issue ,as OBIEE generates multiple physical queries rather than 1 query for single fact.

It is grouping by different columns in  those multiple queries for a single report.

And this is affecting performance as well.

Can any one please suggest me the root cause for it and how to overcome this kind of scenario.

Thanks

Neethu

Answers

  • DanPryke
    DanPryke Rank 3 - Community Apprentice

    Hi,

    Could you provide some more information please, this is quite vague.

    Could you paste the actual physical query your BI server is running (obfuscate any table names or subject areas as you want)? What does your model look like and what are the joins doing?

    Happy to help, but need more information

  • 3027402
    3027402 Rank 1 - Community Starter

    thank you for your reply, I have a single report  and I am getting the query as below dividing it into multiple queries . I want to get a single query instead of these

    3 queries.




    QUERY1:

    select sum(T45014."EQUIPMENT_TEU_RATE") as c1,

         count(distinct T45014."DW_EQUIPMENT_KEY") as c2,

         count(distinct case  when T44451."BOOKING_REFFERENCE_NUM" = 'UNSPECIFIED' then T45014."DW_EQUIPMENT_KEY" end ) as c3,

         T44861."CITY_CODE" as c4,

         T44607."EQUIPMENT_TYPE" as c5,

         T44764."MARKETING_AREA_REGION_CODE" as c6,

         T44861."MARKETING_AREA" as c7

    from

         "DW_EQUIPMENT_D" T44607,

         "DW_STATUS_D" T53286,

         "DW_BOOKING_D" T44451,

         "DW_LOCATION_D" T44861,

         "DW_LESSEE_D" T44764,

         "DW_INVENTORY_BALANCE_F" T45014

    where  ( T44451."DW_BOOKINGS_KEY" = T45014."DW_BOOKINGS_KEY" and T44607."DW_EQUIPMENT_KEY" = T45014."DW_EQUIPMENT_KEY" and T44764."DW_LESSEE_KEY" = T45014."DW_LESSEE_KEY" and T44861."DW_LOCATION_KEY" = T45014."DW_LOCATION_KEY" and T45014."RESALE_FLAG" = 'N' and T45014."REPAIR_FLAG" = 'N' and T45014."DW_STATUS_KEY" = T53286."DW_STATUS_KEY" and T45014."SOLD_FLAG" = 'N' and T53286."STATUS_CODE" = 'AV' )

    group by T44607."EQUIPMENT_TYPE", T44764."MARKETING_AREA_REGION_CODE", T44861."CITY_CODE", T44861."MARKETING_AREA"

    QUERY2:

    select count(distinct T45014."DW_EQUIPMENT_KEY") as c6,

         count(distinct case  when T44451."BOOKING_REFFERENCE_NUM" = 'UNSPECIFIED' then T45014."DW_EQUIPMENT_KEY" end ) as c7,

         T44607."EQUIPMENT_TYPE" as c8,

         T44764."MARKETING_AREA_REGION_CODE" as c9

    from

         "DW_EQUIPMENT_D" T44607,

         "DW_STATUS_D" T53286,

         "DW_BOOKING_D" T44451,

         "DW_LOCATION_D" T44861,

         "DW_LESSEE_D" T44764,

         "DW_INVENTORY_BALANCE_F" T45014

    where  ( T44451."DW_BOOKINGS_KEY" = T45014."DW_BOOKINGS_KEY" and T44607."DW_EQUIPMENT_KEY" = T45014."DW_EQUIPMENT_KEY" and T44764."DW_LESSEE_KEY" = T45014."DW_LESSEE_KEY" and T44861."DW_LOCATION_KEY" = T45014."DW_LOCATION_KEY" and T45014."RESALE_FLAG" = 'N' and T45014."REPAIR_FLAG" = 'N' and T45014."DW_STATUS_KEY" = T53286."DW_STATUS_KEY" and T45014."SOLD_FLAG" = 'N' and T53286."STATUS_CODE" = 'AV' )

    group by T44607."EQUIPMENT_TYPE", T44764."MARKETING_AREA_REGION_CODE"

    QUERY3:

    select count(distinct T45014."DW_EQUIPMENT_KEY") as c8,

         count(distinct case  when T44451."BOOKING_REFFERENCE_NUM" = 'UNSPECIFIED' then T45014."DW_EQUIPMENT_KEY" end ) as c9,

         T44607."EQUIPMENT_TYPE" as c10,

         T44764."MARKETING_AREA_REGION_CODE" as c11,

         T44861."MARKETING_AREA" as c12

    from

         "DW_EQUIPMENT_D" T44607,

         "DW_STATUS_D" T53286,

         "DW_BOOKING_D" T44451,

         "DW_LOCATION_D" T44861,

         "DW_LESSEE_D" T44764,

         "DW_INVENTORY_BALANCE_F" T45014

    where  ( T44451."DW_BOOKINGS_KEY" = T45014."DW_BOOKINGS_KEY" and T44607."DW_EQUIPMENT_KEY" = T45014."DW_EQUIPMENT_KEY" and T44764."DW_LESSEE_KEY" = T45014."DW_LESSEE_KEY" and T44861."DW_LOCATION_KEY" = T45014."DW_LOCATION_KEY" and T45014."RESALE_FLAG" = 'N' and T45014."REPAIR_FLAG" = 'N' and T45014."DW_STATUS_KEY" = T53286."DW_STATUS_KEY" and T45014."SOLD_FLAG" = 'N' and T53286."STATUS_CODE" = 'AV' )

    group by T44607."EQUIPMENT_TYPE", T44764."MARKETING_AREA_REGION_CODE", T44861."MARKETING_AREA"

  • Hamd Salman-Oracle
    Hamd Salman-Oracle Rank 1 - Community Starter

    Hi Neethu,

    Please refer below document and see if it helps resolve your issue:

    How To Configure OBIEE To Generate A Single SQL Query Instead Of Multiple Queries. (Doc ID 1506188.1)


    Mark this answer as correct if it helps !!!


    Thanks