View Full Version : Working on ETL tools interoperability using Common Warehouse Model (CWM)

12-22-2008, 10:09 AM
Hi All,

Its just a piece of information and not a question.

I have been working on proving the ETL tools interoperability using Common Warehouse Metamodel(CWM), an OMG standard. The whole concept is to take out the metadata from an ETL tool say OWB and put it into CWM Metadata Repository and this metadata can be used for building the same project in any other tool say Informatica or may be in the same ETL tool.

The main thing in this process is to map each ETL tool with the CWM concepts and then using Model to Model Tranformations(Technologies like Xtend) one can set up a communication between different ETL tools.

Till now I have worked with OWB only. I, with my team, have extracted all information from an OWB project (which has medium level of complexity like two oracle modules(schemas) and few tables, views and mappings with various operators) and put it in CWM repository and extracted it back from CWM MDR to OWB itself. We haven't worked with any other ETL tool because of the unavailablity of any other ETl tool with us. We will be working with Pentaho kettle in near future and try to prove the whole process as two way communication.

The whole process can be described in steps as below :
1. Creation of a manual OWB Ecore model(model representaion in Eclipse Modelling Framework) which gives all dependencies and reletionships in OWB objects like Project,OracleModule etc.
2. Creation of CWM ecore model from rational Rose mdl which has been provided by OMG on their site.
3. Generation of Java code(Gen Model) from the above mentioned ecore model(It is needed to create an object from OWB).
4. Extraction of project from OWB using public views which has been exposed by OWB itself. You can refer to http://download.oracle.com/docs/cd/B31080_01/doc/owb.102/b28225/toc.htm link for OWB public views and other APIs.
5. (Actually Step 4 is a part of this step only )Writing a Java code which has JDBC connection for accessing OWB public views and Ecore model as imported java files(Step 3 has been done for this part only). This java code will return an OWB project object(instance of the Ecore model) which will be used in further steps.
6. Writing an Xtend code to do a model to model tranformation from OWB to CWM.
7. Writing an Open Architecture Workflow to combine all the steps in one step which will take the output of java code(step 5) and put it into Xtend Code(step 6) and then will take the output of Xtend code and give it to the XMIWriter(an OAW component) to write an XMI which is actually a CWM Ecore Model instance.
8. Saving above XMI(CWM model instance) to the CWM MDR using Hibernate and Teneo.

In the same way we can extract metadata from CWM MDR and put it into OWB. But the only problem with OWB is that we cannot persist OWB object in OWB repositories as OWB tables are very cryptic and tough to understand. So for that we have used TCL scripts(OMB Plus scripts) to create a project in OWB using OWB ecore instance. You can refer to the above oracle documentation link for TCL scripts.

Let me know if I can assist you if you are working on the same.

You can mail me for any queries. My email id is DeepakSharma1307@yahoo.com.


05-21-2014, 08:40 PM
I would use CWM to ensuring interoperability between tools is it necessary that each tool generate an xml file to do the exchange ?

can you give me some more information please about how CWM can make exchange because i have a problem to understand that.