Multiple or single database for different schemas in presentation layer?

Question

OBIEE 12.2.1.4

We have to create several models. Each model have data in its own database schema.

Whats is the best(correct) way - create multiple databases for each model or one for all?

I'd prefer multiple because

a) it is inconvenient to keep all tables in one folder(I mean in one Conn Poll and database) even with using aliases.

b) tit is additional work to grant privileges and create synonyms for all objects in one common schema

[Deleted User] · Answer

Not much to add except:

- remember the RPD is just metadata and you can arrange that metadata pretty much as much as you want whereas the DBAs have to actually do work to move things around

- you can always use the "Physical Display Folder" object type to make things appear as you want in the physical layer (and remain somewhat independent of real physical structures)

RPD = low effort. Database = a lot more effort with a lot more impact - not only OBI will be using those objects.

Gianni Ceresa · Answer

First, there isn't a "correct" way because there isn't really a wrong one. There also isn't a "best" way.

Your database can be as you want, and it's generally not the BI defining it but more the DBAs and the security rules you have in place.

I would say that a "not good" practice for me is creating synonyms to have all the objects in a single schema in the database. The database has grants for a reason, so you can have a technical account which has no content and just has grants to access all the other objects. This would give you, in the physical layer, the various schemas and the objects underneath, giving you an easier structure than an infinite list of objects all into the same schema.

What you have to consider is that every connection pool define new connections to the database. Having multiple connection pools could help the DBAs with their auditing etc. as they would more easily track what is accessing what else.

Another thing to keep in mind: in the RPD you can have multiple physical database objects, each having one or many connection pools. Nothing forbid you to have multiple database defined in the RPD but all using the same credentials to connect to the same database. It would be a way to structure things in the RPD (but again the number of total connections to the database will be impacted by this choice).

I would say that this topic is something you have to ask the DBAs to define and drive, they are the people in charge of dealing with the grants and making sure only the allowed people can access some information. If you have a single database account with everything inside, you have no security in the database. There is never enough security, so I would take an approach using some technical accounts in the database granted the rights to select data from the other schemas. It's probably the cleaner and easier way to manage things in the DB, and gives you already a layer of security in OBIEE too.

deniska · Answer

Tables and views for each BI-model are physically in different schemas of one oracle database

And question is about BI databases in Physical layer

Several bi-databases for each model(different Oracle schemas)

or

One bi-database for all models(one Oracle schema with synonyms to objects from another schemas)