We were recently asked about using d-portal data as an external database in Power Bi.
i've not come across Power Bi so had to do some amount of researching. There is a web app you could download on your browser which is great as i'm on a Linux machine so i began installing it on Firefox. Almost immediately, it became apparent that it was not going to be a seamless experience - you needed a company email to register so a gmail account will not suffice and you needed to pay for a Pro License to be able to use the app in any useful manner. Thankfully there was a free trial for 60 days and since i just needed to see if the d-portal link would work, it seemed an ok compromise to go with.
The user interface is vast - it looks like you could do many things on the Power Bi app - you just needed to not be overwhelmed by the options.
After a few frustrating tries, it became obvious that i was not able to use a non-Microsoft external database on the app but i could do this on the Desktop version instead. i booted up my non-work Windows machine and began the installation.
Power Bi Desktop is free and its user interface feels a lot more intuitive than the web app. Having read through some forums of the right way to use an external link to a database, i was able to get live d-portal data to show up as tables on Power Bi desktop.
- Go to File > Get data > Web
- Paste the d-portal link and click OK
- Click Load
The application chugs away for a while and before you know it, it loads up as a clickable database on the page. You'd then have to pick a visualisation (pie chart, stacked column, scatter plots; etc) and then choose the data fields you want to populate the graph. In less than 5 minutes, i had a nice, interactive graph showing IATI COVID-19 data in Afghanistan using a d-portal csv query.
The next bit was to be able to share this somehow - and this is when it gets a little bit complicated.
First, you had to publish it to a workspace on he Power Bi web app. Then you had to have the right administrative rights to be able to publish that report to the web, publicly. Otherwise, you would have to log in as a Power Bi user to be able to view it!
Next, i had to add a TXT record to my dns as proof that i owned the domain and email i signed up to Power Bi with and then i had to allow 'publish to web' in the admin portal of Power Bi via a series of portals and a series of other portals, all Microsoft-alike.
And this is what you see at the bottom of this post - a live d-portal csv query for IATI Covid-19 Afghanistan data, displayed as an embedded Power BI report.
i don't know what will happen after the 60 days trial. This report could disappear and all access to getting anything online could be taken away. We will have to wait and see.
Update: Looks like the embeds have been disabled unless I upgrade to Power Bi Pro so I have downloaded it as a powerpoint slide, uploaded it to Google Slide and publish it to web as an embed below instead.
Source, 23 September 2020.