Open BI

Query Twitter with Talend to see what people think about …

2011-07-26T07:09:00.001-07:00

Hi all,

Almost on holidays after a very hard working year. I have some beach time.

This morning, I tried to query Twitter and to process the data. My purpose is to quickly build a data set showing what people are talking about … let’s say, Obama. Easy, Twitter is providing an interface to run queries and retrieve the results back with json format.

Well, my proposition is to implement a basic word frequency analysis. My tool is Talend, but this process is also easy to set up using java, php, python, shell, Kettle …

Here are some details :

fileInputJSON : just read the json from Twitter search engine. The syntax is : http://search.twitter.com/search.json?&q=YOUR_QUERY_HERE&rpp=10000, where YOUR_QUERY_HERE = the key word you want to search for. Don’t forget to distribute the answer into a string column :
- answer ====> “$..text” : where “$..text” stands for the tweet message you want to read.
tmap : just to transform the text into lower case. I tried to do it on the fly in the json step, but it does not work.
tnormalize : transform all words from anwer into rows (separator is space).
tfilter : filter your data in order to avoid “|” (pipes) and other special chars + get rid of words having less than 4 letters.
taggregate : aggregate on the words and add a new column named nb that will store the distinct count of all words.
tsortrow : now you have words and counts, just sort the data from higher counts to lower (desc).
tsamplerow : as we just want to read the first top 20 words, we create a sample based on the sorted rows with range “1..20”.

Finally, I print everything into the console by using a tlogrow + “:” separator.

Here are the data for the keyword “obama”, the query was done on July 26 at 16h00.

Finally, I will use Jonathan Feinberg, wordle, to create a nice and sexy word map with the above results.

Setting up the process and testing it only took 20 minutes. Of course, this can be improved a lot by adding string cleaning, custom data filtering… or better :

Capture who created the tweet and who received it : you can create relationship networks with, for instance, networkx.
Send the data to a powerfull text analytics framework like NLTK, for better analytics.
Create the word map picture into Talend. I have some source code for this, will have to work on it …

I ll see how to add one of these features soon.

Interfacing Talend with Amazon SDB (AWS SDB) – quick way

2011-07-19T08:59:00.001-07:00

Hi all,

I had the following challenge : read some ftp account informations (ftp server, username, password, target directory) stored in Amazon SDB and use it in a Talend transformation, published as a web service. You know about SDB I hope. For those who don’t, SDB is a key / value database provided by Amazon. So you can name SDB a noSQL database.
I played with the SDB API from Amazon, and succeeded after some coding and “Talending”. Here is how I did.
Below is only a small part of a much larger project, composed of a large webservice collection, created for my client.
This project does the following :

Query into a database using dynamic params given by the user at run time from a Flex portal (a query engine like business objects !),
Return a rowcount of the query, into the portal. I’m working in web marketing : counting people (segments) before creating a campaign is very important …
Generate an extract of the data, process this extract according to the params given by the user (separator, encoding, spliting, zipping …),
Send this data file to different “tubes” : router, ftp, AWS S3, local download …

Here we focus on the ftp sending part, using SDB to retrieve infos.

The process.

The job (partial).

Data structure in AWS SDB.

I’m using a very nice firefox plugin in order to have easy and quick access to my SDB ecosystem : sdbtool. My data structure is simple ( “dd” is of course not the true value …) :

Item : ftp
- Attribute names : Address :
  - Attribute names :Login
    - Attribute value : dd
  - Attribute names : PKey
    - Attribute value : dd
  - Attribute names : Password
    - Attribute value : dd
  - Attribute names : Port
    - Attribute value : 21

Here is a screencap of my sdbtool view :

Explanations.

First, we load all the needed libraries, using tlibraryload component.

aws-java-sdk-1.0.14.jar
commons-codec-1.3.jar
commons-httpclient-3.0.1.jar
commons-logging-1.1.1.jar
jackson-core-asl-1.4.3.jar
stax-api-1.0.1.jar
stax-1.2.0.jar

For the aws-java-sdk-1.0.14.jar import, I had to write some imports. These imports are required to be able to use the aws jdk.

Then we have a tRowGenerator in which I create the value for the variable myDomain, that will be used in SDB queries (see code below). You can avoid this step, I created it only for quick testing purpose.
Then, we have to code a little tJavarow. This java code will :

connect to AWS SDB. You must have an account for AWS SDB.
run several queries, using SQL, to retrieve ftp account informations :
- ftp server address
- ftp server login
- ftp server pass
- ftp server port
- ftp server pkey, if needed
store the query results in output_row.[name] so they can be used in Talend process.

