Open

Understanding system architecture and data migration

Partners transitioning from another solution to CommCare may feel the urge to transfer all their historical data into CommCare. However, in most cases, they are primarily concerned with the most recent updated values of the entities they track. This facilitates a smooth transition of future data collection from their previous tool to CommCare. In these instances, Dimagi strongly advises preserving all historical data in secure environments such as cloud-based data warehouses and only importing the latest data into CommCare to ensure uninterrupted future workflows. Historical data can be integrated with BI tools along with data from CommCare for business intelligence purposes.

The reasoning behind this recommendation

The architecture of technology systems varies, leading to differences in how data is processed across platforms. On CommCare, for example, any data collected via CommCare apps in the field are considered “form submissions” - in other words, frontline workers (called mobile users/workers in our ecosystem) login to their CommCare apps and open individual forms and submit them after collecting data for clients or entities they are tracking. Once these forms are submitted, depending on what data model has been designed for that specific workflow, CommCare can create new cases, update existing ones or simply save these submissions on the server. The same underlying principles apply for web apps (CommCare apps running on the browser with no offline capabilities) submissions as well. In other words, data submitted on CommCare may have multiple dependencies.

As partners transition to CommCare, they are usually interested in shifting their current workflows to the new system after a certain cutoff date where they use the last updated values for the entities they are tracking, upload them to CommCare and continue tracking them through CommCare for the remaining life cycle of their projects. The following sections provide a walkthrough for both scenarios: a migration of last updated values to CommCare for continuing the project on the platform and a historical migration of all data (including form content and case properties) into CommCare.

Migrating Last Updated Values to CommCare

Open

High Level Data Flow when switching from another tool to CommCare

Here’s an example:

Partner A is running a community health program tracking pregnant mothers. They are currently using Kobo Toolbox. Steps to follow:

1. When it is time to switch to CommCare, Partner A will download all their data into a secure environment. These can be robust data warehousing solutions like Snowflake, SQL databases like MySQL, cloud storage like OneDrive or SharePoint.

2. Partner A will create their data model on their CommCare project space, ensuring case properties are created for all the indicators they wish to track for their clients or other entities via CommCare. This will include building app(s) on CommCare with the corresponding case type, case properties and workflows required for future data collection.

3. Partner A will then use CommCare’s built-in case importing feature via Excel to upload the last updated values from their previous database into CommCare. This will require them to format the excel file with the corresponding column headers for all the case properties they wish to populate in CommCare.

4. Once the data is successfully imported into CommCare, Partner A will proceed to use CommCare for future data collection.

Preparing application content and data model for CommCare

Preparing data from previous system for importing into CommCare

Migrating Historical Data into CommCare

Data Assessment and Planning

• Identify Data Sources: Determine the systems from which data will be migrated (e.g. Kobo, ODK, excel files).

• Data Mapping: Define how data from the existing systems will map to the CommCare structure, including cases, forms, and users.

• Data Cleaning: Clean the data to remove duplicates, errors, and irrelevant information.

• Setup Database: Load all data into structured tables in a SQL database.

Setting Up CommCare

• Project Space: Ensure the CommCare project space is set up with the necessary applications, forms, and case structures.

• User Accounts: Set up user accounts and assign appropriate roles and permissions.

• Location Structure: If data is mapped to hierarchical location structures, make use of CommCare’s organization feature to set up the location structures.

Data Migration Process

• Extract Data: Use appropriate methods (e.g., SQL queries, Kobo API, ODK exports) to extract data from the source systems.

• Structured Database: Ensure that all finalised data is saved to a SQL database that can be scaled for larger data sets and provides data transformation and mapping features if needed.

Data Transformation and Loading

• Data Mapping and Transformation: Map the extracted data to the corresponding CommCare fields and transform it as needed to fit CommCare’s data model.

• Automated Uploads: For dealing with large datasets or requiring regular updates, use CommCare’s APIs or integration tools like Azure Blob Storage

• Here's a simplified table listing tools that can be used for this purpose, in order of technical complexity:

Data Validation

• Check Data Integrity: Verify that all data has been correctly migrated and is accessible in CommCare.

• Validation Testing: Perform tests to ensure data accuracy and completeness.

User Training and Support

• Training Sessions: Conduct training for users to familiarize them with the CommCare interface and new data workflows.

• Support Channels: Provide support through helpdesks, documentation, and FAQs.

Best Practices and Tips

• Consistent Data Structures: Maintain consistent data structures across different source systems to simplify mapping and migration.

• Documentation: Keep detailed documentation of the migration process, including data mappings, transformation rules, and validation checks.

• Pilot Testing: Conduct a pilot migration with a subset of data to identify potential issues and refine the process before full-scale migration.

