Generating multiple physical queries for single Report having single fact.

Question

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

Hamd Salman-Oracle · Answer

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

3027402 · Answer

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"

DanPryke · Answer

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