Forum Stats

  • 3,837,853 Users
  • 2,262,302 Discussions


OBIEE cross database join


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?



  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,648 Gold Crown

    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.

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,638 Blue Diamond

    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)

  • User_Q7GV4
    User_Q7GV4 Member Posts: 2 Red Ribbon

    Hi Gianni

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