Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
OBIEE is forming two queries
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.
Answers
-
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?
0 -
I have couple of dimensions and only 1 fact, All dimensions are connected to this fact through inner join.
0 -
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.
0 -
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.
0 -
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.
0 -
How many logical table sources do you have in your logical fact?
0 -
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.
0 -
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 */
0 -
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"
0 -
looks like your measure has 2 sources:
distinct T45303.N_LIFETIME_PD
&
sum(T45303.N_EXPOSURE_AT_DEFAULT_RCY)
0