Archive for January, 2019

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 »

ORACLE SQL for EPM tips and tricks S01EP01!

Posted in DEVEPM, ETL, Oracle, Oracle Database, Performance, SQL, Tips and Tricks, Uncategorized, WITH Clause with tags , , , , , , on January 21, 2019 by RZGiampaoli

Hey guys how are you? I decide to start a new series called ORACLE SQL for EPM tips and tricks. The idea here is to show the most useful SQL commands for EPM, how to improve performance, tips, tricks and everything that can be useful from a SQL point of view!

And to start well, I’ll show something very old but very useful that I don’t see too many people using these days. “WITH” clause.

I love to use “WITH” in my code. It helps organize the code, helps to optimize it and more important, to make it more efficient.

When you use “WITH” Oracle treats your query inside it as an inline view or resolved as a temporary table, making it easier and faster for Oracle to access that data if you need it again.

Simply putting, every time you needs to right a query that uses the same table over and over, it’ll probably be way more efficient if you use “WITH”.

The “WITH”clause works a little bit different from a regular SQL. We can say that we split the query in 2, one is the “WITH” declaration (That will behave like a table) and the other is the SQL that will Query the “WITH”. 

WITH name_of_temp_table_here AS
(
    YOUR QUERY HERE
),
   name_of_temp_table_here2 AS
(
   SELECT *
   FROM name_of_temp_table_here, another_table...
)
SELECT *
FROM name_of_temp_table_here, name_of_temp_table_here2

In the “WITH” you can have any kind of query you want. You can do joins, group by, you can also have more than one “WITH”, you can use the result of one “WITH” in the next “WITH”, you can do a lot of things.

But for now, lets take a look in a more real example. Let’s say that you have a table like I do, that contains all metadata from all yours applications:

Let’s say you want to get the Parent of a attribute that is associated with your Entity dimension. You probably will be doing something like this:

In the “FROM” we call the table 2 times and we join and filter everything we need. Since we don’t have attribute association in all levels we do a “Left Join” to make sure all Entities comes in the query. If we run a Explain Plan now we’ll get something like this:

As you can see, Oracle is querying the METADATA_EXTRACT table twice and each time it’s doing a FULL in one Partition (‘ENTITY’ and ‘PHYSICAL_GEOGRAPHY’ partitions).

Now, if we change the query (and we can do it in different ways, this is just one of them) to a “WITH” clause we ‘ll have something like this:

As you can see, we achieved the same results with the code a little bit different. Now I have all my filters in the “WITH” query and in the bottom I just call the “WITH” query 2 times and do what needs to be done.

If we run a Explain Plain now we will have:

As you can see, Oracle now is querying the METADATA_EXTRACT table just once and then his queries the SYS.SYS TEMP table twice. The only problem with this query and the way I did is that since we are creating a temporary table filtering 2 partitions and then later I’m filtering again, it’s basically doing 2 FULL scan in the same TEMP table, and even so, it’s a few seconds faster then the original query.

But this is just an example on how we can reduce the amount of times that Oracle needs to query a table. WITH is not a miracle clause or anything like that, is just another tool that we can use to optimize our code, and its performance needs to be evaluated in a case-by-case basis.

And even if the performance doesn’t change, I believe using “WITH” clause makes any query easier to ready, to test, to update and to right since you can divide your huge query in small bits and then join
everything in the bottom query.

“WITH” is a huge subject and we’ll be talking more about it in the next post, and this time we’ll be improving performance as well using “WITH” with “CONNECT BY”.

1 Comment »

Guest Post – Automatic data type conversion for different technologies in ODI

Posted in Guest Post, ODI, Tips and Tricks with tags , , on January 14, 2019 by Rodrigo Radtke de Souza

/* This is a guest post written by Eduardo Zancanella, one of our friends at DEVEPM. Enjoy and thanks Eduardo for the content!*/

Hello folks,

Hope you are having a great day today.

We would like to share a quick tricky when it comes to transport data between different technologies.

Let’s suppose we were requested to create an ETL process to migrate data from PostgreSQL to Oracle.

Our source has some columns set as TEXT, which does not exist in Oracle. To be able to perform the ETL we would need to translate it to CLOB or VARCHAR2 for instance. But how would ODI knows it to create the temporary tables accurately?

Easy peasy, go to your Physical Architecture, select PostgreSQL and check if the data type is in there. If not, create it following the steps below (if it already exists, go straight to the step 3!)

1) Right click on Data Types, New Datatype

figure01

2) Fill it up the information as below, special attention to what is highlighted:

figure02

3) Click on Converted To and set to which datatype you want it to match in your target, in this case we have chose VARCHAR2.

figure03

After getting all this setup done, let’s run through our example really quick.

Firstly, let’s reverse our source and check if the TEXT fields are in there, keep in mind that we are trying to simplify, so don’t expect to see a full picture of the tool.

figure04

Secondly, let’s create our target table, be aware that here you must use the datatype you chose on the step 3 above for any fields that will be converted. After reversing it you will see as below:

figure05

At this time, our mapping is created, LKM SQL to Oracle and IKM Oracle Incremental Update have been chosen. A quick check on how the CUSTOM_2716 field looks like:

figure06

The hint SOURCE is an extra tip, the transformation for this case has to happen before the data is inserted into the temporary tables, always keep that in mind.

Time to run!

As a first step, ODI will create the C$ and here is where the magic happens:

figure07

C$ was successfully created!

After, ODI will follow its flow and everything should be fine.

That is how we can automatically convert different datatypes among different technologies.

Thank you everyone.

Cheers!

1 Comment »

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 »