Data Modelling books for the Enterprise Data Warehouse (EDW)

In other parts of this series we have had a look at data warehousing books that cover the design and architecture of a business intelligence solution. I have also covered data warehousing books in the world of Oracle and data warehousing and business intelligence books for project management and business analysis. Today we’ll discuss data modeling books for building an Enterprise Data Warehouse

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

Comparing Exadata and Netezza TwinFin

Comparison between Exadata and Netezza Twin Fin. Ok, it comes from Netezza and as such is biased, but still an interesting read.

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?

OBIEE 11G: 9 September

No it’s not the fecking release date . It’s just the next date for the Irish BI SIG to meet.

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

Utilizing Help Files in OBIEE

Hi all, my name is Helena Bennett and I am currently working in the BI space as a Business Analyst, I came across help files recently and thought I would put together a “how to”….

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!

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)

Irish BI SIG, 1 June Dublin: Oracle Data Integrator 11G, Oracle GoldenGate, and SOA

Thank god Craig Stewart is coming over to Dublin next week. I missed his presentation at the Rittman Mead BI Forum in Brighton last week as I had to fly out early on Friday morning after an epic pub crawl the night before (anyone remembers where those Jägerbombs came from???). Anyway, I learned a lot over at Brighton and one of the things is that I am getting old…

Over at the forum Craig promised me to have version 2 of his ODI 11G presentation ready for us on 1 June. Other highlights of the day will include real-time data warehousing with Oracle GoldenGate presented by Gary Chape and SOA data integration with ODI presented by Ian Milne. For details and to download the presentations have a look at the agenda.