Excel template for IATI data and guidence on database design

Moderators

No moderators on this discussion

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?

Comments (9)

r_clements • 8 years ago

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

Brent Phillips • 7 years ago

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?

Ben Webb - IATI Secretariat • 7 years ago

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

Brent Phillips • 7 years ago

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.

Ben Webb - IATI Secretariat • 7 years ago

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

Anonymous • 8 years ago

BrentPhillips:

Can anyone refer us to a database model we can replicate that’s been tested and improved through use?

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.

Brent Phillips • 8 years ago

Siem,

Thanks so much. That’s a huge help!

Anonymous • 7 years ago

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

Perhaps Vincent van 't Westende could assist your here for more specifics if the docs won’t help you out.

Dale Potter • 7 years ago

siemvaessen:

Perhaps the OIPA db model could help you

Hi [~379] - 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 Brent Phillips - 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.

Please log in or sign up to comment.