Archive for the ODI SDK Category

Playing with ODI and Groovy – Part 4 – Exporting/Importing ODI Scenarios with SDK

Posted in GROOVY, Java, ODI SDK with tags , , on April 9, 2019 by Rodrigo Radtke de Souza

Hi all, I’m back with the continuation of this Groovy and ODI series. Last post we saw how to find the different scenarios between two environments. Today we will look on how we may export those different scenarios from our source repository and how to import them in our target repository. We will do a two-step operation: first we will export the different ODI objects from our source repository as XML files into a folder and then we will import those xml files into our target repository.
Our code is very similar to the one that we did for post 3, but we will need to enhance it a little bit. First thing that we will have to change in our code is the function that creates the list of objects. In the previous post, we were just adding the name of the scenarios to the list. Now we will need to store the object itself in the list, since we will need to have the ODI object (scenario) to have it exported.

def listObjects (odiInstance,odiClass,listOfObjects) {
	odiObjects = odiInstance.getTransactionalEntityManager().getFinder(odiClass).findAll().sort{it.name}
	for (Object odiSingleObject: odiObjects)
		listOfObjects.add(odiSingleObject)
}

Also, we will need to create a variable that will indicate the path where the objects will be temporarily exported.

exportPath = "C:\\Odi"

One import thing that we will need to change is how to compare the objects. In the previous post, we were simply comparing them, as they were strings, which was ok for our propose there. However, now we cannot simple compare the java objects because they will be different even if they represent the same scenario name/version. They are considered “different” because they came from different environments and logically, they represent different ODI entities.

diffScenarios = []
	for (Object odiSingleObject: sourceScenarios)
		if (targetScenarios.find {targetScenarios -> targetScenarios.getName() == odiSingleObject.getName() && targetScenarios.getVersion() == odiSingleObject.getVersion()}.equals(null))
			if (odiSingleObject.getName().startsWith('TEST'))
				diffScenarios.add(odiSingleObject)

I’m basically doing three tests to see if the source scenario will be migrated or not: first I compare its name, than its version and finally if its name starts with TEST (this last step does not need to be done if you want to get the complete scenario list). Next step I just print the scenarios names and versions that will be exported/imported:

println("List of ODI Scenarios that will be migrated")
		for (Object singObject: diffScenarios)
			println(singObject.getName() + "_" + singObject.getVersion())

Now comes the new code:

encode = new EncodingOptions();
transSource = sourceOdiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());
	exportService = new ExportServiceImpl(sourceOdiInstance);
	for (Object singObject: diffScenarios)
		exportService.exportToXml(singObject, exportPath, true, false, encode)

Export objects in ODI SDK is very straight forward: you need to inform which scenarios you want to export (in our case, all objects that were stored in diffScenarios array), the path where the object will be exported and the encode option that will be used. In this case, I just went ahead with the default encode options.

Importing objects is also easy, but similarly to a database, you need to explicitly commit your actions to make it effective in the target repository. Also, for the sake of simplicity, we will import all new scenarios under “root”, but we could explicitly say under which ODI objects we would want to have it imported to:

tm = targetOdiInstance.getTransactionManager()
	transTarget = tm.getTransaction(new DefaultTransactionDefinition());
	importService = new ImportServiceImpl(targetOdiInstance);
	for (Object singObject: diffScenarios)
	{
		println(exportPath+"\\SCEN_"+singObject.getName() + "_Version_" + singObject.getVersion()+".xml")
		importService.importObjectFromXml(ImportServiceImpl.IMPORT_MODE_SYNONYM_INSERT_UPDATE,exportPath+"\\SCEN_"+singObject.getName() + "_Version_" + singObject.getVersion()+".xml", true, null, true)
	}
	tm.commit(transTarget)

Once you run the job, you will get the following:

1

Our target repository already had TEST2, so that’s why its not in the list. When the user connects to the target repository, he will see the following:

2

That’s it for today folks! Hope you like it! See you soon! The code for part 4 can be found here.

Advertisement

14 Comments »

Playing with ODI and Groovy – Part 3 – Comparing ODI Scenarios from two environments

Posted in GROOVY, Java, ODI, ODI SDK with tags , , on February 1, 2019 by Rodrigo Radtke de Souza

