Excel template for IATI data and guidence on database design

Community Zone Using IATI Data
#1

I’m curious if IATI has produced an official spreadsheet template organizations can use to begin databasing activity information using IATI Standard information fields and codes? I’ve seen CSV files published by different organizations but I can’t find a complete spreadsheet with column headings for every conceivable field?

Ultimately we’d like to build a PostgreSQL database and it would be helpful to know how folks are handling database design, tables, columns and rows. Can anyone refer us to a database model we can replicate that’s been tested and improved through use?

#2

Hello Brent, DFID have open sourced the database that we use to generate our IATI v2.01 standard data on GitHub: https://github.com/DFID/SQL-to-IATI-Database.

It has been created in SQL Server but it works with the freely available Express edition of the software.

Kind Regards,
Ross

#3

Hi Brent,

Perhaps the OIPA db model could help you: https://github.com/zimmerman-zimmerman/OIPA/wiki/Database-implementation-model - we updated the model to reflect current state of affairs with OIPA, but some of the documentation needs to be updated. Ping me if you require more info.

#4

Siem,

Thanks so much. That’s a huge help!

1 Like
#5

I’m curious, perhaps this is a dumb question, would it be feasible to use IATI XML XPath elements as column headings? They cover all IATI’s possible data fields.

What are your thoughts? Would it be useful for IATI to create an official list of unique XPath to Heading conversions?

#6

Yes, I think this this would be feasible. We built a tool to do something similar for OCDS and 360Giving - https://github.com/OpenDataServices/flatten-tool

These are JSON rather than XML, so we use JSON Path rather than XPath as column headings.

As an example, see the template for OCDS https://github.com/open-contracting/sample-data/tree/master/flat-template

#7

Thanks, I’ll refer your links to our data scientist. Off hand, for testing, do you use a local copy of the entire IATI dataset? Generating or acquiring one is on our todo list this week.

#8

https://github.com/IATI/IATI-Registry-Refresher is probably the place to start looking for downloading a local copy of all IATI data.

#9

Hi @siemvaessen - was just following up on this thread. Is the OIPA DB model still readily available for interest? I tried the wiki link but this take me to a ‘create new wiki page’ form, so the docs may have been moved.

Also @BrentPhillips - would be great to hear the findings from your data scientist, or any template that is generated. I’m sure this could be of interest to others in future too.

#10

Hi @dalepotter - we do make use of the Github WIKI anymore and have moved all docs to: http://docs.oipa.nl/en/latest/

Perhaps @VincentVW could assist your here for more specifics if the docs won’t help you out.