Archive for February, 2014

Execution Variables trick for old versions of ODI

Posted in ODI, ODI Architecture with tags , , on February 28, 2014 by Rodrigo Radtke de Souza

Hi all, today I’m going to show a very simple trick that is very useful for everyone that uses ODI in version 11.1.1.5 or below. How many times you got a code to debug from Operator and you needed to keep replacing all those ODI variables for its correct values in order to troubleshoot something? If you work with ODI development probably you do it every day. Depending on the case, it can be very time consuming because you need to get all input variables, execute all SQLs from refresh variables and so on. Luckily for those on ODI 11.1.1.6 and beyond, Oracle have added the variables values to the Operator steps, so no need to go hunting variables anymore, but what about the older versions?

There are a lot of alternatives around there and I have used one in particular for a long time. It is a simple Jython procedure that has an option where I add all ODI variables that I want to show and a step with a “raise” Jython command with ignore errors selected:

blog1

blog2

It works pretty fine, when you execute it inside a package all variables values that you have added in the option will show in Operator, but it is always a pain in large projects because you may have 20 variables or so and you need to copy and paste its name one by one to the procedure’s option, but it was way better than having nothing at all.

Tired of doing this copy and paste work, a coworker (thanks Eduardo for the great idea and the SQL!) came with the follow idea: Why don’t we query ODI repository metadata to get all variables that were used in the ODI scenario and “raise” its value? Simple but great idea because it is just a matter to place the procedure anywhere in the ODI package and it will show all the variable’s values at that point.

It’s very easy to accomplish this. First you need to have a topology set to the ODI work repository, and then you need to create a procedure with two steps. First step will have the ODI work repository logical schema set in the “Command on Source” tab:

blog3

And the below Java BeanShell code written in the “Command on Target” tab:

blog4

