7.3 > Querying your Data from DHIS2 - Tips and Assistance

Ministry of Health Data Alignment: Useful Queries

When compiling the three input files for the MoH It may help to ask MoH Staff (or others with appropriate privileges) to pull information from their DHIS2 instances that will provide you the detail to either compile or simplify your files. Below you will find a user guide to some queries that might be of assistance. If you have any questions, you can always contact the Support Team.

 

Indicator Mapping

Hopefully, you will either have a familiarity with the MoH DHIS2 instance and how they collect the data that is represented by DATIM Indicators (e.g. TX_CURR). If you do not know this information, it may be helpful to start by looking at a list of data elements and their category combinations. To get this information, you can use Query IM 1 (below) if your DHIS2 instance is v2.25 later or you can use Query IM 2 (below) if your DHIS2 instance is v2.24 later.

 

Facility Reconciliation

The Facility Reconciliation process requires you to merge additional facilities from your country’s MoH hierarchy into the existing DATIM hierarchy.

Separate instructions have been provided as to how the Support Team can help you to generate the CSV file that conforms to the format.

It may be easier if you are able to first reduce the number of facilities that you are working with by identifying only the facilities that have relevant data. In order to do this, you will need to have identified the relevant DHIS2 data elements on the MoH instance by completing the Indicator Mapping. (See Queries Fac 1a-1b). Note: you will need to match names (or UIDs) exactly to get correct results back.

Once you have done this, you can send this list of organization units back to the Support Team along with the other necessary inputs for us to generate the csv file (see separate guide and we will generate a csv file for you). Alternately, you can provide us with the hierarchy level and then we will write an SQL query that can output the required information required by the Fac Recon CSV file.

 

Results File 

When you are ready to start putting together the results file, you will first need to identify the relevant periods where data exists. Then, you can pull the data for submission.

Step 1: Identifying appropriate periods: It will be helpful to identify what period types the data is stored against for a given data element and disaggregation if you do not already know this. You can get at some of this information by using Query Results 1. If this query returns only one period type for a given data element and disaggregation, obtaining the data should be straightforward. If there is more than one period type, you will need to research further or get in contact with your counterparts at MoH to identify which data should be used for this activity.

Step 2: Pulling data: It should be possible to generate your results file using a pull directly from DHIS2. Please contact the Support Team and we can help you put together an appropriate query. Note that do so, we will most likely need you to gather the information contained in the queries referenced above.

 

Queries 

IM 1 [v2.25 or later]

Notes: This query can be run without any modifications. 

SELECT DISTINCT DE.name AS DE_Name,COC.name AS COC_name, DE.uid AS DE_uid, COC.uid AS COC_uid FROM

(SELECT DISTINCT dataelementid,categorycomboid FROM datasetelement WHERE categorycomboid IS NOT null

UNION ALL SELECT DISTINCT dataelementid,categorycomboid FROM dataelement

) DEC

LEFT JOIN dataelement DE ON DEC.dataelementid=DE.dataelementid

LEFT JOIN categorycombos_optioncombos CCOC ON DEC.categorycomboid=CCOC.categorycomboid

LEFT JOIN categoryoptioncombo COC ON CCOC.categoryoptioncomboid=COC.categoryoptioncomboid

ORDER BY DE.name,COC.name

 

IM 2 [v2.24 or earlier]

Notes: This query can be run without any modifications.

SELECT DISTINCT DE.name AS DE_Name,COC.name AS COC_name, DE.uid AS DE_uid, COC.uid AS COC_uid FROM

(SELECT DISTINCT dataelementid,categorycomboid FROM dataelement

) DEC

LEFT JOIN dataelement DE ON DEC.dataelementid=DE.dataelementid

LEFT JOIN categorycombos_optioncombos CCOC ON DEC.categorycomboid=CCOC.categorycomboid

LEFT JOIN categoryoptioncombo COC ON CCOC.categoryoptioncomboid=COC.categoryoptioncomboid 

ORDER BY DE.name,COC.name

 

Fac 1a [This is the standard query for identifying facilities with relevant data. Query Fac 1b is provided if you want to use uids instead] 

Notes: You need to modify this query. Where it says WHERE name IN('ANC 1st visit','ANC 2nd visit')you will need to modify the portion in turquoise to reference the names used by your country’s MoH DHIS2 instance’s relevant Data Elements. This query will provide OUs associated with any disaggregations. If you’d like to sort out irrelevant disaggregations, please contact the Support Team to put together a more specific query.

SELECT DISTINCT OU.name,OU.code,OU.uid

FROM (SELECT DISTINCT DV.sourceid

FROM datavalue DV INNER JOIN dataelement DE ON DV.dataelementid=DE.dataelementid

WHERE (DV.dataelementid IN (SELECT DISTINCT dataelementid FROM dataelement WHERE name IN('ANC 1st visit','ANC 2nd visit')

))) DVOU

LEFT JOIN organisationunit OU ON DVOU.sourceid=OU.organisationunitid

ORDER BY OU.name,OU.code,OU.uid

 

Fac 1b [This is an alternate version of Query Fac 1a which allows you to search by uid]

Notes: You need to modify this query. Where it says WHERE uid IN('fbfJHSPpUQD','cYeuwXTCPkU')you will need to modify the portion in turquoise to reference the uids used by your country’s MoH DHIS2 instance’s relevant Data Elements. This query will provide OUs associated with any disaggregations. If you’d like to sort out irrelevant disaggregations, please contact the Support Team to put together a more specific query.

