Automating Data Delivery using the OData Endpoint in ODK Central

Overview

ODK Central includes an OData endpoint that can be accessed by tools and languages that support RESTful web services. Both Tableau and PowerBI support OData, allowing users to download survey submissions for immediate analysis. But what about other scenarios? A common requirement is to retrieve ODK submissions, extract the survey data and load it into a data portal or a relational database. ODK Central supports this use case as well.

The goal of this post is to show how survey data can be processed using an open source data integration tool (Kettle). We retrieve submissions from a survey hosted on the ODK Central sandbox server, transform the JSON document into a set of rows, then load the rows into a dataset stored in a CKAN data portal. The workflow is implemented using a small number of Kettle steps and can be freely modified by anyone in the open source community. Additional scenarios are discussed and documentation links are provided at the end.

The complete post can be found at https://github.com/schemetrica/automating-data-delivery-odk-central.

Screenshots:

7 Likes

Thanks for sharing this, @Dave_Henry! There is so much money spent building custom software that do these sorts of integrations and it's amazing that Central and Kettle can make that happen for no code and no money.

If folks are interested in using Kettle, how can they find out more?

2 Likes

There's a very active Kettle community at https://kettle-community.slack.com/. It's in the process of being opened to the general public. In the interim I'm glad to sponsor invitations - send me a request through the ODK forum or at dave.henry@schemetrica.com.

hey this is really rad! i've been preaching the kettle gospel since the inception of Central but it's really nice to see some concrete material describing how to make it a reality. would you be okay if we linked or adapted it to the odk documentation website?

2 Likes

Yes, most definitely ...

@Dave_Henry that looks amazing, fills a gap for non-progammers getting their data out.

May I include your Kettle tutorial in the comparison at ruODK?

1 Like

Yes. I think most shops will benefit from using Kettle side-by-side with R as well as Python.

1 Like

@issa @Matthew_White would it make sense to include Kettle and this tutorial in the "OData" tab? Maybe also in ODK docs?

Wider discussion: Where would be the best place for a (always kept up to date) collection of ODK Central tutorials? How could maintainers of tutorials keep in the loop of upcoming changes to ODK Central's API?

@Dave_Henry see https://github.com/opendatakit/central-frontend/issues/252

hello
One question about kettle. Is there any example of use with Neo4j?
thanks
gus

So this should be possible with node-red as well. When tested I will come with an example of the flow setup.

Hi @Dave_Henry,

Its been sometime on this thread, just to confirm if Kettle is stable with ODK Central v1.3, I have a similar scenario where it would be very helpful to have data in Postgresql DB within tables as well.

2 posts were split to a new topic: See image in OData feed