[Azure Data Factory] Repeat Group Data Flow Configuration

Owned by Rishabh Rath
Last updated: Aug 27, 2024
4 min read
Data Flow for Visits Data

Step 1: Create Visit Cases XML Data in ADF

  1. Create a Data Flow:

    • In Azure Data Factory Studio, go to the Author tab.

    • Create a new Data Flow and name it VisitCaseXML.

  2. Add Sources:

    • Visits Source: Add a source transformation and name it Visits. Connect it to your Azure SQL Database dataset (visit_sqldata). This source will contain columns such as Household_ID, Member_ID, Visit_ID, Visit_Date, Health_Score, Weight, Vaccinated, case_id, parent_case_id, and Visit_Count.

    • Meds Source: Add another source transformation and name it Meds. Connect it to your Azure SQL Database dataset (med_sqldata). This source will contain columns such as Medication_Name, Dosage, and parent_case_id.

  3. Join Transformations:

    • Join Visits and Meds: Add a join transformation named JoinVisitsandMeds. Join the Visits and Meds sources on the parent_case_id. This step ensures that each visit is associated with the corresponding medications.

  4. Create Visit and Medication XML Blocks:

    • Split Nodes: Add a window transformation named SplitNodes to create row numbers for each visit associated with a household member.

      window( over(case_id), asc(Visit_ID, true), node = rowNumber()-1 )

    • Edit Visit and Med XML Block: Add a derived column transformation named EditVisitandMedXMLBlock. Use the following expressions to create the XML snippets for visits and medications. This can be easily copied from the Raw XML section of any form submission within CommCare for a visits registration.

      visitblock_xml = concat( '<visit_counter>', Visit_Count, '</visit_counter>', '<visit_date>', Visit_Date, '</visit_date>', '<health_score>', Health_Score, '</health_score>', '<weight>', Weight, '</weight>', '<vaccinated>', Vaccinated, '</vaccinated>', '<subcase_0>', ' <n0:case xmlns:n0="<http://commcarehq.org/case/transaction/v2>" case_id="', lower(case_id), '" date_modified="', $formatted_datetime, '" user_id="', $mapped_userid, '">', ' <n0:create>', ' <n0:case_name>', Visit_Count, '</n0:case_name>', ' <n0:owner_id>', $mapped_ownerid, '</n0:owner_id>', ' <n0:case_type>visit</n0:case_type>', ' </n0:create>', ' <n0:update>', ' <n0:health_score>', Health_Score, '</n0:health_score>', ' <n0:visit_date>', Visit_Date, '</n0:visit_date>', ' <n0:weight>', Weight, '</n0:weight>', ' </n0:update>', ' <n0:index>', ' <n0:parent case_type="household_member">', lower(parent_case_id), '</n0:parent>', ' </n0:index>', ' </n0:case>', '</subcase_0>', '<n1:case xmlns:n1="<http://commcarehq.org/case/transaction/v2>" case_id="', lower(case_id), '" date_modified="', $formatted_datetime, '" user_id="', $mapped_userid, '">', ' <n1:update>', ' <n1:visit_counter>', Visit_Count, '</n1:visit_counter>', ' </n1:update>', '</n1:case>' ) medblock_xml = concat( '<medication>', ' <medicine_name>', Medication_Name, '</medicine_name>', ' <dosage>', Dosage, '</dosage>', '</medication>' )

  5. Aggregate and Convert to String:

    • Aggregate by Parent Case ID: Add an aggregate transformation named AggregatebyParentCaseID. Group by case_id and collect all visit and medication XML snippets into arrays.

      aggregate( groupBy(case_id), visitblock_xml_array = collect(visitblock_xml), medblock_xml_array = collect(medblock_xml) )

    • Convert to String: Add a derived column transformation named ConverttoString to convert the collected arrays into strings.

  6. Fix Formatting:

    • Fix Formatting: Add a derived column transformation named FixFormatting to remove unwanted characters and add line breaks where necessary.

  7. Combine XML Blocks:

    • Final Output: Add a derived column transformation named FinalOutput. Combine the header, visit, medication, and footer XML blocks into the final XML.

  8. Sink Transformation:

    • SQLDB Sink: Add a sink transformation named SQLDBSink. Configure it to upsert the XML data into the visit_submission table in your Azure SQL Database.

Step 2: Submit Visit XML Data to CommCare

  1. Create a New Pipeline:

    • Name it Visits Submission.

  2. Add Lookup Activity:

    • Lookup SQL Data: Add a Lookup activity named LookupSQLDB. Retrieve the final_xml data from the visit_submission table using the following SQL query:

  3. Add Get Credentials Activity:

    • Get API Key: Add a Web Activity named GetCredentials to retrieve the API key from Azure Key Vault. Set the method to GET and configure the URL to point to your Key Vault secret.

  4. Add ForEach Activity:

    • Submit Forms: Add a ForEach activity named SubmitForm to iterate over the results of the Lookup activity. Inside the ForEach activity, add a Web Activity named CCFormSubmissionAPI to submit each final_xml to CommCare’s API.

  5. Configure Web Activity for Submission:

    • CCFormSubmissionAPI: Set the HTTP method to POST. Add a header for Authorization using the output from the GetCredentials activity. Set the body to @item().final_xml to send the XML data.

  6. Publish and Execute the Pipeline:

    • Publish the pipeline and run it to submit your visit case XMLs to CommCare.