DfID SQL-to-IATI tool - easy to integrate?

Community Zone
#1

Hello,

I’m looking to find a short-to-medium-term solution for publishing at IRC-UK, and the other day a colleague from Malaria Consortium mentioned the SQL-to-IATI. I understand that Malaria Consortium and DfID use it to publish IATI data.

I am keen to hear any experiences relating to this tool. To cue anyone that can chip in…

  • Which software systems has it been tested with?
  • Is there an estimate on start-up times, how long it has taken?
  • Would developers need to be brought it, or can non-devs work it out?

I read through the Git-Hub posting, but I would appreciate any narrative people can offer that explains the tech.

Thanks,

Daniel

#2

Link to GitHub repository: https://github.com/DFID/SQL-to-IATI-Database

Tagging @JohnAdams @r_clements too

#3

Hello Daniel,

There is some narrative text on the IATI website, which might explain the purpose of SQL to IATI a little further: http://iatistandard.org/201/guidance/how-to-publish/select-publishing-tool/#sql-to-iati

We open sourced the database so I will answer your points as best as I can, if you would like any further information please just reply to this post.

•Which software systems has it been tested with

The database was developed on SQL server & worked with the community edition which is freely available to download - though I am not sure what the licensing restrictions are like if you were to use it in a production setting.

• Is there an estimate on start-up times, how long it has taken?

I am afraid not, this database has been in use since we started publishing IATI data, in some form or other, so we haven’t had to start from scratch in a very long time.

The challenge is not the time taken to set up the database, which could be done relatively quickly by a database administrator, it would be trying to integrate the database with your finance system so that it gets populated with accurate information that is suitable for publication.

•Would developers need to be brought it, or can non-devs work it out?

I think you would ideally have a Database Administrator work on the project and somone who understands processing project & finance data from your core systems using SQL (i.e. the job of creating a custom version of p_populate that’s tailored to your organsation’s needs).

Kind Regards,
Ross

#4

Dan, I know that a few others are either using SQL-to-IATI or have tried to use it - OJ in Denmark, Carl and team in Sida, and Antoon in Belgium. It might be worth a chat with them about how they use the tool - or reasons for not using it.

#5

Hi John, Hi Ross

Thanks for both coming back to me so quickly on this one. There are some very useful points in the above.

I’ll do a bit more research and get in touch with others who may have experiences.

Any other thoughts, please add more on this thread.

Best,

Daniel

#6

Yes, in DK MoFA we are happy users of Dfid’s DB. And we can recommend it - but rather as the long-term solution.

We use it as a data-mart - as a destination for updates, processed by our DataWarehouse.

For us, this has several advantages: We maintain our existing architechture; we complete the etl-process in this internal DB, which can then be copied and restored in our DMZ (avoiding any security / Firewall issues); and we can rely on the fact that Dfid’s developers have created a reliable set of stored procedures to generate the ultimate IATI XML files.

We have added a few elements that wasnt completed at the time Dfid published the current DB - and we have added an API of our own design to provide various additional features on top of the DB (standardises pagination - and an o-data feature that allows us to use Excel to pivot the raw data).

Both the updated version we have made of Dfids DB and our API is mae available on GitHub. I haven’t tried that before, so please let me know if there is something we ought to do better for that sort of publication.

Yours OJ

1 Like