Tagged ‘ODI 11G‘

Extreme re-usability in ODI 11g (I can’t believe I am giving away this trick).

Another Christmas and a second baby under my belt it’s time to get back to blogging.

There were recently some good posts by David Allan and Gurcan Orhan on the power of ODI functions. David mentions correctly that ODI functions are the most underrated feature in ODI. Whenever you think of re-usability in ODI think of user functions. Functions can be used anywhere in ODI where you need to write something once and apply it many times. This is not limited to using functions inside interfaces to load columns or the use of wrapping and parameterizing common functionality. You could just write a snippet of SQL, e.g. a commonly used filter in a WHERE clause as a function and reuse it many times. The nice thing about functions is that you can use substitution method API calls in a function, use Java or the ODI tools.

Use case for re-use of user functions

One of the things that have puzzled me about ODI and Knowledge Modules is that some of the steps in the KMs are repeated and re-used over and over again without some central placeholder. Why not write the step once and re-use it many times across the Knowledge Modules? This is exactly what I will show you. We will take table stats gathering as an example. One of the steps in Knowledge Modules is to gather table stats once the target table has been loaded. Why not create a user function that gathers table stats on a target table and takes the estimate percent size as a parameter?

In a first step let’s create the function. As a recommendation I would suggest to prefix your functions with a convention. In a first implementation I had GATHER_TABLE_STATS for the syntax, which wreaked havoc with any KMs that are using the Oracle GATHER_TABLE_STATS procedure in package dbms_stats.

And the implementation


dbms_stats.gather_table_stats ( ownname => '<%=odiRef.getInfo( "DEST_SCHEMA" )%>', tabname => '<%=odiRef.getTargetTable("RES_NAME")%>', degree => DBMS_STATS.AUTO_DEGREE, estimate_percent => $(sample_size), cascade => TRUE  ) ;


Next we will use the function as a step in a (global) Knowledge Module and pass in 10 as a parameter for the estimate_percent part of the stats gathering piece. In this particular case I have modified the IKM SQL Control Append.

Then we create and execute an interface that uses this IKM

As you can see the function was substituted at runtime and stats were gathered on the target table.

This is just one example where you can benefit from user function re-usability. Be creative and think out of the box and you will see user function written over everything.

How you can launch an ODI scenario through a web service call?

ODI and Jetty

The ODI 11g standalone agent now ships with its own lightweight application server (Jetty). The main reason this was included is to make it easier to execute scenarios via web service calls. In the past this was quite painful as you needed a separate application server (OC4J). The other limitation was that it was difficult (read work around) to implement asynchronous web service calls. Luckily, this has all changed with ODI 11g and Jetty. However, I don’t believe that you can run the ODI console in Jetty, which is unfortunate.

Asynchronous web service calls

The WSDL can be found at http://<standaloneagentname>:<port>/oraclediagent/OdiInvoke?wsdl


We can call the web service methods from any SOAP Client, e.g. the OdiInvokeWebService tool built into ODI.

We need to provide our login, the work repository for execution, the name of the scenario, its version, and the context. You can also specify if you want the scenario to be executed synchronously or asynchronously. If you set Synchronous to false then control will be handed back to the client immediately. If you set it to true it will execute and then return control. In asynchronous scenarios you can use the session ID from the response file and the getSessionStatus method to return the status of your scenario execution.

The other thing you have to be aware of is that when you launch a scenario through a web service call it will fork out a new agent based on your odiparams.bat. You need to plan for the extra memory used up.

You can now easily orchestrate your process flows in BEPL or launch them from OBIEE.

Need ODI Training? Learn ODI from the experts!

I am proud to announce that Sonra are now offering ODI classes.

Value-priced. Customised. Onsite-Offsite. Online.

Five day standard training course.
Starts 24 February 2014.
Book before 15 January 2014. Pay $949.

More information on our ODI courses.

ODI Snippets: What is the Optimization Context in ODI?

There are a lot of confusing messages out there on what the Optimization Context in ODI is used for.

This is the setting I am talking about

Is it a runtime setting??

First of all, this is not a runtime or execution context setting. It is only used at design time in ODI Studio.
If you execute an interface in your Test context with the Optimization Context set to Development it will still execute in Test.

It is a design Time Setting!

So what is the Optimization Context used for? ODI uses the Optimization Context to validate your Interface at design time. This means it will validate your filters,functions etc. against the environment set in your Optimization Context.