The code in the tJavaRow. First we create some credentials (use yours) and then create an endpoint with sdb address from AWS : https://sdb.eu-west-1.amazonaws.com. Be carefull to set a valid endpoint, using a valid country zone.
The code is finally simple : create a string containing your sql query, then call a getItems() function. An Item is sent back, simply call a getAttribute in order to retrieve the value you need.
I chose, for simplicity, to run a different query for each item I need from SDB. Of course, you can write it shortly.

  
        BasicAWSCredentials credentials = new BasicAWSCredentials("KL45LKJ4325MLKJ2345", "LKJ45LKJmlkjdlkjGRhjKLJSFSDG432534");    
        
    final String[] FTP_Items;
    
        

        AmazonSimpleDB sdb = new AmazonSimpleDBClient(credentials);
        sdb.setEndpoint("https://sdb.eu-west-1.amazonaws.com");
        try {
        int i = 0;
            String myDomain = "Clients"; 
            String selectExpression = "select FTP_Address from `" + myDomain + "`where code_client = '" + context.client_name +"'";
           
            SelectRequest selectRequest = new SelectRequest(selectExpression);
            for (Item item : sdb.select(selectRequest).getItems()) {
                for (Attribute attribute : item.getAttributes()) {
                    output_row.FTP_Address = attribute.getValue().toString();
                }
            }         
            selectExpression = "select FTP_Login from `" + myDomain + "`where code_client = '" + context.client_name +"'";
            selectRequest = new SelectRequest(selectExpression);
            for (Item item : sdb.select(selectRequest).getItems()) {
                for (Attribute attribute : item.getAttributes()) {
                    output_row.FTP_Login = attribute.getValue().toString();
                }
            }
            selectExpression = "select FTP_Pass from `" + myDomain + "`where code_client = '" + context.client_name +"'";
            selectRequest = new SelectRequest(selectExpression);
            for (Item item : sdb.select(selectRequest).getItems()) {
                for (Attribute attribute : item.getAttributes()) {
                    output_row.FTP_Pass = attribute.getValue().toString();
                }
            }
            selectExpression = "select FTP_Port from `" + myDomain + "`where code_client = '" + context.client_name +"'";
            selectRequest = new SelectRequest(selectExpression);
            for (Item item : sdb.select(selectRequest).getItems()) {
                for (Attribute attribute : item.getAttributes()) {
                    output_row.FTP_Port = Integer.valueOf(attribute.getValue());
                }
            }
            selectExpression = "select FTP_PKey from `" + myDomain + "`where code_client = '" + context.client_name +"'";
            selectRequest = new SelectRequest(selectExpression);
            for (Item item : sdb.select(selectRequest).getItems()) {
                for (Attribute attribute : item.getAttributes()) {
                    output_row.FTP_PKey = attribute.getValue().toString();
                }
            }
        } catch (AmazonServiceException ase) {
            System.out.println("AWSException");
            System.out.println("ErrorMsg:    " + ase.getMessage());
            System.out.println("HTTPStatcode: " + ase.getStatusCode());
            System.out.println("AWS Errcode:   " + ase.getErrorCode());
            System.out.println("Errortype:       " + ase.getErrorType());
            System.out.println("RequestID:       " + ase.getRequestId());
        } catch (AmazonClientException ace) {
            System.out.println("AWSClientException");
            System.out.println("Error Message: " + ace.getMessage());

Final.

After retrieving all the item I need for sending on ftp (server, username, pass, port or location for ssh key), I store all this into global variables. Then, these global variables are used as arguments into two very customized scripts (needed in my case) that will send the files : simple ftp or sftp when needed. Finally, I catch some usefull infos from the custom ftp scripts, process it into a tmap and send this information into a tBufferOutput step. That way, I can provide a soap feed back when calling this webservice.

This post is very consice, feel free to ask me for more infos about this process.

Links.

… about AWS cloud, Talend, Jaspersoft, Postgresql and typical EC2 internal addressing issues …

2011-06-21T09:00:00.001-07:00

Hi all,

I’m terribly late with this article, initially scheduled for January 2011 … sorry. Maybe it is a bit outdated now, anyway, I publish it …
Let’s talk about EC2 cloud computing, Talend, Postgresql and JasperServer. Basic setup.
You already know all the pros and cons with cloud computing, I won’t talk about that. As to me, I love cloud computing and use it everyday, because of these particular advantages :

Scalablity : scale up or down any instance, according to your needs,
Flexibility : create your own instances, boot them, create quick sandboxes, replicate data …
Pay per use : you pay for what you use (cpu, storage, security …),
Opex, no capex !

Cloud computing is still something new, and it is not surprising to discover softwares that are not ready for it or not fully “cloud compliant”. I recently faced such an issue when implementing Postgresql, Talend and Jaspersoft, which remain my preferred open source BI tools.

First issue

Let’s imagine we have a single server, hosting Postgresql. No big deal with that as long as we use this instance in a simple way : I can start my instance, host data on a persistent EBS, connect to it and stop it whenever I want. By using elastic IPs, I can assign a “fixed” IP address to this server and can easily set up a connection string. Note on 16/12/2010 : Amazon is now offering a DNS service.
Now let’s imagine we need a typical BI architecture (tiers) : one ETL (Talend or Pentaho of course !), a Postgresql database in the middle and Jaspersoft for reporting.
That’s a bit more complex because we need our Postgresql server to allow connections from the ETL and from the reporting tool. On top of that, we want to fully leverage all cloud computing features : stop the servers when they are not used, boot them when the service is needed, maybe change their network properties ... eventually we want this to be fully automated and working without any human actions like changing the connection strings, starting/stopping the servers …
Let’s have a look to a little schema now. As you can see, we have now our architecture up and running. We are also using elastic IPs for each server, which is mandatory for the following demonstration. IPs are fake.

How to read Public DNS, Private DNS and Elastic IPs on AWS EC2 ?
Imagine we have an instance running. This instance has an Elastic IP which is 46.52.186.25 and the private IP address is 11.235.33.6.
The Private DNS name is : ip-11-235-33-6.eu-west-1.compute.internal
The Public DNS name is : ec2-46-52-186-25.eu-west-1.compute.amazonaws.com
You see the relationship ?

Ok, now, how do you think we will configure Postgresql server to allow connexions from the ETL server and from the Reporting server ? Easy, here is one answer :

By making the ETL Server and the reporting server point to Postgresql. For that, we will use this nice little Elastic IP we previously set up for Postgresql server because it’s soooo easy to do that way …
By writing the ETL server Elastic IP and reporting server Elastic IP into Postgresql pg_hba.conf of course … because here again it is soooo easy natural to do so.
Don’t forget to open the corresponding ports in your security groups (see picture above).

Ok, easy. Let’s go for it. We make Talend and Jasper point to Postgresql like this :
Jasper server connexion screen : Postgresql database <===> Jasperserver

Talend client connexion screen : your client <===> Talend server

Talend server connexion screen : Talend server <===> Postgresql database

And then we write down the Elastic IPs into the pg_hba file like this, in order to allow Talend server and JasperServer to connect to the postgresql database. This is a basic pg_hba.conf, I encourage you to add stronger authentication.

We are done. Don’t forget to adjust the security groups like this :

Talend Server : allow 8080, allow 22
Postgresql Server : allow 5432, allow 22
Jasperserver : allow 80 (or 443 if https), allow 22

Okay, this stuff is fully working, you can test it.
But wait … that’s not the good way to do ! By using the elastic IPs to set up communication between each server/node, we just created a weird monster that makes the traffic going OUT of the cloud and going BACK INTO the cloud. Don’t forget you are paying for that. Look at this schema.

First solution

The best practice is to avoid using elastic IPs in order to set up network traffic between servers that are hosted inside the EC2 cloud. Instead, use EC2 internal adresses.
Ok, but … wait a minute.

How do I do to retrieve the internal address from inside EC2 ?

The solution rely on a poorly documented EC2 feature : when you query an ec2 public DNS server from inside EC2, you will be given back the corresponding internal IP address. Just what we need !!!!
For instance, if you query your ETL Server from your your Postgresql server, by using the famous host command, you will have :

You see what you have to do ? Replace all elastic IPs, except for your Talend client, by internal IPs. Like that, your internal data won’t leave the cloud, like below.

After using the internal addressing, the connexion screens will look like this :
Jasper server connexion screen : Postgresql database <===> Jasperserver

Talend server connexion screen : Talend server <===> Postgresql database

Second issue

Well, ok, we solved our first issue : using internal addresses between the ETL server and the Postgresql server. But, I can see two other issues :

Postgresql still does not accept DNS names in the pg_hba.conf ! Only IP addresses allowed. So We can’t ask Postgresql and pg_hba.conf to resolve the dns for us.
What if I decide to reboot the ETL server, or the Reporting server ? These internal adresses are nice but they are changing each time I reboot / restart server in EC2. Then, how to keep my Postgreqsl pg_hba.conf updated with frequently changing adresses ?

…not allowed …

Second solution

No, there is still no support for DNS entries in the pg_hba.conf. I know this is a long awaited feature, at least by me. But, unless I’m wrong (tell me), writing down a DNS name in pg_hba.conf won’t work and the server won’t start.
We need to find a way to update the pg_hba.conf with the last / current ec2 internal addresses corresponding to the ETL server and the Reporting server. Easy, we will use a bit of shell code here. This script will retrieve the internal IP Address for each server (ETL and JasperServer) by using the command host and will update this address in the pg_hba.conf by using some sed or awk. Then, by using a sighup, Postgresql server will apply the new address configuration.
Nothing complex, but the success rely on a good timing.

Note here : I created an ORCHESTRATOR, a specialized instance in EC2, to monitor all my servers. This orchestrator will run this kind of script as soon as it detects any change in the internal addressing schema. This ORCHESTRATOR will be detailed in a future article (I made several public presentations, and a lot of people seem interested …).
And the shell script. This shell asks for the internal address, then updates the corresponding line. For that, you must maintain your file in a tidy way : labels are needed.

################################ 
#                              # 

#      IP adress lookup        # 

#                              #  
################################ 
# POSTGRES (DATABASE) Server
# Public DNS : ec2-12-345-678-999.eu-west-1.compute.amazonaws.com 


# TALEND (ETL) Server     
ETL_SERVER=`host ec2-11-222-33-444.eu-west-1.compute.amazonaws.com | sed 's/.*has address //g'` 

# JASPER (BI & reports) Server    
JASPER_SERVER=`host ec2-22-33-444-555.eu-west-1.compute.amazonaws.com | sed 's/.*has address //g'` 



# Echoing all     
echo "" 

echo "################## EC2 Addresses Update ######################" 

echo "Will update EC2 Talend Server address with : " $ETL_SERVER 

echo "Will update EC2 Jasper Server address with : " $JASPER_SERVER 



echo "" 
# Find and replace line Talend Server 

TALEND_NB=`grep -n "Talend server connexion" /mnt/postgres/data/pg_hba.conf | cut -d":" -f1` 

TALEND_NB=$((TALEND_NB+1)) 

sed -i "$TALEND_NB s%.*%host    all         all         $ETL_SERVER/32      md5%" /mnt/postgres/data/pg_hba.conf 
# Find and replace line Jasper Server     
JASPER_NB=`grep -n "JasperServer connexion" /mnt/postgres/data/pg_hba.conf | cut -d":" -f1` 

JASPER_NB=$((JASPER_NB+1))     
sed -i "$JASPER_NB s%.*%host    all         all         $JASPER_SERVER/32      md5%" /mnt/postgres/data/pg_hba.conf

The end

Having a small (or even big) BI architecture up and running into EC2 is not a big deal. Having it properly set – in order not to pay extra fees – is something different and need some basic thinking before doing. The addressing issue which is technically simple, can have negative impact on your project if you don’t manage it from the start.

I will recommand any AWS / EC2 user (BI or not) to create their own admin tools and scripts, based on the various available APIs, in order to :

reduce reaction time,
be fully independent,
spare time (graphical tools are nice but need clicks, clicks and clicks …)

Some usefull links about AWS / EC2 documentation :

Feel free to contact me if this article is not clear enough.

Wordle, wordmap, word clouds … what’s in a name ?

2011-03-28T14:51:00.001-07:00

Hi all,

Wordmaps, wordles, word clouds … are pretty famous these days. But they are still “difficult” to generate : no api, often proprietary code, often only web based tools, difficulty to render good looking fonts ….

Last week, I was attending a client meeting and we showed some wordmaps of our own, created with R : great success. If you are involved into datamining or simply data visualization, these wordmaps / wordle are definitely a must have.

Here is a wordle I created using the excellent web site wordle.net, created by Jonathan Feinberg. Simply paste some of your data (previously prepared and formated) into text fields, press generate and the magic comes !

Wordmap based on the keywords used to reach this blog.

Matthias Oehler and I are currently working on a webservice aimed at creating wordmaps. The process will be : send a soap message with your data in it and you will get your png wordmap in return. Promising, hum ?

Stay in touch …

New Data cleansing / mining using Google Refine in the cloud with AWS

2011-03-24T04:56:00.001-07:00

Hi all,

As promised, an article about a really nice piece of software that will allow you to run data cleansing and data mining jobs with fun.

Let’s implement Google Refine in Amazon Web Services (aka “le cloud”).

Google refine ?

According to Google, “Google Refine is a power tool for working with messy data, cleaning it up, transforming it from one format into another, extending it with web services, and linking it to databases like Freebase.”

With Google Refine, it’s easy to load big datafiles and process this data : cell fusion, clustering, groups, adding key-values, transcoding, data modification / data customization with web service calls … Just imagine an Excel grid, but on steroïds.

Let’s go now …

As usual, you first need a valid AWS / EC2 account. Once it’s been done, you need an instance (a server). I recommend using a Fedora Core instance for Google Refine instead of a Ubuntu one. I’m a great fan of Ubuntu and use them in a lot of crucial apps, but I faced many issues with running Google Refine on top of a Ubuntu Lucid AMI (ram usage, freezing, jdk erratic behaviour). Please, choose instead the fedora one : amazon/fedora-8-x86_64-v1.14-std. Of course, let’s go with 64 bits and with an EBS volume attached to the instance in order to provide data consistency over time (everything not located on EBS is ephemeral).

I won’t detail how to create and start an instance on AWS, but here are the majors steps. You can easily go through these steps by using the Firefox pluging called Elastifox. Or, for the guys having muscles, use the AWS EC2 api, which can be donwloaded here.

Create a key pair and download the private key : this will give you SSH access to your instance.

Create a security group : create a dedicated security group for your instance and open the following ports : 22 for ssh and 3333 for Google Refine webservice.

Choose your instance : amazon/fedora-8-x86_64-v1.14-std is a good choice. This corresponds to AMI ami-1d042f69

Run your instance :
- Be carefull with the availability zone. This one is in USA but you can also place your instance in Ireland or in Asia. Be sure not to put yourself outlaw by placing sensitive data out of your safe harbor.
- Be sure you assign the key pair and the security group you created on steps 1 and 2.
- Choose an instance size. As I have plenty of money, I chose a m2.xlarge instance with plenty of ram.

SSH into your instance : you can use Putty, but don’t forget to convert the pem key (unix style private key) into ppk (windows style private key).

Create a volume and attach it to your newly running instance. This volume will be used to store Google Refine itself and all the data you will work with.
- You can create a volume by using Elasticfox and attach it to the instance. Below you can find the different prompts. I choose 100 Gigas but you can set it smaller.

Once the volume is created and attached to the instance, simply create a filesystem on it, using ext3 for instance : sudo mkfs.ext3 /dev/sdf
Create a mount point in /mnt : sudo mkdir /mnt/refine
Add mount point in /etc/fstab
Mount the newly created volume : sudo mount /mnt/refine

Download and install jdk 1.6
- sudo wget http://cds.sun.com/is-bin/INTERSHOP.enfinity/WFS/CDS-CDS_Developer-Site/en_US/-/USD/VerifyItem-Start/jdk-6u24-linux-x64.bin?BundledLineItemUUID=msmJ_hCxeGwAAAEuarYWpm05&OrderID=uCSJ_hCxvZsAAAEuWrYWpm05&ProductID=oSKJ_hCwOlYAAAEtBcoADqmS&FileName=/jdk-6u24-linux-x64.bin
- Install the jdk : sudo sh jdkxxxxx.bin
- Don’t forget to add jdk_home and path into your bash profile .
- Try your jdk by typing simply typing java in a shell.
Finally, assign an Elastic IP to your instance. This will be easier to connect to your instance and start using Google Refine. Once again, using ElasticFox will save you a lot of time. (of course you can assign an elastic IP before connecting with SSH, which is more logic I admit …).
Download and install Google Refine. This is really a no brainer …
- Go into /mnt/refine (that means goind into your EBS).
- Download Refine : sudo wget http://google-refine.googlecode.com/files/google-refine-2.0-r1836.tar.gz
- unzip and untar the archive :
  - sudo tar xzf google-refine-2.0-r1836.tar.gz
- Start Google Refine
  - sh refine –I 0.0.0.0 –m 8000M
  - That means : start refine, listen to all addresses and assign 8 Giga of memory. Hey, that’s what we need here when playing with data !
  - Google Refine starts ….
    Starting Google Refine at 'http://0.0.0.0:3333/'
    
    10:11:01.905 [refine_server] Starting Server bound to '0.0.0.0:3333' (0ms)
    10:11:01.906 [refine_server] Max memory size: 8000M (1ms)
    10:11:01.955 [refine_server] Initializing context: '/' from '/mnt/refine/google-refine-2.0/webapp' (49ms)
    10:11:03.288 [refine] Starting Google Refine 2.0 [r1836]... (1333ms)
    10:11:03.297 [FileProjectManager] Using workspace directory: /root/.local/share/google/refine (9ms)
    10:11:03.299 [FileProjectManager] Loading workspace: /root/.local/share/google/refine/workspace.json (2ms)

Time to play now !

Ok, most of the work is done. I hope the quick AWS EC2 walk thru is sufficient for most of you. In the other case, feel free to contact me.

Now let’s play with Google Refine. Simply open your browser and point to the IP address you assigned to your instance : http://your IP Adress:3333/

… and you are done !

Ok, I have some more time, let’s create a simple project. Simply choose a file in the Data File zone, name your project and provide some more informations about your file : separator, header, limit, auto detect value types …

After a short while, booom, your file is loaded and you have access to your data, ready to work on it.

Stay in touch for the next coming articles, I will show you how to fully leverage Google Refine and how to enrich your data with spectacular value added and services.

One last thing, you definitely need to go and read my friend’s blog about datamining. His name is Matthias Oehler (dataminer) and he is a kind of R and Google Refine wizard. You will learn a lot reading his articles as soon as his website will be open (from a few hours to a few days according to him …).

More informations

You can compile and deploy Google Refine into Google AppEngine, which is even a better solution.
You can find some screencast about Google Refine here.

Feel free to contact me if needed.

PageRank 4 and next coming articles ...

2011-03-23T12:31:00.000-07:00

Hi all,

First, I want to thank everybody (well, people reading that blog) for my pagerank 4.

It's been a long time since I wrote a post here. As you may remember, I'm deeply involved into a datamining startup as CTO.

Be prepared because I'm back in a short while with a lot of articles / experiences and feed back about :

Datamining
Google APIs
Google Refine
BI and Cloud computing with Amazon Web Services
Json, Soap and BI in cloud environment.

Vincent

Stuff The Internet Says On Scalability For January 28, 2011

2011-01-31T13:27:00.000-08:00

Stuff The Internet Says On Scalability For January 28, 2011: "

Submitted for your reading pleasure...

Something we get to say more often than you might expect - funny NoSQL comic: How to Write a CV (SFW)

Playtomic shows hows how to handle over 300 million events per day, in real time, on a budget.

More Speed, at $80,000 a Millisecond. Does latency matter? Oh yes...“On the Chicago to New York route in the US, three milliseconds can mean the difference between US$2,000 a month and US$250,000 a month.”

Quotable Quotes
- @jkalucki: Throwing 1,920 CPUs and 4TB of RAM at an annoyance, as you do. @jointheflock

In-Memory analytics. 2011 the real take off ?

2011-01-02T05:10:00.001-08:00

Hi all, and happy new year to everybody.

Are traditional databases and data centers going to change in order to be able to deliver the always increasing demand for performance and real time ? For BI and real time data analysis, I believe so. The traditional data path (with its I/Os challenge) has now to evolve after 30 years almost untouched.

In-Memory Analytics (in conjunction with cloud computing or infrastructure like Hive) : the next big thing on 2011 ?

Read more here, an interesting article from the eCRMguide website. Comments (below article) are also precious.

A real time database

New Pentaho Kettle book

2010-12-20T02:26:00.001-08:00

Hi all,

Last year, at the same time, I wrote and article about the first Pentaho book published. Simply called Pentaho Solutions, this book covers the basics of datawarehousing and Pentaho tools. You can find the original article here and order this valuable book here.

Recently, I received my own review copy of a long awaited Pentaho book : Pentaho Kettle Solutions – Building Open Source ETL Solutions with Pentaho Data Integration.

Thanks to Roland, Matt and Jos for sending me this new book.

ISBN: 978-0-470-63517-9

Matt Casters is the Pentaho Chief of Data Integration and Kettle founder (Kettle’s dad, it’s him). Have a look to his prolific blog here.

Roland is an IT expert, ranging from web application development and business process analysis to business intelligence. He co-authored the MySQL Cluster 5.1 Certification Study Guide. Please, have a look to his blog.

Jos is a BI expert with more than 15 years of experience. He created Tholis Consulting and is also covering BI developments for the Dutch Database Magazine.

What is this book about ?

The first book, Pentaho Solutions, was aimed at discovering the basics of BI and Pentaho usage. Now, with this new book, we go deeper into hardcode dataprocessing and datawarehousing using Kettle. But it is not exclusively focused on Kettle : a strong emphasis is placed on data processing basics, technics and theory (Codd vs Kimbal …). Reading this book will get you to the next upper level on these two topics :

Data processing and how to build / feed a datawarehouse,
Kettle development, customization and advanced usage.

Book summary

Introduction
- What’s an ETL and what are Kettle key concepts
- How to install Kettle and configure it
- Real life example : Sakila datawarehouse
ETL and ETL subsystems
- What are the famous ETL Subsystems (Kimabal). A very detailed and inspiring chapter.
- Extraction
- Cleansing and conforming
- Handling dimension tables
- Loading facts tables
- Working with OLAP data.
Management and deployment
- Typical ETL development lifecycle. A must read here !
- Scheduling and monitoring
- Versioning
- Lineage and auditing.
Performance and scalability
- Performance tuning. Here again, a must read that will give you precious information on how to make your Kettle set up reach the hills of performance and stability.
- Parallelization, clustering and Partitioning : my favorite. You have big data and / or strong constraints ? Think parallel and start building your own Kettle cluster / parallel set up. As to me, the best chapter ever written on this topic, all ETLs included.
- Dynamic clustering in the cloud. Once again my favorite. You all know my passion for Cloud Computing ! Very technical article, you need real experience on using AWS tools and APIs.
Advanced topics
- Data Vault Management : interesting concept. You will learn about Data Vault and discover this mixed (Codd with 3NF / Kimbal with star schema) approach in detail.
- Handling complex data formats.
- Web Services. I love that one too ! More and more datawarehouses are now feeded by using web services. Learn how to feed yours by leveraging Kettle.
- Kettle integration
- Extending Kettle. Yummy ! If, like me, you created your own Kettle plugins or want to, this chapter is a must read. Java programing experience is needed.
The Kettle Ecosystem
- Kettle enterprise edition features : comparative matrix.
- Built in variables and properties reference : a must read in order to be aware of Kettle internals, and be able to create fully automatised / self sufficient jobs.

My opinion

This book is a fantastic concentration of knowledge. You will learn from ETL basics, advanced topics, performance management, Kettle development and cloud dataprocessing. Matt, Roland and Jos met a risky challenge : writing a book that do the splits from basic knowledge to high level technics while staying focused on how to use Kettle to solve actual and concrete data problems.

They succeeded.

This book is now sitting on my reference BI shelf, it entered my personal BI Book Hall of Fame.

Talend : Customer feed back

2010-12-14T02:56:00.001-08:00

Hi all,
Here is an interview I gave to Talend in order to explain what I did as Architect for Score-MD.
This article is written in french, sorry. I can translate if needed.
The key points are :

Cloud environment (AWS) with home brew orchestrator,
Heterogeneous data sources and large data volume,
Flexibility, scalability and cost management (by leveraging all cloud features)

Update : Amazon Simple DB data loading with Kettle

2010-12-06T13:44:00.001-08:00

Hi all,
This is just an update for this old article : Amazon Simple DB data loading with Kettle.
The files are now back and you can download them :

You can find the Kettle transformation HERE.
You can find the Jscript HERE.
You can find my little flat file HERE.

Just discovered that … love it !

2010-11-30T05:27:00.001-08:00

commandlinefu.com

No excuse for not being a shell guru now.

Shell rulez …

Postgresql dumps and storage on S3 : the sequel … using dynamic temp EBS

2010-11-26T07:23:00.001-08:00

Hi all,
Here you can find a new and customized approach for Postgresql dumps and storage on S3. Based on my previous post, this script uses the EC2 API and has new features and will :

Create an EBS volume on EC2,
Prepare the EBS volume filesystem and mount it on you database server (in EC2 cloud),
Run the postgresql dump utility and store the dumps on the EBS volume,
Rend the dump files to S3, into the bucket you want,
Manage dump file collections in the bucket : clean / delete the previous dump files according to the retention date/period,
Un-mount and delete the EBS volume, like that you no longer pay for something you don’t need / don’t use.

Here is the script descriptions :

Create the temporary EBS volume,
- VOLUME=`ec2-create-volume --size $VOL_SIZE --region $REGION -z $AVAIL_ZONE | cut -f2`
Building the filesystem and mounting as /mnt/something
- STATE=`ec2-attach-volume $VOLUME --instance $INSTANCE --device $DEVICE --region $REGION | cut -f5`
Start database dump
- su - postgres -c "$PG_DUMP $BASE | gzip | split -b $SPLITS - $DUMP_FILE"
Send the dump files to S3 with s3cmd
- s3cmd -p put $DUMP_FILE* $S3ENDPOINT
Detaching the EBS Volume
- STATE=`ec2-detach-volume $VOLUME --region $REGION | cut -f5`
Clean up the historical dump files on S3, based on
- for FILENAME in `$S3PUT ls $S3ENDPOINT | cut -d":" -f3` ====> delete

After running, the temporary EBS volume is no longer attached to the server and delete can be performed.

Below, you can find the script. Please note :

use database_name = bucket name, for convenience. Otherwise you will have to modify the script.
use database_name as parameter $1.
the final routine (lookup on s3 bucket files for cleanup) is a modified version of the one found here.
Be sure you have a working installation for the EC2 API (+path)
Variables to check :
- Java path
- Availability zone (EC2)
- Region (EC2)
- Device (something from sdf to sdxxxx)
- Dump dir : will be hosted on the temp EBS once mounted on /mnt/something
- Volume size : the size of the volume to create. Be sure you have enough space for your dumps
- Splits : size for multi dump files.

#!/bin/bash
######################################################
#                                                    #
#        POSTGRES startup script                     #
# Author : Vincent Teyssier                          #
# Date   : 20/11/2010                                #
#                                                    #
#####################################################
#
# General variables
export JAVA_HOME="/mnt/postgres/jdk1.6.0_21"
export EC2_HOME="/mnt/postgres/ec2-api-tools-1.3-53907"
export EC2_PRIVATE_KEY="/mnt/postgres/ec2-api-tools-1.3-53907/keys/pk-file.pem"
export EC2_CERT="/mnt/postgres/ec2-api-tools-1.3-53907/keys/cert-file.pem"
export JDK_HOME="${JAVA_HOME}"
export PATH="${JAVA_HOME}/bin:${PATH}"
export PATH="$PATH:$EC2_HOME/bin"

# EC2 Variables
AVAIL_ZONE="eu-west-1a"
REGION="eu-west-1"
INSTANCE="your EC2 instance id"
DEVICE="/dev/sdh"
DUMP_DIR="/mnt/postgres/dumps"
VOL_SIZE="100" # in mb

# Dump variables
PG_DUMP="/usr/lib/postgresql/8.4/bin/pg_dump"
S3PUT="/mnt/postgres/s3tools/s3cmd-1.0.0-rc1/s3cmd"
BASE=$1
SPLITS="70m"
DUMP_TIME=`date +"%Y%m%d"`
DUMP_FILE="$DUMP_DIR/$DUMP_TIME.gz"
S3ENDPOINT="s3://postgresql-dumps/pilotroi/$BASE/"

echo "***********************************************"
echo "*    DUMP/ BACKUP PROCESS                     *"
echo "*    Starting at :" `date` 
echo "***********************************************"

# Create volume
echo ""
echo "Create Volume"
VOLUME=`ec2-create-volume  --size $VOL_SIZE --region $REGION -z $AVAIL_ZONE | cut -f2`
while ! ec2-describe-volumes $VOLUME --region $REGION | grep -q available; do sleep 1; done
echo "Created volume " $VOLUME "with size of "  $VOL_SIZE " Gb"

# Attaching volume
echo "***********************************************"
echo "Now attaching volume"
STATE=`ec2-attach-volume $VOLUME --instance $INSTANCE --device $DEVICE --region $REGION | cut -f5`
while ! ec2-describe-volumes $VOLUME --region $REGION | grep -q attached; do sleep 1; done
echo "Volume " $VOLUME "has state : " $STATE

# Building filesystemm and mounting
echo "***********************************************"
echo "Now building filesystem"
while [ ! -e $DEVICE ]; do echo -n .; sleep 1; done
sudo mkfs.ext3 -F $DEVICE
if [ ! -d $DUMP_DIR ]
then
sudo mkdir $DUMP_DIR
fi
sudo mount $DEVICE $DUMP_DIR
sudo chown postgres:postgres $DUMP_DIR
sudo chmod 777 $DUMP_DIR
su - postgres -c "touch file.txt"
echo "Volume " $VOLUME "mounted on " $DUMP_DIR


# Dumping
echo "**********************************************"
echo "Dump started at " `date`
echo "su - postgres -c $PG_DUMP $BASE | gzip | split -b $SPLITS - $DUMP_FILE"
su - postgres -c "$PG_DUMP $BASE | gzip | split -b $SPLITS - $DUMP_FILE"
echo "Dump ended at " `date`
# Sending
echo "**********************************************"
echo "Send to S3 started at " `date`
$S3PUT -p put $DUMP_FILE* $S3ENDPOINT
echo "Send ended at " `date`
#echo "**********************************************"
#echo "Deleting local dump files"
#rm $DUMP_DIR/$BASE.gz*


# Cleaning all
sudo umount $DUMP_DIR
echo "$DUMP_DIR unmounted"
sudo rm -Rf $DUMP_DIR

# Detaching volume
echo "***********************************************"
echo "Now detaching volume"
STATE=`ec2-detach-volume $VOLUME --region $REGION | cut -f5`
while ! ec2-describe-volumes $VOLUME --region $REGION | grep -q available; do sleep 1; done
echo "Volume $VOLUME has now state : $STATE and can be deleted"
ec2-delete-volume $VOLUME --region $REGION
echo "Volume $VOLUME currently deleting"
echo "Database dump is finished at : " `date`
echo "***********************************************"
echo "Cleanup process"
echo "Starting at :" `date`

LIMIT=`date --date="5 day ago" +"%Y%m%d"`
echo $LIMIT
echo `date '+%F %T'` - Timestamp of 5 days ago: $LIMIT
echo `date '+%F %T'` - Getting the list of available backups
TOTAL=0
for FILENAME in `$S3PUT ls $S3ENDPOINT | cut -d":" -f3`; do
if [[ $FILENAME =~ ([0-9]*)\.gz* ]]
then
echo ${BASH_REMATCH[1]}
TIMESTAMP=${BASH_REMATCH[1]}
echo `date '+%F %T'` - Reading metadata of: $FILENAME
echo -e "\tFilename: $FILENAME"
echo -e "\tTimestamp: $TIMESTAMP"
if [[ $TIMESTAMP -le $LIMIT ]]; then
let "TOTAL=TOTAL+1"
echo -e "\tResult: Backup deleted\n"
$S3PUT del "s3:$FILENAME"
else
echo -e "\tResult: Backup keeped\n"
fi
fi
done
echo `date '+%F %T'` - $TOTAL old backups removed

It’s working nicely on all my postgres servers. I’m currently finalizing a custom script for retrieving and re-creating a database back from the dump file collection.

Feel free to contact me for any question.

Postgresql dumps and storage on S3

2010-11-22T02:18:00.001-08:00

Hi all,
I recently had to manage all my backups from Postgresql instances on the cloud. For all these cloud-based backup jobs, I’m using a three way approach. Maybe you think it’s a bit redundant, but I don’t like surprises.

Database dump to S3 : daily, depends on criticity …
EC2 instance snapshot : daily or weekly, depends … I like to know I can keep several generation of instance image, even if I do not change them quite often now …
EBS volume snapshot : hourly or daily, depends on criticity,

Today, I want to share with you what I did as first backup process : database dump to S3. Of course, you need to have knowledge of Amazon S3 (as well as an account) for that. In the next coming articles, I will show you what I created in order to automate and manage all EC2 instances & volume snapshots.
For the moment let’s go back to db dump to s3. My process is a simple shell script that will :

Connect to Postgresql,
Start a database dump (database name is passed as script argument),
Split the dump files into several pieces (chunk size is passed as script argument),
Send the dump files into S3.

Note : your postgresql server (from which you want to dump) does not have to be hosted on AWS/EC2. This process also works with on premises architecture.

Tools of the trade : pgdump and S3CMD

I’m using a wonderful tool called s3cmd, you can find it here. This tool allows you to send, retrieve and manage data on Amazon S3. It also offers bucket management, GPG encryption and https transfer. A very good command line tool that will find its place in your EC2 toolbox.
Installing s3cmd is quite easy and, when using s3cmd –config you will be prompted with your S3 account credentials and other params. You can later retrieve this data into the file called .s3cmd.
The commands are quite simple :

Send file to S3 : s3cmd put file_to_send your_s3_endpoint
List buckets : s3cmd ls
List content of a bucket : s3cmd ls “Bucket_name” : Bucket_name will have the form : s3://bucket_name
Retrieve a file from a bucket : s3cmd get s3://“Bucket_name”/”file_name”.

As you can see, really easy usage and fits perfectly in a shell script.

Pg_dump is the regular backup client application for Posgresql database. Its syntax, simplified here, is : pg_dump “dbname” > outfile. You can learn more about pg_dump here. For this current example, I will use the following command :

pg_dump db_name | gzip | split -b 50m – dump_file
- pg_dump : the command,
- db_name : the db name you want to dump,
- gzip : create gzipped archives,
- split : split the dump into several pieces,
- -b 50m : split size = 50 Mo
- dump_file : the dump file (the split option will create a collection with extensions like gzaa, gzab …).
Add –v if you want a verbose log.

Process prerequisites

First, I created an internal account for my Postgresql database. This account has only rights to connect from the database server itself (or localhost). All passwords were disabled for this account, only “trust” is present in pg_hba.conf. Select rights were also granted.
Then, I created a dedicated directory on the database server to hold the dump files. This directory will only hold the dump files for very limited time, they will be deleted after been pushed on S3 with s3cmd. I recommend to store these temp dump files on a separate and dedicated EBS volume.
Last, we need a minimum setup on S3 side. Create the appropriate bucket hierarchy you need. Mine is quite simple for this backup process :

1st level : /postgresql-dumps
- 2nd level : product name : “pilotroi”, the product name of my company (we have several products)
  - 2nd level : /database name, one bucket by database. This is the second parameter I will pass to the shell script. For flexibility, I use the exact same name as for the postgres database I wan to dump. This allows me to build my bucket endpoint like :
    - s3://postgresql-dumps/pilotroi/$BASE/"

Custom script

Below is my custom script. A lot of “echo”, hum ? As I said, I don’t like surprises and prefer having verbose logfiles. This script is cronted and needs two parameters :

Database name : the name of the db you need to dump.
Splits : the size of the chunks you want to generate. For instance, 50m is for generating files having a size of 50 Mo max.

Call the script like that, using two params ($1 and $2) : sh s3_pg_dump.sh client_base 50m

#!/bin/bash

################################## 
#                                # 
# POSTGRES DUMPS FOR PILOTROI    # 
# Vincent Teyssier               # 
# 19/11/2010                     # 
#                                # 
##################################

echo "******************************************************" 
echo "Database dump is starting at : " `date` 
echo "******************************************************"

PG_DUMP="/usr/lib/postgresql/8.4/bin/pg_dump" 
S3PUT="/mnt/postgres/s3tools/s3cmd-1.0.0-rc1/s3cmd"

BASE=$1 
SPLITS=$2 
DUMP_FILE="/mnt/postgres/dumps/$BASE.gz" 
S3ENDPOINT="s3://postgresql-dumps/pilotroi/$BASE/"

echo "*****************************" 
echo "Parameters : " 
echo "Base : " $BASE 
echo "Splits : " $SPLITS 
echo "S3 Endpoint : " $S3ENDPOINT 
echo "*****************************"

echo "Dump started at " `date` 
su - postgres -c "$PG_DUMP $BASE | gzip | split -b $SPLITS - $DUMP_FILE" 
echo "Dump ended at " `date` 
echo "*****************************" 
echo "Send to S3 started at " `date` 
$S3PUT put $DUMP_FILE* $S3ENDPOINT 
echo "Send ended at " `date` 
echo "*****************************" 
echo "Deleting local dump files" 
rm /mnt/postgres/dumps/$BASE.gz*

echo "******************************************************" 
echo "Database dump is finished at : " `date` 
echo "******************************************************"

Process output

This script file will produce the following output.

Data is now on S3

Using S3 Explorer Firefox plugin, you can see all our files are now stored in their dedicated bucket on S3.

As usual, drop me a message to learn more if needed.

Open source Datamining with R

2010-11-09T04:36:00.001-08:00

Hi all,

Today, I want to give you a very valuable link. You maybe know I’m currently using cloud architectures, map/reduce … etc … but I’m also implementing open source datamining tools like RapidMiner and the famous R.

You can find here a very interesting link – I mean a goldmine – about THE french R specialist : Romain François. His blog contains anything you want to know about R : from implementation to advanced features and analytics.

He recently gave me some precious advices about RProtobuf custom compilation.

Enjoy our visit.

Meeting with Brian Gentile (CEO – Jaspersoft) in Paris

2010-11-03T04:05:00.001-07:00

Hi all,

Yesterday, I had the pleasure to meet Brian Gentile, Jaspersoft CEO, in Paris. We spoke during 1h30 around a coffee about BI, french market, new trends, cloud computing, etc … and of course about JasperSoft (October 7, 2010, Jaspersoft announced JasperReports Server Professional Edition).

It was delightful to speak with someone having a true 360° vision of our industry, having no limits in imagination and providing elegant yet realistic / pragmatic visions of the future. Visions are something really important for me and my way of doing business. I’m often sad to see we have little visions in France …

I’m not Jaspersoft spokesman, but I can say the following topics are - right now – part of their technology roadmap. And that’s really exciting !

Hive,
Distributed data processing with Hadoop, map / reduce …,
Cloud computing,
Accurate geo location features, using best of breed APIs,
Data analysis with interactions with – to name one – R,
Large scale analytics : Unstructured data to predictive,
Elegant mobile BI and real time BI for, as Brian Gentile says, “analytics everywhere”.

Thanks to Naoual Mameche – EMEA Account Manager – for the meeting organization.

You can read more from Brian Gentile on his blog called The Open Book on BI.

Shooot, I just turned 37 y/o and my starting baldness is gaining ground …

As a reminder, I implemented Jaspersoft for Score-MD this year (ASP style datamining / scoring start-up for which I’m currently working). We are using a cloud infrastructure on EC2 and we are using Jaspersoft for BI & reporting features delivered to our final customers (some are ww and 24/24).

Also implemented in Score-MD, full cloud : Talend (TIS), PostgreSQL, R, Rapid Miner and Protocol Buffers.

Modified Firefox ElasticFox plugin (handling micro instances)

2010-10-14T07:05:00.001-07:00

Hi all,
ElasticFox is a wonderful plugin for you guys, AWS and Firefox geeks.
We all know AWS added a new instance size recently : micro. Micro instances are really small setups with approx 650 Mo RAM and are very useful to build small servers, controllers, dns, ftps …
Unfortunately I’m afraid there is no “micro” instance size in ElasticFox so far, have a look below.

That’s why I did my own hack. Note : this is a personal hack, not a new version of ElasticFox. Now you can launch a new instance using “micro” size. This is working perfectly for me (beware using micro instances with Ubuntu Lucid : issues with the fstab as described in http://alestic.com/).
Please let me know if this hack is usefull and working for you as well.
You can download this Elasticfox plugin here.

The new micro instance size

Easy spatial reporting with jscript and JasperSoft

2010-10-11T15:22:00.001-07:00

Hi all,
Today, a quick post to demonstrate how to create some spatial reporting using simple javascript and Jasper server. Hey, I was about to forgot … everything is hosted on Amazon Cloud Computing of course ;)).
Here is the scenario :

