View images in Power BI

[fmi]

Hi Kobo Community!

I have managed to connect my kobo datasets to Power BI. I would like if possible to view the images which I can see in kobo gallery in my Power BI dashboards - is this technically possible? When I view my image data on power bi currently I just see e.g. xyz.jpeg.

Many thanks for your advice!

[stephanealoo]

Hi @fmi
When you pull data from Power BI you only get the data and not the charts that appear on the report. You need to see how to create charts in PowerBi to make a full utility of Power Bi. Unfortunately, this is beyond the scope of support we can provide but some private reading such as this one could help.

Regards,
Stephane

[fmi]

Thanks for your quick response @stephanealoo! I didn’t mean the charts from the reports but the actual images that we upload during the data collection. Is this also not possible?

[stephanealoo]

Hi @fmi
In that case, then you must be able to do tweaks on the PowerBi side such as what is explained here. You will then need to use the approach of directing URLs to the images within KoBoServers as explained in the article here

Stephane

[fmi]

This is exactly what I needed thank you!

[ivanradisson]

Hey @fmi ,

Can you please explain how you solved this?

I’ve followed the instructions presented at KoboSupport (UNHCR) and successfully connected PowerBI to Kobo’s server. All the data is coming through except for full URL’s of the images.

In PowerBI, I can only see the filename (eg. 1639402857588.jpg ) and that is obviously not enough.

In KoboToolbox, in the Data view, i can see the photos and if i Right-click > Open image in new tab, I can get their URL. And because the form is publicly accessible, the URL always work (even if i’m not signed in to KoboToolbox).

