Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Joining dimension in different schema
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.
Answers
-
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 Salman0 -
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.
0 -
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 ...
0 -
Thank you very much Thomas.
0 -
Thank you very much for detailed explanation.
0 -
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.
0