You can easily try this out.

  • Set your Optimization Context to Development
  • Go to your source table, right click, and select Data…
  • This will display the Data in the source table in your Development environment
  • Now change the Optimization Context to Test.
  • Go back to your source table and select Data… again
  • This will now display the data in your test environment

What is this useful for?

Personally I can’t really think of anything, but let me know if you are using this feature.

How to query a whole schema or even database?! This tip will save you hours and hours of boring work.

I really like this tip especially its simplicity. It has saved me hours and hours of mind numbing work in the past.

The use case

Imagine the following situation. You are tasked to complete a source to target map. With the help of various source system SMEs, legacy data models, and the data dictionary of the source database you have been able to complete 99% of the mappings. However, there is that one field that is nowhere to be found in the source system (there’s always one). The business users have given you some sample values for this field. Wouldn’t it be nice to run a query such as SELECT column_name FROM schema/database WHERE value = ‘Sample Value’.

I will show you how you can achieve something similar with ODI.

The trick is to dump the content of your schema or database into a folder and then use grep your Windows search or whatever to search for your sample value.

In ODI this is extremely easy. Using the Command on Source/Target functionality it can be completed with two lines of code.

An example

Our objective is to find the table where ODI stores Option values for interfaces.

In a first step we create the procedure that dumps the content of the ODI schema to a folder

Command on Source

We query the data dictionary for those tables that are owned by the ODI work repository user. If you want to dump out the whole database just get rid of the WHERE clause.

SELECT table_name,owner from all_tables WHERE owner = ‘<name of your work schema>’

Command on Target

We use the tool OdiSqlUnload to unload those tables from the Command on Source recordset. For each table we create a separate text file.

OdiSqlUnload “-FILE=C:\unload\#table_name.txt” “-DRIVER=oracle.jdbc.driver.OracleDriver” “-URL=<your connection>” “-USER=#owner” “-PASS=<your password>” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=;” “-ROW_SEP=\r\n” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1″ “-XML_CHARSET_ENCODING=ISO-8859-1″

SELECT * FROM #table_name


In a second step we enter a dummy value for one of our interfaces. This will be the value that we will search for in the dump.

Our dummy value is XXXXXX.

Next we run the procedure. This will dump the table contents into files.

Finally, we use Windows Search, grep or similar to find the file where our sample value is stored.

Windows search comes back with one result. The last column of table SNP_UE_USED, which is I_TXT_VALUE.

I am sure you can think of many more use cases for this. Of course, you can make all of this more sophisticated and wrap it up in a package with options to run against different databases such as MS SQL Server etc.

For very big databases you will need to watch performance. You may want to only extract a subset of tables and search for your sample value there before moving on to the next set and so on.

Best practice of organizing interfaces and data stores into projects and models in ODI

