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

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

Tagging John Adams r_clements too

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

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