SELECT DISTINCT VAR FROM (SELECT LEVEL, VAR_NAME, SYS_CONNECT_BY_PATH(VAR_NAME || ‘: #’ || VAR_NAME, ‘ \n ‘) AS VAR FROM (SELECT  ROWNUM AS CURR , ROWNUM -1 AS PREV , SSS.VAR_NAME , SST.DEF_TXT, SSTL.TASK_END , SSTL.TASK_BEG FROM ” + odiRef.getSchemaName( “ODI_WORK_REPOSITORY”,”D” )+”.SNP_SESS_TASK SST, ” + odiRef.getSchemaName( “ODI_WORK_REPOSITORY”,”D” ) + “.SNP_SESS_TASK_LOG SSTL , ” + odiRef.getSchemaName( “ODI_WORK_REPOSITORY”,”D” ) + “.SNP_SESS_STEP SSS WHERE 1 = 1 AND SST.TASK_TYPE = ‘V’ AND SSS.STEP_TYPE IN (‘V’, ‘VS’, ‘VE’, ‘VD’) AND SSTL.NNO = SSS.NNO AND SSTL.NB_RUN = SSS.NB_RUN  AND SST.SESS_NO = SSTL.SESS_NO  AND SST.SESS_NO = SSS.SESS_NO  AND SST.SCEN_TASK_NO = SSTL.SCEN_TASK_NO  AND SSS.SESS_NO = ” + odiRef.getSession(“SESS_NO”) + “) CONNECT BY PREV = PRIOR CURR  START WITH CURR = 1  ORDER BY LEVEL DESC ) WHERE ROWNUM = 1

In resume, the above query goes against the ODI repository and returns one line containing all ODI variables used in the ODI scenario until that point. This query can be accomplished in several different ways (for example using listAgg Oracle’s feature in a newer database) and you may tweak it to return more valuable information if needed. With this information added to “odivars” variable, it is just a matter to “raise” it in the second step:

blog5

Let’s see how it works. Imagine the below ODI scenario:

blog6

When we execute it and take a look in ODI Operator, this is what we get in the first procedure execution:

blog7

It shows the values “123” and “A” for the first two variables. When we look at the second procedure execution we get all the three variables:

blog8

As you can see, it works with all kind of variables (refresh, set, declare, global, project only, etc) and if you want even more freedom, you may add this code inside your Knowledge modules in the very first steps, so you will know all the variables values that was used in your integration steps without the need to drag and drop a procedure to the ODI package.

That’s it folks. I hope it is useful for you. Thanks again Eduardo for the idea! See ya!

Advertisement

4 Comments »

[1Z0-482] – Oracle Data Integrator 11g Certified Implementation Essentials

Posted in ODI, ODI Certification with tags , , , , on February 18, 2014 by Rodrigo Radtke de Souza

Hi all,

Last week I got the response from Oracle about my ODI certification exam that I did back in December when it was still in beta. I got approved with a 91% score! It was a very good exam and I’ll try to share some thoughts about it here in the blog.

As I said before, I took a beta exam (named 1Z1-482, 1Z1 indicates beta, 1Z0 production exam) which is a little different from the regular Oracle exams. Here are some of the differences:

  • Beta exams are pre-production exams used by Oracle to evaluate new exam questions with the participation of a live audience;
  • Data gathered during beta testing are used to select the best questions for the production exam;
  • Beta exams are offered at a deeply discounted price (I took for $50, now it is $245);
  • Candidates usually have 2.5 to 3.5 hours to complete the exam, instead of 2 hours;
  • Need to answer from 120 – 220 questions, instead of 71 questions;
  • Passing a beta exam is the same as passing a production exam;

As you can see, as a Beta tester you have some extra work to do since you have to answer way more questions in a longer duration exam but the good thing is that the exam is way cheaper. So, after you have done your exam, Oracle analyzes all beta exams and from those 120-220 questions, only 71 gets to the official exam and those are the ones that you are scored against. So my 91% was related to the final 71 questions and not against all 124 questions that I answered.

But you should not worry about this beta thing anymore, since the certification is already in production, so let’s talk about what is in the exam. I found it very good, with several questions about every day ODI usage with a VERY BIG exception: EDQ (Enterprise Data Quality) questions. There were a lot of questions about it and they were really a pain…. But prior to talk about them, let’s go by each exam topic, so I can explain each one better. You can see more information about the exam and all the related topics in Oracle’s page:

ODI Certification information

Introduction to Oracle Data Integrator (ODI), ODI Architecture Overview, ODI Models and Datastores, ODI Project Development

I’ll talk about all those topics together because they are the day to day ODI operations, so if you work with ODI, you will be fine on those topics. I really liked the certification exam because a lot of questions were focused in these topics and for me those are the ones that make more sense to be in the exam, since these are the topics that your actually work with. I’ll not go into details here because I am assuming that those who want to become certificated have already a hands on experience in the tool and since there are a small amount of questions about every topic you can expect everything from simple questions about ODI variables and interfaces to some more complex questions like which/where temporary tables are created in a specific situation. But again, if you work with ODI, you will be more than fine here.

Managing ODI Execution

Although it may be considered a daily operation, I wanted to talk about this topic separately because this was one topic that kind of surprised me due to the big amount of questions about Load Plans. If you are an older ODI developer like me, you will know that Load Plan is a feature released in ODI 11g version, so it may be considered pretty new. I know a lot of places and projects that they don’t even know what a Load Plan is because they can get around executions only by using ODI scenarios. So here is my tip: you need to know everything about Load Plans. How to create parallel executions, how to restart it using all restart options, how to edit load plans and do on. Even if you work with Load Plans already, study them. There are always those options that you generally don’t use, but you need to know for the exam.

Integrating Oracle GoldenGate with ODI

OGG is a great product to integrate with ODI for real timing integrations and Oracle wants to be sure that their specialists knows how to use those two together. There were some questions about CDC in general and some more specifics regarding CDC using OGG. CDC is a concept and ODI has some possibilities to implement it besides OGG, so you need to be aware of all them. You need to know what is a journal and subscribers, how to properly setup up journalizing, which types exists, what are the structures that ODI creates both in source and target databases, how do you Extend a Window, Lock Subscribers, which ODI tools are used to work with CDC and so on. A good material about CDC can be found in Oracle documentation:

ODI CDC

For CDC with OGG you need to worry more about the details. You need to know how the Knowledge module works, which are its parameters and will even need to know about the basic commands in OGG to start the Extract and Replicat processes. Oracle has a great documentation about ODI + OGG integration. If you have a chance to actually build a small environment with OGG and ODI I strongly recommend it, so you can see with your own eyes all the details, architecture and so on.

ODI OGG

Doing more with ODI

It may be considered the “advanced” topics in ODI certification. These topics are the ones that most of the ODI developers do not generally work in every project that they are engaged (big data is a good example) since they are very specific. I’ll go by each subtopic to explain it better:

SDK

This is a complicated topic because there is a lot of content that you may study and learn about it but just a few questions are in the exam. Below is the link to ODI SDK java API with all classes, functions and so on. Of course that you will not need to know all of it by heart, but you need to know the basics. My recommendation is to read the main classes of API and create some examples on your own just to get the handle of it. ODI Experts site has a lot of SDK examples, so grab some of them and try them out.

SDK API

ODI Web-Based Components

There were some questions about ODI Console, but they were fairly simple if you already work with it. If you want to know more about it, you may read Oracle’s documentation:

ODI Console

The role of ODI in Big Data

This topic was the one that I actually had to study about it. Although I already read about big data stuff, I had never taken a look on how ODI worked with it. So here you need to know about what big data, Hadoop and Hive are and especially and most importantly which KMs and adapters ODI has to manipulate/load/extract big data. There is an OBIEE/ODI/HADOOP series in Rittman Mead blog that I strongly recommend:

ODI Big Data

There is some good information here as well:

ODI Big Data 2

The role of ODI in Business Intelligence integration

Have you already heard of Data lineage using ODI and OBIEE? If no, you should, because there are a few questions about it in the exam. Study its architecture, lineage options and how to use it. Oracle has a good documentation about it here:

ODI Data Lineage

Oracle Enterprise Data Quality (EDQ) Profiling, Oracle Enterprise Data Quality Batch Processing

Here is the big villain of ODI certification exam in my option: EDQ. A lot of questions about EDQ appeared in the beta exam and I really pray that Oracle have removed some of them when they created the production exam. Honestly, how many of you already worked with it? How many production systems exist that are currently using EDQ? Very, very few…. I personally don’t know anyone. EDQ is a data quality tool that identifies, categorize and even do some ETL to fix your data, but hey ODI may also do it using constraints. Of course EDQ is intended to be used by the business folks that are the “owners” of that data, so EDQ provides some nice dashboards and web accesses, but believe me, almost 100% of the time, business users are interested in their business not IT. IT folks will take care of any errors they find in their data and provide them what they want and if IT owns this responsibility, they will use ODI for it, not EDQ. But I understand Oracle’s movement: it has an entire set of great integration tools and EDQ is the tool responsible for data quality. Since nobody is actually using it, they added it to the exam to increase its visibility and try to get some movement around it.

Ok, let me stop whining about it and start to tell what I did to study this tool. First I read about it in Rittman Mead blog which contains a lot of good information:

Introduction to EDQ

ODI and EDQ integration

In EDQ’s case, reading will not be enough, at least in my case it wasn’t. I truly recommend you to download, install and play with it a little. You will need to know about EDQ architecture, its modules, how to do basic profiling with its pre-build processors, how EDQ and ODI talks to each other and so on. This is the hardest part of the exam for sure and there is no easy way to study it.

That’s it folks. I hope these tips can help you to achieve ODI certification. It is a good challenge but it is worth it!

12 Comments »