Hi all!

Now that we can connect to any ODI environment and list all sorts of ODI objects, its time to compare them and find its differences. Today’s example will do a simple check on ODI Scenarios between two environments based on its name and version. We will increment our code logic in later posts by adding different types of filters to make our code more efficient, but as for now let’s do the basic stuff just for us the get the main idea.

One important thing to notice here is that we will compare the objects only by its name and version number, not by its content. In other words, if you have the same ODI object regenerated with the same version number over and over again, this code won’t probably work in the way you are expecting too, since it may tells you that SCENARIO_001 is the same as SCENARIO_001, but in fact it may not be if you regenerated the source scenario more than once.

Let’s pause and talk briefly about ODI Scenario versions

Just a small comment on using different ODI scenario version numbers whenever the code has changed: I’m a huge fan of that and I always do a big effort to implement this concept in every project that I work on. The concept is very simple, but a lot of people don’t do it:

  • Every time that the scenario leaves the DEV environment to be imported in a subsequent environment, it should have an incremental version number.

For example: when you create a new ODI scenario in DEV, you may keep regenerating it repeatedly with the same number, let’s say 1_00_00. After some time, you will deploy it to test environment using version 1_00_00. Now you need to do some code fix or enhancement to it. Instead of regenerate it as 1_00_00 and move it forward, you should increment a version number, like 1_00_01. Your test environment will have both scenarios, but you may call them always using “-1” in the Load Plans or Scenario calls in procedures, which will ensure that ODI will always use the latest one (1_00_01). You may end up with several test interactions/fixes and end up with 10 versions of the same scenario, but that is fine. Once test finishes, you move just the latest one to Production, which will be 1_00_10.

Some people don’t like this approach and their main argument is that it creates a bunch of “repetitive” scenarios over the environments. However, they don’t realize (at first) that this approach creates a lot of benefits over time, such as:

  • Traceability: if you have a defect tracking tool, you may add a comment in the defect (or even in the ODI scenario comments) identifying which defect generated which scenario version, so you may answer all those answers like: “is the code fix xxxx that was generated in version xxxx already in test environment?”. It’s impossible to answer that question if the version is always 001;
  • Control: you may easily see how many defects/interactions a particular ODI scenario had in that development cycle and who did each one of them, so you may have a sense if a code (or even a developer) is having problems to deliver things properly;
  • Roll back is a breeze: if you add something to production but needs to roll it back due to an unexpected behavior, you just remove the latest scenario and that’s it. ODI will pick the old scenario and you are good to go. No need to export the ODI scenario first as a backup, then import it again in case of any problem;
  • Number of scenarios is manageable: if someone still argues about the number of similar objects over the environments, we may always adopt “the last three” versions approach, where we maintain only the last three versions of a scenario in each environment. Three is a good number because its very unlikely that you will want to rollback your code to older than three versions.

Getting back to our main topic

Getting back to our post (and really hoping that you start to follow the incremental version approach from now on, if you dont already), lets change our code to connect to two repositories instead of one.  We already have a code to connect to a source repository, so its just a matter to duplicate it to a target repository (or you may create a function as well to reuse some of the code there):

targetUrl = "jdbc:oracle:thin:@YOUR_SERVER_INFO"
targetSchema = "DEV_ODI_REPO"
targetSchemaPwd = "XXXXXXXX"
targetWorkrep = "WORKREP"
targetOdiUser = "XXXXXXXX"
targetOdiUserPwd = "XXXXXXXX"

targetMasterInfo = new  MasterRepositoryDbInfo(targetUrl, driver, targetSchema, targetSchemaPwd.toCharArray(), new PoolingAttributes())
targetWorkInfo = new WorkRepositoryDbInfo(targetWorkrep, new PoolingAttributes())

targetOdiInstance = OdiInstance.createInstance(new OdiInstanceConfig(targetMasterInfo, targetWorkInfo))
targetAuth = targetOdiInstance.getSecurityManager().createAuthentication(targetOdiUser, targetOdiUserPwd.toCharArray())
targetOdiInstance.getSecurityManager().setCurrentThreadAuthentication(targetAuth)

Next thing is that we will slightly change our listObjects function to receive a list object that will hold our source and target ODI scenarios:

