CommCare/Power BI Integration Tutorial
PCI Global, one of our dear customers, created this wonderful tutorial on CommCare/PowerBi Integration.
Excel dashboard feeds from Step 2 in the Excel Dashboard Tutorial can also be used to import data into PowerBI. PowerBI is an interactive data visualization tool, allowing for deeper insight into your organization's data.
Please Note that updates to PowerBI made by Microsoft may break the workflow below.
Due to issues with PowerBI queries unable to properly handle basic authentication, the following is a workaround which allows for a connection with PowerBI. In order to connect to the HTML tables created in Step 2, perform the following steps:
- Open Power BI desktop (download here) and from ‘Get Data’ select ‘Blank Query’.
- Go to ‘View’ and select ‘Advanced Viewer’.
- Delete the default code and copy + paste the below code:
Source = Web.Page(Binary.Buffer(Web.Contents("URL HERE")))
- In HQ locate your form or case export that you created under the ‘Excel Dashboard Integration’ link. Click the blue ‘Copy Dashboard Feed Link’ button and copy the URL. Paste the URL over the bold text in the above code.
Either a screen will pop up asking for your credentials, or you need to click on "Edit Credentials".
Select ‘Basic’ and enter your CommCare username and password. The result should look like the below image:
- Currently the captions for HTML tables are all set to null, but the first one will contain your forms and the subsequent rows are your repeat tables. There will be a separate row for each repeat which will all need their separate query (which can be done by duplicating the query at this stage). The document row contains metadata about the html page which you won’t need to use. In order to get the data click on the highlighted ‘Table’ text in the ‘Data’ column as shown below:
- Wait until your data downloads, and proceed with visualizing your data!
NOTE: The data needs to be pulled/downloaded every 35 days (see warning label below) to maintain an active data connection/link. This can be achieved by refreshing the data connection within the allocated expiration period of 35 day in Power BI. Otherwise, the data connection will expire and Step 4 needs to be repeated to re-establish a data connection.