ODI 11g - Scripting a Reverse Engineer

A common question is related to how to script the reverse engineer using the ODI SDK. This follows on from some of my posts on scripting in general and accelerated model and topology setup.

Check out this viewlet here to see how to define a reverse engineering process using ODI's package.

Using the ODI SDK, you can script this up using the OdiPackage and StepOdiCommand classes as follows;

  1.  OdiPackage pkg = new OdiPackage(folder, "Pkg_Rev"+modName);
  2.   StepOdiCommand step1 = new StepOdiCommand(pkg,"step1_cmd_reset");
  3.   step1.setCommandExpression(new Expression("OdiReverseResetTable \"-MODEL="+mod.getModelId()+"\"",null, Expression.SqlGroupType.NONE));
  4.   StepOdiCommand step2 = new StepOdiCommand(pkg,"step2_cmd_reset");
  5.   step2.setCommandExpression(new Expression("OdiReverseGetMetaData \"-MODEL="+mod.getModelId()+"\"",null, Expression.SqlGroupType.NONE));
  6.   StepOdiCommand step3 = new StepOdiCommand(pkg,"step3_cmd_reset");
  7.   step3.setCommandExpression(new Expression("OdiReverseSetMetaData \"-MODEL="+mod.getModelId()+"\"",null, Expression.SqlGroupType.NONE));
  8.   pkg.setFirstStep(step1);
  9.   step1.setNextStepAfterSuccess(step2);
  10.   step2.setNextStepAfterSuccess(step3);

The biggest leap of faith for users is getting to know which SDK classes have to be used to build the objects in the design, using StepOdiCommand isn't necessarily obvious, once you see it in action though it is very simple to use. The above snippet uses an OdiModel variable named mod, its a snippet I added to the accelerated model creation script in the post linked above.

Comments:

A useful post to check for package creation using SDK is the one on the ODIexperts blog.... http://odiexperts.com/creating-package-using-sdk/

Cheers
David

Posted by guest on December 03, 2012 at 10:25 AM PST #

David,
thanks just what I was looking for

Mark Holton

Posted by guest on April 29, 2013 at 07:05 AM PDT #

how do reverse data stores in this way...I can understand every model will have a model ID..so packages can be used...but how can we reverse engg a flat file (data store) using ODI SDK..

awaiting your reply

thanks

Posted by parth d on August 26, 2013 at 10:13 AM PDT #

Hi

You can use an RKM or build one to to construct the metadata from any source you would like.

ODI ships the RKM File (from Excel) which reads the file metadata from an Excel spreadsheet for example, so you can define the column names and datatypes in the spreadsheet and load the definition into ODI. There is a blog post below giving details of that specific RKM;
http://john-goodwin.blogspot.com/2009/03/odi-series-reversing-collection-of.html

This is defined in the ODI documentation below, John's blog post gives a better insight;
http://docs.oracle.com/cd/E23943_01/integrate.1111/e12644/files.htm#CJADIHEJ

Cheers
David

Posted by David on August 26, 2013 at 10:51 AM PDT #

Yes that's true. We can use RKM file but then my problem is that normally without the SDK we manually click on the Reverse engineering button and then we can see the data appearing...how to replicate this action using the ODI SDK is my issue

Thanks to u I am able to atleast set the RKM and other options for my data store...but not able to reverse engg it

Posted by parth d on August 27, 2013 at 09:21 AM PDT #

So you want to know how to set the RKM on a model, then execute the reverse engineer from scripting?

Cheers
David

Posted by David on August 27, 2013 at 09:28 AM PDT #

Yes right.

Let's say i want to reverse engg a flat file source (.txt)
What I would do is

1) Without SDK

Create a model say file_model , set schema, name etc
Then right click the model, add the data store, set the delimiters if any and then click reverse engg option..so I can see my data and then I add the columns...and so on

2) With SDK

I am currently to able to create the file_model, set the schema, name etc...create data store, set delimiters but not able to reverse engg.

I even saw this post: https://forums.oracle.com/thread/2560964
but it doesn't work for me.

Can you help me out over here?

Thanks a lot for your time and quick reply

Posted by guest on August 27, 2013 at 09:37 AM PDT #

Hi

There is no public SDK today for that functionality on the (file) datastore. You could use the SDK to create the columns based on a parse of the file, or use the RKM File from Excel to automate the construction of the file datastores.

Cheers
David

Posted by David on August 27, 2013 at 11:12 AM PDT #

Ok thanks! So that means at present no such class or interface exists using which I can perform the "interactive" reverse engg task for a datastore, right?

Also do you think using the RKM would help achieve my task. Does it have a method or so for the same. I searched the docs but couldn't find one! :(

Posted by parth d on August 27, 2013 at 06:40 PM PDT #

If you define the shape of the file in an Excel spreadsheet you can use the RKM File form Excel to reverse engineer all of the file metadata. You can use this via the SDK by following the instructions from the post on creating a package.

Cheers
David

Posted by David on August 28, 2013 at 08:21 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today