PowerBI Integration

[rmy...@sl.goal.ie]

Hi all,

I am currently using Microsoft PowerBI for a lot of our visualizations and reporting. We are looking to use CommCare for the majority of our programmes, however whenever I have tried to import data, it says my account is not authorized. I have no problem doing the same following the instructions for creating Excel dashboards. PowerBI allows for a number of different authentication methods including Basic and using an API Key, neither of which work. I'm not sure if this is a CommCare or PowerBI issue, but I thought I would try CommCare first.

It would be great to be able to integrate PowerBI with CommCare as they are working hard to compete with solutions such as Tableau (PowerBI is waaaay cheaper) and really see this as the way forward to replacing Excel Dashboards.

Thanks!

Ralph

[Cory Zue]

Hey Ralph,

Can you explain a bit more about how you're trying to connect PowerBI to CommCare? Not sure how many people on this list are familiar with it, so understanding that might shed some insight into why it isn't working.

My gut is that the best workflow would be for you to use the commcare data export tool to sync commcare data to a local database, and then connect PowerBI directly to that database.

Cory

--
You received this message because you are subscribed to the Google Groups "commcare-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to commcare-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[rmy...@sl.goal.ie]

Hi Cory,

So on PowerBI, web imports seem to work much like in excel: https://support.powerbi.com/knowledgebase/articles/461315-tutorial-importing-and-analyzing-data-from-a-web

However, when it comes to authentication I get an error saying that I am an unauthorized user.

I am hesitant to start using the export tool as it means adding a number of additional steps to our information flow as well as creating a dependency on the IT department. As it is a Microsoft tool it seems to me that PowerBI should be able to have the same access to html tables as Excel.

Ralph

[Cory Zue]

Hi Ralph,

What URL are you trying to load the data from?

thanks,

Cory

[rmy...@sl.goal.ie]

Hi Cory,

I'm using a URL for daily web updates. This seems to work fine when importing off the web using Excel. e.g. https://www.commcarehq.org/a/goal-sierra-leone/reports/export/saved/download/c9f5495ef9f791ed5d0faa72217af07e/?group_export_id=272aa9b8c6c9b13ee031208f42135fe3

Ralph

[Cory Zue]

Thanks. 

You should be using digest authentication. Does PowerBI support letting you select that? If not, what options are available?

[rmy...@sl.goal.ie]

As far as I can see what comes closest is Basic Authentication. Here is an overview of the types of authentication:

There are alternative import methods to web though. I believe they are in the link I sent previously.

Cheers!

Ralph

[Cory Zue]

Hey Ralph,

Currently that URL doesn't support basic authentication. Could you report an issue and we can see if our team can add it?

thanks,

Cory

[Ralph Myers]

Ok will do!

 

From: commcar...@googlegroups.com [mailto:commcar...@googlegroups.com] On Behalf Of Cory Zue
Sent: Tuesday, September 29, 2015 2:42 PM
To: commcar...@googlegroups.com
Subject: Re: PowerBI Integration

 

Hey Ralph,

 

Currently that URL doesn't support basic authentication. Could you report an issue and we can see if our team can add it?

 

thanks,

Cory

On Tue, Sep 29, 2015 at 6:07 AM, <rmy...@sl.goal.ie> wrote:

As far as I can see what comes closest is Basic Authentication. Here is an overview of the types of authentication:

There are alternative import methods to web though. I believe they are in the link I sent previously.

 

--
You received this message because you are subscribed to a topic in the Google Groups "commcare-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/commcare-users/iziM4ouhIgY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to commcare-user...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Disclaimer GOAL: This communication contains information which may be confidential, personal and/or privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, forwarding, copying or use of this communication or the information in it is strictly prohibited. Any personal views expressed in this e-mail are those of the individual sender and GOAL does not endorse or accept responsibility for them. Prior to taking any action based upon this e-mail message, you should seek appropriate confirmation of its authenticity. GOAL Ireland is a registered charity in Ireland: Charity Reg No: CHY 6271, GOAL UK in the United Kingdom: Charity Reg No: 1107403, GOAL USA in the US as a 501(c)(3) not-for-profit organization and contributions are deductible to the fullest extent allowed by the law.

