IT-eye

One of the features I like in Pentaho Data Integration (version 3.1.0) is the “dimension lookup/update” transformation step to implement a Slowly Changing Dimension type II.
It works quite easy and saves a lot of work. I worked out a small example to illustrate how it works: just an Excel file to populate a dimension table.

The complete transformation from Excel file to dimension table only takes two transformation steps.

The Excel file has the following columns:

• klantnummer (customer number)
• voornaam (first name)
• achternaam (last name)
• woonplaats (city)
• geboortejaar (year of birth)
• geslacht (gender)
• type (type)

The dimension table DIM_KLANT (customer dimension) has the following properties:

As you can see, it has some additional columns compared to the Excel file:

• technical_key
• start_date
• end_date
• version

I skip the first transformation step and go straight to the second one.

Here is how this transformation step is configured:

And here is how it works.

We start with the following data file (Excel).

After running the transformation, the dimension table contains the following data.

As you can see, all records are inserted and have version number 1. Also does every inserted record have a (default) start date and end date. And, of course, a technical key.

Now we change the data in the Excel file.

Let’s modify Ineke’s year of birth (from 2000 in 2001). We also add Janneke, Klaas and Laura.

Run the transformation again. The data in the dimension table should now look like this:

As you can see, Ineke has a new record in the dimension with version number 2. Her (previous) record with version number 1 now has an updated end date. New records are also added for Janneke, Klaas and Laura. All with version number 1 and with a technical key.

Finally, let’s make some changes to the data. We modify the dates of birth for Ineke, Janneke, Klaas and Laura.

After running the transformation, we expect 4 new records to be added to the dimension table. Janneke, Klaas and Laura should get records with version number 2. Ineke shoud get a new record with version number 3. All previous versions of those records should get an updated end date.
This is what the dimension table now looks like.

Here ends this small example. The functionality of the transformation step “dimension lookup/update” works quite nice and easy. It also saves a lot of work compared to programming this functionality yourselves.

Just posted on The Java Posse: a podcast interview with Ted Farrell and Duncan Mills about the Oracle Java Tools. Download it here: Java Posse #212 – Oracle Developer Tools Interview.

download the binary for the jaxws (JAX-WS RI 2.1.4) reference implementation library
execute the jar : java -jar JAXWS2.1.4-20080502.jar

In Jdeveloper follow the next steps

- Create a new Application : JAXWSRest (No Template)

- Create a new Project : HelloWorldRestService
Edit the project properties > Libraries > Add Library
Add a new User Library : jaxws-ri
And add the libraries from the ‘jaxws-rilib’-directory

Add a new java-class : HelloWorldImpl

package nl.iteye;

import javax.annotation.Resource;
import javax.xml.transform.Source;
import javax.xml.transform.stream.StreamSource;
import javax.xml.ws.BindingType;
import javax.xml.ws.Provider;
import javax.xml.ws.WebServiceContext;
import javax.xml.ws.WebServiceProvider;
import javax.xml.ws.handler.MessageContext;
import javax.xml.ws.http.HTTPBinding;
import javax.xml.ws.http.HTTPException;
import java.io.ByteArrayInputStream;
import java.util.StringTokenizer;

@WebServiceProvider
@BindingType(value=HTTPBinding.HTTP_BINDING)
public class HelloWorldImpl implements Provider<source> {

    @Resource(type=Object.class)
    protected WebServiceContext wsContext;

        public Source invoke(Source source) {
            try {
                MessageContext mc = wsContext.getMessageContext();
                String query = (String)mc.get(MessageContext.QUERY_STRING);
                String path = (String)mc.get(MessageContext.PATH_INFO);
                System.out.println("Query String = "+query);
                System.out.println("PathInfo = "+path);
                if (query != null && query.contains("name=")) {
                    return createSource(query);
                } else if (path != null && path.contains("/name")) {
                    return createSource(path);
                } else {
                    throw new HTTPException(404);
                }
            } catch(Exception e) {
                e.printStackTrace();
                throw new HTTPException(500);
            }
        }    

