Versions Compared

Old Version 62

changes.mady.by.user Gillian Javetski

Saved on

compared with

New Version Current

changes.mady.by.user Gillian Javetski

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Excerpt

Supported by CommCare OData Feeds functionality, many CommCare users integrate with Microsoft Power BI to meet their data visualization needs. This page details everything that you need to know about using CommCare OData Feeds with Power BI.

...

Table of Contents
stylenone

Multiexcerpt include macro
macro_uuid9078ada9-7c16-4fe4-af1c-4ddd91c1ebe9
nameSoftware Plan - Advanced
templateDataeJyLjgUAARUAuQ==
page[Internal] CommCare Public ManagementCommCare Help Site Design Guidance
addpanelfalse

...

Integrating your CommCare data with Power BI or Tableau has never been this easy!

  • Step 1: Configure your OData feed (just like you configure a Form or Case Export)

  • Step 2: Copy the OData feed/s 

  • Step 3: On Power BI / Tableau Desktop, select OData Feed/OData as your connection type. 

  • Step 4: Enter your OData feed/s, and login using your CommCare credentials (Note: If you are using 2FA or SSO, specify your API key as the password. Your API Key can be found under your My Account Settings.) 

    • Power BI Desktop: Use Basic Authentication

    • Tableau Desktop: Use authentication type 'Username and Password'

You can also follow along in the video below to see how the integration is set up with Tableau:

...

Useful docs from Microsoft and Tableau:

Panel
panelIconIdatlassian-info
panelIcon:info:
bgColor#EAE6FF

Important:

  • If you are using 2FA or SSO, please enter your email address and your API Key (as password) when Power BI/Tableau Desktop asks for credentials. 

  • OData feeds are not recommended to be used with MS Excel, and support will not be provided for workflows that utilize the method.

  • The "number" column in the OData feeds will export the case_id or form_id of the case instead of a serial number. This is because “numbers” usually shift around with pagination and when data being continuously updated. Case_ids or form_ids are the only ID that remain constant, and hence we’re using the case or form ids in the number column, so that when a refresh is done, the export is able to identify case or form ids that were previously exported, which then prevents duplicates.

(info) Performance Considerations: 

  • OData feeds is our entry-level solution for integration with PowerBi and Tableau. It has been optimized for ease of use, so that non technical users can build quick integrations. 

  • However, OData feeds do not support incremental refresh, i.e. each time you refresh, the entire dataset is refreshed. You can mitigate performance issues by publishing the data viz/report from Tableau/PowerBi Desktop to Tableau Online or PowerBI Service, where you can configure the data viz/report to refresh automatically on a schedule defined by you. If you are still unhappy with the performance, please consider using our Data Export Tool to build a CommCare → SQL DB → PowerBi/Tableau data pipeline. Our Data Export tool supports incremental refresh and is designed for enabling performant data pipelines. Performance can vary depending on the data model, but generally speaking we have started to see performance issues start around 500,000 rows per case or form export configured via the OData feed.

Panel
panelIconIdatlassian-check_mark
panelIcon:check_mark:
bgColor#EAE6FF

Useful Tips

Set-up a web user read-only role limited to read OData feeds:

  • Navigate to Users / Roles & Permissions

  • Click +Add Role at the bottom of the page

  • Provide a name to this role: e.g. Read-Only OData

  • Unselect all permissions except the ones below:

    • Export Form Data (if necessary)

    • Export Case Data (if necessary)

    • PowerBI/Tableau Integration

    • Allow role to access data from all locations

  • Save and assign this role to a user

...

The bulk delete Power BI/Tableau integration feed gives you the option to bulk delete multiple exports at once. 

These are the steps to bulk delete form export

  • In the Export Dashboard you will find a check box to select all or specific exports

  • On checking the box, you will notice a "Delete Selected Export" button becomes available on the left side of the page

  • Click on the "Delete Selected Export" button and you will be promoted to confirm the bulk delete action

  • Once Delete Export is clicked it will bulk delete the exports selected at once

...

Setting Up & Understanding OData Feeds

If you haven’t yet, please check out the OData Feeds page for how to set up your OData feeds with PowerBI, as well as best practices.

Other Sub-Pages

Child pages (Children Display)
depth1
allChildrentrue
style
sortAndReverse
first0

Troubleshooting PowerBI Errors

Below is a list of potential errors that you may receive when connecting CommCare to PowerBi

Unable to connect

...

Plausible root cause: A checkbox question could be causing an issue in your feed. You may need to either de-select the checkbox questions option, or add a delimiter option for the erroring property.

Solution 1

  1. Navigate to the feed that is causing the error on CommCare HQ

  2. Click on Copy & Edit Feed button and uncheck the Expanded Checkbox Questions checkbox

  3. Then use the edited feed in the integration with Power BI

...

Solution 2

  1. Navigate to the OData feed that is causing an error on CommCare HQ

  2. Click on Copy & Edit Feed button

  3. Then change the display field of the question causing the error to contain the word {option} as a delimiter

  4. Then use the edited feed in the integration with Power BI

...

There was an error processing your data set

...

Plausible Root cause: If you have a large number of feeds or data, the request may be timing out. You may need to increase the timeout period for the query.

Solution

Source: https://community.powerbi.com/t5/Desktop/How-to-set-command-timeout-option/m-p/920420

  1. Open the Advanced Editor for your query in the Desktop App

...

  1. Then change the settings as follows in the Implementation box for each feed included in your query:
    let
        Source = OData.Feed("https://www.commcarehq.org/a/[domain]/api/v0.5/odata/cases/[uuid]/feed"", null, [Implementation="2.0", Timeout=#duration(0, 0, 90, 0) ])in
        Source

  2. Then click on Done once finished with making the changes.

Best Practices from Partners

Below are some resources from other partners who are using PowerBI with CommCare!