When I look at the json file (https://kobo.humanitarianresponse.info/assets/xxxxxxxx/submissions/?format=json ), i can see that the full URL of all photos are actually in there. But for some reason PowerBI is not importing that information! Why would it do that? And is there a workaround that you know of ?

[Josh]

Hi @ivanradisson, this will fortunately be much easier soon once this code is in production — should be in the coming weeks.

[ivanradisson]

Thanks @Josh
Is there a workaround in the meantime?

PowerBI does get the other columns, including [#“formhub/uuid”] and [_uuid] .

So I’ve tried creating a custom column and build the URL based on that.
Formula:
"https://kc.humanitarianresponse.info/media/original?media_file=MyKoboUsername/attachments/" & [#"formhub/uuid"] & "/" & [_uuid] & "/" & [#"group_photo1/input_photo1"]

Details:

• “formhub/uuid” is always the same.
• “_uuid” changes per row (i mean it changes for each form submission).
• “group_photo1/input_photo1” is the name of the column that holds the filename (eg. 1638903629888.jpg ).

The custom column generates a perfectly formed URL. But it only works in a single instance belonging to a recently submitted form (using ODK Collect).
Previously submitted entries that were also submitted via ODK Collect for some reason don’t work and the browser loads a page that says “Attachment not found”.
I know that the culprit is the [_uuid]. Because it matches perfectly (i can see it in the json file) in the single case where it works and it doesn’t match in the other cases where it does not work. It’s like it’s been changed or something.

Now I’m suspecting this could be related to these entries having been updated recently via the web. I needed to change some of the details so I edited the forms in KoboToolbox web. I did not update the one that is currently working. Does my suspicion make any sense ?

[Josh]

Hi @ivanradisson, you are correct in that the _uuid value is the culprit. Each time a submission is edited, a new uuid (or instanceID) value is created and the old value becomes the deprecatedID. You can see this if you view the XML of your submissions.

[york_rff]

Hi Josh

What is the estimate on when this feature will be available?

Kind regards
York

[Josh]

Hi @york_rff, we are preparing the release to go out as soon as possible — hopefully in the next few days.

[Josh]

Hi all (@fmi, @york_rff, @ivanradisson), please note that this feature is now available and documented here:

Release Notes - version 2.022.08, 2.022.08a

Hello, everyone. We’ve just launched a new release (effective at 21:00 UTC on 17 March 2022) on both kf.kobotoolbox.org and kobo.humanitarianresponse.info. Components comprising this release: KPI version 2.022.08 KoBoCAT version 2.022.08 Enketo Express version 2.8.1 Pyxform version 1.7.0 Security Fix This release includes a fix for an issue discovered by a recent security audit. If the following conditions were all met: A project allowed anonymous submissions; A person knew of the existen…

[ivanradisson]

That’s great to hear!

I solved it before with custom columns in Power BI (i created a formula to build the correct url’s).
But getting the correct data directly from the source is much better…

I’m not working with PowerBI and Kobo right now because the project finished earlier this year. But I plan to go back to it soon, since we have additional forms we want to create on Kobo, and the corresponding data has to appear on PowerBI.

What exactly changed and how does affect us?
I had a look at the release notes but I don’t fully understand it. Is it this part (see below)?

Synchronous Exports (via API only)

Synchronous CSV and XLSX exports are now available at /api/v2/assets/{asset_uid}/export-settings/{settings_uid}/data.csv (and …/data.xlsx). If an export is requested multiple times for the same settings, it will be regenerated only once every 5 minutes. Synchronous exports may fail for large projects, as their processing must complete within the web application server’s 2-minute timeout (compared to 30 minutes for asynchronous export tasks, which are available at /api/v2/assets/{asset_uid}/exports/).

This was implemented in #3586 , #3700 , and #3716 . It was tracked by #3319.

[Josh]

Hi @ivanradisson, that’s great to hear! You are correct, that is the relevant change for connecting to your data using PowerBI, etc.

There is currently an issue with authenticating from PowerBI with the redirect method used, so currently one must share the project data publicly for it to work with Power Query. This change will be in the next release to fix that issue.

[siwei]

Dear @Josh,
the new Synchronous Export feature is fantastic and our team would like to implement it quickly in order to retrieve media urls trough api calls in Power Platform. Do you have any information about the release date of the next fix? And until then, is there any other way to access the media url trough api? We tried with Authorization token but didn’t work.

Thanks a lot.

[Josh]

Hi @siwei, I’m glad you’re finding it useful there is no fixed date for the next release but we are wanting to get it out as soon as possible. Before then, you can still make use of the feature in PowerBI (and include the media URLs) by making the data public (since the issue is with PowerBI’s process of authenticating requests).

[janna]

Hi There!
Thanks for this thread - I’m facing similar challenges/needs to display the images I’ve collected in Power BI. I cannot make the data public, as the data stored is personal (which I assume many humanitarian and development users of KoBoToolbox might also be facing).

When making the ‘_attachments.download_url’ column (accessed through the API into Power BI) into a ‘Web URL’ in Power BI - and clicking it - it’s taking me to a KoBoToolbox page that says 500 Error.

This is quite strange, as about half an hour ago, I was at least able to enter my KoBo Credentials, and it pulled in thumbnail images from KoBoToolbox.

So, as you mentioned previously - I’m assuming this has something to do with Power BI’s authentication requests? But not sure why it was working a few minutes ago, and now isn’t working?

However, my real question is: I’m just trying to have a good solution for accessing images and displaying in a dashboard.

One idea I had was to somehow duplicate the images over into a web server (e.g., AWS) that could then have images set to public (with periodic updates) (which doesn’t require also having the private data set to public) - and then linking from Power BI to the path where the duplicated images are stored.

Just seeing if anyone has worked through this previously and would be available to walking through a good work-flow with me?

Thanks!
Janna

[ivanradisson]

@janna

One idea I had was to somehow duplicate the images over into a web server (e.g., AWS) that could then have images set to public (with periodic updates) (which doesn’t require also having the private data set to public) - and then linking from Power BI to the path where the duplicated images are stored.

This is an important question, one that many users out there have to face sooner or later.

The project I’m working with deals with public data overall, and so I have the form set to public, and it hasn’t been an issue so far. But really the only reason to have it public is because of the photos. If there was a workaround, it would be great and I would set the forms to private.

One idea is if Kobo Toolbox had a setting that allowed us to just share the photos publicly, but not the forms and answers.

[ivanradisson]

When the team releases the new fix, will that also apply to media files ?

To be more clear about my question:
Is it going to be possible to show photos in PowerBI (using the Kobo API connection) from a Kobo form that does not have those two sharing options enabled ?

[ivanradisson]

Hi @ivanradisson, you are correct in that the _uuid value is the culprit. Each time a submission is edited, a new uuid (or instanceID ) value is created and the old value becomes the deprecatedID . You can see this if you view the XML of your submissions.

Btw, is this behavior ever going to change? I mean, are there any planned updates?

I’m asking because this creates some problems for people like me, using Power BI.

If someone edits a form submission, then it gets a different UUID, but the media is still accessible by a URL that is based on the original UUID. As such, because I’m dynamically forming an URL that is based on whatever the current UUID is, then the images no longer show up in Power BI as you can see in the screenshot below.
This is a table visual and I show some data as well as photos on each column:

The arrow points towards the images that are not loading correctly (because that specific form submission has been edited).
The other parts I had to censor, but I hope it’s still clear.

Edit 1:
I guess my real question is:
With the update that was released in March 17th, is there anything I can use to overcome situations like the one I mentioned above? I connected to a new Kobo form just a couple of days ago and I still don’t see the full URL to the photos, anywhere in the data.
So as I said, my solution is to form the URL in a new column, in PowerBI.

This is how i generate the original image URL
https://kc.humanitarianresponse.info/media/original?media_file=pdul_admin/attachments/" & [#"formhub/uuid"] & "/" & [_uuid] & "/" & [#"group_photo1/input_photo1"]

This is how i generate the thumbnail URL
https://kc.humanitarianresponse.info/media/small?media_file=pdul_admin/attachments/" & [#"formhub/uuid"] & "/" & [_uuid] & "/" & [#"group_photo1/input_photo1"]

Note: group_photo1/input_photo1 is the name of the image field in Kobo.
With the exception of edited submissions, these URL work fine!

On those edited submissions, I have to manually replace values in the _UUID column. I replace the new ID by the old ID. And then my generated URLs work again. But obviously, this is not a good solution, only a temporary one.

Edit 2:
Just noticed this column called “Attachments” and it seems it has a list of all the URL in there (original, small, medium sizes).

Now to expand on this…