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:
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&orgUnitIdScheme=NAME&includeDeleted=false&children=true&idScheme=CODE&startDate=2019-10-23&endDate=2020-01-23&orgUnit=O6uvpzGd5pu&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="(?<YEAR>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.