Here are few questions with respect to OCDM Architecture. I would really appreciate if someone can answer them:
1) The OCDM tutorials (as in http://st-curriculum.oracle.com/tutorial/OCDMTutorial/player.htm) mentions that the contents
in OCDM Base Layer has the enterprise DW in 3 NF. Since an ETL is required to populate the OCDM Base Layer based on data in underlying Oracle Database, what are the different transformations that needs happen in the ETL?
2) Does OCDM have compatibility to work with databases that are other than Oracle Database 11g?
3) The OCDM has over 1200 tables & the OBIEE repository (in reports/information access layer) seems to refer the data from within OCDM. Are there scenarios where OBIEE repository refers to tables in underlying Oracle Database (once ETL is completed)?
Below are my comments numbered in the same order as you have followed.
1. Hope you might have understood through the product documentation that OCDM already has Intra ETL which can move the data from 3NF layer to DWH tables, but Source ETL is some thing which is not part of the OCDM product and was supposed to be developed during the implementation of the product. It would be premature to comment on transformations required in source ETL as this largely depends on the content and quality of data which we are working with during implementations. As you know the content and quality of data vary from customer to customer.
2. If your question is whether OCDM accepts data from other databases(Non-Oracle), then answer is yes OCDM can accept data from any other source databases. But the final dwh tables are in Oracle only, you can use any other databases for staging to process or store data before you load the data into OCDM(Oracle) entry layer(3NF).
3. Yes as long as these tables have the proper relationship defined with other OCDM tables this should not be an issue as extendability is the striking feature of this model.
A quick complement on point 2:
If your question is about whether OCDM is working with Oracle DB 10g or lower, the answer is no.
That would be equivalent of only using the logical data model from OCDM, which could in principle be used on any database. But you would not benefit of all Oracle 11g features we use in OCDM for best possible performance without tuning, nor you could run the install scripts with Oracle Universal Installer.