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:
- ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (Error message in Power BI web service)
- _index column contains blank values (Error message when refreshing the data in Power BI desktop)
- 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.
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.|
_index column contains blank values
500: Internal Server error