API - ability to get full extended submission schema - so PBI knows the structure to expect

[nat]

What is the general goal of the feature?
To simplify the process for people who integrate KoBo with Power BI/PowerQuery so that they can

1. Be sure that their transformations will be suitable for all submissions not just the first few
2. See the structure that the data will be returned in (even if no submissions yet)

The idea is, that it would be possible to call the API (either a flag on the existing submissions API, or somewhere else) such that it returns the full data structure/schema even if no submissions have yet populated all fields.

The JSON currently (and correctly) only returns fields that have been populated for the given submission, so unless users have programmed their transformations flexibly they will not automatically accept the new fields. This is point 2 here

Also, even if there’s a submission that has filled in all fields, PowerQuery/Power BI’s data modelling only takes into account the first X (1000) rows - which, depending on the transformation, could include a much smaller number of submissions. So if there’s a field that’s only present in a submission past this cutoff, PBI won’t identify it in the data model so it will get ignored.

It would be good to be able to access the full schema so that transformations can be set up to take account of any possible field combination in a submission.

Detailed design required as this suggestion is just a proposal rather than a full build spec - but hope the desired outcome is clear.

What are the most likely user stories for how and when this would be used by someone on your team?
Anytime we are building a dashboard or doing data analysis in PowerBI/Excel PowerQuery using KoBo API.

Can you sketch out graphically how you think this should look/work in practice?
Either a new endpoint or a toggle/flag on the existing endpoint - to return the full schema at start of response, so it can be used to set up the transformations.

How useful would this feature be to other users or organizations?
Very, based on the multiple support threads about missing data in PBI/PQ.

This ticket is spun off from @tom.joseph 's suggestion:

All columns do not appear when connecting to powerbi

@Kal_Lam To clarify, when querying Kobo from PowerBi, if there are optional parameters that could be passed to ensure all columns are generated within PowerBi. The challenge is that unless I send in several test submissions that complete all of the possible fields within kobo, less than 50% of the fields will generate within powerbi. As additional submissions are created, that would then require additional ongoing maintenance to update the powerquery so that it accounts for the new submissions…

What can you contribute to making this feature a reality?
Conversations, test support

Thanks for considering!

[Josh]

Hey @nat, thanks for the great suggestion and context. I’ll see if we can slot this in soon It seems like a new /data/schema (or whatever) endpoint could work nicely for this.