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 (ODI) web services (SOAP client via ODIInvokewebservice) and the OBIEE web services API.

As outlined in a previous post ODI has a built in SOAP client via the ODIInvokewebservice tool. One of its limitations is that it doesn’t run on Java 6.

Another limitation is that it doesn’t like SOAP headers. So if you are used to SOAP headers you find in tools such as soapUI you are out of luck for the moment. Note 1143755.1 explains that an enhancement request has been logged to include this in a later release, but as of release 10GR3 it is not supported.

So what use is the ODIInvokewebservice? You could, e.g. query the Amazon product advertising API or the ebay equivalent. In this post I’ll show you how you can query the OBIEE web services API. I am currently working on a project to extract the group, user, privilege etc. information from the OBIEE presentation catalog. This is useful to automate the documentation of group hierarchies, inherited privileges etc. I’ll keep you posted on the progress.

The OBIEE web services API does not like Single Sign On (SSO). If you are using IIS as your application server you need to create a second virtual directory and disable integrated security.

Once this is done we are ready to go. Create a new package in ODI and add an ODIInvokewebservice tool. On the General tab click on the Advanced… button. This will bring up the ODI SOAP client.

In the URL field type in the path to the OBIEE WSDL file: http://”server_name”/analyticsSOAP/saw.dll?wsdl. This should point to the new virtual directory in the step above. Next click the connect to WSDL icon.

This will return all of the services and methods that are available from the OBIEE web service API. The web service we are interested in is the SAWSessionServiceSOAP and in particular the logon method. Each request to the OBIEE web services API needs to be authenticated and the logon method returns a sessionID for us.

Populate name and password with username and password of an OBIEE account with SOAP privileges and click the Invoke web service icon

This will return the sessionID that we can use in other requests to the OBIEE API later on.

Click on OK to return to the previous screen. On the General tab you can then define a response file that ODI writes the returned XML to from your web service call.

This will produce an XML file in the specified location similar to the one below:

<?xml version=”1.0″ encoding=”UTF-8″?>
<ns1:logonResult xmlns:ns1=”com.siebel.analytics.web/soap/v5″>
<ns1:sessionID xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:type=”xsd:string”>5lll90nu19bbhi49u57h8vdott63gcs5j19g9vazOr07UFe9W00</ns1:sessionID>
</ns1:logonResult>

We can then extract and load the sessionID via an ODI Interface into a database or temporarily store in the hsqldb odi memory engine.

There is one issue with this, however. The ODI XML parser doesn’t like xsi attributes such as xsi:type. So before we can reverse engineer the logon.xml from above we need to get rid of any occurrence of xsi:type in the logon.xml file. I have written an ODI procedure in Jython that does exactly that. As a parameter it takes the file path to the logon.xml and replaces any occurrence of xsi:. Of course, if you are on Linux you should use sed & awk to do this as performance is better.

s = open(”<%=odiRef.getOption(”FILE_PATH”)%>”).read()
s = s.replace(’xsi:’, ”)
f = open(”<%=odiRef.getOption(”FILE_PATH”)%>”, ‘w’)
f.write(s)
f.close()

Now we are ready to reverse engineer the XML and load the sessionID into a relational database. These steps are well documented so I refer you to the “Oracle by Example Series: Oracle Data Integrator” site or to Craig Stewart’s ODI video tutorials.

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)

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.

Nesting ODI Substitution Methods. Part II.

Today we look at how we can escape quotes when nesting ODI substitution method calls at different levels of the multi pass parser.

As an example we will store the columns of the C$_ datastore and the columns of a target datastore in a two dimensional java array.

,<%=snpRef.getColList("{", "\u0022[COL_NAME]\u0022", ",", "}", "INS")%>};
int k =0;
int l =1;
for (int i = 0; i < ColList.length; i++) {
   for (int j = 0; j < ColList[i].length; j++) {
      out.println(ColList[j][i]);
   }
}
?>

As you can see, we are nesting a call to the ODI substitution API inside a parse block. Normally in Java we escape a quote with \”. In ODI this would throw an error. What we do instead is to use the Unicode representation of the quote character: \u0022

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)

Calling custom Java classes and JAR files in ODI via Jython or Java BeanShell

First we create a simple Java class that creates and writes to a text file and save it as FileWrite.java

import java.io.*;

public class FileWrite
{

  public void writeFile()
  {
    FileOutputStream fos;
    DataOutputStream dos;

    try {

      File file= new File("C:\\MyFile.txt");
      fos = new FileOutputStream(file);
      dos=new DataOutputStream(fos);
      dos.writeInt(2333);
      dos.writeChars("Hello World");

    } catch (IOException e) {
      e.printStackTrace();
    }
  }
}

Next we compile the .java class from the command line

c:\javac FileWrite.java

Next we create a .jar file from the class

c:\jar cf FileWrite.jar FileWrite.class

We then copy and paste the .jar archive to the ODI drivers folder

Next we restart the ODI agent.

We can now call methods in this class from either Jython

import FileWrite

fw=FileWrite()
fw.writeFile()

or we can call it from the Java BeanShell

import FileWrite;

FileWrite fw = new FileWrite();
fw.writeFile();

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)

Craig Stewart’s Oracle Data Integrator (ODI) video tutorials

Craig Stewart has put together a superb set of ODI video tutorials. This stuff is just brilliant. Thanks a lot Craig, for letting me publish these on the blog. You’ve really earned your new master of the universe title

How to define a PostgreSQL in ODI – then reverse engineer in the Designer to access the data
Defining a PostgreSQLserver in ODI

ODI has some automatic features which will generate the Group By Statement for you when you use any of the aggregation functions, this is a short demo of how to use it
How to use Aggregation Functions in ODI

What is the Common Format Designer (CDF), and what can we do with it? Short demo of the features, generating schemas and generating interfaces automatically.
ODI’s Common Format Designer

Short demo on the Metadata Navigator of ODI
ODI’s Metadata Navigator

OdiZip is a useful tool and this short screencam illustrates its use
OdiZip How to use

Sybase ASE to IQ knowledge module
Sybase ASE to IQ KM demo

How to use custom Java classes in your ODI procedures
Using custom Java code in ODI

Using Excel in ODI, including getting round the problem with the limitation of fixed named ranges
Using Excel in ODI

How to define and use Flexfields to extend ODI’s metadata
Using FlexFields in ODI

Defining XML file in Topology and reverse engineering
XML 1 Defining

What does the XML structure look like in ODI?
XML 2 The Rendered Data Structure

When the definition doesn’t work, how do you find out what has gone wrong?
XML 3 Topology

How to use ODI to populate a simple XML structure
XML 4 Populating Simple XML

Populating a more complex XML structuire – requiring the use of multiple interfaces. Writes to a file with a dynamic name – in this case the session_id.xml
XML 5 Populating Complex XML

How to configure external database storage for the JDBC Driver for XML- useful when dealing with large XML files
XML 6 External Database Storage

How to set up the file name for the XML file so that it accommodates the use of multiple different file names
XML 7 Reading a Dynamically named XML file

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)

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)