Add OData spec endpoints for project export and visualisation

[spwoodcock]

Is your feature request related to a problem? Please describe.

• ODK uses the OData spec: https://www.odata.org/documentation/
• The v4 OASIS spec is here: http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part1-protocol.html
• OData is a standardised endpoint that many different tools can read, e.g. PowerBI, Tableau, etc.

Describe the solution you'd like

• It would be nice to add OData endpoints: /projects/{project_id}.svc
• To implement filters the odata-query looks good.
• A Pydantic model should be made for the OData output.

Describe alternatives you've considered

• There are other specs available. This is probably one of the best and most supported.

[spwoodcock]

As a first pass, perhaps it might be good to just add some documentation for how to connect PowerBI or other tools directly to the ODK Collect OData endpoints?

This is a very quick win, as the endpoints are already available and should provide most of what we need!

[spwoodcock]

Example data

Example OData endpoint (requires auth): https://odk.hotosm.org/v1/projects/86/datasets/features.svc/$metadata

This endpoint will be used to get all available endpoints and data, e.g. Entity data: https://odk.hotosm.org/v1/projects/86/datasets/features.svc/Entities.

Possible OData Clients

• PowerBI

  • Proprietary Microsoft.
  • Supported by default not recommended target.

• Tableau

  • Proprietary.
  • Supported by default, but not recommended target.

• Grafana

  • Open-source.
  • More for service monitoring, but can be used for all sorts.
  • Probably won't look as good though, as it's not really made for this use case.

• Redash

  • Open-source.
  • Probably the best option for us - we can load the OData JSON endpoints then filter as we need.
  • Can probably load any JSON data, not just OData, so is more flexible.
  • We could load ODK Collect OData + FMTM endpoints we have currently, with filtering in place.
  • Note the setup repo to run this: https://github.com/getredash/setup

Plan of action

• We test making ODK Collect OData endpoints accessible - to do this it looks like we need to connect with ODK Collect basic auth?
• We could possibly relay this via an FMTM endpoint, to actually create a session and return the data without using basic auth. Then pass the data onto the OData client. To do this we may need to make the endpoint public, or use another auth method though.
• Test out using Redash and likely recommend that as the visualisation platform.
  • We could even bundle this with FMTM and have a preconfigured dashboard.
  • For non-technical users we could recommend PowerBI as it's likely the easiest option.

[spwoodcock]

Using PowerBI (Desktop) for OData Endpoints

1. Start a new project, add a new data source 'OData Feed'

   

2. Use the OData URLs shown in the ODK docs: a. Submission data: /v1/projects/{projectId}/forms/{xmlFormId}.svc e.g. https://odk.hotosm.org/v1/projects/86/forms/df9135c8-84b1-4753-b348-e8963a8b4088.svc a. Entity data: /v1/projects/{projectId}/datasets/{name}.svc e.g. https://odk.hotosm.org/v1/projects/86/datasets/features.svc

   

3. Enter your credentials using Basic Auth

   

4. Preview the data

   

5. Transform data as needed, load into charts, graphs, etc, to create the dashboard you need.

   

This very basic example shows the mapping progress of project 86 in the production ODK Central server.

It shows that about 50% of buildings were mapped, with about 2% having issues (status=6)

[!NOTE] This is using ODK Central OData endpoints that are already available. This issue is about creating new OData endpoints.

While this may be useful to reassess in the future, I actually think the ODK Central endpoints provide pretty much everything we will need to cover most use cases!

@ramyaragupathy @manjitapandey I am confident this approach can fulfill all the requirements for the Tokha project coming up:

• We can view submissions and Entity data for each project as a data source.
• The data sources can be combined in any way we may require into tables, graphs, dashboards.
• Like I said in the call: FMTM should not reinvent the wheel. Powerful visualisation dashboards has been done 1000 times over for business intelligence needs. Developing a bespoke for FMTM would be unwise in my opinion.
• Ideally we should 1) facilitate data collection 2) make the data available in a useful format that other tools can ingest.

I'm going to close this issue in favour of another around documenting this process well, so that people can create their own visualisations easily.

[!NOTE] I do still think we should have a basic visualisation dashboard in FMTM to cover most user needs, for those that can't /won't be able to set up the proposed solution. But it's not the primary goal of FMTM.

[manjitapandey]

so @spwoodcock , we will be using PowerBi or Redash?? I am not clear on this.

[spwoodcock]

Both!

Redash needs more research into the config and is a possible long term solution for us + recommendation for technical users.

PowerBI is recommended for immediate use and non-technical users too. For Tokha at least we definitely recommend this 👍

[spwoodcock]

Additional Research

Describing various options in different forms.

Desktop Apps

• PowerBI is the simplest and most powerful. The files can be shared with other users of PowerBI Desktop. The dashboards cannot easily be shared on web however.
• Excel can also load OData, but it's not as visually appealing or easy to use as PowerBI.
• Haven't tested Tableau, but it's meant to be good (less powerful that PowerBI).

Hosted Solutions

Note with these options we need to pay to host them...

• ~Redash, acquired by databricks… (limping on as OSS currently, so let's see where it goes)~
• ~Apache Superset & Metabase don’t support OData~

• Grafana has an OData plugin and is a fantastic CNCF project.

  • Probably our best choice until we discover something else.
  • A bit of a pain to configure the data provider / dashboard, but works.
  • https://grafana.com/grafana/plugins/dvelop-odata-datasource/
  • Docker compose config to test it:

    
    services:
    grafana:
    image: grafana/grafana-oss:11.2.0
    container_name: grafana
    restart: unless-stopped
    ports:
     - '3022:3000'
    environment:
      GF_INSTALL_PLUGINS: dvelop-odata-datasource
    volumes:
      - grafana-storage:/var/lib/grafana

  volumes: grafana-storage:

Web

• We could possibly use the XOData chrome extension, but it looks a bit old. Not tested.
• Final option is to make our own simple dashboard using a Graph library (charts.js or Apache ECharts) and HTML tables.
  • The creds for OData endpoints would need either be:
  • Embedded in the web app - not an option 😅
  • Configured by the user - not that user friendly.

Summary

In summary this is a tough decision!

Recommendation:

• Have simple visualisations for most common requirements built into FMTM (as we do currently, but improved upon).
• For more complex / bespoke needs, just ask the user to install PowerBI and make a dashboard (this could be done for them by sharing a pre-configured .pbix file dashboard).