Have you ever wondered what the best way is to structure your objects in ODI into projects? Look no further. I will outline what works well for an Enterprise Data Warehouse.
Let’s assume you follow the Oracle reference architecture for data warehousing and you have a couple of source systems, a staging area, a foundation layer (core data warehouse), and a bunch of data marts.
For each of these layers we will create a model folder and a project (minus the source layer for projects.
We should end up with a structure similar to below.


You can further subdivide or group the Source System model by type of technology, e.g. File, MS SQL, Oracle, XML etc.

Similarly, you should subdivide your stage model into various sub-models based on source.

The same applies to the Stage project


Now we come to the interesting part. The EDW should be structured based on the subject areas in your Enterprise Data Model. If your organization is not mature enough and does not have one then it should get one asap (easier said than done). In the meantime, structure ODI based on the analysis performed for the data warehouse.

Each subject area in your EDM gets its own folder in the EDW project. Each entity in the EDM has a home in one of the subject areas. The interface that populates the corresponding ODI data store will go to the corresponding ODI project folder.

Similarly you structure your EDW model into sub-models corresponding to the EDM subject areas.

Data Marts

You could take the same approach for the data marts as for the EDW. However, as your dimensions are de-normalized and may span multiple EDM entities rooted in different subject areas my preference is to split out the Data Marts project into a Dimension, Facts, and Aggregate Facts folder.

One note at the end: You find statements out there claiming that you should never exceed 300 objects in a project. I am not sure where these come from. I have never seen any issues exceeding this number. If it was the case then this would be a severe limitation in ODI.

ODI snippets: Generating ANSI compliant Joins in Oracle

It’s great to see that the Oracle technology in ODI 11g now supports ANSI compliant JOINS.

However, when you create an interface that joins one or more tables this is not the default option.

To enable this you need to click on the Join icon between your tables and select checkbox ‘Use Ordered Join Syntax’.

ODI 11g in a Services Oriented Architecture. Part IV. Consuming a SOAP OBIEE web service using ODI 11g

This post completes our series on using ODI in a web services environment.

In part one of this series (Oracle Data Integrator in a Services Oriented Architecture) I had laid the groundwork and explained the basics of a SOA environment and how ODI fits into it. In part two we then looked at creating and deploying data services with ODI 11g in Weblogic. In part three we looked at how to consume data services using ODI 11g.

In this last example we will examine how we can invoke Oracle Business Intelligence Session-Based Web Services to query the Oracle Business Intelligence Presentation Services. The Oracle Business Intelligence Presentation Services allow us (amongst various other things) to retrieve catalog information such as user names, groups, object privileges etc.

The services and methods that are available can be retrieved from a WSDL at the following location


You can find a full list of all the methods and a description in the documentation

For the purpose of this tutorial we will retrieve the members of a given OBIEE catalog group.

Oracle Business Intelligence Session-Based Web Services require a valid Oracle Business Intelligence session ID to be passed as a parameter.

In a first step we will generate this session ID.

In ODI we create a new package, name it OBIEE and add an OdiInvokeWebService tool.

Next we highlight the OdiInvokeWebService tool and click the Advanced… button. This will launch the ODI web service client. Next we supply the WSDL URL, e.g. http://localhost:9704/analytics/saw.dll/wsdl/v6
and click the Connect to WSDL button. This will display all of the available web services and methods.

From the web services dropdown we select the SAWSessionServiceSoap service and highlight the logon method. In the Editor pane we submit an OBIEE username and password with SOAP Access privileges and click the Invoke Web Service button. This will return a new OBI session ID. Close the ODI web service client by clicking OK.

As parameters for the OdiInvokeWebService tool we then submit the following additional parameters.

Storage Mode for Response File: NEW_FILE
File Encoding for Response File: UTF8
XML Encoding for Response File: UTF-8
Response File: C:/obiee_session_id.xml
Response File Format: XML

Now we are ready to execute the Package using our local agent.

Once the execution of the package has finished successfully we should find the obiee_session_id.xml file on our local C:\ drive

We are now ready to extract the session ID from the XML using ODI. As in the previous example we will have to create a new physical and logical data server and a new model for this XML file.

In ODI Studio Topology insert a new XML data server in the Physical Architecture.

In the JDBC Driver field submit com.sunopsis.jdbc.driver.xml.SnpsXmlDriver and for JDBC Url submit jdbc:snps:xml?f=C:/obiee_session_id.xml

Next click the Test Connection button and test the connection with the Local Agent.

This will throw an error. We have come across ODI bug 9159263, which is documented on the My Oracle Support site in note 971754.1. In summary the ODI XML driver can not parse XML files that contain any xsi: references. The note suggests using a workaround and deleting any references to xsi: in our XML.

Let’s edit our XML and remove any instances of xsi. Your XML should look similar to the one in the figure below. Later when we extract information from the XML file in our package we will write some Jython code to replace any xsi: instances in the XML file at runtime.

Now let’s retest the connection to the XML.

Next we create the physical schema

Then we create the logical data server XML_OBIEE_SRC

Next we create a model for the XML_OBIEE_SRC logical schema.

Finally we reverse engineer the model

We are now ready to assign the session ID to an ODI variable. Create a new variable V_OBIEE_SESSION_ID. As the schema for the variable choose XML_OBIEE_SRC and for the query supply ‘select sessionid_data from OBIEE.SESSIONID’

As mentioned earlier, before we can add the ODI variable to our package we will first need to create a step that removes any xsi: instances from the obiee_session_id.xml file. We will use Jython to accomplish this.

We create an ODI procedure XSI_REPLACE and add one step “replace xsi” to it. As Technology, we select Jython.

s = open("").read()
s = s.replace('xsi:', '')
f = open("", 'w')

Next we create an ODI option FILE_PATH. The procedure we have just created will take the path to an XML file passed in via the option and replace any occurrence of xsi:

Next we will add our new procedure and the ODI variable we created earlier on to our OBIEE package. We also enter the path to the c:\obiee_session_id.xml file into the option field as per figure below.

In a final step we will add another OdiInvokeWebService tool, which we will use to retrieve the members of a given group.

As per figure below add the following piece of code to the XML Request parameter. The name BI_RPT is the name of the catalog group in OBIEE that we will retrieve the members from. You need to replace this with a catalog group that exists in your own OBIEE environment. As account type we pass in 1 for accounts of type group. As sessionID we pass in the ODI variable #V_OBIEE_SESSION_ID that we populated in an earlier step in our package.


The values for the other parameters are

WSDL URL: http://localhost:9704/analytics/saw.dll/wsdl/v6
Port Type: SecurityServiceSoap
Operation: getMembers
Storage Mode for Response File: NEW_FILE
File Encoding for Response File: UTF8
XML Encoding for Response File: UTF-8
Response File: C:/OBIEE_group_members.xml
Response File Format: XML

Leave all the other parameter fields blank.

We are now ready to execute our package and retrieve the members of the BI_RPT catalog group.

We execute the OBIEE package with our local agent

As you can see from the figure below, our package executed successfully.

Let’s have a look at the output of the XML. As you can see from the figure below there is one user in our given catalog group.

Congratulations. We have completed the walkthrough. You now know how you can use ODI in a SOA environment and should be able to query SOAP and RESTful web services using ODI.

Copying and moving objects between projects in ODI

One of the really annoying things about ODI is that it is not easily possible to move or copy objects between projects. Below is a step by step guide you can follow to copy or move objects between projects. We will take the object of type package as an example as this is the most complicated and covers off any of the others as well. The method shown uses Duplication mode import. While this method is not recommended by Oracle support. I never had any issues with it.

So let’s get started:

1. Migrate any of the variables used inside the package using Export and Import (Duplication Mode). A variable with the same name may already exist in the target project. This means you have to rename the variable and fix any references to it in interfaces, procedures, packages, and the Topology (the details on this follow below).

2. Migrate any of the project functions and project Knowledge Modules used using Export and Import (Duplication Mode)

For the KMs consider using global KMs instead

3. Export the root folder (with child components) of where the package is located. The reason for this is how Duplication mode import in ODI works. Basically it does not recreate internal IDs for objects that are part of the export XML. This strategy is used to minimise the number of missing references and as a result the amount of work we need to do.

4. Next we import the exported folder into our new project.

Note the import needs to be made in Duplication Mode.

5. Next we move (drag and drop) the object(s) we need from our imported folder to our target folder in the new project.

6. Next we can delete the imported folder

7. Any missing references to Knowledge Modules need to be recreated for all of the interfaces used in the package

Note: This also applies to all of the options for each KM.

If variables are referenced using the old project code in any of the KMs or you had to rename a variable during import then these need to be updated as well

8. Update references to any variables or project functions used in procedures

If variables are referenced using the old project code in any of the Procedures or you had to rename a variable during import then these need to be updated as well

9. Update references to any variables or project functions used in other variables

If variables are referenced using the old project code in any of the Variables or you had to rename a variable during import then these need to be updated as well

10. Update references to any variables used in the Topology, e.g. as part of physical data servers

11. Update references to any variables or project functions used in other project functions

12. Next we need to replace any missing references inside the package

These are typically only references to variables unless your package also references procedures etc. from outside the root folder that we exported/imported

We need to recreate those links by deleting the variables in the package and adding them again. Make sure that you select the correct Variable Type when recreating the variable in the diagram (Refresh Variable, Declare Variable etc.).

Open the legacy package for a side by side comparison to minimise mistakes.

13. If you reference scenarios in your package and you pass variables to these scenarios you will also need to repoint the variables in the Additional Variables tab for this scenario

14. Replace and fix references to new markers

15. Delete any scenarios for the legacy object(s) and then for the migrated object(s) if applicable.

16. Generate scenario for the migrated object(s)

17. Export legacy objects for backup (optional, if you are the cautious type)

18. And finally: Delete any of the legacy objects you migrated.

Need ODI Training? Learn ODI from the experts!

I am proud to announce that Sonra are now offering ODI classes.

Value-priced. Customised. Onsite-Offsite. Online.

Five day standard training course.
Starts 24 February 2014.
Book before 15 January 2014. Pay $949.

More information on our ODI courses.

Get a list of all of your ODI objects including the path in your project or model

Below is a query that will retrieve all of the ODI objects in your project and model. It will also spit out the path where they sit in your hierarchy. Very useful if you need to know which objects have changed over the last week or if you want to track down one of the thousands of objects in your ODI projects and models.

WITH obj as(
SELECT i_package i_instance, i_folder, NULL i_project, pack_name obj_name,3200 obj_type, 'Package' obj_type_name, last_date, last_user FROM SNP_PACKAGE
SELECT i_pop i_instance, i_folder, NULL i_project, pop_name obj_name,3100 obj_type, 'Interface' obj_type_name, last_date, last_user FROM SNP_POP
SELECT i_trt i_instance, i_folder, i_project, trt_name obj_name,3600 obj_type, CASE trt_type WHEN 'U' THEN 'Procedure' ELSE 'Knowledge Module' END obj_type_name, last_date, last_user FROM SNP_TRT
SELECT i_var i_instance,  NULL i_folder, i_project, var_name obj_name, 3500 obj_type, 'Variable' obj_type_name, last_date, last_user FROM SNP_VAR t
SELECT i_table i_instance, i_sub_model i_folder, i_mod i_project, table_name obj_name, 2400 obj_type, 'Table' obj_type_name, last_date, last_user from snp_table t
,fd (i_folder, i_project, folder_name, folder_path, lv) AS(
SELECT i_folder, i_project, folder_name, folder_name folder_path, 1 lv
  FROM snp_folder
 WHERE par_i_folder IS NULL
SELECT tf.i_folder, tf.i_project, tf.folder_name, fd.folder_path||'\'||tf.folder_name,
  FROM snp_folder tf JOIN fd
    ON fd.i_folder = tf.par_i_folder
,mpl as (
SELECT sm.i_smod i_mc, 'sm' typemc, COALESCE(sm.i_smod_parent,sm.i_mod) i_mp, NVL2(sm.i_smod_parent,'sm','m') typemp, sm.smod_name name --, m.i_mod_folder
  FROM snp_sub_model sm
SELECT i_mod, 'm' typ, i_mod_folder, 'mf', mod_name
  FROM snp_model m
SELECT i_mod_folder, 'mf', par_i_mod_folder, 'mf', mod_folder_name FROM snp_mod_folder
,mp (i_mc, typemc, i_mp, typemp, model_tech, model_path, lv) AS(
SELECT i_mc, typemc, i_mp, typemp, name tname, name model_path, 1 lv
  FROM mpl
SELECT mpl.i_mc, mpl.typemc, mpl.i_mp, mpl.typemp, mp.model_tech, mp.model_path||'\'|| model_path, lv
  FROM mpl JOIN mp
    ON mpl.i_mp = mp.i_mc AND mpl.typemp=mp.typemc
SELECT obj.i_instance, OBJ_NAME, CASE WHEN COALESCE(project_name,mp.model_tech) IS NULL THEN 'Global ' || OBJ_TYPE_NAME ELSE OBJ_TYPE_NAME END obj_type_name
      ,COALESCE(project_name,mp.model_tech) project_model
      ,COALESCE(fd.folder_path,mp.model_path) path  
  FROM obj
  JOIN fd
    ON fd.i_folder = obj.i_folder AND obj_type_name!='Table'
  JOIN mp
    ON mp.i_mc = obj.i_folder AND obj_type_name='Table'
  JOIN snp_project p
    ON p.i_project = COALESCE(obj.i_project, fd.i_project)
  JOIN snp_model m
    ON m.i_mod = obj.i_project

ODI 11g standalone agent as a windows service

In the documentation for ODI 11g you can read that you need Oracle Process Manager and Notification Server (OPMN) to create your standalone agent as a windows service.

OPMN is not available as a standalone install but is installed as part of Web Tier, Oracle Portal, Forms, Reports, and Discoverer Installation, Oracle Identity Management Installation.

I know you are gonna ask me: So what about ODI 11g? Well, after some digging and an SR I found out that even though the ODI 11g documentation mentions OPMN it does not ship with ODI.

As part of the SR, Oracle support has submitted an enhancement request on my behalf. So hopefully, there will be some sort of a solution to this problem.

So what are your options in the meantime.

If you are lucky and have one of the above applications licensed you can leverage OPMN off them.

If you are not so lucky you can still use the old wrapper method to create your agent as a windows service. However, the license terms for the Java Service Wrapper have changed. It used to be open source software. Not so any more. As per Oracle support that is the reason why this does not ship with ODI anymore. You can download a free trial of the Tanuki Java Service Wrapper from their website.

Let’s hope that Oracle sort out this issue soon.

If you want to master scripting in ODI get the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns


The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)