3 Replies Latest reply on Mar 14, 2016 7:33 PM by Hamd Salman-Oracle

    Generating multiple physical queries for single Report having single fact.

    3027402

      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

        • 1. Re: Generating multiple physical queries for single Report having single fact.
          DanPryke

          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

          • 2. Re: Generating multiple physical queries for single Report having single fact.
            3027402

            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"

            • 3. Re: Generating multiple physical queries for single Report having single fact.
              Hamd Salman-Oracle

              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