def listObjects (odiInstance,odiClass,listOfObjects) {
	odiObjects = odiInstance.getTransactionalEntityManager().getFinder(odiClass).findAll().sort{it.name}
	for (Object odiSingleObject: odiObjects)
		listOfObjects.add(odiSingleObject.getName() + " - " + (odiSingleObject.getClass()==OdiScenario.class? odiSingleObject.getVersion() : "NA") )
}

We will call it for both source and target connections:

println("Creating Source Scenarios List")
sourceScenarios = []
listObjects (sourceOdiInstance,OdiScenario.class,sourceScenarios)

println("Creating Target Scenarios List")
targetScenarios = []
listObjects (targetOdiInstance,OdiScenario.class,targetScenarios)

Now that we have both lists, is just a matter to compare those. Its very easy to compare and even filter the results in Groovy. The code below will get all ODI scenarios that exists in the source environment but do not exists in target and will filter this list to retrieve only the scenarios that begins with TEST. This kind of filtering is useful, since sometimes you just want to search for certain scenarios, not all of them (again, we will increase the filtering options on later posts):

diffScenarios = (sourceScenarios-targetScenarios).findAll(){sourceScenarios -> sourceScenarios.toUpperCase().startsWith('TEST')}
println("The difference (with the filter) is:")
println(diffScenarios)

If we execute our code, the result will be something like this:

post1

TEST2 scenario was already imported in my target environment, so the result says that TEST1/TEST3/TEST4 exists in source but not in target.

That’s it for today folks. Next post we will learn how do we get this list of different objects and import them in our target environment. If you want the code that was presented in this post, please click here.

Thanks!

1 Comment »

Playing with ODI and Groovy – Part 2 – Listing all kinds of ODI objects

Posted in Java, ODI, ODI SDK with tags , , on January 22, 2019 by Rodrigo Radtke de Souza

Today’s post is short as we will learn how to list any kind of ODI objects using ODI SDK. Although it is simple, it can be used for several different reasons in your daily activities and we will use it to list all the existing scenarios, load plans and folders in our ODI utility. ODI SDK has a very simple way to search for its objects as we can see below:

odi.getTransactionalEntityManager().getFinder(odiClass).findAll()

From “odi” instance object, we get an Entity Manager, which provides the methods to interact with the persistence context in order to make CRUD (Create, Read, Update, Delete) operations against IOdiEntity instances. ODI entity is any object that reside in an ODI repository and so is capable of being persisted (like scenarios, load plans, folders, etc).

From Entity Manager, we may get a Finder, that will receive an ODI Class as a parameter and will return a collection of all objects that belongs to that class. You can “find” any object that implements the IOdiEntity interface. Some examples of ODI classes that you can use are:

  • OdiDataStore
  • OdiFolder
  • OdiIKM
  • OdiLKM
  • OdiLoadPlan
  • OdiLogicalSchema
  • OdiModel
  • OdiPackage
  • OdiPhysicalSchema
  • OdiProcedure
  • OdiScenario
  • OdiScenarioFolder
  • OdiSession
  • OdiSessionFolder
  • OdiUser

So, lets create a procedure in our code that will list all the correspondent ODI objects from a given ODI instance object and a class:

def listObjects (odi,odiClass) {
	odiObjects = odi.getTransactionalEntityManager().getFinder(odiClass).findAll().sort{it.name}
	if (odiObjects.size() > 0) {
		for (int i = 0; i < odiObjects.size(); i++) {
			odiSingleObject = odiObjects.toArray()[i]
			println(odiSingleObject.getName() + " - " + (odiSingleObject.getClass()==OdiScenario.class? odiSingleObject.getVersion() : "NA") )
		}
	}
}

A couple of things about this code. You can see that I’m sorting all the objects that will be displayed by its name. But if I needed something more complex, like sort by name and version number, I could write something like this:

sort {a, b -> a.name.toLowerCase() <=> b.name.toLowerCase() ?: a.version  b.version}

However, this sort wouldn’t work for all classes, since we are using VERSION, which may not be applicable to all ODI objects, like folders. On those cases, we may do a simple check to see if that object belongs to a specific class or not:

odiSingleObject.getClass()==OdiScenario.class? odiSingleObject.getVersion() : "NA"