Extract data from database : french departments and a simple KPI : total number of contacts.
Create an XML file for the extracted data because we don’t want live queries running on the database. Regarding the limited amount for data we need, it’s better to query a simple xml file here.
Add spatial coordinates (GPS coordinates for each french department) using Google API or Yahoo API.
Implement some javascript to :

Call a google map,
Place markers on the map, for each french department.
Add some html to display the values for each map marker (number of contacts).

Data files

Ok, let’s go : extract data and generate XML file containing our data. Easy, here is the output using internal xml functions of Posgresql. Below we have an excerpt showing the structure :

name : number for french department (from 01 to 95)
nb_conctacts : our main KPI, the number of contacts
lat and lng : latitude and longitude.

I will explain, in a future post, how to geocode data using different APIs. For now, consider we have spatial data coming out of the database.
I will create two files : one for Paris and suburbs, the other one for whole french territory. Like this I will be able to choose between a focus on Paris (economic center) or the rest of the country. The names are 'cp_stats-idf.xml' (Paris and suburbs) and 'cp_stats-province.xml' (rest of the country).
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<markers>
    <marker>
        <name>01</name>
        <nb_contacts>39600</nb_contacts>
        <lat>46.24757</lat>
        <lng>5.1307683</lng>
    </marker>
    <marker>
        <name>02</name>
        <nb_contacts>25507</nb_contacts>
        <lat>49.47692</lat>
        <lng>3.4417367</lng>
    </marker>
    <marker>
        <name>03</name>
        <nb_contacts>14143</nb_contacts>
        <lat>46.311554</lat>
        <lng>3.4167655</lng>
    </marker>
    <marker>
        <name>04</name>
        <nb_contacts>14814</nb_contacts>
        <lat>44.077873</lat>
        <lng>6.2375946</lng>
    </marker>
<markers>

Coding

Now, let’s use some Javascript. First, we need the jQuery library, written by John Resig (http://jquery.com/). Download it and put the library in your project directory.
Now, let’s code the second Javascript file, the one that will create the Google Map and place the markers. Here is the code, I called it ‘google_put_markers.js’.
$(document).ready(function() {
$("#map").css({
        height: 500,
        width: 700
    });
var LatLngCoord= new google.maps.LatLng(48.859112, 2.346800);
MAP.init('#map', LatLngCoord, 11);
$("#show_PAR").click(function(e){
        MAP.PutMarkers('cp_stats-idf.xml');
});
    $("#show_PRO").click(function(e){
          MAP.PutMarkers('cp_stats-province.xml');
});
});
var markersArray = [];
function clearOverlays() {
if (markersArray) {
    for (i in markersArray) {
      markersArray[i].setMap(null);
    }
}
}
var MAP = {
map: null,
    bounds: null
}
MAP.init = function(selector, latLng, zoom) {
var myOptions = {
    zoom:zoom,
    center: latLng,
    mapTypeId: google.maps.MapTypeId.ROADMAP
}
this.map = new google.maps.Map($(selector)[0], myOptions);
    this.bounds = new google.maps.LatLngBounds();
}
MAP.PutMarkers= function(filename) {
clearOverlays();
    $.get(filename, function(xml){
        $(xml).find("marker").each(function(){
            var name = $(this).find('name').text();
            var address = $(this).find(nb_contacts).text();
            var lat = $(this).find('lat').text();
            var lng = $(this).find('lng').text();
            var point = new google.maps.LatLng(parseFloat(lat),parseFloat(lng));
            MAP.bounds.extend(point);
            var image = 'logo.png';
            var marker = new google.maps.Marker({
                position: point,
                map: MAP.map,
                title:"Statistiques",
                icon: image
            });
            var infoWindow = new google.maps.InfoWindow();
            var html='<strong>Département : '+name+'</strong.><br />'+address + ' contacts';
            markersArray.push(marker);
            google.maps.event.addListener(marker, 'click', function() {
                infoWindow.setContent(html);
                infoWindow.open(MYMAP.map, marker);
            });
            MAP.map.fitBounds(MAP.bounds);
        });
    });
}
As you can see, we first create a map, centered on France / Paris. We have several functions for cleaning the map (when changing point of view between regional and Paris), placing markers (two buttons) on that map and displaying infowindows.

HTML

Ok, almost done. Now we need a simple html file to put everything in it. Here is the file. As you can see, I created two buttons : one for displaying the markers for the whole french territory, and the other for displaying only markers for the core economic center (Paris and suburb). I call this file ‘display_geostats.html’.
<!DOCTYPE html>
<html>
<head>
<title>Google Maps</title>
<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>
<script type="text/javascript" src="js/google_put_markers.js"></script>
<script type="text/javascript" src="js/jquery-1.4.1.min.js"></script>
</head>
<body><div id="map"></div>
<div ><input type="button" id="show_PAR" value="Paris et region parisienne" /><input type="button" id="show_PRO" value="Province" /></div>
<div ></div>
</body>
</html>

Last checking

Ok, this is what we should have now : two XML data files, a javascript directory (called typically ‘js’) containing two files (the jquery lib and the file for displaying the markers I called ‘google_put_markers.js’), and the logo file (png). Below the snapshot of my setup.

Let’s test it

Ok, now let’s open the html file. You should have something like below. By default, the we can see the view called ‘Province’ which means ‘the whole country’ (pic 1). Each french department has a marker (on this map the marker represents the logo of the company I’m currently working for). If we click one of this marker, we will display the typical Google Map html box with the KPI called ‘number of contacts” in it (pic2).
Ps : better results with Firefox …

Pic 1 : the whole country

Pic 2 : displaying the content of a marker

Now let’s click on the button ‘Paris et region parisienne’ (means Paris and the suburbs) and we will display a different map : a zoom on Paris and new markers for this region.

JasperSoft / JasperServer integration

Ok, now we need to implement everything into JasperServer. Easy, deploy the whole directory we created above (html renamed into jsp file, data files, js directory with the two javascript files). Create the report like you would do for any other report and then you are ready to display it inside your JasperServer portal.
Sorry, I can’t give you the portal address … ;)

Summary

This was a very simple (and short) example on how to :

generate maps with Google Maps,
Place markers on this map
Display dynamic information (coming from db) inside the markers
Put everything, simply, into JasperSoft portal.

In the next future, I will code more dynamic routines allowing to display icons inside the infowindows and adding colorization / dynamic formatting …
Please feel free to contact me for deepest explanations or more details.
Next article to come will be about using a dozen lines of java to run geocoding lookups with Google or Yahoo (quickest !).

News !!

2010-09-17T01:26:00.000-07:00

Hi all,

Ive been quite busy since June. Sorry for that.
I'm currently participating in building a datamining company. We offer direct marketing scoring and segmentation in asp mode.
Quite a lot of work here.

Our architecture is :
- Postgresql (around 1To)
- JasperSoft
- Talend (TIS)
- R, RapidMiner
- Ubuntu.

I have done a cloud implementation on AWS. Works really fine, and really a good thing to manage IT cost as expenses (no CAPEX, only OPEX). 4000 $ per month for the whole thing since I precisely manage the uptime and downtine dynamically depending on the workload and processing windows.
Today, we are in production and have 8 more clients to implement before 31/12/2010.

We did a lot of open source as you see, and we had to create a lot of programs, plugins etc ...
I have, in stock, enough material to write 100 articles here !
I promise I will soon find some time to share all this knowledge with you.

Best regards,

Data replication with Postgresql and Slony

2010-05-18T08:54:00.001-07:00

Hi all,
I have been working on data replication around Postgresql. For that purpose, I used the famous Slony replication engine. Very nice piece of work but a cruel lack of documentation and how to. So, here is mine. First I want to say that my work was inspired after reading a similar article called “PostgreSQL, Slony-I, pgadmin, win32, First time config / installation” found on ChiChing means blog.