    private Source createSource(String str) {
        StringTokenizer st = new StringTokenizer(str, "name=&/");
        String name = st.nextToken();
        String body =
            "<ns :helloWorldResponse xmlns:ns="http://iteye.example.org"></ns><ns :return>"
            +"Hello " + name
            +"</ns>";
        Source source = new StreamSource(
            new ByteArrayInputStream(body.getBytes()));
        return source;
    }
}

With annotations we define the WebServiceProvider and which binding we want to use for the service (http binding).
The Provider interface let us implement the T invoke(T p1) { } method.
This method will get the context. From the context it will get the url by which our service is invoked.
The createSource helper-method will get the the value of the parameter from the url (parameter ‘name’) and return the xml response back.

Add a new J2EE Deployment Descriptor
File > New > Deployment Descriptors > J2EE Deployment Descriptor Wizard > web.xml > 2.4

Use this for the web.xml

<web -app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
    <description>restful</description>
    <display -name>restful</display>
    <listener>
        </listener><listener -class>com.sun.xml.ws.transport.http.servlet.WSServletContextListener</listener>

    <servlet>
        <description>JAX-WS endpoint - restful</description>
        <display -name>restful</display>
        </servlet><servlet -name>restful-sayhello</servlet>
        <servlet -class>com.sun.xml.ws.transport.http.servlet.WSServlet</servlet>
        <load -on-startup>1</load>

    <servlet -mapping>
        </servlet><servlet -name>restful-sayhello</servlet>
        <url -pattern>/sayhello/*</url>

    <session -config>
        </session><session -timeout>60</session>

</web>

Here we map the url /helloworld to the WSServlet class of jaxws implementation.

In the WEB-INF directory create a new file : sun-jaxws.xml

<endpoints xmlns="http://java.sun.com/xml/ns/jax-ws/ri/runtime"
    version="2.0">

    <endpoint name="restful-addnumbers"
        implementation="nl.iteye.HelloWorldImpl"
        url-pattern="/sayhello/*" />
</endpoints>

In here we will map the call to the service ’sayHello’ to our package ‘nl.iteye.HelloWorldImpl’.

Finally we need a deployment profile.
File > New > Deployment Profiles > WAR File > MyHelloWorldRestApp
In General set ‘Specify J2EE Web Context Root’ to MyHelloWorldRestApp
In File Groups > WEB-INF/lib > Contributors, select jaxws-ri (or the name you gave your user library)

We ready for testing.
Deploy the service to the application server

To test the service we can use and url of the format :

http://<host>:<port>/<web CONTEXT ROOT>/<url PATTERN FROM SUN-JAXWS.XML>/<parameter_name>/<parameter_value>

For me it would be :

http://localhost:8888/MyHelloWorldRestApp/sayhello/name/Eric

So we use the pattern sayhello in the url, this will do the call to our HelloWorldImpl class, and call the invoke-method.
This will return a piece of xml.

And the output in our browser is :

<ns :helloWorldResponse xmlns:ns="http://iteye.example.org"></ns><ns :return>Hello Eric</ns>

But due to javascript errors i can’t download it. Not sure what is causing this, maybe some javascript files that don’t get through the firewall? Or maybe some simple javascript coding errors. Is it my browser, is it the page? Try it here: JDeveloper 11g download.

This week Tim O’Reilly wrote a post called Why Dell.com (was) More Enterprise 2.0 Than Dell IdeaStorm, in which he states his current definition of web 2.0:

I define Web 2.0 as the design of systems that harness network effects to get better the more people use them, or more colloquially, as “harnessing collective intelligence.”

This is exactly the point i made in my Web 2.0 Enterprise 2.0 presentation The real meaning of web 2.0 for your business. The internet allows companies to take advantage of all the knowledge in the world, and businesses can improve their level of service by using this knowledge.

The example i used was amazon.com. By using their customers, and the collective intelligence of their customers, amazon.com has created a store that’s better than real-world stores, because they really help their customers pick the right product. No longer is an internet store better because it’s always open, and it doesn’t require you to travel. Now it’s also better because customers are better informed to choose the right product.

If i am in a real-world store, i miss amazon.com because i need the information that’s only available on amazon.com. And now with the mobile web becoming mature, amazon.com is able to compete with physical stores right inside these stores.

Your customers, and the knowledge of your customers, become an essential part of the level of service you can provide as a company. This is the profound impact of the internet on businesses.