This one is checking if the current class is OdiScenario. If true, then it will get its version value, otherwise it will just get “NA”.

To run our procedure, it is just a matter to do something like this:

try {
	listObjects (sourceOdiInstance,OdiScenario.class)
	listObjects (sourceOdiInstance,OdiLoadPlan.class)
	listObjects (sourceOdiInstance,OdiScenarioFolder.class)
}
catch (e){
	println(e)
}

The result will be a print of the list of objects:

1

2

That’s it for today folks. You can look at the code in this link (I’ll add one for each post, so its easier for the readers to follow).

See ya!

2 Comments »

Playing with ODI and Groovy – Part 1 – Getting things ready

Posted in ETL, GROOVY, Java, ODI, ODI SDK with tags , , , on January 8, 2019 by Rodrigo Radtke de Souza

Hi all, how are you doing? It has been a long quiet period here in the blog and the reason is always the same: too much work, projects, personal things and so on. To “force” myself in getting some time to write in the blog (while I still have the “new year” feeling), I’ll start this series of ODI and Groovy development. Not sure how many posts I’ll write, but it will be a step by step on how to create your own ODI utilities using Groovy scripts. We will start from looking on the necessary tools that we will need to use, and the final goal is to have an ODI utility to solve a specific ODI developers’ problem. Let’s start then.

So, what is the problem that we are trying to solve?

ODI developers knows that, besides all their project’s problems, they need to deal with boring/repetitive/error prone daily activities that are often underestimated by people and that may cause big issues over time in large ODI environments. One of those simple (yet boring) tasks is to keep ODI environments in sync regarding to ODI scenarios. How to make sure that all ODI scenarios in my DEV environment were already migrated to TEST or PROD? What should have been deployed but was not? How can I see a list of those differences and figure out who/when that was done?

Almost every time that I need to answer one of those questions, I go to ODI repository metadata tables and start to write down queries to get the necessary information and compare them between the different environments. Although it works, it is time consuming, its manual and I need to have access to read the ODI metadata tables, which is not possible in a lot of places due to security reasons.

So, thinking about all that, I decided to create my own ODI utility that can connect to different ODI repositories, compare what is different between them and deploy any missing scenario that I wish to deploy. Although the idea sounds simple, it’s a pretty useful tool that may save us a lot of time and it can be reused in any project that you work on. Also, it can serve as a base for you to create any ODI utility that you may want to, so you can make your job more productive and automate all the boring/manual tasks.

Ok, you have convinced me. So, what do I need to get it done?

As the title of this post mentions, you will need ODI SDK libraries (they come as part of ODI install), Groovy/Java and a tool to write down your code. I choose Eclipse IDE because I was more familiar with the tool from my past Java developer days, but you can use anything that you want. In fact, ODI already comes with a Groovy editor that you could use, however it is a very basic editor that won’t give you a lot of the cool stuff that all those modern Groovy/Java IDEs can provide to you, like code completion, automatic library imports and so on.

You mentioned Groovy/Java. Why?

That’s a good question, since some people get confused about those two languages. As I’ve written before, in the way past, I used to adventure myself in Java code as a developer, which got very limited to a few scripts now and then once I started to work with data integration. At first, I thought about creating the utilities all in pure Java (due to my background), but ODI already comes with Groovy support, so I decided to look at it. Although I knew what Groovy concept was, I had never worked with it, so I had to do some study before start dealing with it.

Groovy is (a resume from its site): a powerful, optionally typed and dynamic language, with static-typing and static compilation capabilities, for the Java platform aimed at improving developer productivity thanks to a concise, familiar and easy to learn syntax. It integrates smoothly with any Java program, and immediately delivers to your application powerful features, including scripting capabilities, Domain-Specific Language authoring, runtime and compile-time meta-programming and functional programming.

Some key points that we should take from this resume and that drove my decision to use Groovy was:

  • Simplicity and Dynamicity: you can write the same code in Groovy compared to Java with less line codes, so it gets faster for you to code and read. Since its simpler, its great for writing concise and maintainable automation tasks/scripts (which is our goal here).
  • Smooth Java integration: Seamlessly and transparently integrates and interoperates with Java and any third-party libraries, which means that it is very easy for Java developers to learn and use Groovy.

