PowerBI Integration

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

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
https://confluence.dimagi.com/display/commcarepublic/CommCare+Data+Export+Tool
to
sync commcare data to a local database, and then connect PowerBI directly
to that database.

Cory

··· On Fri, Sep 25, 2015 at 1:49 PM, wrote:

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


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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

··· On Friday, September 25, 2015 at 5:49:30 PM UTC, rmy...@sl.goal.ie wrote: > > 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 >

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

··· On Friday, September 25, 2015 at 10:49:30 PM UTC+5, Ralph Myers wrote: > > 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 >

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

··· On Friday, September 25, 2015 at 7:49:30 PM UTC+2, Ralph Myers wrote: > > 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 >

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: > > 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 >

Hi Cory,

So on PowerBI, web imports seem to work much like in excel:

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

··· On Monday, September 28, 2015 at 12:39:05 AM UTC, Cory Zue wrote: > > 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 > > On Fri, Sep 25, 2015 at 1:49 PM, <rmy...@sl.goal.ie > wrote: > >> 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 >> >> -- >> 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. >> > >

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

··· >>

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("")))
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

··· On Thu, May 12, 2016 at 1:52 AM, Javariya Aamir wrote:

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

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

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


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-users+unsubscribe@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

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

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

On Friday, September 25, 2015 at 10:49:30 PM UTC+5, Ralph Myers wrote:
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

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-users+unsubscribe@googlegroups.commailto:commcare-users+unsubscribe@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©(3) not-for-profit organization and contributions are deductible to the fullest extent allowed by the law.

Hi Ralph,

What URL are you trying to load the data from?

thanks,
Cory

··· On Mon, Sep 28, 2015 at 10:02 AM, wrote:

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

On Monday, September 28, 2015 at 12:39:05 AM UTC, Cory Zue wrote:

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
https://confluence.dimagi.com/display/commcarepublic/CommCare+Data+Export+Tool to
sync commcare data to a local database, and then connect PowerBI directly
to that database.

Cory

On Fri, Sep 25, 2015 at 1:49 PM, rmy...@sl.goal.ie wrote:

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


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.


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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

··· On Tuesday, September 29, 2015 at 8:16:53 AM UTC, Cory Zue wrote: > > Hi Ralph, > > What URL are you trying to load the data from? > > thanks, > Cory > > On Mon, Sep 28, 2015 at 10:02 AM, <rmy...@sl.goal.ie > wrote: > >> 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 >> >> On Monday, September 28, 2015 at 12:39:05 AM UTC, Cory Zue wrote: >>> >>> 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 >>> >>> On Fri, Sep 25, 2015 at 1:49 PM, wrote: >>> >>>> 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 >>>> >>>> -- >>>> 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. >>>> >>> >>> -- >> 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. >> > >

Thanks.

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

··· On Tue, Sep 29, 2015 at 4:24 AM, wrote:

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

On Tuesday, September 29, 2015 at 8:16:53 AM UTC, Cory Zue wrote:

Hi Ralph,

What URL are you trying to load the data from?

thanks,
Cory

On Mon, Sep 28, 2015 at 10:02 AM, rmy...@sl.goal.ie wrote:

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

On Monday, September 28, 2015 at 12:39:05 AM UTC, Cory Zue wrote:

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
https://confluence.dimagi.com/display/commcarepublic/CommCare+Data+Export+Tool to
sync commcare data to a local database, and then connect PowerBI directly
to that database.

Cory

On Fri, Sep 25, 2015 at 1:49 PM, rmy...@sl.goal.ie wrote:

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


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.


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.


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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
There are alternative import methods to web though. I believe they are in
the link I sent previously.

Cheers!

Ralph

··· On Tuesday, September 29, 2015 at 8:53:17 AM UTC, Cory Zue wrote: > > Thanks. > > You should be using digest authentication. Does PowerBI support letting > you select that? If not, what options are available? > > On Tue, Sep 29, 2015 at 4:24 AM, <rmy...@sl.goal.ie > wrote: > >> 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 >> >> On Tuesday, September 29, 2015 at 8:16:53 AM UTC, Cory Zue wrote: >>> >>> Hi Ralph, >>> >>> What URL are you trying to load the data from? >>> >>> thanks, >>> Cory >>> >>> On Mon, Sep 28, 2015 at 10:02 AM, wrote: >>> >>>> 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 >>>> >>>> On Monday, September 28, 2015 at 12:39:05 AM UTC, Cory Zue wrote: >>>>> >>>>> 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 >>>>> >>>>> On Fri, Sep 25, 2015 at 1:49 PM, wrote: >>>>> >>>>>> 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 >>>>>> >>>>>> -- >>>>>> 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. >>>>>> >>>>> >>>>> -- >>>> 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. >>>> >>> >>> -- >> 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. >> > >

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, wrote:

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

https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
There are alternative import methods to web though. I believe they are in
the link I sent previously.

Cheers!

Ralph

On Tuesday, September 29, 2015 at 8:53:17 AM UTC, Cory Zue wrote:

Thanks.

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

On Tue, Sep 29, 2015 at 4:24 AM, rmy...@sl.goal.ie wrote:

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

On Tuesday, September 29, 2015 at 8:16:53 AM UTC, Cory Zue wrote:

Hi Ralph,

What URL are you trying to load the data from?

thanks,
Cory

On Mon, Sep 28, 2015 at 10:02 AM, rmy...@sl.goal.ie wrote:

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

On Monday, September 28, 2015 at 12:39:05 AM UTC, Cory Zue wrote:

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
https://confluence.dimagi.com/display/commcarepublic/CommCare+Data+Export+Tool to
sync commcare data to a local database, and then connect PowerBI directly
to that database.

Cory

On Fri, Sep 25, 2015 at 1:49 PM, rmy...@sl.goal.ie wrote:

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


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.


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.


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.


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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Ok will do!

··· From: commcare-users@googlegroups.com [mailto:commcare-users@googlegroups.com] On Behalf Of Cory Zue Sent: Tuesday, September 29, 2015 2:42 PM To: commcare-users@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, <rmyers@sl.goal.iemailto:rmyers@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:

https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
There are alternative import methods to web though. I believe they are in the link I sent previously.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Cheers!https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Ralph

On Tuesday, September 29, 2015 at 8:53:17 AM UTC, Cory Zue wrote:https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Thanks. https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
You should be using digest authentication. Does PowerBI support letting you select that? If not, what options are available?https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
On Tue, Sep 29, 2015 at 4:24 AM, rmy...@sl.goal.ie wrote:https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Hi Cory,https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
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=272aa9b8c6c9b13ee031208f42135fe3https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Ralph

On Tuesday, September 29, 2015 at 8:16:53 AM UTC, Cory Zue wrote:https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Hi Ralph,https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
What URL are you trying to load the data from?https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
thanks,https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Coryhttps://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
On Mon, Sep 28, 2015 at 10:02 AM, rmy...@sl.goal.ie wrote:https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Hi Cory,https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
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-webhttps://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
However, when it comes to authentication I get an error saying that I am an unauthorized user.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
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.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Ralph

On Monday, September 28, 2015 at 12:39:05 AM UTC, Cory Zue wrote:https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Hey Ralph,https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
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.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
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.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Coryhttps://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
On Fri, Sep 25, 2015 at 1:49 PM, rmy...@sl.goal.ie wrote:https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Hi all,https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
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.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
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.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Thanks!https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
Ralphhttps://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG

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.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG

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.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG

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.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG

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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG

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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.https://lh3.googleusercontent.com/-zg58sVyIkmI/VgpVWKATptI/AAAAAAAAAAM/kSrkdmyipsw/s1600/powerbi.JPG
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©(3) not-for-profit organization and contributions are deductible to the fullest extent allowed by the law.

Hi all,

I’m facing an issue with trying to integrate CommCare with PowerBI. I never recieve an “edit credentials” message when I enter the source address as follows:

Web.Page(Binary.Buffer(Web.Contents(“https://www.commcarehq.org/a/billy-excerpt/data/export/custom/dailysaved/download/c70eeabde28c1b1d9bab9b382bc95cfc/”)))

Does anyone know how I can fix this?

Thanks,
Soha

Do you get any type of error message? What do you see when you open up the query editor?

Hi @soha,

I am also using Power BI Integration with CommCare and here the steps I follow which would be helpful for you:

  1. Create a data feed or as it is called the Dashboard Feed via CommCare’s Excel Dashboard Integration. You can find this by clicking on the Data tab, then go to Export Data, then click on Excel Dashboard Integration
  2. Once the Dashboard Feed is created, click on the Copy Dashboard Feed Link button. Press CTRL+C to copy the link, paste the link into notepad!
  3. Open Power BI Descktop
  4. Click on Get Data and choose Blank Query and then click on Connect
  5. The Query Editor will open
  6. While in the Query Editor, click on Advanced Editor
  7. The Editor will open with this code snippet:
let
    Source = ""
in
    Source
  1. Edit this piece of code so it looks as below:

let
Source = Web.Page(Binary.Buffer(Web.Contents(“Url”)))
in
Source

  1. Copy the link you saved in step 2 and paste in place of Url in the code snippet in step 8. So this piece of code should look as below(depending on the URL for your dashboard feed):

let
Source = Web.Page(Binary.Buffer(Web.Contents(“https://www.commcarehq.org/a/billy-excerpt/data/export/custom/dailysaved/download/c70eeabde28c1b1d9bab9b382bc95cfc/”)))
in
Source

  1. Make sure there are no Syntax Erros at the bottom of the Query Editor Window!
  2. If the system asks you for a username and password, put the same username and password you login to CommCare’s Project Space containing your dashboard feed!
  3. After clicking on Done, you will see a table with a column named Data! That is your Data Feed!:blush:
  4. Click on the arrow next to the Data column which looks like this <-> then choose the columns that you need to bring into Power BI!
  5. Click OK
  6. After step 14, you should be in the Query Editor and ready to transform your data however you wish!
  7. After all your transforms, click on Close&Apply and your data should be well placed into Power BI for visualization/reporting.
  8. To get fresh set of data from CommCare, refresh CommCare Dashboard Feed and then refresh your data in Power BI.
  9. Note that CommCare auto-refreshes its Dashboard Feed once in a day and so you would need to manually refresh the feed if you do not have to wait for about 8 hrs scheduled Auto-Refresh!
  10. I hope you will follow all these steps, including step 19 :blush:

Best wishes,
Chaiwa.

Hi Chaiwa,

Thank you so much for the detailed response. This is super helpful. I’ll try to run the steps suggested and let you know if I can stuck anywhere.

Thanks,
Soha

1 Like