Create BASIC connection

Note: DHIS supports both Basic and OAuth2 authentication, we’ll go for Basic for now to avoid creating a client_id on the server.

Go to your mart Admin > Connections https://extranet.who.int/xmart4/YOUR_MART/admin/connections

Create a new WebService Basis Auth Connection using the created username/password on the remote DHIS instance: image

Pipeline using GetWebService to fetch the CSV format

Note: JSON export is also supported by DHIS but it returns a larger response than CSV due to repeated properties foreach row and CSV is flat by design, so better choice here

<XmartPipeline> 
  <Extract>
    <GetWebService Url="https://play.dhis2.org/2.36.3/api/dataValueSets.csv?dataElementIdScheme=NAME&amp;orgUnitIdScheme=NAME&amp;includeDeleted=false&amp;children=true&amp;idScheme=CODE&amp;startDate=2019-10-23&amp;endDate=2020-01-23&amp;orgUnit=O6uvpzGd5pu&amp;dataSet=Nyh6laLdBEJ" ConnectionName="DHIS2_BASIC">
      <GetText Delimiter="," OutputTableName="IDSR_WEEKLY" />
    </GetWebService>
  </Extract>
  <Load>
    <LoadTable SourceTable="IDSR_WEEKLY" TargetTable="IDSR_WEEKLY" LoadStrategy="REPLACE">
      <Transform>
        <SplitColumnByRegEx Column="period" RegEx="(?&lt;YEAR&gt;20\d{2}).*"  />
        <AddColumn Name="COUNTRY" FillWith="SLE" />
        <AddColumn Name="VACCINE" FillWith="Unknown" />
      </Transform>
      <LookupIDs>
        <SysIDLookup SourceResultColumn="COUNTRY_SYSID" SourceColumns="COUNTRY" LookupTable="REF_COUNTRIES" LookupColumns="CODE" />
        <SysIDLookup SourceResultColumn="VACCINE_FK" SourceColumns="VACCINE" LookupTable="REF_VACCINES" LookupColumns="CODE" />
      </LookupIDs>
      <ColumnMappings>
        <ColumnMapping Source="YEAR" Target="YEAR" />
        <ColumnMapping Source="COUNTRY" Target="ADMIN_0_RAW" />
        <ColumnMapping Source="VACCINE_FK" Target="VACCINE_FK" />
        <ColumnMapping Source="COUNTRY_SYSID" Target="ADMIN_0" />
        <ColumnMapping Source="orgunit" Target="ADMIN_1_RAW" />
        <ColumnMapping Source="DATAELEMENT" Target="DISEASE" />
        <ColumnMapping Source="value" Target="DOSES" />   
      </ColumnMappings>
    </LoadTable>
  </Load>
</XmartPipeline>

Result

Using batch debugger, the actual data fetched from the server is available. image