Subselect, derived tables, and subqueries in ODI 11G

I think one of the best new features in ODI 11G is the subselect/subquery feature. In ODI 10 this could only be achieved by a workaround as outlined in a previous post Using subqueries in Oracle Data Integrator.

What is the advantage of a subquery?

In previous versions of ODI we had to physically set down the data for each indiviudal query, e.g. if we wanted to rank a dataset and then select the top ten out of that dataset we had to create an interface for the ranking operation and an interface for the top ten operation. At each point we had to set down the data thereby increasing I/O and decreasing overall performance. In ODI 11 this has changed. While we still need to create two temp interfaces for the operation as a whole we can now tell ODI to treat the first temp interface as a derived table. ODI 11G will then use this to generate a subquery.

How does the subquery work in ODI 11?

The way this works is fairly simple. For each subquery/derived table in your query you create a temp interface. You embed your various subqueries by simply telling ODI that you want to use the temp interface as a subquery.

A step by step guide

We will look at an example from the SH schema. The task at hand is to load a table with the top ten customers based on sales amount.

To accomplish this task we need three temp interfaces.

The first temp interface (INT_SALES_CUST) will aggregate the sales amount from the sales table by cust_id

The second temp interface (INT_SALES_RANK) will take the resultset from the interface in the previous step and dense rank the customers’ sales data.

The third interface (INT_TOP_TEN) will then select the top ten customers, join to the customers table, and physically set down the data.

So far so good. These are the same steps we took in ODI 10.

In a next step we need to subquery enable the interfaces. We open interface INT_SALES_RANK and click on the INT_SALES_CUST data store. In the Source Properties section you will find a checkbox Use Temporary Interface as Derived Table. Select this checkbox.

We also need to perform the same step for our third interface INT_TOP_TEN.

Once this has been done we can execute interface INT_TOP_TEN in Simulation mode.

This will generate the required query with the embedded subqueries from the temp interfaces. What a great feature.

INSERT INTO sh.cust_top_ten
            (sales_rank,
             cust_first_name,
             cust_id,
             sales_amt,
             cust_last_name)
SELECT sales_rank,
       cust_first_name,
       cust_id,
       sales_amt,
       cust_last_name
FROM   (SELECT sales_rank.sales_rank     sales_rank,
               customers.cust_first_name cust_first_name,
               customers.cust_id         cust_id,
               sales_rank.sales_amt      sales_amt,
               customers.cust_last_name  cust_last_name
        FROM   (SELECT sales_cust.cust_id
                       cust_id,
                       sales_cust.sales_amt
                       sales_amt
                       ,
                       Dense_rank() over (ORDER BY
                       sales_cust.sales_amt DESC) sales_rank
                FROM   (SELECT sales.cust_id           cust_id,
                               SUM (sales.amount_sold) sales_amt
                        FROM   sh.sales sales
                        WHERE  ( 1 = 1 )
                        GROUP  BY sales.cust_id) sales_cust
                WHERE  ( 1 = 1 )) sales_rank,
               sh.customers customers
        WHERE  ( 1 = 1 )
               AND ( sales_rank.cust_id = customers.cust_id )
               AND ( sales_rank.sales_rank <= 10 )) odi_get_from

Oracle Data Integrator: ODIInvokeWebService and Java 6

ODI has a built in SOAP client, the ODIInvokeWebService tool. However, there is one caveat when you try using this with Java 6/JDK 6. It simply doesn’t work and you will get the following error:

org.apache.xerces.dom.DocumentImpl.getXmlStandalone()Z

This is documented in note 1085594.1 and the suggested workaround is to fall back to an earlier version of Java. Another option of course is to install another agent that uses Java 5 alongside your Java 6 agent.

The first step we need to take is to install JDK 5. You can download JDK 5 from the Oracle website.

If repositories are running on Oracle we also need to download the compatible Oracle JDBC 5 driver. If you are running your repositories on another RDBMS get the equivalent JDBC driver.

Next, inside the oracledi folder we create a new folder and name it bin_jdk5.

We then copy the content of the bin folder into bin_jdk5.

The next step will be to create a new environment variable and name it ODI_JAVA_HOME2. The variable needs to be pointed to the JDK 5.

As a next step we need to edit the odiparams.bat file in the bin_jdk5 folder. Open the file in Notepad and replace ODI_JAVA_HOME with ODI_JAVA_HOME2