SELECT DISTINCT OU.name,OU.code,OU.uid

FROM (SELECT DISTINCT DV.sourceid

FROM datavalue DV

INNER JOIN dataelement DE ON DV.dataelementid=DE.dataelementid

WHERE (DV.dataelementid IN (SELECT DISTINCT dataelementid FROM dataelement WHERE uid IN('fbfJHSPpUQD','cYeuwXTCPkU')

))) DVOU

LEFT JOIN organisationunit OU ON DVOU.sourceid=OU.organisationunitid

ORDER BY OU.name,OU.code,OU.uid

 

Res 1

Notes: You need to modify this query. Where it says WHERE name IN('ANC 1st visit','ANC 2nd visit')you will need to modify the portion in turquoise to reference the names used by your country’s MoH DHIS2 instance’s relevant Data Elements. This query will provide Period Types (e.g. monthly, daily) associated with data element and disaggregation combinations. It will include any disaggregations that exist for a data element. If you’d like to sort out irrelevant disaggregations, please contact the Support Team to put together a more specific query. 

SELECT DISTINCT DE.name AS DE_name,COC.name AS COC_name,PET.name AS periodType

FROM (SELECT DISTINCT DV.dataelementid, DV.categoryoptioncomboid,DV.periodid

FROM datavalue DV

INNER JOIN dataelement DE ON DV.dataelementid=DV.dataelementid

WHERE (DV.dataelementid IN (SELECT DISTINCT dataelementid FROM dataelement WHERE name IN('ANC 1st visit','ANC 2nd visit')

))) PEOU 

LEFT JOIN dataelement DE ON PEOU.dataelementid=DE.dataelementid

LEFT JOIN categoryoptioncombo COC ON PEOU.categoryoptioncomboid=COC.categoryoptioncomboid

LEFT JOIN period PE ON PEOU.periodid=PE.periodid

LEFT JOIN periodtype PET ON PE.periodtypeid=PET.periodtypeid

ORDER BY DE.name,COC.name,PET.name

 

 

Generate Facilities CSV File: How the Support Team can help

Background

If your country’s MoH uses DHIS2 and you would like help generating the CSV file for the Facility Reconciliation process, the Transformer Team would be happy to help! We will need two pieces of information from you. Once you have shared this information via SharePoint, we will upload a CSV file on the MoH SharePoint site in your country’s folder.

 

Required Information from Country Team

Information on Organization Units

To pull the required Organisation Unit information, you (or a contact at the MoH) will need to

1. Log onto your MoH instance
2. Navigate to the following api endpoint:
   
   /api/organisationUnits.json?paging=false&fields=id,name,code,coordinates,parent,level&order=level

• g. if your MoH instance is https://www.dhisInCountry.com, you will navigate to https://www.dhisInCountry.com/api/organisationUnits.json?paging=false&fields=id,name,code,coordinates,parent,level&order=level

3. Wait for data to load.

• NOTE: You may need to wait a few minutes as the amount of transferred data is likely to be fairly large, depending on the number of organization units in the hierarchy and how detailed the coordinates are.

4. Save the resulting data. If you are using Chrome: on Windows: type CTRL+S, then save to your desired location locally; on a Mac: type command+S, then save to your desired location locally.
5. Upload file to SharePoint

 

Information on Organization Unit Hierarchy

We will need to know

1. What Level Countries are saved at. (For example: 1)
2. What Level Regions are saved at.
3. What Level Zones are saved at.
4. What Level Facilities are saved at. 

If you do not know this information, you can

1. Ask a contact at MoH if they know
2. Look at the information available in the maintenance app (Go to Maintenance>Organisation Unit>Organisation Unit Level). You can post a screenshot to SharePoint if you are unsure of how to interpret.
3. Look at the information available in the web api.
   1. Log onto your instance
   2. Navigate to the following api endpoint:

/api/organisationUnitLevels?paging=false&fields=id,name,level&order=level

• g. if your MoH instance is https://www.dhisInCountry.com, you will navigate to https://www.dhisInCountry.com/api/organisationUnitLevels?paging=false&fields=id,name,level&order=level

1. Wait for data to load.

 

Troubleshooting

Information on Organization Units

Issue: Cannot get information on Organization Units because website won’t load 

If the api request in parts 2 and 3 time out on you after a few minutes, you can try to save the output directly into a file, using a command-line request. For example:

If you are using, a Unix-based system (Linux, Mac OS), you can use a cURL request

• Open new terminal window
• Navigate to the directory where you would like to save the output file, e.g.

cd /Users/yourname/Documents/

• Use a cURL command to make request

cURL -X ‘https://www.yourinstance.com/api/organisationUnits.json?paging=false&fields=id,name,code,coordinates,parent,level&order=level’ -u username:password -o ‘yourfile.json’

• Look for file in the directory where you saved it, then upload to SharePoint

If you are using a Windows system, you can use PowerShell (or another command line tool)

Issue: Facilities are not all at one level

If you discover that facilities (or zones or regions) are not at consistent levels throughout your hierarchy, we can still help as long as there is some kind of pattern. For example, we can probably still generate a CSV file for you if:

• For Zone A: Facilities are at Level 5, For Zone B: Facilities are at Level 6
• Facilities are at different levels but they all contain the word “Facility” in their name

If facilities (or zones or regions) do not follow any particular pattern, you will first need to identify a list of all facilities before we can help with creating a csv file. In this situation, it might be easier for you to create the csv file directly.