The tools

For this tutorial, you need your favourite linux box, running a Postgresql database and Slony installed. I encourage you to use the Postgres Application Stack builder which is a ”download and installation wizard combined with a set of pre-configured packages to complement PostgreSQL's one-click installers on Windows, Mac and Linux.” Very nice installer that will take care of all the deployment process. On top of that, you will also use your trusted pgadmin because my tutorial is about setting up replication by mainly using the gui (we’ll see later for the script mode …).
Of course, you need to be root.

Stack installer main screen

Database setup

For this tutorial, you want a source table – Fact_Table in Database SOURCE and Schema A - to be replicated into a target table – Fact_Table in Database TARGET and Schema A (same schema name). For the moment, I’m still having troubles to replicate data into a target schema with different name.
If you don’t have a table ready for that tutorial, here is a code for creating a small and very stupid fact table. Any table you want to replicate needs to have a unique constraint (create a primary key and go on). Create it on SOURCE and TARGET database.

CREATE TABLE “Schema_A'”.”Fact_Table”
(
“ID” bigint NOT NULL,
“DATE” date,
“PRODUCT” chararcter(100),
“PRICE” double precision,
“Quantity” double precision,
CONSTRAINT “PK_FACTS” PRIMARY KEY (“ID”)
)

In this tutorial, I assume the following :

You want to replicate data from database=SOURCE, schema=Schema_A and table=Fact_Table to database=TARGET, schema=Schema_A and table=Fact_Table.
You use the built in postgresql user
Both source and target schema / tables have been previously created. Slony can manage DDL change / propagation during replication but this will be explained in a further article.

Setting up a simple replication process with pgadmin III

First create 2 databases, one called SOURCE and the other called TARGET. The SOURCE will be the Master, while the TARGET will be the Slave. Right click on database and choose “New Database” (see below). Once done, you will have 2 more dbs in your object browser (below).

Now it is time to create the replication objects : everything has to be done into the Replication items inside the two newly created databases. The thery is simple : the two replication objects need references to each other, have a look below :

1 - Create base replication objects (screen caps below)

For the Master :

Right click on SOURCE: Replication and choose "New Slony-I Cluster"
Join existing cluster : Do not check this !
Cluster name : choose a name, here it is : REP_CLUSTER
Local Node Left : 1
Local Node Right : Master Node
Admin Node Left : 99
Admin Node Right : Admin Node
Press OK.

For the Slave :

Right click on TARGET : Replication and choose "New Slony-I Cluster"
Join existing cluster : Has to be checked
Server : localhost
Database : SOURCE
Cluster name : REP_CLUSTER (should be automatically recognized)
Local Node Left : 10
Local Node Right : Slave Node
Admin Node : 99 - Admin Node
Press OK.

Master Node Slave Node

2 – Create path setup (screen caps below)

For the Master :

Right click on SOURCE : Replication : sample : Nodes : Master Node : Path and choose New path
Server : 10 - Slave Node
Connect Info : host=localhost port=5432 user=postgres password=yourpassword dbname=TARGET
Conn retry : 10
Press OK.

For the Slave :

Right click on TARGET : Replication : sample : Nodes : Slave Node : Path and choose New path
Server : 1 - Master Node
Connect Info : host=localhost port=5432 user=postgres password=yourpassword dbname=SOURCE
Conn retry : 10
Press OK.

Path for Master Node Path for Slave Node

3 – Define replication set (screen caps below)

Right click on db SOURCE : Replication : REP_CLUSTER: Replication Sets and choose new Replication Set
ID : 1
Comment : Replication Set 1

Now we choose the table we want to be replicated from SOURCE to TARGET.

Right click on Replication Sets : sample set : Tables and choose New tables
Table : Any table of your choice
ID : 1
Index : Auto filled when choosing table. That’s important to have a unique constraint on the table you want to replicate (primary key for instance).
Comment : a nice comment
Press OK.

3 – Create a new Subscription (screen caps below)

Right click on SOURCE : Replication : sample : Replication Sets : sample set : Subscription and choose New subscription.
Origin : write “1”
Provider : Choose “1 - Master Node”
Receiver : Choose “10 - Slave Node”
Can forward : Keep it unchecked
Press OK.

4 – Object browser overview

Replication object for the Master Replication object for the Slave

Well, we are done with the database setup using pgAdmin III. Now it’s time to … start the replication !

Start the replication

Now it’s time to write some linux commands. Go to /opt/PostgreSQL/8.3/bin (or any other location depending on your linux flavour and / or Postgresql installation path). You will find there some interesting scripts. Have a look to slon. This is the slony daemon you will start twice : one for the MASTER, the other for the SLAVE.
Note : if you were setting up replication between two physical servers, you would have to start slon on each machine.
slon syntax is quite simple, here it is :
slon [Options] [ClusterName] [ConnexionInfos]
For the Master :

./slon –d 1 REP_CLUSTER “user=postgres password=vincentt host=localhost port=5432 dbname=SOURCE”

For the Slave :

./slon –d 1 REP_CLUSTER “user=postgres password=vincentt host=localhost port=5432 dbname=TARGET”

The command line output is like below, for the Master :
And for the Slave, a bit different :
We can see we have a successfull truncate table (on the target Fact_Table, which was empty). We also have a “copy set 1 done” which means our replication is running fine.
Let’s have a look on the target table to see if everything went fine ….
Yeeeeah, it went fine : our Source data is here in the Target table. Now let’s add some data into the Source table …
And let’s check on the Target db if the replication did the job we are expecting …
… yes Sir, it worked !! You can also check by reading the slon command line output.
You are now ready to create your own custom replication scripts using Slony.
I hope this tutorial helped you. Keep me in touch or contact me if you need more informations.

A quick ‘n cheap geocoding process using Talend, Soap and web service …

2010-05-05T06:07:00.001-07:00

Hi all,
Today, let’s play with Talend Open Studio (TOS). Here is a quick and cheap way to process ip adress geocoding with Talend, by using web service call. For this purpose, I will call an ip lookup service provided by ippages.com. Using the free version, you will be able to send 20000 ip geocoding queries, that’s good.
First, let’s design our job. I use a tWebServiceInput step, configured like below.

As you can see, we have to tell this step where the WSDL is. In that case, the web service interface is : http://www.ippages.com/soap2008/lookupserver.php?wsdl. Then, don’t forget to call the appropriate method : callshowmyip_lookup. Finally, the web service is waiting for 7 parameters : you put the ip adress on the first one and – for instance – the country code in the fourth one. Make sure you will store the webservice answer into a dedicated row (for me it is named “line” and your tWebServiceInput step is now ready.
The ippage web service will send you back an XML answer. You will have to manage / process this XML.
Now you can export your ip geocoding data into a new xml file, like in my simple example. Or, better, you can use a tExtractXMLField to parse – using XPATH - your XML and distribute the data you need for any further step. Your XPATH code will start with \root\row\line.

The XML answer back from ippages will look like this :

The free usage of ippages has some restrictions : some informations are not available and you have to subscribe to a commercial offer to have them. If you have a look to the XML up there, you will notice that you still have a lot of interesting informations with the free offer.
Happy geocoding.

Plugins and Kettle V4

2010-04-26T08:50:00.001-07:00

Hi all,
A quick word to say that the following plugins are running fine with Kettle V4 :

Qkr : Transformation plugin to create QRCodes coming from DBs.
Kgeocoding : Transformation plugin for geocoding addresses.
krrd : Transformation plugin to feed RRD files (RTG …).

The plugin SendToS3 (job plugin) is not working on Kettle V4 for the moment. Some AbstractMethodError is flying around. I have to find why and patch …
Vincent

Geocoding with Kettle : new plugin

2010-04-26T04:57:00.000-07:00

Hi all,

The Kettle geocoding plugin is HERE.
But shhhhhh .... don't tell it too loud .... ;)
And give me feedback if you want new features, improvements, etc ...

Ps : some usefull infos about the API term of use here.

Vincent

Kettle plugin with Google map GeoCoding : I’m wrong !

2010-04-22T06:10:00.001-07:00

Hi all,
Here is a link to a thread I initiated this morning after I was warned about my wrong usage of the Google Map API. Note that the limit for geocoding is now 2500 and no more 15000, which is not anymore a good thing in term of ETL process.
GeoCoding plugin thread on Google groups.
According I’m currently violating the term of use, the plugin will be unavailable for download today at 00:00 CET.
I’m sorry for that. I will start coding a new geocoding plugin for Kettle, using another – free - geocoder / API …

GeoCoding with Kettle : new plugin

2010-04-21T11:03:00.001-07:00

Hi all,
I created a plugin for geocoding addresses into Kettle v3.5. This plugin is using the google maps API. You can learn more about this API HERE.

What is Geocoding ?

According to wikipedia, geocoding is “the process of finding associated geographic coordinates (often expressed as latitude and longitude) from other geographic data, such as street addresses, or zip codes”. Normalization is the process to clean an input address and putit into a normalized, standardized format.
Reverse geocoding is the opposite : finding a complete address from GPS coordinates.

Raised relief map … a basic tool for geocoding.

The plugin

For the moment, it is a basic V1 release, but fully working. A lot more features are about to be added (advanced geocoding).

Here is the plugin screen, in Kettle. This is a basic screen as you can see. You need to enter the following :

GMapKey : your google map key. The geocoding works without it … well for me. But I recommend you to sign on for the API and use your Google key.
Input Address Field : the address field, from the incoming rows, on which you want to process the geocoding
Normalized address : give the column name in which the normalized address will be stored.
City Field : give the column name in which the city name will be stored.
GPS Coord Fields : give the column name in which the GPS Coordinates address will be stored.

Here is the main Kettle screen with a transformation sample.

Let’s see how it works

For the example above, I used 4 row creation steps to create 4 types of addresses (French, USA, Asia, Africa). Here is the output : a code, a raw adress (with typos and disorder) and a comment.

Let’s imagine now we want to normalize the Raw address content field and retrieve the corresponding GPS coordinates for each address. Let’s do it, we set up the plugin screen with the following informations : your GMap key, the “Raw address” input field and the names for the normalized address, the city field and the GPS coords.

Now we can plug everything and start the transformation. The plugin is asking for geocoding to the Google map API for each address. You will find the result set as follows :

The original fields are still here (Code, Raw Adress and Comment), but the plugin added 3 more fields according the names you set up previously (Norm_address, City and GPS_Coord). As you can see, the adress is normalized and formated, thanks to Google map API. The GPS coords are : lat / lng.

Limits

After some readings, I noticed you can ask for geocoding up to 15.000 time per day. This is a limitation of the Google map API. I didn’t try to go above 15.000 addresses / geocoding demands. I let you check this (create 15001 lines in the row creation steps …).

I want it

No problem. You can download the plugin HERE (plugin, xml file and icon) and test it. Like usual, everything is packed into a single jar using fatjar.

What’s next ?

This is a basic geocoding process. I’m currently working on something more powerfull, with more features : using all the API attributes, give ability to the user to choose which attributes he wants / doesn’t want, reverse geocoding … etc …
Please, if this plugin is usefull for you, tell me more about your needs. I will be happy to upgrade this plugin for your usage.

OSBI & ATOL

2010-04-13T11:35:00.000-07:00

Hi all,

Today, I want to put Sylvain Decloix, OSBI and ATOL into the lights.

Sylvain Decloix is a french BI Manager focused on Open Source, working for ATOL company.
He has a blog HERE, where you can find very good articles (in french) about BI, data integration, datawarehousing and reporting.
Not only a consultant and a manager able to provide high quality support on projects , Sylvain is what we can call a visionnary : he has very good knowledge of the BI industry, often delivers very sharp analysis about the market, trends and actors.
On top of that, he is very experienced with spatial data management and geocoding.

I recommend a frequent visit to his blog, and if you are looking for BI skills and consulting in France, you can definitely ask ATOL for assistance.

QRCode encoder with Kettle : new plugin

2010-04-12T05:41:00.001-07:00

Hi all,

I’ve made a new Kettle plugin to answer to a specific need from one of my clients in retail industry : a QRCode encoder.

What is a QrCode ?

It is a matrix code created by Denso Wave, a japanese company. QR stands for “quick response” because of the high speed decoding process. Today, most of our mobile phones (ex : iPhone) can read (decode) these QRCodes and read the information within to feed an application (agenda, adress book …). Some companies use it for encoding inside a logistic process. These codes are also widely used in ads. More infos HERE.

One of the code above is mine. But which one ?

The Kettle plugin

I used the famoux ZXING java library to encode any data into a QRCode. This library is quite complete and powerfull, yet sometimes a bit difficult to install and understand (read the wiki). You can find it HERE.

The plugin is quite simple : a single screen holds all the needed informations.

Here are the fields :

Step name : the step name.
QRColumn : the column you want to encode, coming from a previous step.
Size x : the X size of your generated QRCode file (recommanded : 128).
Sixe y : the Y size of your generated QRCode file (recommanded : 128).
Picture format : png or gif
Destination dir : the destination directory where all the pictures / QRCodes will be written.

Running the Plugin

Very simple. Have a look below to my sample transformation. A data input coming from a csv file (can be a query or anything else) and the plugin itself. The plugin will read all the incoming data from the QRColumn you specified and will create a file for each value. The generated files will have the same name but will have a counter in the filename, corresponding to the rownumber.

The generated files, from my examples, are now on my C:\, as specified in the plugin window.

I want it !

Ok, no problem, you can find the package HERE. As usual, everything is compiled with fat jar in order to have only one jar file. The package holds :

The plugin itself (+ xml file and icon file),
The data sample csv file,
My QRCode, to add me to your favorites ;)

Don’t forget to have a look to Pentaho and the new Kettle / PDI release 4.

Have fun and keep me informed about your usage / testing or new feature request.

Plugin : Update RRD Tool with Kettle (Cacti, MRTG …).

2010-04-08T12:55:00.001-07:00

Hi all,
This post will give you more details about my new Kettle plugin to feed RRDTools database.

What is RRDTool ?

According to Tobias Oetiker, RRDtool is the OpenSource industry standard, high performance data logging and graphing system for time series data. Use it to write your custom monitoring shell scripts or create whole applications using its Perl, Python, Ruby, TCL or PHP bindings.
You can learn more about RRDTools and Tobias Oetiker fantastic work on his homepage HERE.
With RRDTools, you can easily store time series data and create realtime graphics like the one below. For instance, I use it today for one of my client on Paris in order to monitor various real time business / IT indicators : travel booking, passenger reservation, search engine sollicitation, xml proxy load and mainframe usage.

The libs I used

I used JRobin, a java port of Tobias Oetiker RRDTools. JRobin was made by the talented Sasa Markovic. The JRobin home page are HERE and HERE. I recommand a visit in order to be fully aware of all JRobin features.
According to Sasa Markovic, “JRobin is a 100% pure java implementation of RRDTool's functionality. It follows the same logic and uses the same data sources, archive types and definitions as RRDTool does. JRobin supports all standard operations on Round Robin Database (RRD) files: CREATE, UPDATE, FETCH, LAST, DUMP, XPORT and GRAPH. JRobin's API is made for those who are familiar with RRDTool's concepts and logic, but prefer to work with pure java. If you provide the same data to RRDTool and JRobin, you will get exactly the same results and graphs.”
I confirm everything.
The graphical rendering is very good looking as you can see below.

The plugin

First, I recommand to read carefully everything related to RRDTools and JRobin. You must be familiar with this technology first.
The Kettle plugin is quite simple : a single user interface to create a RRD file, add archives and feed the file.

A short description of this window :

Nom étape (sorry in french, will be translated) : Step name
RRD File : the RRD file to be created. This file will hold all your time series data and archives.
Datasource : an RRD file can have 1 or more datasource. For the moment, my plugin is restricted to 1 datasource, which is most of the time enough.
Type :
- Gauge : Does no store the rate of change, it saves the actual value itself.
- Counter : To store the rate of change of the value over a step period (assume the value is always increasing). Ex : traffic counters.
- Derive : The same as Counter, but will handle negative values. Ex : free disk space.
- Absolute : To store the rate of change, but the previous value is set to 0.
Heartbeat : If the RRD file does not receive value (PDP) within 300 seconds, it will wait for another 300 seconds (total = 600 seconds). If no value after 600 seconds, the flag UNKNOWN will be stored.
Starttime : The unix timestamp as the RRD file starting point. Must be a unix timestamp. In a future release, I will code a converter and place it into the user interface. You can easily compute unix timestamps by using this web page or this one. This timestamp must be lower than the one coming from your data.
Min and Max : The minimum value and the maximum value, if predictable.
The combo zone : This combo gadget will be used to define RRA : Round Robin Archives. An RRA will define how the consolidated data is stored. We have 4 major parameters :
- CF, for Consolidation Function :
  - AVERAGE : Store the average value
  - MIN : Store the minimum value
  - MAX : Store the max value
  - LAST : Store the last known value
- xff : XFile factor. This is the percentage of values that can be unknown without making the recorded value flagged as UNKNOWN. Must be between 0 and 1, with 0.1 intervals.
- Steps : Number of values to be consolidated, regarding the chosen CF. Must be integer.
- Rows : Number of samples to keep. Must be integer.

The Add button will add the RRA (round robin archive) in the combo list, and then will be used for the RRD file creation.
Once the RRD file is successfully created, you will see a little message on botton of the user interface.

Let’s have a look to some RRD file internals, using another nice tool from Sasa Markovic : RRD inspector. I’m sure you will easily understand the RRD structure, if you are not already familiar with RRDTools.
The above screen shows us a RRD file created with one datasource called Speed, using a type GAUGE with a heartbeat of 600 seconds, with minimum and maximum values set to 0 to 2000.
This RRD file also has a unique RRA (round robin archive), using the AVERAGE consolidation function, with xff set to 0.5, 1 step (compute each value = no average in fact) and 24 rows.This file has been created using the Kettle plugin on my C:\ harddrive.
If we select then panel “Archive data” we will be able to see all the values currently stored into the RRD file.
RRD inspector is a fantastic little tool, very usefull when creating RRD files and checking everything is well done.

