Oracle Business Intelligence

Products Banner

OBIEE is forming two queries

Received Response
283
Views
19
Comments

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:

Answers

  • Joel
    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?

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

  • Joel
    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.

  • 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.

  • 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 ✭✭✭✭✭

    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.

  • 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 */

  • 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 ✭✭✭✭✭

    looks like your measure has 2 sources:

    distinct T45303.N_LIFETIME_PD

    &

    sum(T45303.N_EXPOSURE_AT_DEFAULT_RCY)

  • Exactly my question why two sources when my logical table is only one and my physical table is also only one

  • Both the columns are from same logical table and physical table why obiee is forming two queries

  • 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.

  • 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.

  • Joel
    Joel ✭✭✭✭✭

    If the "factors" aren't facts, then they should be derived from a dimension as dimension attributes.

  • My Fact has columns as follows Prod_skey,Date_skey, location_skey, tweleve_month_pd, n_exposure_at_default_at_rcy, Why would i have a dimension for just a multiplication factor i i cant understand, That factor is at the Prod, date, location granularity.

  • Finally found the solution, It was not the table issue, It was more of a repository modelling issue which i have corrected.

  • What kind of modeling issue? It would be good etiquette to share the solution with others.

  • I wanted to review my complete rpd just to make sure i have done everything properly before raising SR with oracle. When i was investigating my BMM layer i have found out that the measure column i have set the aggregation to be sum and then i have created my calculation column like measure*multiplication_factor which was looking like 'sum(sales)*pd_percent' it was evident that first part was summation and the second part was just a factor multiplication, So obiee was framing two queries then i have duplicated the measure column and kept aggregation as none then used this new column in creating a multiplication which was looking like sales*pd_percent then i have added sum explicitly in the column formula which solved my issue.