Last week I was asked If I could help design the integration between ERP systems and a Jira application, the idea was to deploy a new SQL server instance and an application server to create a “funnel” application of some sort. The ERP data should be massaged into an access friendly format for the Jira app.
I like agile development so I created a crude prototype for testing and then iteratively refine the prototype together with users, the first thing I did was to create a process map:
For the prototype I suggested the Data Warehouse since it is up and running and is designed for this type of applications. For this first prototype we decided to use a few handmade MS Excels as data, these Excels were FTPed over to the DW FTP landing zone, and then I created an ITL script:
If you look carefully you can see I added support for an XML and a cvs format as well.
The I run the script:
And the result:
Now we have to wait wait for the Jira guys to tell if this table is OK for them or if we have to modify our prototype.
As you can see there are some functionality cramped into the script. Look at the <forevery> line, intersect=FTP, means this list is sifted by the actual FTP directory, removing lines which have no corresponding file in the FTP directory, which makes the script simpler since we do not have to think about ‘file does not exists’ conditions.
FTP is not something used for anything else than truly public data these days, but it is easily to setup and for the prototype with test data it works well.