How to use the plugin ?

Very simple. First you create a RRD File using the user interface shown above. Then you have to control the file has been created, just to be sure. Finally, you can connect the step to a previous one in Kettle. In my example, I used a flat file containing some simple timestamps and values.
Here is my flat file : a unix timestamp with 5 mins intervals (starting Thu, 8 Apr 2010 12:00:00 UTC) and some simple values from 5 to 140.
TimeStamp;Value
1270728000;5
1270728300;10
1270728600;15
1270728900;20
1270729200;25
1270729500;40
1270729800;50
1270730100;60
1270730400;70
1270730700;80
1270731000;90
1270731300;100
1270731600;120
1270731900;140
And here is my sample transformation.

Hit play, and voilà … the plugin will feed the RRD file and give you a nice output log for each value. To be short : a RRD file is only expecting a unix timestamp and a value.

Generating a graph

Well, this is not really Kettle oriented, but I will give you some code to create graphics from your RRD file, previously loaded with Kettle.
This simple java snippet …
public static void RenderRRDGraph(long TimeStart, long TimeStop, String Consol, String RRDGraphFormat) throws IOException, RrdException{
    //Create gif graph
    RrdGraphDef graphDef = new RrdGraphDef();
    graphDef.setVerticalLabel("m/s");
    graphDef.setTimeSpan(TimeStart, TimeStop);
    graphDef.datasource("myspeed", "C:\\testRRD", "speed", Consol);
    graphDef.line("myspeed", new Color(0xFF, 0, 0), null, 2);
    graphDef.setFilename("C:\\testRRD." + RRDGraphFormat);
    RrdGraph graph = new RrdGraph(graphDef);
}

… will create this png.
Very simple as you can see (well this example is really really simple compared to what we can really do, but I can’t give you any snapshot of the graphs I did for my client – I have NDA on this). You can imagine now creating some real time graphics (RTG) using this technology.

The package

Let’s go back to Pentaho and Kettle : I created a package for you. You will find the plugin itself (compiled and archived under Eclipse using Fat Jar in order to embedd the JRobin library, the icon, the xml file, the flat file and a sample transformation (the one described above).
This package can be donwloaded on its Google code page.
Please keep me informed about your testing, and feel free to contact me if further features (or fixes !) are needed.

Update RRD Tool with Kettle (Cacti …). New plugin.

2010-04-07T12:48:00.001-07:00

Hi all,

I finally finished my new Kettle plugin. This one will be used to create and manage RRD files as well as feeding these RRD files. Of course, you need to use Pentaho Data Integration Tool for that (aka Kettle).

Reminder : RRD – Round Robin Database - is an open source tool for storage and retrieval of time series data. Very usefull when you need to monitor a couple of values in real time.

This new plugin is fully working and will be released tomorow for the community. I still have to do some cleaning, write a how-to and describe all the library I used (and give full credit their authors). But for the moment, you can find some pictures below showing the stuff running in Kettle with a simple transformation sending data from a csv file.

Above : You can create up to one datasource (enough for a first release) and you can create as many Consolidations as you want (very usefull), using the grid and the helper to choose the right values.

Here is what you can do with RDDTools !!! Pretty sexy, no ? I will use this Kettle plugin for one of my clients in Paris, who wants to monitor sales, booking and search engine optimization in real time, using a window gadget or a web page. The charts will look like these examples …

After this first coming release, I think I will add some features like dumping values or – better – updating the charts after the data feeding. Ah, no … just one in priority : convert epoch to human readable date …. ;) (because my plugin needs unix timespamp for the moment), but that’s an easy one.

Please, contact me if you are interested by testing this plugin.

Download new Kettle Job Plugin : SendToS3 – Release 2

2010-03-29T08:44:00.001-07:00

Hi all,
As said in my previous post (below), the release 2 for the Kettle Job Plugin I made is available for download HERE. This release offers a bucket management screen (creation, listing, assign …).
More to come : after been asked for, I will add some metadata / object attributes to be linked with the file in S3. Very usefull if you need, like me, to manage file collections. Have a look below for an example of object attributes.

Enjoy,
Vincent

NEW FEATURE : Kettle Job Plugin : SendToS3

2010-03-26T04:21:00.001-07:00

Hi all,
Here is an overview of a new feature for the Kettle Job plugin “Send files to Amazon S3”. As you can see, I added a screen for bucket management : creation, listing, autorefresh … For those who are new to S3, a bucket is – more or less – like a folder but with specific S3 constraints.
During the last week, I was asked to add some more features : zip file, file renaming, file timestamping … Seems people are sending a lot of things on S3 now …
I will find time to add these features during April.
Still need to clean my code and I will soon push a release on the plugin Google code page.

Kettle job plugin : send files to Amazon S3

2010-03-22T08:29:00.001-07:00

Hi all,

I created a job plugin for Kettle for file sending to Amazon S3. You can download this plugin here. It is based on Jets3 toolkit (jets3t-0.7.2). You can download the plugin HERE.

Below is the plugin GUI, as well as an example and you can also see the log output.

The plugin needs :

Access Key : Your S3 access key. You must have a S3 Account.
Private Key : Your private key. This key won’t be displayed in the Kettle log output.
S3 Bucket : A bucket is like a directory. It must be existing.
Filename : The path and filename for the file you want to send to S3.

Once pushed in Amazon S3, you can see your file in the target bucket (here, a stupid win dll was sent).

And, this is the job icon.

I will soon add some new features like : bucket creation in the UI, bucket listing, xml parsing for S3 return code and maybe encryption.

Feel free to contact me.

The beast

2010-03-19T09:34:00.001-07:00

Hi all,

Here is “my” IBM SVC 4800 SAN (middle & bottom) for one of my projects in Paris. Fiber channel SAN.

10 To and 24 Go of cache memory. I’m expecting good IOs.

Yum.

WIP : New book about Kettle

2010-03-15T04:08:00.000-07:00

Hi all,

I'm excited (not usual for a Monday morning) to give you this info / link to Roland Bouman's blog.
He - with Matt Casters and Jos Van Dongen - is preparing a book about Kettle ! Everything you ever wanted to know about sex … sorry … about Kettle will there !
Wait until September 2010 ....

More infos here :
http://rpbouman.blogspot.com/2010/03/writing-another-book-pentaho-kettle.html

Currency repository with kettle

2010-03-10T16:02:00.001-08:00

Hi all,

Today, I had to create a currency repository for one of my client in financial services. Easy with Informatica connected to one of the real time financial interfaces (Bloomberg, Reuters, etc …). The challenge was to gather data for all major currencies and store the Euro exchange rates over time. Easy I as said. It was done in no time.

Then, on coffee time, I thought : “How to do that with Kettle, with a complete free approach ?”. Here again, easy. Let me explain.

The data sources

From 2003 to 2005, I worked for the very official French National Bank (Banque de France - BDF) and the European Central Bank (ECB). Since that time, I still have a lot of links and data sources about economics and stats. For Euro exchange rates, we have 2 possible data sources which are FREE of access and usage :

RSS streams : broadcasted daily, at 14:15 (not a minute more), these RSS are easily accessible and you can leverage them to build your own exchange rate repository. Some data transformation are needed but it is very simple. These RSS streams offer 5 days of historical data. They are available on the ECB website HERE.
XML file : like the RSS Stream, a daily XML file is available. It contains only data for the current day. Here again, you can easily load it with Kettle and the XML step with the proper parameters and configuration. The XML file is available on the ECB website HERE.

Let’s see everything in detail.

Solution Nb1 : RSS streams from European Central Bank.

If you go on the RSS page (HERE), you will see a lot of streams available for every currency on the market.

The RSS stream is easy to read and understand. Let’s click on the first one : US Dollar. As you can see, we have the currency exchange rate with the Euro and a date. We will need to do some parsing here.

Okay, we have now the RSS links and we just had a quick overview of its internal structure, now time to go playing with Kettle. First we put a “RSS Reader” on the workbench and we set it up. As you can see, I added each RSS link corresponding to each currency. No possibility to use a single RSS stream for all currencies (I will investigate this point).

On the second tab, nothing to do, just be sure you have a 0 in the field “Max number”.

On the third tab, Fields, we want to choose only 2 fields : “Date de publication” (exchange rate timestamp) and “Titre” (the string holding the exchange rate for the Euro). Let’s use String as datatype. Below is the Field tab.

If you hit the preview button, you will see the RSS stream popping out on your screen like this. Cool.

Okay, now we have to process our data in order to feed a table, somewhere on your datawarehouse or your application. Let’s have a look at the transformation I did in 3 mins for that purpose.

You can see the RSS Reader on the left, no need to go futher on this one. Then some other steps :

Field split : the field Titre, as we saw it with the RSS Reader, is a long string containing all what we need : the exchange rate and the international currency code (USD, EUR, CHF …). With this step, I split the Titre field into two new fields that will hold the exchange rate (Cours) and the currency code (Devise). The delimter is a space, that’s why you can’t see it on the screeshot below :

RATES data input : We feed the target database / table. Take care to manage your historical data here, remember the RSS stream is sending 5 days of historical data each day.

Keep single currency : That’s the second part of the transformation. Here, we need to build a table with the couples : currency code / currency name. Remember we only have the currency code (USD …) and it would be nice to build a tiny dimension with the real name of the country and the currency. This step will only keep distinct values.

Map ECB currency code / currency name : the previous distinct values will be mapped with the currency real and long names. Look at the configuration screen below, a new field is created (Devise) and you can copy paste the currency names from the ECB page where we grabbed the RSS links. The currency codes are international standards, no risk to see them changing one morning.

CURRENCIES data input : Final step, we feed the currency dimension with the couples code / currency name. Since it is a typical short dimension and new currencies are not frequent, you can update this dimension once in a while, or when a new RSS is added …

If we make a quick extract of the data we created, the output will look like this for the rates (left) and the currencies (right).

Solution Nb2 : XML file from the European Central Bank.

Well, doing the same with an XML input is possible and easy too. First, we need to find the appropriate XML file. This one can be found HERE. It is quite simple and interesting data is the timestamp, the currency and the rate. Remember, this XML file only contain data for one day.

This XML file is available everyday at 14:15, so you can schedule your job to run in order to gather the lastest data.

Let’s process this file now. For this, we will create a transformation looking like this one below. We will also create a currency dimension table, like we did for the previous example.

XML Extract : this is the core component of this transformation. It can read an XML file, parse it, process it, based on your Xpath query. Let’s have a look about configuration.