• Data Privacy and Security: Ensure compliance with data privacy regulations and secure sensitive data during migration.

Common Challenges and Solutions

• Data Quality Issues: Address data quality issues before migration to avoid complications.

• Data Volume: Manage large data volumes by breaking them into smaller, manageable chunks.

• System Compatibility: Ensure compatibility between source systems and CommCare to minimize data transformation complexities.

This tutorial explains how data from other tools can be programmatically migrated to CommCare using Azure SQL Database and Power Automate.

Historical Data Migration Walkthrough

Existing Data Structure

Data can be structured in numerous ways in other tools. Depending on the complexity of this structure, the level of effort involved in translating it to CommCare’s data architecture can vary from high to low. If single entities like clients or households are being tracked, setting up the similar structure in CommCare would be easier compared to a hierarchical structure with multiple related entities and repeat data.

Let’s look at a complex structure to understand how it can be replicated on CommCare. This will also inform approaches for more simpler structures.

Sample Data Structure in Other Tool

Here's a summarized table based on the diagram, detailing the entities, their relationships, and key indicators:

• Household is the top-level entity, identifying the group with unique identifiers and location.

• Household Member is linked to the Household, with specific details about each member.

• Visit records are associated with each Household Member, tracking various health-related indicators.

• Medicines and Dosage are specific to each Visit, detailing the medical treatment received.

Setup in CommCare

The output of this setup will look like:

Using the documentation on the help site, this can be easily built as an application in a project space on CommCare.

Sample Application Structure for Example Above

The form submission raw XML can be accessed from multiple reports. This is an example of how it can be accessed and copied from the Submit History report.

Setup in SQL Database

The ideal approach to migrating data from other sources to CommCare requires standardisation of storage in a structured, secure and scalable tool like a SQL DB. As different tools may have different options for data extraction, this tutorial assumes that relevant steps would be taken to load all data to be migrated into structured SQL tables.

Sample tables in SQL

Setup in Power Automate

Create individual flows for every case type. In this example there are three flows:

CommCare Migration - Parent Cases: This flow will receive data from SQL, transform and make API calls to submit forms to CommCare for all household (parent case) data. See https://dimagi.atlassian.net/wiki/spaces/commcarepublic/pages/edit-v2/2487386235?draftShareId=24543a2d-811f-49be-9835-ae33b09cf14a

CommCare Migration - Child Cases: This flow will receive data from SQL, transform and make API calls to submit forms to CommCare for all household members (child case) data. See https://dimagi.atlassian.net/wiki/spaces/commcarepublic/pages/edit-v2/2486534302?draftShareId=295a2966-c16a-48f1-928f-0f7fe8d6769e

CommCare Migration - Repeat Groups: This flow will receive data from SQL, transform and make API calls to submit forms to CommCare for all visit (child cases to household members) data along with repeat group data within the visit data for medicines. See [Power Automate] Repeat Group Data Flow Configuration

Setup in Azure Data Factory

Create individual data flows and pipelines for every case type. In this example there are three sections:

CommCare Migration - Parent Cases: These pipelines contains a data flow for the parent case type data wherein data is transformed and final XML content is saved to the SQL DB and actions through which this data is submitted to CommCare. See https://dimagi.atlassian.net/wiki/pages/resumedraft.action?draftId=2545680449&draftShareId=f26f7fc0-837c-4821-9610-df7dfeaf668f

CommCare Migration - Child Cases: These pipelines contains data flows for the parent case block and household member data block wherein data is transformed and final XML content is saved to the SQL DB and actions through which this data is submitted to CommCare. See [Azure Data Factory] Child Cases Flow Configuration

CommCare Migration - Repeat Groups: These pipelines contains a data flow for the visit case type data (child cases to household members) along with repeat group data for medicines wherein data is transformed and final XML content is saved to the SQL DB and actions through which this data is submitted to CommCare. See https://dimagi.atlassian.net/wiki/pages/resumedraft.action?draftId=2545877064&draftShareId=afb0f9f5-f777-4efe-8e86-3394be76f8ce

Approaches to consider

1. CommCare offers robust documentation for each of these steps and partners can easily follow instructions to get the latest updated values of their data from a previous tool into CommCare through self-service.

2. All users are highly encouraged to complete Dimagi Academy courses and go through the CommCare Help Site and User Forum to familiarise themselves with the platform. 

3. CommCare Onboarding - Dimagi’s Customer Success team is well positioned to support new partners in setting up CommCare to receive data from a previous tool. Please contact us at sales@dimagi.com for more information and the best suited plan for this support.

4. Professional services support from Dimagi. Please contact us at sales@dimagi.com for more information and the best suited plan for this support.