CommCare Sync

 

New community supported open source tool

These features are supported by the CommCare Community of Practice. While Support for these features is not included as part of your CommCare subscription, please feel free to refer any questions or issues about the content of these pages to the CommCare Forum.

CommCare Sync is a self-hosted, standalone, web-based app that can be used to manage a CommCare “data warehouse.” It’s a suite of configurations, with an improved UX from the command-line Data Export Tool. This turn-key tool allows you to export your data from CommCare and save it to a local or cloud-based database (MySQL, PostgresSQL, Amazon RDS, GCP Cloud SQL, Azure SQL Database).

https://www.youtube.com/watch?v=73QxEK1xgGY

Table of Contents

Installation

If you wish to try out, or self-host CommCare Sync, you can do so by setting it up from the source code which is available in the commcare-sync Github repository.

For help installing and managed production environments you can follow the documentation of the commcare-sync ansible repository.

Note that Dimagi does not provide support for self-hosted environments. For any issues or questions on hosting, please post to the open source developer forums. If you want Dimagi help hosting CommCare Sync, you can contact our delivery team.

Functionality

This set of tools provides a user-friendly interface for administrators to set up their CommCare data pipeline. The functionality includes the ability to:

  • Automatically generate a Data Export Tool config file from CommCare.

  • Create project space(s) in CommCare Sync, connected to your CommCare project space(s) and your database(s)

  • Upload a DET config file that will automatically pull data from your CommCare project space to your database on a set schedule

  • Monitor your data syncs via CommCare Sync's log feature

Configuration Steps

Steps to configure CommCare Sync, as represented in the demo video:

  1. In CommCare HQ Data page, create a form or case export 

  2. Download the DET config file

  3. Open your Excel DET config file to see the fields from your export with the option of mapping specific data types. If you aren't transforming your data, there's no step needed here.

  4. Open CommCare Sync, create a new account (instructions below)

  5. Add a project by pasting your CommCare project space name

  6. Add your database via the Admin Site (can be any available database)

  7. Add an export from your new project, and add your database and your config file you downloaded in step 2

  8. Run export. This applies the configuration file to do an initial sync of all the data from your CommCare project space. 

  9. View the log to see more info - like to confirm how much data was pulled in

  10. Connect your BI tool of choice, and start exploring the data

Note for projects syncing data from multiple CommCare project spaces:

The process described above is for connecting a single CommCare project space. If you are connecting data from multiple CommCare project spaces, you need to add each project space as a Project in CommCare Sync (step 5), and repeat the process of downloading each DET config file per project space (step 2), to then each be uploaded to CommCare Sync (steps 7 & 8).  (IMPORTANT:  there is a new feature release that will allow applying the same DET config file to multiple project spaces in the CommCare sync tool).

Download your DET config file from CommCare

Create a CommCare Sync account

Ask a site admin to create an account for you and share credentials, then change your password.

Sync your data

To sync data, follow the following steps:

  1. If you haven’t already, add the CommCare project space in the “CommCare Setup” tab.

  2. If you haven’t already, add a CommCare account that has access to the project space.

  3. Important note to Dimagi users: Do NOT use your superuser @dimagi.com account, follow the steps below to create a user below. Create a web user for a specific project, and set up API key for that user (under Account Settings). You can use a "+" in your email address that tells you what your web user is for. e.g. "firstnamelastname+demo-cc-sync@dimagi.com"

  4. Add the export from the “Exports” tab.

  5. On the export details page, click “run”.

  6. When the run completes, view the logs to confirm it ran successfully.

Data will be updated for all exports on a schedule (currently every 12 hours, managed by a system admin).

Data Export Tool Best Practices

Some recommendations for modifying the DET config files downloaded from HQ:

  1. Double check the name of the sheet (tab) in your DET config workbook to be something specific to your project / case type. The tab's name, not the .xlsx file name, will be used as the table name in SQL. The default of  “Cases” or “Forms” should not be used, but instead changed to e.g. “covid_19_index_cases”

  2. Add a “str2date” mapping to any date properties and fields. This will make it easier to use them in various BI tools.

  3. There is a subtle difference between the 'Extra Arguments' field in a CommCare Sync Export and a CommCare Data Export Tool parameter. If an argument in CommCare Sync takes multiple parameters ('since' and 'until,' for example) it must be formatted like: --until=2020-09-30

Adding Databases

Databases can be added by site admins by using the "databases" link in CommCare Sync sidebar navigation. The database may need to also be separately created by a system admin on the server.

Administration

System administration is documented in our production environment documentation.

CommCare Sync Data

View Exact Data Sent to the Device

You can use the same API URL which is used by the mobile devices to see what data will be sent to the phone or web application during a sync.

View Restore Data for Any Mobile User in HQ

View Restore Data for Any Mobile User in HQ

View All Data Associated with a User:

http://www.commcarehq.org/a/{my-project-space}/phone/restore/?as={user}@{my-project-space}.commcarehq.org&version=2.0

App Specific:

http://www.commcarehq.org/a/{my-project-space}/phone/restore/{app_id}/?as={user}@{my-project-space}.commcarehq.org&version=2.0 (assumes current state of the app, not particular build)

Build Specific:

http://www.commcarehq.org/a/{my-project-space}/phone/restore/{build_id}/?as={user}@{my-project-space}.commcarehq.org&version=2.0

Additional Parameters

since={previous restore id}

Incremental response since the previous sync.

items=true

Include the item number in the response.

overwrite_cache=true

Don't return a cached value but rather recompute the response. Deletes the cache, so the next time you sync on the phone, it will use the newly computed result.

raw=true

Raw restore response without the UI.

device_id={device_id}

Fake a restore from a particular device id. WebApps uses a device ID of the form "WebAppsLogin".

app_id={app_id}

Restore using app aware sync. This will only sync down the reports for that particular application.

case_sync={clean_owners | livequery}

Select which case sync algorithm to use with this restore

hide_xml=true

Shows only aggregate case counts, without loading associated XML data

fail_hard=true

Useful to debug issues where restore has missing or unexpected data. Instead of swallowing up UCR fixture errors during a restore, this forces a 500 error which a dev can investigate to find the root cause.