In the same file we also need to replace any occurrence of the word drivers with drivers2

Next we create a new folder drivers2 inside the oracledi folder and copy and paste the downloaded Oracle JDBC 5 driver into it. Alos copy any other drivers from the drivers folder into the drivers folder that you may want to use with this agent

Finally we create a new agent that uses the JDK 5 as a Windows service.

First edit file agentservice.bat and replace bin\odiparams.bat with bin_jdk5\odiparams.bat as per figure below

Then we create a copy of file snpsagent.conf in folder oracldi\tools\wrapper\conf. Replace any occurrence of drivers with drivers2.

We also replace wrapper.working.dir=../../../bin/ with wrapper.working.dir=../../../bin_jdk5/

Next we create the physical and logical agent in Topology Manager.

Finally we install the agent as a listener or scheduler agent.

In one of the next posts I will show how we can make good use of ODIInvokeWebService to query the OBIEE web services API.

ODI and analytic functions (again???)

My friend and colleague Maciej Kocon has come up with the following trick. Rather than use a user defined function to implement an analytic function such as MAX() OVER or SUM() OVER as suggested by note 807527.1 there is another way to work around the getGrpBy bug.

We will take advantage of the ODI multi pass parser and hide the analytic function from getGrpBy in our mappings:

<?out.print(”SUM”);?>(SALES.AMOUNT_SOLD) OVER (PARTITION BY PRODUCTS.PROD_CATEGORY)

The above SUM gets only generated once getGrpBy has been parsed and as a result no GROUP BY clause is generated, which is the correct behaviour.

A brilliant yet simple trick.

Cheers Maciej.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

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

Jython Essentials (O’Reilly Scripting)

ODI and Integrated Security/Authentication with Microsoft SQL Server

Setting up integrated security with the SQL Server JDBC driver can be tricky. You often end up with the message “This driver is not configured for integrated authentication”. Below are the steps that you need to perform to get this to work.

Note: This will only work where the ODI agent has been installed on a Windows OS.

1. Download the Microsoft SQL Server JDBC Driver 2.0 from the Microsoft website. This is compatible with versions 2000, 2005, and 2008 of MS SQL. You will need to be up and running on JDK 5.0 or later with your ODI installation. If you haven’t done so already now is the time to install JDK 5.0 or even better JDK 6.0.
2. Unzip the content of the download.
3. The driver comes with a separate dll (sqljdbc_auth.dll) that will allow you to connect to SQL Server via your Windows login. Copy the sqljdbc_auth.dll to the \oracledi\drivers folder.
4. Copy the sqljdbc4.jar file to the \oracledi\drivers folder
5. Open odiparams.bat and add the following entry: set ODI_ADDITIONAL_JAVA_OPTIONS=”-Djava.library.path=\oracledi\drivers”, e.g. set ODI_ADDITIONAL_JAVA_OPTIONS=”-Djava.library.path=c:\oracle\oracledi\drivers”

6. If you have set up the ODI agent as a Windows service you need to re-install the agent. Stop the agent and uninstall the agent with the agentservice –r command. Next you need to edit the “snpsagent.conf” file in the \tools\wrapper\conf directory.

7. Re-install the ODI agent with the “agentservice -i” command.
8. Open Topology Manager module and create Physical Server for MS SQL technology. Leave username and password blank

Go to JDBC tab and enter

jdbc:sqlserver://<name_of_server>:<port>;databaseName=<db_name>;integratedSecurity=true;

9. One last thing. Of course, your ODI agent must not run as the Local System account but use a network account that has the appropriate MS SQL privileges to log into the SQL Server.

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

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

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

Jython Essentials (O’Reilly Scripting)

What has ODI encryption got to do with clearing the OBIEE cache???

As part of a data mart refresh I am using nqcmd to clear the cache for the corresponding subject area in OBIEE. Thanks to John Minkjan for his post on properly documenting how the OBIEE cache can be cleared.

Basically I am executing nqcmd -d AnalyticsWeb -u Hans -p Zipfel -s d:\obiee\scripts\Purge_XXX_Cache.txt in an ODI procedure with technology set to Operating System.

I have currently installed an agent on the box for the BI server to launch nqcmd. However, I think that alternatively you can perform a custom install of the BI ODBC driver and the System Management component on the server where your ODI agent is running.

