Oracle Analytics and AI

Vineela1

Obiee version:12.2.1.2.0

I created an analysis & it generated a backend query .I got both of these from Administartion->Manage sessions

Obiee logical query vs db query

Filter condition in logical query:

FROM "POC"
WHERE
(((("DIM_TD_SUMMARY"."BUDGET_AMOUNT" <> 0) AND ("DIM_TD_SUMMARY"."TRANSACTION_AMOUNT" <> 0) AND ("DIM_TD_SUMMARY"."SALARY_COMMITMENT" <> 0) AND ("DIM_TD_SUMMARY"."FISCAL_YEAR" = 0)) OR ("DIM_TD_SUMMARY"."FISCAL_YEAR" <> 0)) AND (("DIM_TD_SUMMARY"."PO_REFERENCE" <> 'PO') OR (("DIM_TD_SUMMARY"."PO_REFERENCE" = 'NULL') AND ("DIM_TD_SUMMARY"."TRANSACTION_DATE" = timestamp '2020-05-01 00:00:00') AND ("DIM_TD_SUMMARY"."IS_SMARTBUY" = 'Y'))) AND ("DIM_TD_SUMMARY"."FISCAL_YEAR" >= 2021) AND ("DIM_TD_SUMMARY"."FUND_CODE" IN ('FC')))

Filter condition in the backend query in obiee:

where  ( T163020.FUND_CODE = 'FC' and T163020.TRANSACTION_ID = T163287.VOUCHER_ID and (T163020.IS_SMARTBUY in ('Y') or T163020.PO_REFERENCE <> 'PO') and (T163020.PO_REFERENCE in ('NULL') or T163020.PO_REFERENCE <> 'PO') and (T163020.FISCAL_YEAR <> 0 or T163020.TRANSACTION_AMOUNT <> 0) and (T163020.TRANSACTION_DATE in (TO_DATE('2020-05-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')) or T163020.PO_REFERENCE <> 'PO') and (T163020.FISCAL_YEAR <> 0 or T163020.SALARY_COMMITMENT <> 0) and (T163020.BUDGET_AMOUNT <> 0 or T163020.FISCAL_YEAR <> 0) and T163020.FISCAL_YEAR >= 2021 )

Simple example of my scenario:

Logical query:A OR(B AND C)

Db query: (A or AND (A OR C)

My need is to have condition similar to logical query but why is it different when it is sending to db?

Please let me know the reason

Thanks

Gianni Ceresa

Why the physical query is different than the logical one? Because that's what you paid for when you bought the tool.

I know this answer is not something you will like...

OBIEE does generate queries for you, as you see you don't write physical SQL anywhere. You model things, you write logical queries (you did it clicking around in the criteria page, but still you were just writing a logical query) and then OBIEE will take that and look at how to best turn it into a physical query.

Conditions are rewritten to try to get the best minimal condition out of it, mainly when having multiple AND and OR in your logical query, OBIEE always put its hands in there and rewrite them when generating the physical query to try to improve things by removing unneeded pieces or trying to optimize things a bit.

Does it always do a good job? Not at all, it isn't a database in the end and therefore can't know how to really optimize a query like a database would do. But still it isn't wrong because your logical query is for the BI Server, not for a physical source.

If the physical query generated isn't something you like, you can try to change your logical query to see if you can trick the BI Server in generating what you want, but there aren't miracles: the BI Server physical query generation is a blackbox. If you can't make it work, you should consider remodeling things in a way making your queries different.