OBIEE is forming two queries

Deepak_R-Oracle

In OBIEE 12.2.1.3.0 Whenever any fact table is queried, two queries are issued(one to get distinct dimensions value and another to get fact data). Ideally only one query should be issued but 12c is issuing two queries and then doing full outer join.

I have fixed all the Hierarchy warnings but still i'm getting the same results any help is greatly appreciated.

Joel

The joins that OBIEE creates are based on your rpd model. What does your model look like between your logical dimension and your logical fact?

Deepak_R-Oracle

I have couple of dimensions and only 1 fact, All dimensions are connected to this fact through inner join.

Joel

Are you getting data from multiple dimensions? If so, I'd suggest gradually removing dimension attributes until you pin point which dimension has the problem and then you can look at your logical joins.

Deepak_R-Oracle

What i have observed is my fact has couple of measures such as Sales, Profit etc and couple of factors like multiplying factors at times i need to multiply my measure with the factor, when i go for multiplication obiee is forming two queries first query will have distinct dimensional values  which would include my factors also and the second query will have calculation of sum(), count() etc. and i can't see how the joining is happening between these two queries mostly it is happening at bi server.

Deepak_R-Oracle

And i tried not to include any dimension(not in both select and filter) and just did factor*measure that also resulted in two queries, Strange.

Joel

How many logical table sources do you have in your logical fact?

Gianni Ceresa

As Joel said, the BI Server generate queries based on what the RPD tell it to do (and some settings telling the system what your database understand).

So far it's a very generic thread without examples or queries, so not sure what else could be said. What I can say, and you will not like it, is that my OBIEE doesn't do that. So this exclude that it's the unique standard behaviour of OBIEE.

How to find out why your analysis generate 2 queries? Start posting queries (logical and physical), the analysis, the model, ... Anything which could be used to try to identify why you get that behaviour.

Deepak_R-Oracle

only 1 LTS and below is the physical query that got generated

select /*+ PARALLEL(T45303 16) */ distinct T45303.N_LIFETIME_PD as c1

from

  FCT_LLFP_ACCOUNT_SUMMARY T45303 /* Fct LLFP Account Summary */

select /*+ PARALLEL(T45303 16) */ sum(T45303.N_EXPOSURE_AT_DEFAULT_RCY) as c1

from

  FCT_LLFP_ACCOUNT_SUMMARY T45303 /* Fct LLFP Account Summary */

Deepak_R-Oracle

Logical SQL

SELECT

   0 s_0,

   "Loan Loss Forecasting and Provisioning"."F0006 Fct LLFP Account Summary"."Lifetime Pd"*"Loan Loss Forecasting and Provisioning"."F0006 Fct LLFP Account Summary"."Exposure At Default Rcy" s_1

FROM "Loan Loss Forecasting and Provisioning"

Joel

looks like your measure has 2 sources:

distinct T45303.N_LIFETIME_PD

&

sum(T45303.N_EXPOSURE_AT_DEFAULT_RCY)