The major problem I have with the above solution is that the password for the Administrator password is sent across as clear text. Any ODI Operator module user would be able to view this. A gaping security hole. My first instinct when looking for a solution was to look for an encryption mechanism for this in OBIEE itself. Unfortunately no such thing is provided (as far as I know).

ODI to the rescue

I was rather frustated and ready to give up when I remembered that ODI has an encryption mechanism of its own.

Below are the steps I took to encrypt the password.

Right click on the procedure and select Encrypt. In the popup select Get a new encryption key

Save the key in a secure location as you will need it should you ever wish to decrypt the procedure (yes it can be easily lost…).

When you now execute the procedure only gobbledygook (what a great word) will show up in the Operator module.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

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

Jython Essentials (O’Reilly Scripting)

Nesting ODI substitution method calls. Part 1.

I am not getting around doing too much blogging on ODI these days. I have recently started a new project on OBIEE and ODI and it must be two years or so since I have last used OBIEE. I can tell you that there is a lot of catching up to do. Anyway, today’s post is about why I love ODI and one of the features that makes it the best ETL tool on the planet.

First we will have a look at nesting ODI methods at the same parse level. This is fairly straightforward:

What we do here is create a procedure with just one step. For this procedure we create an Option SESSION_PARAM. We use this option to make a call to the getSession method. As value for SESSION_PARAM we could pass in SESS_NO or SESS_NAME.

--<%=odiRef.getSession("" + odiRef.getOption("SESSION_PARAM") + "")%>

We just use double quotes and the plus sign to append the second substitution method call. The first double quote is used to as part of getSession and the second pair is used to allow us make a nested call to the substitution API.

Note: The double minus — is used to comment out the output of our API calls in the Oracle technology as otherwise we would throw an error.

Output of the above step is as follows ( I passed SESS_NO as the value of the option).

Next we look at some more interesting stuff and I’ll show you how you can nest calls to the substitution API at different levels of the ODI multi pass parser.

Recently I’ve had a requirement to create indexes on the fly for temporary interfaces. I was able to solve this by using the UD5 marker and nesting of ODI substitution calls.

We will first modify the IKM SQL Control Append and insert a step at the end of the knowledge module.

<? i=0; ?>

BEGIN

<%=odiRef.getColList("\t", "\tEXECUTE IMMEDIATE 'CREATE INDEX IDX_" + odiRef.getInfo("TARG_NAME") + "_" +

"<? i=i+1;out.print(i);?>" +

" ON " + odiRef.getInfo("TARG_NAME") + "([COL_NAME])" +

"'", ";\n", ";","UD5")%>

END;

In the above step we are nesting calls to the API at two different levels. Calls at the <% %> level are always executed before calls to the API at level <? ?>. As you can see we are using the UD5 marker to filter out columns where the UD5 checkbox has been selected.

Note: Inside the delimiters we are using the Java BeanShell scripting language.

When we use the knowledge module in a temp interface, the result of the first parse pass at runtime is as follows:

<? i=0; ?>
BEGIN
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(PROD_ID)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(CUST_ID)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(TIME_ID)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(CHANNEL_ID)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_<? i=i+1;out.print(i);?> ON SALES_TARGET(PROMO_ID)';
END;

This is then passed on to the next level where at runtime this results in:

BEGIN
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_1 ON SALES_TARGET(PROD_ID)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_2 ON SALES_TARGET(CUST_ID)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_3 ON SALES_TARGET(TIME_ID)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_4 ON SALES_TARGET(CHANNEL_ID)';
	EXECUTE IMMEDIATE 'CREATE INDEX IDX_SALES_TARGET_5 ON SALES_TARGET(PROMO_ID)';
END;

Eventually this is executed by the Oracle technology as dynamic SQL. You need to be careful when you nest different parse levels with escaping quotes. I will deal with this in the next part of this series.

Once you get your head around this stuff you can literally meet any requirement that is thrown at you. What a great tool.

Don’t you feel the love yourself?

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

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

Jython Essentials (O’Reilly Scripting)

ODI snippets: Query to retrieve source and target columns in an interface

The following query can be used to retrieve the source columns/tables and target columns/tables for interfaces in a specified project

SELECT
   c.table_name AS source_table,
   e.col_name AS source_col,
   f.table_name AS target_table,
   a.col_name AS target_col
