Oracle Business Intelligence

Products Banner

OBIEE cross database join

Received Response
36
Views
3
Comments

Hello,

I have two data sources 1)Snowflake 2)Oracle

snowflake has 4 tables(2 fact 2 dim) Oracle has 1 dim

as per my requirement, I have to join snowflake dim to oracle dim in physical

and in bmm, I am bringing oracle dim columns as separated LTS in the snowflake dim(1st LTS IS snowflake table , 2nd lts (join between snowflake table and oracle table)


when I generate the report with all the columns, I expect obiee fires 2 queries(1 for snowflake and 1 oracle) , but obiee is firing 3 queries

1) all the snowflake tables except the dimesion table that joined with oracle

2)only snowflake dimesnion table(which it is supposed to be in the 1st query) without any joins

3)oracle dimesnion


because of 2 nd query, the report keep on running, as it doesn't have any join and I assume it is doing full outer join

how to model in rpd in such a way, obiee fires only 2 queries?

Tagged:

Answers

  • 1.) What exact version? And as a reminder: "12c" isn't an exact version.

    2.) Neve assume anything. Verify. The log precisely states what the Server does. Log Level 5 will show you. Get that log. Read it. Find what kind of join is done. Post the result.

    3.) Even at the 99.9% chance risk of this third point meaning the other two don't get done: Most likely based on your model (which governs how queries get produced), the engine does an in-memory stitch join between the data sets which is clearly visible in the log.

  • as per my requirement, I have to join snowflake dim to oracle dim in physical

    But do you actually have the physical join between the 2 tables? Because you jump straight into describing your logical model and the LTS, without saying if you actually have a physical join or not...

    (This is for point 3) above posted by Christian)

  • Hi Gianni

    Yes, I do have physical join in physical layer between snowflake dim and oracle dim.