The link to the XML file has to be written in the main tab. The second tab is more sensitive : here we have to specify a XPath for the document. In our case, the XPath must be : /gesmes:Envelope/*[name()='Cube']/*[name()='Cube']/*[name()='Cube']

The last tab (Fields) is also very important : we need to indicate the XPath attributes in order to reach the elements we need. In our case, @currency, @rate and @time are mandatory.

RATES : the XML file is directly written into a target database / table. No need for custom transformation here. The target data looks like the previous example.

Map ECB currency code / country : we still need to create a tiny dimension with the couples :currency code / currency full name. Same process as the previous example. Luckily, the currency codes are a worldwide standard and we can re use the step from the previous transformation.

CURRENCIES : finally, the currency dimension is written into its target database / table.

The rates are on the left while the currencies – inchanged – are on the right. Remember : only one day of data is available with the XML file. You will maybe notice that the date format is different compared to the RSS data (yyyy-MM-dd versus dd/MM/yy) but this is something you can easily manage if necessary.

Well, this was a quick and handy way to reach some official currency data and process it in Kettle. Of course, you can easily customize and optimize these jobs.

If you have troubles running theses examples, feel free to reach me and I will provide you with the transformation files.

Send file to S3 with cool GUI

2010-03-09T07:01:00.001-08:00

Hi all,
A cool way to manage your S3 assets : http://s3fm.com/. You can create / edit / delete buckets as well as uploading files and downloading also.
I’m currently writing some code that could be used in Kettle for sending and retrieving files to/from S3. Soon to come, I’m still working on it.

In this example, I’m sending US public data set (consumer expanditure survey) flat file, available on Amazon, that will be later used for data processing and stats in order to validate a stream.

Sending tweets with Kettle !

2010-03-08T16:23:00.001-08:00

Hi all !

Recently, while meeting clients, I was speaking a lot about pervasive BI, operational BI, real time BI …. well all these “new” tendancies about BI. On top of that, I’m currently delivering some mobile BI features for one of my clients, based on Cognos tools.

Then I thought to myself : “Why not sending *intelligent* tweets with an ETL tool ?”.

Let’s imagine we are a very successfull company and we want to tweet on how fantastic are our sales. Or imagine that, for any reason, you regularly gather some data and put it on twitter for public release (I’m doing this with some weather probe data from the family farm in the south, while I’m living in Paris …).

Ok, just ask and it’s done.

Sending tweets with Kettle

The process is the following :

Query a table to retrieve the nb of items sold and the total amount of sales. For simplicicty you can also generate rows in kettle.
Build the tweet and the command that will send it,
Send the tweet.

I will use one transformation and one job. The transformation will retrieve the data from the table, then send this data (1 row, 2 columns) to the job. This job will build the tweet and send it to twitter. Easy.

The transformation

First, a Table input. This step holds the SQL query to retrieve the data I need (1 row, 2 colunms) :

Select sum(sold_items), sum(amount) from sales_facts

Then, the Set Variables step will capture and map the query results with variables. These variables will be accessible into the job. Use the button Get Fields for simplicity.

The Job

The job has 3 steps : the usual Start step, the Transformation step linked to the transformation we just created above and a Shell step. Have a look.

Nothing special here, except I checked the option “Copy previous results to args” inside the transformation settings.

Then, we have the Shell step. This step will call a famous linux utility : cURL. cURL is a command line tool for transferring data with URL syntax, supporting FTP, FTPS, HTTP, HTTPS, SCP, SFTP, TFTP, TELNET, DICT, LDAP, LDAPS, FILE, IMAP, SMTP, POP3 and RTSP. curl supports SSL certificates, HTTP POST, HTTP PUT, FTP uploading, HTTP form based upload, proxies, cookies, user+password authentication (Basic, Digest, NTLM, Negotiate, kerberos...), file transfer resume, proxy tunneling.

I recommand using cURL under Linux. The windows release is a pain in the neck (installation issues, dll issues, issue with libeay32.dll, etc ….).

The command to send a tweet with cURL is quite easy : curl --basic --user yourtwitteraccount:yourtwitterpasswd --data status=”Your tweet” http://twitter.com/statuses/update.xml

You can build this command with the Shell step with the following configuration :

Some explanations here :

I’m not calling cURL but I use a wrapper script file. I had some issues calling directly the cURL executable with /usr/bin/curl. Using a wrapper script file seems more appropriate. Look at my file below. Parameter $2 has to be embrassed with double quotes ; usefull if you need to send tweets with more than one word ;)

As you can see, my curl wrapper file is waiting for 3 params, and these 3 params are passed in the Fields zone of the Shell window step. I obfuscated my Twitter account, in red. Here is the detail for these parameters :

$1 : The Twitter username and password with the syntax [username:password],
$2 : The tweet message, with double quotes,
$3 : The endpoint : the Twitter destination aka http://twitter.com/statuses/update.xml

In my exemple, I will send a tweet saying : “We sold [total of items sold] items for a total amount of [total amount of sales] USD.”

Sending the tweet !

Let’s save the work and start the job. You will see a verbose XML command output, this is good sign. Have a look below :

Is my tweet really published ?

Yes, of course it is ! Connect to your Twitter profile, and check for the tweet.

The goodies

The transformation can be downloaded HERE.

The job can be downloaded HERE.

Enjoy and find new usage for this !!

Amazon SimpleDB data loading with Kettle !!

2010-03-05T07:57:00.001-08:00

Hi all !

It's been a few days I'm thinking about feeding Amazon SimpleDB with an ETL tool like Kettle / PDI.
Well, it's done. I have a working prototype. It’s a “quick and dirty” prototype of course but it works. I hope we will soon have an official Kettle plugin for that.

Requirements

You have to be familiar with Amazon AWS, EC2 and SimpleDB. Of course you need a valid account on Amazon Web Services. If you want to learn more about SimpleDB, click HERE. You can play with SimpleDB with a graphical interface before starting hard stuff, click here for the ScratchPad (don’t forget to browser the javascript source code, a lot to learn here !).

You need to know how to use Kettle, the famous data integration tool from Pentaho. To learn more about Kettle, follow this link. To discover the full Pentaho BI solution, click here. I recommand you to discover Pentaho BI Suite Enterprise Edition.

The process

First you have to know how SimpleDB is organized and how it’s working.
For the developper, SimpleDB is not seen a traditional relational database like the ones he’s used to work with. Instead of thinking in terms of tables and columns, you have to face a different approach : data is organized within Domains, which are similar to an Excel tab. Then, inside a domain, data is stored with the couple : Attribute/Value. XML guys won’t be suprised with this storage method.
Let's first have a look at a typical relational table. Just a reminder ;)

Now, let’s see how your data will look like once store inside SimpleDB. A bit of XML now. As you can see, this extract represents the first line of the relational table show above. This row is composed of an item name (let’s say for convenience, but it’s false, it’s like the primary key) and attributes. These attributes are made of a Name and an associated Value.

See the difference ? That's the Amazon SimpleDB API. I'm pretty sure that data, at low level, is finally stored into a relational schema, somewhere... But for the developper, this is the way it’s must be done.

Okay, okay. But how to transform my relational structure into something that will be received and understood by the SimpleDB API ? We have two challenges here : transformation and sending. Ok, go for it.

The Mapping !

Here is my transformation, done with Kettle. Pretty simple, uh ? Let’s go in detail now…

First you have a CSV file input. This data input will be reformated to build Name/Values couples and then these couples will be concatenated into a valid URL. Once signed, this URL will be sent to Amazon API and the data will be inserted into the domain (previously created). You can see, on my transformation, a File Output : I use it, sometimes, for debugging. In our exemple, it was easy for me to see and analyse the generated URL into a notepad (here, the link is not activated).

In my example I will use a typical csv file as data source (based on the same relational table shown above). Here is my flat file, typical with ; as separators.

ID;Category;Subcat;Name;Color;Size;Make;Model;Year
Item_01;Clothes;Sweater;Cathair Sweater;Siamese;Small, Medium, Large;Nike;Swoosh;2003
Item_02;Clothes;Pants;Designer Jeans;Paisley Acid Wash;30x32, 32x32, 32x34;Trusardi;BigButt;2005
Item_03;Clothes;Pants;Sweatpants;Blue, Yellow, Pink;Large;Diesel;Steel;2006, 2007
Item_04;Car Parts;Engine;Turbos;Pink;Medium;Audi;S4;2000, 2001, 2002
Item_05;Car Parts;Emissions;O2 Sensor;Black;Small;Audi;S4;2000, 2001, 2002

The JScript Code !

I confess : I only wrote 5% of the JScript code. Let me explain. When you suscribe to Amazon SimpleDB, you can download the official API, written in Java, and use it to create, manage and populate your SimpleDB domain. Java is very usefull of course, but I was looking for Jscript in order to put everything into Kettle. Then I downloaded Amazon SimpleDB ScratchPad. This is a nice utility that allows you to play with SimpleDB without coding, just the mouse. When looking into this application directories, you can find all the Jscript source code needed ! Then my work consisted on porting the ScratchPad code into a Kettle Jscript step, with some adjustments.

This code is a bit long to be shown here, so click HERE to download it. Let’s have a hi level overview of the JScript layout.

The process if very simple : each row is cutted into Name/Values couples (URL building & URL formating routines), these couples are then concatenated into a valid URL (URL concatenation). This URL is then signed (SHA-1 hash algo) and sent to the HTTP client step.

Here is the basic code to create the URL :

var URL2POST = "https://sdb.amazonaws.com"
+ "?SignatureVersion=1&Action=" + "PutAttributes"
+ "&Version=" + encodeURIComponent("2009-04-15")
+ "&DomainName=" + encodeURIComponent('MyStore')
+ "&ItemName=" + encodeURIComponent(ID)
+ "&Attribute.1.Name=Category"
+ "&Attribute.1.Value=" + encodeURIComponent(Category)
+ "&Attribute.2.Name=Subcat"
+ "&Attribute.2.Value=" + encodeURIComponent(Subcat)
+ "&Attribute.3.Name=Subcat"
+ "&Attribute.3.Value=" + encodeURIComponent(Name)
+ "&Attribute.4.Name=Color"
+ "&Attribute.4.Value=" + encodeURIComponent(Color)
+ "&Attribute.5.Name=Size"
+ "&Attribute.5.Value=" + encodeURIComponent(Size)
+ "&Attribute.6.Name=Make"
+ "&Attribute.6.Value=" + encodeURIComponent(Make)
+ "&Attribute.7.Name=Model"
+ "&Attribute.7.Value=" + encodeURIComponent(Model)
+ "&Attribute.8.Name=Year"
+ "&Attribute.8.Value=" + encodeURIComponent(Year)
+ "&Timestamp=" + timestamp
+ "&AWSAccessKeyId=" + encodeURIComponent(accesskey);

Note that in my Jscript code, I didn’t make any loop to go though all the source columns. As it is a quick proof of concept, based on fixed length data structure, I used one line of code for each column in order to create Name/Value couples. If you look closely into Amazon Scratchpad code source, you will see a loop in the function “generateSignedURL”. This is how things have to be done of course !

The final URL looks like this one :

Let’s see it more in detail :

The endpoint : https://sdb.amazonaws.com
The SignatureVersion, always 1 for me.
The Action needed, in our case PutAttributes, in order to load data into the domain.
The Version, always 2009-04-15. Don’t know why …
The DomainName : MyStore, in my case. You can create yours easily.
The ItemName : Item_01 corresponding to my primary key.
Then you have all the Name/Values couples : Attribute names and Attribute value.
A timestamp : calculated by a Jscript function.
Your AWS Access Key. Mine is obfuscated in the exemple above.
Your Signature : this is you secret AWS Access key that will be signed by the SHA-1 hash algo, as seen above. Obfuscated here again.

Security

Let’s talk about these AWS Access Keys and Signature. In my proof of concept, these keys are stored in clear in my JScript. Of course, this is not recommanded. I let you imagine a more convenient way to be more secure (parameters, repository …).

Let’s send it to Amazon !

Pretty easy now, each row will be sent to a HTTP client step, using a Jscript variable called URL2POST. This step will send the URL to Amazon SimpleDB and the row will be inserted into your domain.

For the moment, I have no time to handle any return code from Amazon API but it’s very easy since Amazon sends you back an XML message like the one below, in case of success. In case of failure, the message is self explanatory.

The goodies !

You can find the Kettle transformation HERE.

You can find the Jscript HERE.

You can find my little flat file HERE.

How to be sure the data is in ?

Pretty easy. Start the Amazon Scratchpad utility, enter your access code and key, go to GetAttributes API drop down menu and fill in the Domain Name and one Item_Name. Have look here. Note : my keys are obfuscated here again.

Hit “Invoke Request” button, and see your data.

There is another way to check your data. You can write a SQL query in order to see the whole data stored in a given DomainName. Here again, with the ScratchPad, go to “Select” in the API drop down menu. Then enter “select * from MyStore” in the Select Expression field. Hit Invoke Request button, and you will see all your data.

The output will look like this one (continues for each Item …).

That’s nice, but what for ?

Imagine you have, like me, to think about storing emails or a call center knowledge base on the cloud. You have messages, and you have headers. Why not storing headers in SimpleDB and message bodies into S3 ? That’s a good solution. In that case, SimpleDB will handle a few attributes while the heavy data will be stored into S3, with the help of any third party database (open source or not). Of course, you have to manage the link between S3 data and SimpleDB headers, but that’s another story …

More to come

Please, give me a feedback for this article. I’m currently working on something more reliable and more professional. If I have time, I will try to write a Kettle plugin.

FOSDEM !!!!

2010-02-01T03:33:00.000-08:00

Don't miss it !

ULB Campus Solbosh
Avenue Franklin D. Roosevelt, 50
1050 Bruxelles

Sparklines for Excel !

2010-01-27T06:59:00.001-08:00

Hi all,

Today I want to speak about Sparklines for Excel, "a set of free user defined functions for Excel to create sparklines".
Sparklines ? You know, those little graphics, often cell sized, as seen on a lot of financial / stock exchange / scientific screens.

Well, Frabrice Rimlinger is a smart french guy who coded these sparklines for Excel. VBA code, for all Excel flavors.

Have a look to his blog and download the macros. It is worth a try !
http://sparklines-excel.blogspot.com/

And now, my own personal usage of Sparkline : graphically showing source table sizes, related to my current datawarehouse project in Paris. I'm using the treemap component : each square size is calculated reagarding the table size. Colorization is done with a dynamic color scale.

Nice, isn't it ? And you know what ? Theses sparklines can be used with an OLAP Excel frontend : PALO, TM1 ... your choice...

Scripts for the EC2 !

2009-12-10T09:26:00.000-08:00

Hi all,

I’m currently deep into EC2, mixing cloud computing and BI. Very interesting, awesome. As you know, Amazon released two interesting features a few days ago :

you can store an instance image in EBS, not in S3. You will only pay for EBS.
you run and stop an instance, instead of just launching it and terminate it. The stop feature halts the instance and stops the money counter. Once you decide to run it, you will find your machine back with all the customization you did before. Note : you have to suscribe to Elastic IP in order to have your external ip adress unchanged.

As I said, I’m now deep into EC2, making prototypes around open source BI. That’s why I started to build my own tool box. Today I want to share two scripts with you. These scripts were written to start and stop instances from EBS backed AMIs :

stop instance script : use it to stop a specific instance (previously started). I added some extra infos about the instance to be stopped. Look at the command output below.

start instance script : use it to start specific instance (previously stopped). I added some extra infos about the instance to be stopped. Look at the command output below.

Here is the stop script :

#!/bin/bash
#Script by Vincent Teyssier-12/2009 
speeder_spinner()
{
PROC=$1;COUNT=0
while [ -d /proc/$PROC ];do
echo -ne '/\b' ; sleep 0.05
echo -ne '-\b' ; sleep 0.05
echo -ne '\\\b' ; sleep 0.05
echo -ne '|\b' ; sleep 0.05
done
} 
get_ami_infos()
{
X=0 
    for ELEMENT in `ec2-describe-instances $INSTANCE`
        do
            MY_ARRAY[$X]=$ELEMENT
            ((X = X + 1))
        done 
echo " "
echo "-- STOPPING INSTANCE --"
echo -e "Instance :""\t\033[1m$INSTANCE\033[0m"
echo -e "From AMI :""\t${MY_ARRAY[6]}"
echo -e "Desc     :""\t${MY_ARRAY[3]}"
echo -e "Config   :""\t${MY_ARRAY[10]}"
echo -e "OS       :""\t${MY_ARRAY[13]}"
echo "------------------------"
echo " "
} 
get_stopping_result()
{
# Stop the ESB backed instance
Y=0
    for ELEMENT2 in `ec2-stop-instances $INSTANCE`
        do
            MY_ARRAY2[$Y]=$ELEMENT2
            ((Y = Y + 1))
        done 
echo " "
echo "-- RESULT FROM STOPPING COMMAND --"
echo -e "Instance      :""\t${MY_ARRAY2[1]}"
echo -e "Initial state :""\t\033[1m${MY_ARRAY2[2]}\033[0m"
echo -e "New state     :""\t\033[1m${MY_ARRAY2[3]}\033[0m"
echo "----------------------------------"
echo " "
} 
get_instance_status()
{
status=${MY_ARRAY2[3]} 
while [ "$status" == "${MY_ARRAY2[3]}" ]
do
#echo $status
status=`ec2-describe-instances $INSTANCE | cut -f6 | sed -n 2p`
done 
echo " "
echo "-- INSTANCE STATUS --"
echo -e "Instance :""\t$INSTANCE"
echo -e "From AMI :""\t\033[1m$status\033[0m"
echo "------------------------"
echo " "
} 
#MAIN
INSTANCE=$1 #get params, more to come
echo "Please wait, gathering infos "
get_ami_infos &
speeder_spinner $! 
echo "Please wait, stopping the instance "
get_stopping_result &
speeder_spinner $! 
echo "Please wait, doing the job "
get_instance_status &
speeder_spinner $! 
exit

And here is the start script :

#!/bin/bash
#Script by Vincent Teyssier-12/2009 
speeder_spinner()
{
PROC=$1;COUNT=0
while [ -d /proc/$PROC ];do
echo -ne '/\b' ; sleep 0.05
echo -ne '-\b' ; sleep 0.05
echo -ne '\\\b' ; sleep 0.05
echo -ne '|\b' ; sleep 0.05
done
} 
get_ami_infos()
{
X=0
    for ELEMENT in `ec2-describe-instances $INSTANCE`
        do
            MY_ARRAY[$X]=$ELEMENT
            ((X = X + 1))
        done
echo " "
echo "-- STARTING INSTANCE --"
echo -e "Instance :""\t\033[1m$INSTANCE\033[0m"
echo -e "From AMI :""\t${MY_ARRAY[6]}"
echo -e "Desc     :""\t${MY_ARRAY[3]}"
echo -e "Config   :""\t${MY_ARRAY[10]}"
echo -e "OS       :""\t${MY_ARRAY[13]}"
echo "------------------------"
echo " "
} 
get_starting_result()
{
# Start the ESB backed instance
Y=0
    for ELEMENT2 in `ec2-start-instances $INSTANCE`
        do
            MY_ARRAY2[$Y]=$ELEMENT2
            ((Y = Y + 1))
        done
echo " "
echo "-- RESULT FROM STOPPING COMMAND --"
echo -e "Instance      :""\t${MY_ARRAY2[1]}"
echo -e "Initial state :""\t${MY_ARRAY2[2]}"
echo -e "New state     :""\t\033[1m${MY_ARRAY2[3]}\033[0m"
echo "----------------------------------"
echo " "
} 
get_instance_status()
{
status=${MY_ARRAY2[3]}
#last_status=${MY_ARRAY2[3]} 
while [ "$status" == "${MY_ARRAY2[3]}" ]
do
#echo $status
status=`ec2-describe-instances $INSTANCE | cut -f6 | sed -n 2p`
done 
echo " "
echo "-- INSTANCE STATUS --"
echo -e "Instance :""\t$INSTANCE"
echo -e "From AMI :""\t\033[1m$status\033[0m"
echo "------------------------"
echo " "
} 
#MAIN
INSTANCE=$1 #get params, more to come
echo "Please wait, gathering infos"
get_ami_infos &
speeder_spinner $! 
echo "Please wait, starting the instance"
get_starting_result &
speeder_spinner $! 
echo "Please wait, updating status"
get_instance_status &
speeder_spinner $!
exit

Enjoy and give me feed back about your EC2 adventures.

It’s not open source, but it’s cloud related

2009-12-04T03:33:00.001-08:00

Hi all,

Microsoft SQL Server 2008 + analytic functions are available on the cloud. Nicely done, I have to admit. Speed has still to be improved.

If you want to have a look and play with some analytics, datamining etc … click here. Datasets are already available for playing. It’s Public access, no credentials. You can also play with it by using an Excel plugin.

Have a nice play.

New and very interesting features on EC2 !!

2009-12-04T02:10:00.001-08:00

Hi all,

I received this notification from Amazon this morning. New sexy features like booting from ESB snapshots and a new API to create images (no more command line !).

Please read below (part of the Amazon communication).

Amazon EC2 Boot from Amazon EBS
Amazon EC2 has also announced the ability to boot instances directly from Amazon EBS snapshots, providing significantly increased flexibility in how customers can manage their instances. You can still save an Amazon Machine Image (AMI) in an Amazon S3 bucket and boot it from the local instance store, but you can now also choose to save AMIs as Amazon EBS snapshots and boot directly from an Amazon EBS volume. When an instance is booted from an Amazon EBS snapshot, the root partition of the instance is created on an Amazon EBS volume. Instances booted from Amazon EBS volumes can be stopped and later restarted, preserving any of the state that is saved to your volume and allowing you to modify some properties of your instances while it is stopped. For example, you can change your instance size or update the kernel it is using, or attach your root partition to a different running instance, making it easier to do debugging when you are creating new boot images. When booting from an Amazon EBS volume, AMIs and root partitions are no longer limited to 10GB, but can be up to 1TB in size, enabling significantly more complex images. Additionally, you are not charged for stopped instance hours and you will only incur charges for your Amazon EBS volumes while your instance is stopped, allowing you to reduce your Amazon EC2 costs when you do not need your instances running. Customers can now use a newly launched API that makes it easy to bundle images without using the command line tools, and can also take advantage of the fact that the content of an Amazon EBS volume is available to the instance immediately on volume creation which can lead to much faster instance boot times. For more details on this new addition to Amazon EC2, please see the Boot from Amazon EBS Feature Guide.

Pentaho Solutions book by Roland Bouman

2009-12-01T13:14:00.001-08:00

Hi all !

Today’s book review is about “Pentaho Solutions – Business Intelligence and Datawarehousing with Pentaho and MySQL”, written by Roland Bouman and Jos Van Dongen. Thanks to Roland and Wiley for my own copy.

ISBN: 978-0-470-48432-6

Roland is an IT expert, ranging from web application development and business process analysis to business intelligence. He worked for Inter Access, MySQL AB, Sun Microsystem and is now working for Strukton Rail. He co-authored the MySQL Cluster 5.1 Certification Study Guide. Please, have a look to his blog.

Jos is a BI expert for more than 15 years. He is also a well know author and a presenter. After a long career in BI, he created his own consulting business in 1998 : Tholis Consulting. He is also covering BI developments for the Dutch Database Magazine.

This book is really an impressive work, I mean it. I’m quite used (and sometimes bored) to read technical books about BI, datamining, data management, etc … but this one has its own sound, a familiar sound. It’s like having Roland behind your shoulder for a private master class on Pentaho tools. Within some short minutes, you have a complete Pentaho system running on your server/workstation and ready for testing, developping, trying features … well, doing anything you always wanted to do with Pentaho. Roland and Jos have has succeeded within the challenge of writting a book for different kind of readers :

The complete newbie in BI who wants to learn the basics of our datawarehousing and decision support, learn more about BI processes, tools, approaches, data modelling, datamining … with Pentaho tools.
The expert in BI architecture design and implementation, who wants to learn how to deploy a Pentaho solution, how to set up a complete ETL hub with PDI, create PDI clusters …

Let’s have a look to the book agenda :

Getting started with Pentaho
- Basics, from first installation to a complete and detailed view on the Pentaho stack.
Dimensional modelling and datawarehouse design
- Learn (or refresh your knowledge) the dimensional modelling used in datawarehouses.
ETL and data integration
- Discover PDI and learn how to developp transformations. From beginners to advanced users.
Business Intelligence Applications
- Learn everything on metadata management, scheduling, bursting, dashboards, OLAP and datamining.

This book will help you to :

Discover Pentaho concepts and approach.
Refresh your knowledge on dimensional modeling and data warehousing design (Kimball’s star schemas).
Develop data transformations, jobs, decision streams with Pentaho Data Integration (PDI, aka Kettle).
Use advanced and powerfull PDI features like variables, remote execution and clustering.
Make complete design and deploy reports / charts using Pentaho Report Designer.
Take advantage with OLAP engine and create flexible pivot tables with typical drill up/drill down features using Pentaho Analysis Services.
Search for patterns in your data and make your first steps in datamining using Pentaho data mining.

I recommand this book to anyone involved in Business Intelligence and Open Source. If you are thinking about trying some Pentaho software (as a new project or with coexistence with your actual and proprietary – expensive - BI stack), this book is definitely for you. You will stop spending time reading forums, tech reviews, looking for feedback (frequent pains with open source …) and will access directly the knowledge you need. If you are currently developing an open source BI project with Pentaho, this book will help you to implement the whole stack with best practices and deliver a world class BI solution for a low TCO.

If you are a newbie into Business Intelligence, you will be pleased to discover a book you can easily understand thanks to Roland and Jon’s talent for popularizing datawarehouse fundamentals and technology. This book will go along with you and ease your work on BI and Pentaho, the most exciting open source BI suite available today.

You can buy this book on Amazon at an attractive price, here.

PDI clusters – Part 1 : How to build a simple PDI cluster.

2009-11-23T04:43:00.000-08:00

Hi all !

I would like to start a collection of posts dedicated to PDI / Kettle clustering.
After surfing the web, I noticed a lot of people is asking how to build PDI clusters, how to test and deploy them in a production environment. Also a lot of questions about Carte usage. So, I will try to make some tutorials about this fantastic feature offered by PDI.
At that time, I want to recommend you a book : “Pentaho Solutions – Business Intelligence and Datawarehousing with Pentaho and MySQL”, written by Roland Bouman and Jos Van Dongen. This book is a fantastic source of knowledge about Pentaho and will help you understanding the Pentaho ecosystem and tools. My complete review about this book here.

Agenda

How to build a simple PDI cluster (1 master, 2 slaves). This post.
How to build a simple PDI server on Amazon Cloud Computing (EC2).
How to build a PDI cluster on Amazon Cloud Computing (EC2).
How to build a dynamic PDI cluster on Amazon Cloud Computing (EC2).

This first post is about building a simple PDI cluster, composed of 1 master and 2 slaves, in a virtualized environment (vmware).
After this article, you will be able to build your PDI cluster and play with it on a simple laptop of desktop (3 giga of ram is a must have).

Why PDI clustering ?

Imagine you have to make some very complex transformations and finally load a huge amout of data into your target warehouse.
You have two solutions to handle this task :

SCALE UP : Build a strong unique PDI server with a lot of RAM and CPU. This unique server (let’s call it an ETL hub) will handle all the work by itself.
SCALE OUT : Create an array of smaller servers. Each of them will handle a small part of the work.

Clustering is scaling out. You divide the global workload and distribute it accross many nodes, these smaller tasks will be processed in parallel (or near parallel). The global performance equals the slowest node of your cluster.
If we consider PDI, a cluster is composed of :

ONE MASTER : this node is acting like a conductor, assigning the sub-tasks to the slaves and merging the results coming back from the slaves when the sub tasks are done.
SLAVES : from 1 to many. The slaves are the nodes that will really do the job, process the tasks and then send back the results to the master for reconciliation.

Let's have a look to this schema. You can see the typical architecture around a PDI cluster : data sources, the master, the registered slaves and the target warehouse. The more PDI slaves you implement, the better parallelism / performance you have.

The virtual cluster

Let's build our first virtual cluster now. First, you will need vmware or virtual box (or virtual PC from Ms). I use vmware, so from now I will speak about vmware only, but you can transpose easily. I decided to use Suse Enterprise Linux 11 for these virtual machines. It is a personal choice, but you can do the same with Fedora, Ubuntu, etc …

Let's build 3 virtual machines :

The Master : Suse Enterprise Linux 11 - this machine will host PDI programs and PDI repository, a mysql database with phpmyadmin (optional).
The Slave 1 : Suse Enterprise Linux 11 - this machine will host PDI programs and will run carte.
The Slave 2 : Suse Enterprise Linux 11 - this machine will host PDI programs and will run carte.

As you can see below, the three virtual machines are located on the same subnet, using fixed IP adresses ranging from 192.168.77.128 (Master) to 192.168.77.130 (Slave 2). On the vmware side, I used a "host only" network connection. You have to be able to ping your master from the two slaves, ping the two slaves from the master and also ping the three virtual machines from your host. The easiest way is to disable the firewall on each Suse machine because we don't need security for this exercise.

The Master configuration

As I said, the Master virtual machine is hosting PDI, a mysql database and the PDI repository. But let's have a closer look to the internal configuration, especially with the Carte program config files.
From Pentaho wiki, Carte is "a simple web server that allows you to execute transformations and jobs remotely". Carte is a major component when building clusters because this program is a kind of a middleware between the Master and the Slave servers : the slaves will register themselves with the Master by notifying they are ready to receive tasks to process. On top of that, you can reach Carte web service to remotely monitor, start and stop transformations / jobs. You can learn more on Carte from the Pentaho wiki.

The picture below explains the registration process between slaves and a master.

On the Master, two files are very important. The files are configuration files, written in XML. They are self explanatory, easy to read :

Repositories.xml : your slave must have a valid repositories.xml file, updated with all informations about your repository connexion (hosted on the Master for this example). See below for my config file.
Carte xml configuration file : located in /pwd/, this file contains only one section for defining the cluster master (ip, port, credentials). In the /pwd/ directory, you will find some example configuration files. Pick one, for instance the one labelled "8080" and apply the changes described below. I will keep the port 8080 for communication between the Master and the two Slaves. See below for my config file.

Repositories.xml on Master

Carte xml configuration file on Master

The Slave configuration

As I said, the two Slave virtual machines are hosting PDI. Now let's have a look on how to configure some very important files, the same files we changed for the Master.

Repositories.xml : your slave must have a valid repositories.xml file, updated with all informations about your repository (hosted on the Master for this example). See below for my config file.
Carte xml configuration file : located in /pwd/, this file contains two sections : the master section and the slave section. In the /pwd/ directory, you will find some example configuration files. Pick one, for instance the "8080" one and apply the changes described below. Note that the default user and password for Carte is cluster / cluster. Here again the file is self explanatory, see below for my config file.

Repositories.xml on Slave1 and Slave2 :
Same as for the Master, see above.

Carte xml configuration file on Slave1 (note address is 192.168.77.128, don’t write “localhost” for Slave1)

Carte xml configuration file on Slave2 (note : address is 192.168.77.130, don’t write “localhost” for Slave2)

Starting everything

Now it is time to fire the programs. I assume you have already started mysql and your PDI repository is active and reachable by PDI. It is quite recommended that you work with a repository hosted on a relational db. Let's fire Carte on the Master first. The command is quite simple : ./carte.sh [xml config file].

This output means that your Master is running and a listener is activated on the Master adress (ip address) on port 8080. Now let's start the two slaves. Here again, the command is simple : ./carte.sh [xml config file]. Look below the output for the Slave1, you can see that Carte has now registered Slave1 (192.168.77.129) to the master server . Everything is working fine so far.

Finally the output for Slave2. Look below the output for the Slave2, you can see that Carte has now registered Slave2 (192.168.77.130) to the master server . Everything is fine so far here again.

   
At that point, we have a working Master and two registered slaves (Slave1 and Slave2) waiting to receive tasks from the Master. It is time, now, to create the cluster array and a PDI transformation (and a job to run it). Let's go for it.     
    
    
    PDI configuration
   First we have to declare the slaves previously created and started. That's pretty easy. Let's select the Explorer mode on the left pane. Do a left click on the "Slave server" folder, this will pop up a new window in which you will declare Slave1 like below.      
      
      
                  
            
Repeat the same operation for Slave1 and Slave2 in order to have 3 registered servers like the picture above. Don't forget to type the right ip port (we are working with 8080 since the begining of this exercise).              
              
Now we have to declare the cluster. Right click on the cluster folder (next folder) and choose New. This will pop up a new window in which you will fill the cluster parameters : Just type a new name for your cluster and then click on the "select servers" button. Now choose your three servers and click ok. You will then notice your cluster is created (Master and Slave) like below.         
       
      
      
      
      Creating a job for testing the clusterFor this exercice, I won't create a job but will use an existing one created by Matt Casters. This transformation is very interesting and will only read data from a flatfile and compute statistics in a target flatfile (rows/sec, throuput ...) for each slave. You can download this transformation here, the job here and the flat file here (21 Mo zipped).           
          
I assume you know how link a transformation into a job. Don't forget to change the flatfile location on source (/your_path/lineitem.tbl) and on destination (/your_path/out_read_lineitems). Then, for each of the first four steps, right click and assign the cluster (you named previously, see above) to the step. You will see the caption “Cx2” on top right of each icon. There is nothing else to change. Here is a snapshot of the contextual menu when assigning the cluster to the transformation steps (my PDI release is in french, so you have to look at “Clustering” instead of “Partitionnement”).         
        
        
      
      
      Have a look to the transformation below. The caption “Cx2” on top right of the first four icons means you have assigned your cluster to run these steps. On the contrary, the JavaScript step “calc elapsed time” won’t run on the cluster but on the Master only.         
                
And have a look to the job (calling the transformation above). This is a typical job, involving a start step and the “execute transformation” step. We will start this job with Kitchen later.         
       
      
      Running everything         
        
Now it is time to run the job/transformation we made. First we will see how to run the transformation within Spoon, the PDI gui. Then we will see how to run the job (containing the transformation) with pan in the linux console and how to interpret the console output. 
      First, how to start the transformation within Spoon. Simply click on the green play symbol. The following window will prompt at your screen. Once again, my screen is in french, sorry for that. All you have to do/check is to click on the top right button to select the clustering execution (“Exécution en grappe” in french). I suppose you are already quite familiar with that screen so I won’t continue explaining it.         
      
      
      Then you can run the transformation. Let’s have a look at the Spoon trace (don’t forget to display your output window in PDI, and select the Trace tab).         
        
            
This trace is fairly simple. First we can see that the Master (ip .128)found his two slaves (ip .129 and ip .130) and the connexion is working well. The Master and the two Slaves are communicating all along the process. As soon as the two Slaves have finished their work, we receive a notification '(All transformations in the cluster have finished”), then we can read a small summary (nb of rows).       
      
Let’s have a look on the Master command line (remember we started Carte by using the Linux command line). For the Master, we have a very short output. The red lines are familiar to you now, they correspond to Carte startup we did a few minutes ago. Have a look below on the green lines : these lines were printed out by Carte while the cluster was processing the job.       
             
        
        Let’s have a look at Slave 1 output. Here again, the red lines are coming from Carte Startup. The green lines are interesting : you can see Slave 1 receiving its portion of the job to run … and how he did it by reading rows (packets of 50000). You can also notice the step names that were processed by the Slave 1 in cluster mode : lineitem.tbl (reading flatfile), current_time (catch current time), min/max time and slave_name. If you remember well, these steps were flagged with a “Cx2'” on their icon on top right corner (see below) when you assigned your cluster to the transformation steps.           
          
                   
          
                 
          
          The output for Slave 2, displayed below, is very similar to Slave 1.             
                     
            
            That’s very funny to do ! Once you started Carte and created your cluster, you are ready to execute the job. Then you will see your linux console printing informations while the job is being executed by your slaves. This post is about understanding and creating the whole PDI cluster mecanism, I won’t talk about optimization for the moment.
            
            Hey, what’s the purpose of my transformation ?               
              
As I said before, this transformation will only read records from a flatfile (lineitem.tbl) and compute performance statistics for every slave like rows/secs, throuput … The last step of your transformation will create a flatfile containing these stats. Have a look at it. 
                         
              Once formated with a spreadsheet tool, the stats will look like this. 
               
              Don’t pay too much attention to the start_time and end_time timestamps : the time setup was not done on my three virtual machines, hence they are not in synch. You will also notice that, in the exemple above, the performances for these two slaves are not homogeneous. That’s normal, don’t forget I’m currently working on a virtualized environment built on a workstation and this tutorial is limited to demontrating how to create and configure a PDI cluster. No optimization was taken in account at that time. On a fully optimized cluster, you will have (almost) homogeneous performance.                 
                
Running with the linux Console                 
                
If you want to execute your job from the linux command line, no problem. Kitchen is here for you. Here is the syntax for a job execution. Note : VMWARE-SLES10-32_Repo is my PDI repository running on the Master. I’m sure you are already familiar with the other parameters. 
                             
                For executing your transformation, use pan. Here is the typical command.                   
                     
Conclusion and … what’s next ?                     
                    
Well, I hope you found here some explanations and solutions for creating basic PDI clustering. You can create more than 2 slaves is you want, the process is the same. Don’t forget to add these new slaves in the cluster definition in Spoon. As I said, no particular attention was given on optimization. This will be the topic for a next post in the near future. Feel free to contact me if you need further explanations about this post or if you want to add some usefull comments, I will answer with pleasure.
                  Next post will be about creating the same architecture, with … let’s say 3 or 4 slaves, in the Amazon Cloud Computing infrastructure. It will be a good time to speak about could computing in general (pros, cons, architecture …).

Spatial, you said spatial ?

2009-11-18T08:26:00.000-08:00

Hi all,

Spatially enabled datawarehouses, geospatial datatypes, geospatial BI ... etc ... a lot of buzz around this topic over the past year. Maybe the next BI grail !

Geospatial data helps users to visualise how usual business data (customer, products, time ...) are impacted by geography.
For instance, a lot of telcos are building spatially enabled analysis and reporting. Just imagine some advanced analytics like : how is my customer using his cellphone, when is he using his cellphone and WHERE is he using it ? Now imagine the answer displayed on a map instead of a classical dashboards and spreadsheets. Amazing, no ?

Have a look on this web site : Spatialytics
You will find some very interesting explanations about geospatial datawarehousing and also a spatially enabled version of Kettle : GeoKettle. Also a release of Mondrian, called GeoMondrian .... spatially enabled. According to Spatialytics, GeoMondrian is the first open source SOLAP player.

I started to test GeoKettle. Quite interesting. Of course you need to have some spatial data near you to play with but this should not be an issue for you according to Franklin (1992 - An introduction to Geographic Information Systems : linking maps to databases) : "About 80% of all data stored in corporate databases has a spatial component".

Have a look to your data and become spatially enabled !

Another reading ...

2009-11-16T23:52:00.001-08:00

Hi all,

Another interesting reading (at least for me) about BI and Open Source.
This one is coming from Claudia Imhoff. Usually I'm not a great fan of her and her visions but I think she wrote quite a good white paper here.

BI and Open Source

Happy reading.

Massive but agile : very good article from Forrester

2009-11-16T12:22:00.000-08:00

Hi all,

I just finished reading this study from Forrester.
Massive but Agile, the next generation Enterprise Datawarehouse.
Very good reading on how to make a big elephant move like a ballet dancer ;)

Forrester Massive but Agile

Enjoy and ... discuss if you want.

Cleaning strings

2009-11-14T13:14:00.000-08:00

Hi all,

A javascript, usefull for a string manipulation toolbox.
Puts the first character in uppercase, the others in lowercase. Example : Vincent instead of vincent.
This code is specific to Kettle (Input.getString) but can be used in Talend with little change.

//First letter in uppercase, others in lowercase

var c = Input.getString().substr(0,1);
if (parseInt(Input.getString().length)==1)
{
var cc = upper(c);
}
else
{
var cc = upper(c) + lower(Input.getString().slice(1));
}

My new NAS

2009-11-14T11:48:00.000-08:00

Hi all,

I'm currently working on personnal developments around BI and I needed a little NAS to store everything.

Finally, last week, I chose the Digitus one. A really nice piece of hardware. I think Digitus is a German company.
Let's have a look closer :

Dual SATA disks,
Raid 1 (mirror),
Embedded HTTP and FTP server,
Multiple filesystems (ext, ntfs, fat ...),
File sharing,
Ethernet,
Torrent features (continue to download from torrents when your PC is off),
Dual USB2 plugs and special features to copy from usb to internal disks.

I also bought two SATA disks with 1 To. These two disks are now in Raid 1 (mirror) in order to secure all my data.

Speed is good, both with reading and writing. I just tried to open and use a vmware virtual machine from this NAS and everything worked well. The NAS is provided with an external PSU and is not very noisy (ok to stand on a corner of my desk).

Here are some pics.

Quick list of ETL tools

2009-11-13T09:00:00.000-08:00

Hi all,

A lot of work these days ... I'm back.
Soon to come : a complete overview of my current BI work on the Amazon Cloud (EC2).

For now, a quick list of - more or less - free ETL tools.

PDI (Kettle) from Pentaho : http://www.pentaho.com
Talend Open Studio : http://www.talend.com
KETL : http://sourceforge.net/projects/ketl/
Apatar : http://www.apatar.com
Pequel : http://sourceforge.net/projects/pequel/
Jasper ETL (= Talend Open Studio) : http://www.jaspersoft.com/jasperetl
Clover ETL : http://www.cloveretl.com/
Octopus : http://octopus.enhydra.org/
Benetl : http://www.benetl.net/

Free/open source DB Modelling tools

2009-03-11T11:35:00.000-07:00

Hi all,

A quick overview of free and/or open source DB modelling tools.
I use them quite often, depending on the work I have to do and features I need.

ARGO UML : http://www.argouml.org/
AZZURI : http://www.azzurri.jp/en/software/clay/index.jsp
DB DESIGNER 4 : http://www.fabforce.net/dbdesigner4/
DIA : http://www.gnome.org/gnome-office/dia.shtml
DRUID : http://druid.sourceforge.net/
TCM : http://wwwhome.cs.utwente.nl/~tcm/

Feel free to send a comment if you use another tool.

Kettle : transforming number into hh:mm:ss

2009-02-13T14:48:00.000-08:00

Hi all,

Another little code for closing the week.
I'm gathering data coming out of PABX systems. Time is stored with numbers.
Sometimes you just don't want to see 3662 seconds but 01:01:02, with [hh:mm:ss] format.

Be carefull, hh:mm:ss is a string format : you won't be able to do calculations nor apply maths functions on it anymore.

Javascript code (sorry for poor blogspot code formating) :

var time_nb = time.getNumber();

var hh,mm,ss;

var TimeFormat;



ss = time_nb % 60;

mm = time_nb / 60;

hh = mm / 60;

mm = mm % 60;



if(hh<10)(hh="0" + hh);

if(mm<10)(mm="0" + mm);

if(ss<10)(ss="0" + ss);



TimeFormat = hh + ":" + mm + ":" + ss;

Email spelling check with Kettle !

2009-02-13T09:31:00.001-08:00

Hi all,

Today, I had to check email spelling across some very large files before loading into database.
Kettle helped me here, with some javascript, regular expression and a conditional branch to route the good and bad emails.
Below is the transformation :

And the javascript code ....

//Javascript with regular expressions to test email spelling

// Code by Vincent Teyssier

var email_string = email.getString();

var pattern=/^([a-zA-Z0-9_\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$/;

if(pattern.test(email_string))

{

var Emails_status = "good";

}

else

{

var Emails_status = "bad";

}

Database comparison

2009-01-31T09:40:00.000-08:00

Hi all,

I want to start a large post dedicated to relational database comparison.
I will focus on Open Source, of course, and will try to do something graphical.
For the moment, have a look to this very interesting wiki, showing some very large comparison matrix.
Worth a look !

Hiring again !!

2008-11-09T10:40:00.000-08:00

Hi all,

I'm hiring again for a CDI contract in France : a Business Objects developper.

Mission :
Reporting and BI Development for Belgium clients and day to day support for internal clients.
Mainly Business Objects.
Development is 70% and day to day support is 30%.

Activity :
Call center.

Location :
Paris, Malakoff. With travels to Belgium.

Profile :
Junior, experienced with 2 years using BO in BI projects.
Good knowledge of BO 6.5.x, Webi, Infoview, Designer, Reporter and Supervisor.
Databases : Oracle 9i (development)
ETL : BO DataIntegrator (BODI XI) is a must have or any other significant experience with other ETL (Powercenter, Datastage ...).
VB, .Net ... would be nice.

If you want to apply / meet me, please reach me here (cut nospam) :
vincent.teyssierNOSPAM@sitel.com

Hiring !

2008-04-18T06:21:00.000-07:00

Hi all,

I'm looking for an ESSBASE consultant for a 15 days mission in Madrid.
Objectives are :
- Analysis and diagnosis of existing architecture,
- Add multi currency features in 2 cubes,
- Training for actual administrator.
English spoken mandatory.
If you want to apply, please reach me here (cut nospam) : vincent.teyssierNOSPAM@sitel.com

Publishing data from datawarehouse to HTTPS

2008-03-25T10:25:00.000-07:00

Hi all,

Last week, I had to publish a datafile to a HTTPS location, for one of our clients here in France.
Easy to do with gui, but how to automate and embed in a DTS or ETL process under Windows ? Not easy.
What did I use ? Open source cURL !!
Here is the recipe I followed.

1 - Install OpenSSL on your machine, Windows compilation of course.
2 - Take cURL, a famous Unix / Linux tool. Take a Windows compilation with SSL support and forget the cygwin version (beurk).
3 - The certificate part, the moste tricky :
Windows / IE use certificates in the form P12 (PKCS12), which is different from the cURL Unix certificate : PEM.
So you have to convert the certificate and extract the userkey file, the usercertificate file and the ca file !
To do the conversion, use the following commands, with OpenSSL. I did it on a Linux box :

First, extract the userkey.pem from the certificate :
vince@fc: openssl pkcs12 -nocerts -in cert.p12 -out userkey.pemEnter Import Password: (insert your certificate password)MAC verified OKEnter PEM pass phrase: (insert your Enter PEM pass phrase)Verifying - Enter PEM pass phrase: (reinsert your Enter PEM pass phrase)

Then extract the usercert.pem file:
vince@fc: openssl pkcs12 -clcerts -nokeys -in cert.p12 -out usercert.pemEnter Import Password: (insert your certificate password)MAC verified OK

Finally, extract the ca.pem file :
vince@fc: openssl pkcs12 -cacerts -nokeys -in cert.p12 -out ca.pemEnter Import Password: (insert your certificate password)MAC verified OK

4 - Then you can type your cURL command :
curl -u toto:tata -E certif.pem:passphrase --cacert ca.pem --proxy-ntlm -U FRLAR01\toto:tata -x proxyserver:8080 https://213.41.176.116/quix -v -T D:\FLATFILES\Client\Export\File_to_export.txt

Explanation of the command :
curl is the command, of course
-u is for user:password to the HTTPS site. Here I anonymised of course ...
-E is for the new converted client certificate in pem format
--cacert is for giving the ca certificate in pem format
--proxy-ntlm is for allowing proxy to connect in case of a proxy usage
-U is the credentials for the proxy. Here again I anonymised the data
-x is the proxy:port adress
-https ... is the destination adress on https
-v is for verbose mode. Usefull.
-T is for indicating the file to transfert. Here the file is name File_to_export.txt and is a txt datafile

You can see here I choose to use the proxy on each command, instead of setting up the proxy on my server. This is why I don't need any ISA proxy client on my datawarehouse server, so by using a direct proxy connection when needed, I save some server resources.

I hope this will help the community for any next HTTPS data push.

BOBatchConverter release 2.2

2008-03-20T13:21:00.000-07:00

Hi all,

After some very interesting feed back from users, I made a new release of the BOBatchConverter. We are now in version 2.2.
What was done / fixed :

handling pathnames with spaces ... like C:\BO DOCS\Report v2.rep,

mailing process was hardened,

new syntax : you have to prefix your command line arguments with the token "--" like --user or --REFRESH,

you can use ; to separate the different emails in the profile manager (previous was a stupid ",").

Please, find the release here.

BOBatchConverter V2 : now more reliable process management.

2008-03-11T14:17:00.001-07:00

Hi all,

Here is new release of my Business Objects converter utility : BOBatchConverter
As usual, you can use this command line tool as a Broadcast Agent (BCA) replacement : refresh, convert to any format (XLS, PDF, CSV, HTML), publish to WebIntelligence and / or send by mail.
This new feature offers a more reliable process management. You can now run several and concurrent instances of BoBatchConverter.

You can download it here.
WARNING : the release 2.2 is available with major fixes.

COLORmyTail : Unix Tail for Windows !

2008-02-19T08:52:00.000-08:00

Hi all,

Today I will give a little program I've coded in .NET and named ColorMyTail.

You all know the tail command on Unix : great command to parse a log file in real time.
I decided to code my own on Windows platform because this command does not exist.

But I also decided to include some value added : colorization !

To be short, my tail command works the same as on Unix but colors can be applied to the lines according some keywords.
Exemple : red when a log line contains ERROR, or green when the line contains SUCCESS.
The keywords / color association can be decided in a Formating.ini file.

Very usefull to monitor some sensitive logs on Windows platforms.
Caution : some troubles with Vista.
The install package is here.

Below some snapshots. The first one is really running in some prod environment for file routing, the second is to illustrate the full colorization.

HALOGEN : olap4j viewer for Mondrian

2008-02-18T00:50:00.000-08:00

Hi all,

Look at this promising HALOGEN web page.
An olap viewer that plugs to Mondrian or XMLA.
Still in early dev stages, but promising.

http://code.google.com/p/halogen/

FOSDEM !

2008-02-18T00:01:00.000-08:00

Hi all,

Don't forget the FOSDEM : http://www.fosdem.org/2008/
The Free Open Source Software's Developpers European Meeting will take place in Brussels (Belgium) from 23 to 24 February.

Matt Casters (Kettle /PDI) will be giving a talk about Kettle.

Talend Open Studio 2.3

2008-02-17T23:48:00.001-08:00

Hi all,

Talend Open Studio 2.3 is now available for download here : http://www.talend.com/download.php?src=HomepageTechNews

Currently in testing on my infrastructure : great product.

Will come back soon with more feed back.

Kettle V3.0

2007-05-30T06:59:00.000-07:00

Hi all,

I'm happy to come back on my blog (I had hard job these days, so no more time to write) and post a message for Kettle V3.0 announcement !
V3.0 is a major breakthrough, according to Matt Casters "we’re aiming for a strict separation of data and metadata".
Performance gain ? Around 15-20% !! Some speed transformations show a x5 factor !
Nice job, guys.
Here you can find a quick speedup comparison made during regression tests against version 2.5.
V2.5 - V3.0 Speedup comparison

New Look & Feel for Kettle 2.5.0 !!

2007-04-12T05:18:00.000-07:00

Hi all,

Just a quick message to show you how wonderfull the new Kettle 2.5.0 gui looks like !
Nice colors, nice menus, more profesionnal screens.
Still not as nice as Talend Studio, but getting closer ...

No BI for today ... just great music !

2007-03-27T04:53:00.000-07:00

Hi all,

Today I'm a bit tired.
Yesterday, I went to see TOTO in concert, in Paris.
Great moment, great music, great band, great Lukather ...

It was really cool to forget BI, servers, data, ETL ... during a while and been transported back in the 80's.

Here is a nice shot of the concert.

My personnal storage system

2007-03-26T06:07:00.000-07:00

Hi all,

I've just received my new storage system : a nice Promise UltraTrak RM4000.
Loaded with 1,5 To disk space in raid5 ... coooool.
Now I can work with really nice amout of data for ETL processes, DB optimization and OLAP cubes (and maybe some mp3 too ...).

Here is a snapshot of the system.

Talend Open Studio 2.0.0 new features

2007-03-26T03:22:00.000-07:00

Hi all,

Today, let's see what happening with TALEND Open Studio (TOS) 2.0.0.

According to talend.com, "Java Generation language is the new core feature added to Milestone 2 of Talend Open Studio v2.0 release. A number of Perl connectors now have their Java counterparts."

Great to see java is now available !

More new features :

New specific MySql and Oracle components,
Mysql "Bulk" components,
tFileFetch component,
tRowGenerator2 component,
Technical components: tFor & tSleep to implement a loop !
tAggregateRow component enhancements for better agregation,
External components, to link to your own production.

More to read here : http://www.talend.com/products/whats-new.htm

A complete Kettle + Mondrian feed back

2007-03-22T08:50:00.000-07:00

Hi all,

After more than 4 months spent on Kettle and Mondrian, I wrote a review on the french developper network.
This review is written in french and you can find it here : http://www.developpez.net/forums/showthread.php?t=253349

Please, feel free to answer and share your personnal feed back.

My Unix way to parallelize PDI/Kettle jobs ...

2007-03-12T08:28:00.000-07:00

Hi all,

Today I want to share some code I used in order to parallelize jobs, at OS level (for me, it's Solaris).
My needs are :
- run 2 jobs in parallel, each on a separate engine,
- run a third job when first 2 parallel jobs are both complete.

I decided to design 2 separate jobs, then manage the execution with simple bash scripting using temp files as flags.
Here is the final code (extract from larger sh file) :

First 2 blue lines : 2 jobs to run in parallel, create a flag file for each command.
Red lines : a flag control to detect if both jobs are complete (= presence of flag file).
Last blue line : the third job to run after the completion of the parallel ones.

Calling a DB procedure within PDI/Kettle

2007-02-26T01:29:00.000-08:00

Hi all,

Today, I want to share an interesting trick to run a DB procedure in PDI/Kettle.

But anyway, where's the trick, since there is a dedicated transformation to run such procedure ?

I discovered the following : when trying to run a DB procedure with PDI/Kettle using this dedicated job, nothing happens, the procedure does not run and no error is logged.

The answer is : first, you need to call a "Row generation" transformation and link it to your "Call DB Procedure" transformation.

But hey, don't forget to write "1" in the Limit field for the "Call DB Procedure", otherwise the procedure will run more than once !!!

I don't know if this is a real issue, but anyway who cares ? The job is finally done !!

Here is the snapshot.

Interesting tip for Mondrian with XML/A

2007-02-19T07:01:00.000-08:00

Hi all,

A few days ago, I discovered an interesting tip when using Mondrian with XML/A.
For the moment, I ignore if this issue is documented (and if it is a real issue ...).

Let me explain : when using Mondrian + XML/A on a schema containing dimensions with "name" properties in hierarchy, the schema validation crashes, so the query execution.

Example of faulty code, in XML/A mode :

Now, the working code. Note : replace Hierarchy name with Hierarchy caption ...

Just keep in mind I'm using Mondrian in XML/A mode on a Sun Sparc server with Solaris 8 and SunONE as application server (Oracle 8i as database).

Another cool new feature for PDI / Kettle !!

2007-02-15T00:44:00.000-08:00

Hi all,

Here is a snapshot of a long awaited feature for PDI / Kettle.
This is an advanced error handling feature !! A step further toward Data Quality Management in PDI !
Have a look to the transformation below, to see how to implement the process. Very simple.
More to learn on Matt Casters Data Integration Blog.

That's no BI, but have a look at it ...

2007-02-08T05:28:00.000-08:00

Hi all,

Today I want to introduce you to marketae.
Marketae will soon become one of a major web 2.B actor.
Did I say Web 2.b ? Well, that's for Web To Business !
The goal of marketae is to create, manage and develop preferred links between a buyer and a seller. See it as a specialized market place (a kind of clever mix between Ebay, Viadeo and LinkedIn), where it will be possible to note to your partner in business.

Christophe Vigliano is the founder of Marketae, with who I had pleasure to work during our SITEL adventure.

Feel free to visit marketae. Official opening in a few days.

A complete fact capture job

2007-01-29T06:25:00.000-08:00

Hi all,

Here is a job used to capture fact data on a daily basis.

Data is coming from a source table where :

- time is stored in columns, 1 column for 1 day = 31 columns for a month, from 1 to 31,

- indicators are stored on rows : an indicator code for each indicator type.

This source structure is not mine, the challenge to integrate and manage, is ;)

Here is the DDL of source table :

HOTEL_CODE,

INDICATOR_CODE,

YEAR,

MONTH,

ACTIVITY_TYPE,

CONCEPT_CODE,

DAY1

...

DAY 31

Here is the DDL of my destination table :

ID,

HOTEL_CODE,

TIME_KEY,

YEAR,

MONTH,

INDICATOR1

...

INDICATOR12

TIMESTAMP

The job begins with a GetVariable job to capture new timestamp for new data from a previous job used to calculate the gap.

Then a first normalization puts data from "31 columns for a month" to one column named "Duration" (coz we deal duration data). At this moment, I chose to store an intermediate level of data in a temporary table, for performance purpose. Then, you can see a fork to load two types of tables :

- an agregated one, with denormalized data for indicators (each code indicator, we have 12, becomes a column),
- normalized one, used to print very specific reporting, back on the intranet application.

Job is working great in prod environment. The SORT operation is a bit slow, but nothing really dangerous for our timing.

For informations, please feel free to contact me : mailto:vteyssier@decisionsystems-studio.fr

A complete dimension update job with PDI

2007-01-19T02:29:00.000-08:00

Hi all,

Here is my dimension update job.

A lot of processes here, as you can see. Works great under PDI 2.4 and I still rely on target database functions to better manage timestamps and sequences.

Kettle / PDI release 2.4 !

2007-01-09T12:24:00.000-08:00

Hi all,
Just received my 2.4 release of Kettle / PDI (Pentaho).
Seems quite exciting, look at these nice new features (sorry, in french).
I'm going to have some testing as soon as tomorow with a +22 million rows table ...

BOBatchConverter : release 1.4

2007-01-08T08:46:00.000-08:00

Hi all,

Here is the last release of BOBatchConverter. This tool will allow you to run, refresh, convert, post, send, email, publish any single Business Objects report or multiple reports.

Each action can be done with specific profiles (emailing ...) and I also provide a gui to manage your profiles (stored as XML files).
It's a command line tool, runs under XP or Server 2003 and can be used in conjunction of a good scheduler to become a BCA like (BCA is the BroadCast Agent, the batch mode of Business Objects).
Sure Business Objects is not free but this tool, which I keep developing since last October in .NET technology, is.

Here is the link to download the archive with documentation on how to use it : BoBatchConverter
Here are 3 snapshots : the profile manager, the xml file and the console output.

Mondrian + JRubik : a good team

2007-01-08T07:41:00.000-08:00

Hi all,

Here is (first picture) a snapshot of a nice cube analysis done with Mondrian in conjunction with JRubik, a very nice MDX editor. On the second picture, you can see the same analysis rendered with JPivot within Internet Explorer.
As you can see, with little code, things can be quite good looking !

This analysis contains 2 time series (A and A-1) and 2 calculated members (difference between A and A-1, and Evolution). As you can see, time is hardcoded for the moment on MDX code, that's because parameters from user will be refreshed at run time directly in the jsp (JPivot architecture).
Some captions are hidden for confidentiality.

Kettle / PDI new version to be released !

2007-01-07T02:59:00.000-08:00

Hi all,

Here is link to a flash animation showing one of the finest Kettle / PDI new funtionnality, soon to be released (2.4).
You will have the ability to load, execute, monitor transformations AND jobs within Spoon !

Flash demo is here, from Matt Casters' blog : Flash new feature
Matt Casters's blog on data integration : http://www.ibridge.be

More on new Kettle / PDI 2.4 release :
- remote transformation execution and monitoring,
- cluster environment for transformation run.
Kettle / PDI now allows to "scale beyond single server processing and into the massive parallel world" (cf Pentaho).

Release date is expected to be during the second half of January.

A cool feature of Mondrian Schema

2007-01-02T08:46:00.000-08:00

Hi all,

Today I tried to write SQL embedded in a Mondrian schema. This feature is really handy when it comes to design very small dimensions on the fly, without creating any object in the DB.
Use it for small dimensions, with few distinct values.

Here is the XML code I used today :

Then you can access your dimension with simple MDX statements :

[FAMILLE DE SERVICE].[Tous Services],

[FAMILLE DE SERVICE].[Tous Services].[Bar],

[FAMILLE DE SERVICE].[Tous Services].[Buanderie] ...

Very nice.