This feature is in development

This tool is in R&D and should not be considered ready for general use. It is not intended to be installed and made publicly available, but should be behind a firewall with only direct access to the source CommCare Instance and target SQL DB.

Table of Contents

Overview

CommCare Sync is a web-based application currently in development by Dimagi that can be used to manage a CommCare “data warehouse.” The CommCare Sync application is intended to act as a suite of Data Export Tool (DET) configurations, with an improved user experience from the current command-line Data Export Tool. This turn-key tool allows you to export your data from CommCare HQ in a simple, configurable, and reusable way and save it to a local or cloud-based database (like MySQL, PostgresSQL, Amazon RDS, GCP Cloud SQL, or Azure SQL Database).

Installation

The source code for the tool, as well as instructions for setting it up can be found in the commcare-sync Github repository. Note that it is still under heavy active development and we are working to improve these instructions.

Deployment scripts, and deployment documentation can be found in the commcare-sync-ansible Github repository.

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 DET config file from CommCare (This feature is also pre-release, contact Dimagi support to enable this feature if you are testing this tool)
  • 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

Demo

A demo server has been stood up here. The source is available on github.

This demo video walks the user through the configuration steps from start to finish:

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 (if you don't see the 'Download DET config' button, contact someone Dimagi support to enable it for you)
  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 using your API key from CommCare
  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

  • Download or create a Data Export Tool config file

    • The easiest way to create these is to start with a normal export configuration on HQ and use this feature flag to download a config file for that export

    • Edit the DET file using the “best practices” below

Create a CommCare Sync account

There are two ways to create an account:

  1. Ask a system admin to open public sign ups, and then click on “Sign Up” on the home page.

  2. 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.

    1. You’ll need your API key from CommCare HQ under Account Settings.

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

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

  5. 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.

Adding Databases

Databases can be added by site admins through the Django admin interface. The database may need to also be created by a system admin on the server.

Administration

The server has two types of administration.

System Admin

The system admin is required to make configuration changes to the underlying server, code, and configuration. System administrators will be developers.

Site Admin

The site administrator can manage other users, as well as access the Django admin site which is the only way to manage connected Databases.






  • No labels