Oracle BI Apps for Oracle eBusiness Suite Adaptor
Informatica requires you to define the database connections in the Workflow Designer. The connection can then be refereneced in the Workflow session parameters. You can enter different target connects within a mapping. Informatica allows you to perform a heterogeneous join between tables from different databases within a mapping.
Oracle eBusiness Suite stores tables in different database schema and potentially allows you to specify a different tablespace for each applications. I guess that this is really for a historical reason. All the EBS tables are made available in the APPS schema.
The EBS database architecture raised an interested question. How should the BI warehouse connect to it? Should we access it via the APPS schema or should we use the schema which stores the tables? What is the problem of using the synonyms or database views instead of tables as the source tables?
In EBS database, it is not uncommon that the tables are cross joined from different products, and even across “pillars” . It is very different from PeopleSoft apps, which always uses different instances for HR, FIN/SCM, CRM, and EPM. Oracle BI apps makes a deployment design by using a single connection for accessing all the products from a single instance. So a given mapping maybe joining the tables from different products. The design assumption made is the same as the design assumption the EBS apps development has.
In the real world deployment, I saw several cases that people do have multiple EBS apps instances running within an enterprise. Oracle its do that in several years ago. It then had a amazing IT project to combine all of them into a single instance. However, there are still companies which run multiple EBS instances which may be under different version and want to have a single BI warehouse. I think that the BI Apps ETL environment is flexible enough to handle this scenarios. However, this won’t be under the “pre-package” apps. Still, the beauty of the Oracle BI apps architecture is that it won’t be impossible, unlike the previous old EBS DBI architecture, or what called Fusion Intelligence.