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

If you are involved in an enterprise data warehouse program (and by that I don’t mean Kimball’s conformed dimension stuff) you need to be familiar with all aspects of data modelling. This includes

- Requirements gathering
- Enterprise data modeling
- Conceptual/subject area modelling
- Logical modelling
- Physical modelling
- Dimensional modelling
- Normalization and denormalization
- Subtypes & supertypes
- Universal data modelling patterns
- Generic data modelling
- ER data modelling
- UML data modelling
- The collaboration process (bridging the business – IT divide)
- Selecting a data modeling tool

A well thought out enterprise data model is the foundation for a successful enterprise data warehouse.

I have put together a list of the most important data modelling books. These will teach you the tools and techniques of the best data modelers out there.

Data Modeler’s Workbench

Data Modeling Made Simple

Data Modelling Essentials

The Data Model Resource Book, Vol. 3

Mastering Data Warehouse Design: Relational and Dimensional Techniques

It is worthwhile to remember though that Exadata is designed for mixed workloads (OLTP and Analytics), which is a key differentiator to any of the other DW appliance vendors.

Interesting posts by Curt Monash on this

http://www.dbms2.com/2009/09/29/integration-oltp-data-warehousing-exadata-2/
http://www.dbms2.com/2010/01/22/oracle-database-hardware-strategy/

Where is the response from Oracle?

This time round the focus will be on OBIEE and in particular on OBIEE 11G.

Matt Harte from Peak Indicators will give a presentation on OBIEE best practice.

Chris Hathaway from Oracle will give a presentation on the new features of OBIEE 11G.

I’ve finally managed to download and install ODI 11G but so far have had no time to play around with it. If I get around to do this I will give a brief demo on ODI 11G.

See you out at East Point

What are They?
In OBIEE help files are .htm files which are used to help users to better understand report content. They can assist with user training of new reports as they can describe any logic contained in the report for example filters which isn’t obvious from looking at the report. They can also describe the attributes and measures displayed on the report and any other report specific information.

How do you create them?
To create a help file you need to do the following:
1. Create a .htm file which contains the content of your help file
2. Save it to the following location: …\app\res
3. Point to the help file location from the report
Click on the edit button in the Title section of the report

Then enter the path and name of your help file:

How to you access them?
When the report is displayed there will be a ‘?’ in the title as shown below

Clicking on this will display the help file for that report

Give them a go – users will love them!

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)

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.

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)

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)

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)