Issues connecting Power BI to Kobo (via synchronous export link)

[niels396]

Dear community,

Recently we’ve been facing issues when refreshing our Power BI dashboards. We have well over 12.260 responses and via a repeat we have another 48.000 records. So far, we have been using the https://kc.kobotoolbox.org/api/v1/data/XXXXXX.xls URL to connect.

I ran into three different errors, these errors appeared without us making any (conscious) changes to the dashboard or queries:

1. ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (Error message in Power BI web service)
2. _index column contains blank values (Error message when refreshing the data in Power BI desktop)
3. 500: Internal Server error

When I started digging into these issues I quickly came upon this article that has recently been updated. Connecting KoboToolbox to Power BI — KoboToolbox documentation I noticed that this article made use of the v2 API whilst we were still working with the v1. In several other. I also came across another post on this forum where advise was given to use the v2 API instead of V1.

Long story short: I didn’t dig any further into the 3 issues that I faced with the v1 API but tried to connect Power BI to Kobo via the steps described here Connecting KoboToolbox to Power BI — KoboToolbox documentation and here Connecting to your data using synchronous exports — KoboToolbox documentation

Unfortunately I’m not able to retrieve any data, it seems to authenticate but the message below just keeps spinning for a long time, after a while I get a time out error message.

So far I’ve tried

• Using Excel instead of Power BI desktop to connect → Same issue, loading takes very long and times out

• Using a remote desktop instead of my local machine to ensure it’s not my connection → Same issue

This situations raises the following questions

• Is it likely that the error messages I received (copied them below in case anyone is interested) are related to API v1 limitations (and our project size)?
• Is the API v2 recommended for every scenario, or are there situations where V1 is advisable?
• Are there currently any issues with the servers that might cause slow data loading?
• Last but not least: Does anyone know how to properly connect Kobo and Power BI? Maybe I’m just making a grave mistake or missing something obvious.

As always any help is much appreciated.

Best,

Neil

API v1 Error messages below

‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

Processing error:	Excel Workbook: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. The 64-bit version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.
Cluster URI:	WABI-US-CENTRAL-A-PRIMARY-redirect.analysis.windows.net
Activity ID:	22e1b559-0049-45e5-bd0d-17e4a98dde82
Request ID:	941de5a4-c281-44cd-bc98-3fe2f4da1d7f
Time:	2022-09-26 21:12:59Z

_index column contains blank values

500: Internal Server error

[peshal]

Hi Niels,
You may take JSON of kobo for Powerbi
Process
go to Web
select advance

1. Put the URL
   https://kobo.humanitarianresponse.info/assets/*******/submissions/?format=json
   (keep form ID in the ***** area)

2. Types
   Authorization
   (Just below the HTTP at enter or select a value place

3. Put
   Token *******
   at the rightside of Authorization at blank place
   After that you may upload your kobo files into the Powerbi
   

   

[niels396]

Hi @peshal,

Thank you for your quick response. I’ve tried your method and it works indeed.

I’m just wondering what the best way forward is. There seem to be different methods API v1, your method and the method in this post: Connecting KoboToolbox to Power BI — KoboToolbox documentation

I’m not sure what the best way forward is because I don’t understand the differences well enough. Before I go and rebuilt our report on the new data structure I would like to be sure that we’re going about it the right way.

I tend to go with the instructions as given by the Kobo team since they were updated recently: Recently Updated Support Article: Connecting KoboToolbox to Power BI But then this method does need to work of course.

Maybe @Kal_Lam is able to help out here?

Again, thanks for you help.

Neil

[Kal_Lam]

@niels396, you could try using the approach outlined by @peshal. Feel free to reach back to the community if you should have any issues then.

[niels396]

Thank you @Kal_Lam for reaching out so quickly. I’m more than happy to use the approach outlined by @peshal.

Just for my own understanding of the situation, could you give me an insight in the reason why you’re suggesting to deviate from the approach outlined in this article? Connecting KoboToolbox to Power BI — KoboToolbox documentation

Are there any known issues with the approach on the Kobo website? Or is there likely to be an issue on our end and is @peshal approach just the quickest way to get us going again?

Thanks again.

Best,

Neil

[randriar]

Hi @niels396!

The synchronous export link returns a file with an .xlsx file, which means there is an extra step in the background that converts the data. This extra step gives you the ‘(504): Gateway Time-out’ error because of the size of your data.
The other method suggested by @peshal returns the data from the server in a JSON format, therefore faster and recommended in your case.

Best!

[Kal_Lam]

@randriar,