1 2 Previous Next 19 Replies Latest reply on Oct 7, 2019 9:21 AM by Deepak_R-Oracle

OBIEE is forming two queries

Deepak_R-Oracle Sep 30, 2019 10:41 AM

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.

1. Re: OBIEE is forming two queries

Joel Acha Sep 30, 2019 10:44 AM (in response to Deepak_R-Oracle)

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?
Like Show 0 Likes(0)

Actions
2. Re: OBIEE is forming two queries

Deepak_R-Oracle Sep 30, 2019 10:59 AM (in response to Joel Acha)

I have couple of dimensions and only 1 fact, All dimensions are connected to this fact through inner join.
Like Show 0 Likes(0)

Actions
3. Re: OBIEE is forming two queries

Joel Acha Sep 30, 2019 11:19 AM (in response to Deepak_R-Oracle)

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.
Like Show 0 Likes(0)

Actions
4. Re: OBIEE is forming two queries

Deepak_R-Oracle Sep 30, 2019 11:38 AM (in response to Joel Acha)

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.
Like Show 0 Likes(0)

Actions
5. Re: OBIEE is forming two queries

Deepak_R-Oracle Sep 30, 2019 11:46 AM (in response to Joel Acha)

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.
Like Show 0 Likes(0)

Actions
6. Re: OBIEE is forming two queries

Joel Acha Sep 30, 2019 12:04 PM (in response to Deepak_R-Oracle)

How many logical table sources do you have in your logical fact?
Like Show 0 Likes(0)

Actions
7. Re: OBIEE is forming two queries

Gianni Ceresa Sep 30, 2019 12:12 PM (in response to Deepak_R-Oracle)

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.
Like Show 0 Likes(0)

Actions
8. Re: OBIEE is forming two queries

Deepak_R-Oracle Sep 30, 2019 12:17 PM (in response to Joel Acha)

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 */
Like Show 0 Likes(0)

Actions
9. Re: OBIEE is forming two queries

Deepak_R-Oracle Sep 30, 2019 12:20 PM (in response to 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"
Like Show 0 Likes(0)

Actions
10. Re: OBIEE is forming two queries

Joel Acha Sep 30, 2019 12:20 PM (in response to Deepak_R-Oracle)

looks like your measure has 2 sources:

distinct T45303.N_LIFETIME_PD
&
sum(T45303.N_EXPOSURE_AT_DEFAULT_RCY)
Like Show 0 Likes(0)

Actions
11. Re: OBIEE is forming two queries

Deepak_R-Oracle Sep 30, 2019 12:23 PM (in response to Joel Acha)

Exactly my question why two sources when my logical table is only one and my physical table is also only one
Like Show 0 Likes(0)

Actions
12. Re: OBIEE is forming two queries

Deepak_R-Oracle Sep 30, 2019 12:24 PM (in response to Joel Acha)

Both the columns are from same logical table and physical table why obiee is forming two queries
Like Show 0 Likes(0)

Actions
13. Re: OBIEE is forming two queries

Gianni Ceresa Sep 30, 2019 12:27 PM (in response to Deepak_R-Oracle)

What is the database type of the database object in the physical layer of the RPD?

You have attributes and measures in the same table, and you say they are from the same LTS, which is, by design, wrong as a fact logical table must have only measures (aggregates) and anything else must be in a dimension.
Like Show 0 Likes(0)

Actions
14. Re: OBIEE is forming two queries

Deepak_R-Oracle Sep 30, 2019 12:31 PM (in response to Gianni Ceresa)

Database Type is Oracle 12c and both the columns are from same table, Seems you didn't understand my fact table structure, I have measures and some factors in my fact table and those factors are needed for calculation.
Like Show 0 Likes(0)

Actions

1 2 Previous Next

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.