FROM
   snp_pop_col a
   LEFT OUTER JOIN snp_src_set b ON (a.I_SRC_SET = b.I_SRC_SET)
   LEFT OUTER JOIN snp_source_tab c ON (b.i_src_set = c.i_src_set)
   LEFT OUTER JOIN snp_txt_crossr d ON (a.i_txt_map = d.i_txt)
   LEFT OUTER JOIN snp_col e ON (d.i_col = e.i_col)
   JOIN snp_pop f ON (a.i_pop = f.i_pop)
WHERE
   a.i_pop IN (
      SELECT
         i_pop
      FROM
         snp_pop
      WHERE i_folder IN (
         SELECT
            i_folder
         FROM
            snp_folder
         WHERE
            i_project = project_id
      )
   )
ORDER BY
   a.i_pop;

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

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

Jython Essentials (O’Reilly Scripting)

ODI Variables and the Operator Module

One of the annoying things in ODI is that you can’t print out the value of an ODI variable to the Operator module without a workaround. In this workaround you throw an exception in Jython or the Java BeanShell and pass the ODI variable as an argument to the exception method,e.g. in Jython you can use raise(’#name_of_project.name_odi_var’) to achieve this.

What is quite cumbersome, however, is that you need to manually put this piece of code into each package for each variable you want to debug.

I have written a generic procedure that will make this easier. This procedure uses the workaround described above to print out each variable in a package to the Operator module.

We use a mix of JBS and Jython to achieve this. In JBS we connect to the ODI work repository and retrieve the names of the variables used in the package in question. We then pass these values to Jython and raise the exception.

The source code for the procedure is as follows. You need to set the technology to Jython.

<%
import java.sql.*;
import java.io.*;
BufferedWriter bw = new BufferedWriter(new FileWriter("d:\log.txt"));
StringBuilder strB = new StringBuilder();
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@bethke:1521:orcl";
Connection con = DriverManager.getConnection(url,"odi_dev","odi_dev");
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
bw.write("raise('");
out.print("raise('");
String str = "SELECT c.var_name,d.project_name  FROM snp_package a JOIN snp_step b On (a.i_package = b.i_package) JOIN snp_var c ON (b.i_var = c.i_var) JOIN snp_project d ON (c.i_project = d.i_project) WHERE  UPPER(pack_name) = UPPER('" +odiRef.getOption("PCK_NAME")+"')";
ResultSet rs = stmt.executeQuery(str);
      while(rs.next()){
         String var_name = rs.getString("var_name");
         String project_name = rs.getString("project_name");
         out.print(var_name+" #" +project_name+"."+var_name+"\n ");
         bw.write("#STUFF."+var_name);
      }
bw.write("')");
out.print("')");
rs.close();
bw.close();
stmt.close();
con.close();
%>

On lines 6-9 we connect to the ODI work repository
On line 11 we print the start of raise method to Jython
The SQL on line 12 will get us the variable and project name for the variables used in our package. Note that we need to pass in the name of the package as an Option to the procedure. So far I have not been able to find a way how to get this at runtime.
On lines 13-19 we iterate over the resultset of the above SQL and pass the name of the variable and package to Jython
Finally we do some clean up work

Let’s see the procedure in action.

First we assign values to two variables

As a last step in the package we execute the ODI debug variable procedure

The result can be seen from the Operator module

Under the hood of the Sunopsis Memory engine. Part 3.

This is the final part in our series on the HSQLDB in memory engine. In the first part of this series we’ve had a look at the Sunopsis Memory Engine (SME) from a high level. In the second part we then looked at two use cases namely using Java methods directly in SQL transformations and using the in memory database for simple, low volume transformations in memory. Craig Stewart commented on this last post and mentioned it would be useful to set a JDBC property that would clean up memory after a disconnect, similar to the drop_on_disconnect JDBC property of the ODI XML driver. I had a look in the HSQLDB documentation and came across the SHUTDOWN property. This property shuts down the database after the last session has been disconnected. The problem is that it’s not available yet in version 1.7.3 (the one that ODI uses). As a result we need to clean up objects ourselves or upgrade the HSQLDB to a higher version. If I find some spare time I will blog on how to do this. Also the other day I came across a corrupted HSQLDB. Another subject I may blog about some time soon.

Ok. Let’s have a look at today’s topic. The SME can be very useful to act as a temporary data store, e.g. you can store a Jython variable in the SME, from there assign it to an ODI variable and then pass it on to another scenario. In today’s post I will show you how to achieve this.

We will first create a procedure using Jython technology. In this procedure we will take a Jython variable and store it in a table in the Sunopsis Memory Engine. In this example we grab the OS username of the logged on user and store it in a table named TTEMP in the in memory database. Thanks to Maciej Kocon for the code.

#matshyeq:20100207
import os, re, time
import java.sql as sql
import java.lang as lang
import os

currentuser = os.environ['USERNAME']

lang.Class.forName("org.hsqldb.jdbcDriver")
url = "jdbc:hsqldb:."
myCon = sql.DriverManager.getConnection(url,"sa","")

mySQL="create table TTEMP (osuser VARCHAR(30), val TINYINT, ts VARCHAR(30))"
try:
        prepStmt = myCon.prepareStatement(mySQL)
        prepStmt.execute()
except:
        pass
mySQL="INSERT INTO TTEMP VALUES('%s',1,NOW())" % currentuser
prepStmt.close()
prepStmt = myCon.createStatement()
prepStmt.executeQuery(mySQL)
prepStmt.close()
myCon.close()

Next we create an ODI variable V_OSUSER that gets its value from TTEMP.OSUSER.

SELECT osuser FROM ttemp

We take this variable and pass it into another scenario.

In this scenario we print the variable to the Operator log to verify that the value of the variable was passed on.

From the screenshot we can see that the OS user uli was printed out, which means that we successfully passed on our Jython variable value to another scenario.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

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

Jython Essentials (O’Reilly Scripting)

ODI: Automating deployment of scenarios to production in Oracle Data Integrator

In this post I will show you how you can automatically deploy scenarios in ODI.

It is rather cumbersome to manually deploy scenarios from a test/development to a production environment.

Typically it involves the following steps:

- Manually (re-)generate all scenarios that need to be deployed and any child scenarios referenced.
- Manually export the (re-)generated scenarios
- Log in to the Operator module for the production environment and manually import the scenarios.

You do the above once or twice manually before getting extremely fed up. Actually I was rather patient (unlike my normal self) and did this about ten times before I got very, very annoyed.

In this post I will show you how you can automate the deployment process. The proposed solution will allow you to logically group scenarios together via marker groups for automatic deployment, thus giving you more flexibility and allowing you to just deploy a subset of scenarios in your project.

To achieve our goal we will make use of the following Oracle Data Integrator features:

- Marker groups
- ODIGenerateAllScen tool
- ODIExportScen tool
- OdiImportScen tool
- Meta-information in the ODI work repository
- Execution of scenarios from a Windows batch file

In a first step we create a new marker group. We will use the marker group to logically group together scenarios we want to deploy. We will subsequently use the marker group in the ODIGenerateAllScen, ODIExportScen, and OdiImportScen tools.

Log on to Designer > Expand Markers > Right click Markers > Select Insert Marker Group

As you can see from the figure above I have named the marker group Scenario and added three markers. One of the markers is named XLS (short for scenarios that load data from Excel sheets). Flagging these scenarios (or rather their packages) via a marker will allow us to deploy them separately.

Next we will add the XLS marker to those packages that we wish to logically group together for deployment. In our particular case, all of the Excel related packages.

Right click package > Add Marker > Scenario XLS

In the next step we will create a package that will (re-)generate all packages marked with XLS.

Create a new package, name it GENERATE_SCENARIOS_XLS, add the ODIGenerateAllScen tool to it, and use the following parameters for the tool:

Project: The name of your project
Mode: Replace. This will overwrite the latest version of your scenario.
Marker Group: Scenario
Marker: XLS

Leave the default values for the other parameters.

In a next step we need to export the (re-)generated scenarios to XML. Unfortunately, the OdiExportScen tool does not allow us to make use of marker groups to logically group together scenarios for export. To achieve our goal we need to make use of a workaround.

As ODI is a meta-driven ELT tool we can retrieve information about the marker groups from the ODI work repository.

The query below will exactly do this. It returns alls packages that are marked as XLS.

SELECT
   scen_name AS pack_name
FROM (
   SELECT
     LAST_VALUE(d.scen_name) OVER
 (PARTITION BY c.pack_name ORDER BY scen_version) AS scen_name,
     MAX(scen_version) OVER
 (PARTITION BY c.pack_name ) max_scen_version,
     scen_version,
     c.pack_name
   FROM
      snp_obj_state a
      join snp_state2 b on (a.i_state = b.i_state)
      JOIN snp_package c ON (a.i_instance = c.i_package)
      JOIN snp_scen d ON (c.i_package = d.i_package)
   WHERE
      state_code = 'XLS'
)
WHERE
  scen_version = max_scen_version

We will employ this query as an implicit cursor in an ODI procedure at the Command on Source. You will first need to create a data server to the ODI work repository in Topology Manager for this to work. As per figure below, set the Technology to the technology of your work repository (in my case Oracle) and set the schema to the logical schema of your work repository (in my case ORCL_ODIWORK_SRC).

We will then use the resultset together with the OdiExportScen tool to create XMLs for our scenario and write them to disk.

OdiExportScen "-SCEN_NAME=#pack_name" "-SCEN_VERSION=-1"
"-FILE_NAME=D:\ODI\SCEN_#pack_name Version 001.xml"
"-FORCE_OVERWRITE=YES" "-RECURSIVE_EXPORT=YES"
 "-XML_VERSION=1.0" "-XML_CHARSET=ISO-8859-1"
 "-JAVA_CHARSET=ISO8859_1"

Important parameters here are

SCEN_NAME: #pack_name. This is the bind variable from our Command on Source.
SCEN_VERSION: -1. This means that we will export the scenario that was generated last.
FILE_NAME: This is the path on your file system where the XMLs will be generated.

Make sure that you have set the Technology to Sunopsis API.

In a next step add the ODI procedure we just created to our package.

Finally, manually create a scenario for this procedure via ODI Designer.

Now we are in a position to import the exported scenarios from their XML files.

Once again we will use a procedure to achieve this

For command on source use the following query:

SELECT
   scen_name AS pack_name
FROM (
   SELECT
     LAST_VALUE(d.scen_name) OVER
 (PARTITION BY c.pack_name ORDER BY scen_version) AS scen_name,
     MAX(scen_version) OVER
 (PARTITION BY c.pack_name ) max_scen_version,
     scen_version,
     c.pack_name
   FROM
      snp_obj_state a
      join snp_state2 b on (a.i_state = b.i_state)
      JOIN snp_package c ON (a.i_instance = c.i_package)
      JOIN snp_scen d ON (c.i_package = d.i_package)
   WHERE
      state_code = 'XLS'
)
WHERE
  scen_version = max_scen_version

For Command on Target use the following command

OdiImportScen "-FILE_NAME=D:\ODI\SCEN_#pack_name Version 001.xml"
"-IMPORT_MODE=SYNONYM_INSERT_UPDATE"

Name the above procedure IMPORT_SCEN_XLS and generate a scenario for it.

We now have all the components that we need for automated scenario deployment. We will just have to glue them together. We will do this via a batch file. In my particular case this will be a Windows batch. Of course, you can achieve the same in a Linux etc. environment.

ODI allows you to execute scenarios via the startscen.bat. You can find this batch file in the oracledi\bin folder in your ODI home. Three parameters are mandatory for executing this batch:

%1: The name of the scenario. In our case these are the GENERATE_SCENARIOS_XLS and the IMPORT_SCEN_XLS scenarios.
%2: The version number of the scenario. In our case -1, as we want to export the last version of the marked scenarios.
%3: The execution context. In our case we deploy the scenarios from UAT to PRD. So for the export of our scenarios we will use the UAT context, as this is where we (re-)generate and export the marked scenarios. For the import of the marked scenarios we will use the PRD context as we want to import the exported XMLs into the production environment.

You will need to either have agents installed on the same server (one for each environment). I explain how you can install multiple agents on one server in a separate post. Alternatively, you should be able to use a shared folder that both agents can access (I haven’t tried this out).

@echo off
cls
d:
echo generate xls scenarios

cd D:\app\oracle\product\odi_home\oracledi\bin\
call startscen.bat GENERATE_SCENARIOS_XLS -1 UAT

echo import xls scenarios

cd D:\app\oracle\product\odi_home\oracledi\agent_prd\
call startscen.bat IMPORT_SCEN_XLS -1 PRD

I would like to hear from you how you deploy your scenarios.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

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

Jython Essentials (O’Reilly Scripting)