Categories
- All Categories
- 70 Oracle Analytics News
- 7 Oracle Analytics Videos
- 13.9K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 38 Oracle Analytics Trainings
- 56 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 2 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Multiple Physical Schema under one database in RPD
Dear All
I am using Oracle database and created two physical schemas (DWH Staging and DWH), all the dimensions are created under DWH schema and all the Facts are created under DWH Staging schemas, joined the tables as per requirement, created BMM and Presentation, it is working fine Fig.1
Tried with two connection pool also as shown in Fig.1 even updating accurate number of records
but when I try to create analysis it is generating an error Fig.2, both schema has all rights on each other
Fig.1, Physical Layer of Staging
Fig.2, Error in Analysis
I have searched and read many questions / answers on this forum but unable to resolve the issue
Anyone please help
regards
Answers
-
Rai Qaiser Hussain wrote:Tried with two connection pool also as shown in Fig.1 even updating accurate number of records
That's all you don't want !
If you need 2 connection pools, one for dims, one for facts, you are moving the whole load of acting as a database on the BI Server. Do not do that!
It make no sense to have 2 connection pools to access on the same database objects of the same business model.
Use a technical account which has read access on objects of the 2 schemas, so the load can be pushed down on the database which will work as a real database applying filters and aggregations.
0 -
Dear @Gianni Ceresa
As I have already written that both schema has read access on each other but still I am facing same issue moreover I tried following two options
- Single Connection pool with two Physical schema (DWH and DWH_Staging) -------- you have suggested this option
- Two Connection Pools with single schema (One connection pool for DWH and one for DWH_Staging)
but still error is same
Can you please guide about any special rights?
regards
0 -
Did you look at the real query generating the ORA-00942 ? What is the table it try to find?
Did you enable fully qualified names in the connection pools?
0 -
Gianni Ceresa wrote:Did you enable fully qualified names in the connection pools?
THIS.
That's why Gianni asked you to check the fully REAL query. It's a very normal config option and it depends on HOW you connect to the data source in detail. What user you're using in the connection pool.
0 -
Alternatively, you can enforce this on the database side by having a single schema/user that has access to the tables across all schemas and this can be controlled by creating synonyms in the schema that you can use for your connection pool.
0 -
Dear Gianni
Thank you very much, problem has been resolved, infact Require fully qualified table names was not enable
regards
0 -
I'm still not sure why by default it isn't enabled as it avoid many issues as soon as multiple schemas are involved.
You can assume that on an Oracle database you can check that checkbox by default as it will avoid you issues in the future (and so far I didn't find issues in always enabling that).
0 -
Because not every data base is an Oracle database and the tool is source agnostic.
0 -
They could still add a "Best practice violation" in the consistency check of the RPD, so they can perform the test only when Oracle is the database.
Let's say there are ways it could have been added, they just didn't
0 -
Sure, but there are about 5000 other good practices they could have implemented and you know as well as I do that nothing will happen anymore
0
