nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Joining dimension in different schema

Reeni

Hi, I am updating RDP for the first time. I have imported a fact table to RPD and trying to add foreign keys to join dimension tables. These dimension tables are in two different oracle schemas. I have imported fact table to ADVHR folder ( this is one schema name and have a separate data source) and dimension tables are in two schemas ADVHR and HRUSER. I need to join dimension table in HRUSER with fact table in ADVHR. Both these schemas have SELECT privilege (READ) on each other. How should I join dimension and fact table with are in different schemas ? Please tell me best way to a join the tables.

Find more posts tagged with

Accepted answers

All comments

Syedsalmancs110

Hello Reeni,

Please go through below Rittman blog which explains with examples on how to work with multiple datasource in OBIEE, see if it answers your query

https://www.rittmanmead.com/blog/2007/10/reporting-against-multiple-datasources-in-obiee/

Thanks and Regards,
Syed Hamd Salman

Gianni Ceresa

Hi,

Congrats on your first RPD editing

You said for now you have 2 different schemas in 2 different data source (so I guess 2 different DB object in your RPD each one with a different connection pool). Right now you can already join things together like you normally do in OBIEE: select the few objects, right click > physical diagram and there draw your joins and set rules.

But ... there is a but: doing that, using tables coming from 2 different DB objects in OBIEE with 2 different connection pools, will give you bad performance because the BI server will do the work of joining things together and not your DB.

You said both schemas have a select privilege on each other, so ideally you must import all the required tables into the same DB object (using a unique connection pool) and link them there (they will be part of the different schemas, that's not an issue). In that way the full query will be pushed down to the DB and the BI server will not have to do the work. Will work better and queries will also be easier to analyze/debug as it will be a single piece of SQL.

The ideal solution is if you can end up with something like this:

One DB object ("01 - Sample App Data (ORCL)") using a single connection pool (single connection to your DB) and having multiple schemas available ("BIFOD", "BISAMPLE" etc.) each with different tables inside.

Thomas Dodds

In addition to the above, if you don't have public synonyms in your database for these tables, make sure you are using fully qualified name setting in connection pool - then these tables get referenced as SCHEMA.TABLE ...

Reeni

Thank you very much Thomas.

Reeni

Thank you very much for detailed explanation.

Gianni Ceresa

You are welcome, and +1 for @Thomas Dodds about the qualified name: I just always believe this one is checked all the time

If you manage to make it work and got all your answers you can maybe close the thread at some point, for now it's still This question is Not Answered.