Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE is forming two queries

Received Response
331
Views
19
Comments
Deepak_R-Oracle
Deepak_R-Oracle Rank 3 - Community Apprentice

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.

Tagged:
«1

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    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
    Deepak_R-Oracle Rank 3 - Community Apprentice

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

  • Joel
    Joel Rank 8 - Analytics Strategist

    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
    Deepak_R-Oracle Rank 3 - Community Apprentice

    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
    Deepak_R-Oracle Rank 3 - Community Apprentice

    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
    Joel Rank 8 - Analytics Strategist

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

  • 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
    Deepak_R-Oracle Rank 3 - Community Apprentice

    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
    Deepak_R-Oracle Rank 3 - Community Apprentice

    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
    Joel Rank 8 - Analytics Strategist

    looks like your measure has 2 sources:

    distinct T45303.N_LIFETIME_PD

    &

    sum(T45303.N_EXPOSURE_AT_DEFAULT_RCY)