There are other benefits/drawbacks about using Groovy over pure Java, but since ODI supports Groovy and its simpler to code (specially to create small automation scripts), those seemed good compelling reasons for me to use it.

Installing Eclipse with Groovy support

I’m going to describe here the steps to have Eclipse working with Groovy, so if you are using another IDE or even coding directly in ODI Groovy editor, you may skip this part. Installing Eclipse (Eclipse IDE for Java Developers) is very straight forward. You just need to go to Eclipse site and install it. However, Eclipse is aimed for Java development, not Groovy, which needs additional steps to get it installed in Eclipse. So, I read this site and replicated step 3, installing the “Groovy Eclipse plug-in” manually. I also executed step 6 just to make sure that my Eclipse/Groovy install was correct.

post1

Connecting to ODI

Let’s create a script that simply connects to an existing ODI instance, just to validate our Eclipse/Groovy/ODI SDK installation. First, we need to import the necessary Jar files to our Groovy project in Eclipse. Right click and select “Build Path/Configure Build Path”:

post2

On Libraries, select “Add External Jars”:

post3

There are some Jars that you will need to import to make it work. Here is the list:

  • Go to “Path to your ODI install\odi\sdk\lib” and import all Jar files from that folder;
  • Go to “Path to your ODI install\oracle_common\modules\oracle.jdbc” and import ojdbc8.jar from there;
  • Go to “Path to your ODI install\oracle_common\modules” and import all javax* jar files. Those are only needed to clear some weird warning messages that appears when connecting to ODI repository using Eclipse;

Now one important step that needs to be done if you are using Eclipse and ODI SDK Jar files. Once you import the above list, click on “Groovy Libraries” and click in “Remove” as below:

post4

This “removal” will remove the Groovy Libraries that were added as part of Groovy plugin install that we did before.  This removal is needed because ODI SDK libraries already contains Groovy libraries and they may conflict if they are in different versions. Below is an example of what happens if you don’t do this removal step in Eclipse.

post6

The code to connect o an ODI instance is very simple as we can see below. It imports a few libraries, create some variables that will be used as the login information and gets authenticated in the Master/Work repository.


import java.util.logging.Logger;
import java.util.logging.Level;
import oracle.odi.core.OdiInstance;
import oracle.odi.core.config.MasterRepositoryDbInfo
import oracle.odi.core.config.OdiInstanceConfig
import oracle.odi.core.config.PoolingAttributes
import oracle.odi.core.config.WorkRepositoryDbInfo
import oracle.odi.core.security.Authentication

logger = Logger.getLogger("oracle.jdbc");
logger.setLevel(Level.SEVERE);

sourceUrl = "jdbc:oracle:thin:@YOUR_SERVER_INFO";
driver = "oracle.jdbc.OracleDriver";
sourceSchema = "DEV_ODI_REPO";
sourceSchemaPwd = "XXXXXXXX"
sourceWorkrep = "WORKREP";
sourceOdiUser = "XXXXXXXX";
sourceOdiUserPwd = "XXXXXXXX";
sourceMasterInfo = new MasterRepositoryDbInfo(sourceUrl, driver, sourceSchema, sourceSchemaPwd.toCharArray(), new PoolingAttributes());
sourceWorkInfo = new WorkRepositoryDbInfo(sourceWorkrep, new PoolingAttributes());

sourceOdiInstance = OdiInstance.createInstance(new OdiInstanceConfig(sourceMasterInfo, sourceWorkInfo));
sourceAuth = sourceOdiInstance.getSecurityManager().createAuthentication(sourceOdiUser, sourceOdiUserPwd.toCharArray());
sourceOdiInstance.getSecurityManager().setCurrentThreadAuthentication(sourceAuth);

println("Connected to ODI! Yay!")

When we execute the code (Run/Run As/Groovy Script), we can see that it connects successfully to our ODI instance. You may also decrease the ODI log level if you don’t wish so many details, but as for now, I’ll leave it as is.

post5

That’s it folks for our first post. Next one I’ll talk about how to get all ODI scenarios, load plans and folders and display it in a tree component, similarly on what we have in ODI Operator.

See ya!

2 Comments »