[rmy...@sl.goal.ie]

Pleased to report that integration with Power BI is now possible!

[Rob]

Could you let me know the settings that you used in Power BI. I am also stuck at the Basic Authorization stage - Thanks Rob

[Javariya Aamir]

Hi all
I am having the same issue. As Ralph has reported that commcare integration is possible with power BI, can someone please update on how it is possible? Because I am getting the same authorization error on all options. much thanks

Javariya

[Sheel Shah]

Hi,

It looks like Power BI/ Excel Power Query doesn't properly handle basic authentication.  See this article:

https://social.technet.microsoft.com/Forums/en-US/3d81cbcf-2510-41bb-b325-693d986d6641/power-query-vs-basic-authentication-webserver-with-ssl?forum=powerquery

You can get around this by adding your own custom power query expression.  

- In Excel, go to New Query, but choose Other, Blank Query

- In the query editor, then click on View, Advanced Editor

- Then paste in the following:

let

    Source = Web.Page(Binary.Buffer(Web.Contents("<URL To Saved Export>")))

in

    Source

Note that the URL needs to end with a slash. Excel will prompt you to enter your username / password via Basic Auth then.  You can then use the query editor to choose the data you want.

Thanks,

Sheel

--

You received this message because you are subscribed to the Google Groups "commcare-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to commcare-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Sheel Shah

Project Manager | Dimagi

m: +1.781.428.5419 | skype: sheel_shah

[Ralph Myers]

From the CommCare team:

 

Hey Ralph,

It looks like Excel's Power Query doesn't properly handle basic authentication.  See this article:

https://social.technet.microsoft.com/Forums/en-US/3d81cbcf-2510-41bb-b325-693d986d6641/power-query-vs-basic-authentication-webserver-with-ssl?forum=powerquery

You can get around this by adding your own custom power query expression.
- In Excel, go to New Query, but choose Other, Blank Query
- In the query editor, then click on View, Advanced Editor
- Then paste in the following:

let

    Source = Web.Page(Binary.Buffer(Web.Contents("https://www.commcarehq.org/a/goal-sierra-leone/reports/export/saved/download/e54dcc5e1ec5d511ebbc1c09938460da/")))

in
    Source

Note that the URL needs to end with a slash. Excel will prompt you to enter your username / password via Basic Auth then.  You can then use the query editor to choose the data you want.

Thanks,
Sheel

 

From: commcar...@googlegroups.com [mailto:commcar...@googlegroups.com] On Behalf Of Javariya Aamir
Sent: Thursday, May 12, 2016 5:53 AM
To: commcare-users <commcar...@googlegroups.com>
Subject: Re: PowerBI Integration

 

Hi all

--
You received this message because you are subscribed to a topic in the Google Groups "commcare-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/commcare-users/iziM4ouhIgY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to commcare-user...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Disclaimer GOAL: This communication contains information which may be confidential, personal and/or privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, forwarding, copying or use of this communication or the information in it is strictly prohibited. Any personal views expressed in this e-mail are those of the individual sender and GOAL does not endorse or accept responsibility for them. Prior to taking any action based upon this e-mail message, you should seek appropriate confirmation of its authenticity. GOAL is a registered charity in Ireland: Charity Reg No: 20010980, CHY 6271, GOAL (International), trading as GOAL UK, in the United Kingdom: Charity Reg No: 1107403 and GOAL USA Fund in the US as a 501(c)(3) not-for-profit organization and contributions are deductible to the fullest extent allowed by the law.

[Ralph Myers]

Hi all,

the new URL structure CommCare introduced means that linking through Power Query/Power BI is no longer possible. When editing Basic Authentication credentials it returns the message that credentials are invalid.

This is a BIG setback for us. Your help on this issue is greatly appreciated.

Regards,

Ralph

[Ralph Myers]

Hi all,

solved it. The new format is http instead of https, so just adding an s solves the issue.

Thanks!

Ralph

On Friday, September 25, 2015 at 7:49:30 PM UTC+2, Ralph Myers wrote: