PowerBI Integration

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鈥檛 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鈥檓 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鈥檚 Power Query doesn鈥檛 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鈥檓 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 鈥渃ommcare-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鈥檙e 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鈥檛 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鈥檓 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 鈥渃ommcare-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鈥檓 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鈥檓 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鈥檙e 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鈥檛 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鈥檓 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 鈥渃ommcare-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 鈥渃ommcare-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鈥檛 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鈥檓 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鈥檙e 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鈥檛 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鈥檓 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 鈥渃ommcare-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 鈥渃ommcare-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 鈥渃ommcare-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鈥檛 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鈥檓 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鈥檙e 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鈥檛 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鈥檓 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 鈥渃ommcare-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 鈥渃ommcare-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 鈥渃ommcare-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 鈥渃ommcare-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 鈥渃ommcare-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鈥檓 facing an issue with trying to integrate CommCare with PowerBI. I never recieve an 鈥渆dit 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鈥檚 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(鈥淯rl鈥)))
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鈥檚 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鈥檒l try to run the steps suggested and let you know if I can stuck anywhere.

Thanks,
Soha

1 Like

Hello everyone I have a little piece of knowledge regarding 鈥淗ow to import Rest API in Power BI鈥 and here is the link:

https://zappysys.com/blog/howto-import-json-rest-api-power-bi/

Just wanted to revive this thread with some resources that we鈥檝e put together on how to integrate Power BI and CommCare and thought I鈥檇 share. For a step by step guide with images of what @Chaiwa_Berian mentioned above you can go straight to 鈥淨uerying CommCare Data from Power BI.鈥

We鈥檝e started building quite a few dashboards now to manage the implementation of data collection and know that we鈥檝e just scratched the surface on the power (pun intended) of BI and that鈥檚 just using mostly meta data to track the implementation. Would love to start a community of practice of sorts to expand, share, and create especially for us that are not coders but clickers鈥 - so if anyone is interested and wanted to share I鈥檇 be happy to connect on this.

@ttseng Thank you for documenting these steps. I followed the steps and successfully setup the Power BI Gateway for the scheduled data refresh! Everything looks and works just fine, except CommCare still only updates the data feed once in 24 hours regardless of how often the Gateway is scheduled to pull data. The Gateway is scheduled to pull data every after one hour during the day but it will always get the same data CommCare pushed to the data feed at midnight, unless you manually update data on the data feed in CommCareHQ!

Do you have any ideas?

Thanks,
Chaiwa.

@Chaiwa_Berian Glad you found it useful.

My understanding is that you cannot schedule a data refresh in CommCareHQ like you can in Power BI and that you are correct that you鈥檒l have to wait once every 24 hours or manually update it. I鈥檓 assuming you鈥檙e wanting to see the data more than once every 24 hours? If so, I鈥檓 not sure what can be done.

If your forms aren鈥檛 too complex and don鈥檛 change too often you could consider integrating with Zapier to a MySQL database. This way you can even do a Direct Query in Power BI and have your data displayed in real time! https://zapier.com/apps/commcare/integrations

Alternatively you could consider using CommCare鈥檚 Data Export Tool, although you will probably have to combine it with SQL as well: https://confluence.dimagi.com/display/commcarepublic/CommCare+Data+Export+Tool

@ttseng TONY!!! You sir are a star!

1 Like

Hi All,

You may have already received an email from us about this, but wanted to update here for good measure.

Last week we launched our most direct integration with PowerBi and Tableau ever! :point_down:t5:

All CommCare users on Advanced and Enterprise subscription plans now have access to this new integration. Pro plan users can also add these integrations to their subscription for a small fee.

Want to upgrade your Community, Standard or Pro subscription? Please reach out to me at dmore@dimagi.com to discuss!

Thanks,
Dev