Exporting data through SQL View

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

···

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss

···

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Hi Tantely,

Here is a sample query that will give you what I think you want:

···

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

Thank you Randy and Knut for the scripts. While I am running the sample script and customizing with the dataset name, I am facing another issue with a timed out connection due to a busy server. I cannot output any result yet. Do you know if it is a server memory issue?

Best Regards.

Tantely.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

···

On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Here is a sample query that will give you what I think you want:


select dv.sourceid as organisationunitid,

ou.name, ou.code as facilitycode, de.name, ‘Vaccination’ as datasetname,

pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv

inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)

inner join dataelement de on (dv.dataelementid=de.dataelementid)

inner join period pe on (dv.periodid=pe.periodid)

inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)

where dv.dataelementid in

(select dataelementid from datasetmembers where datasetid in (

select datasetid from dataset where name=‘Vaccination’))


Just replace ‘Vaccination’ with the name of your dataset. You can also create a variable for the dataset name of your SQL view in the newer versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Hi Tantely,

Are you limiting the data to a single data set at a time? You might try to limit the result set by putting “limit 100” at the end of the query. That way you can at least see if the query is working. There are often limits set on the postgres server for the number of records that can be returned, especially on cloud servers. You might need to change a parameter in the config.

Randy

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

···

On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy and Knut for the scripts. While I am running the sample script and customizing with the dataset name, I am facing another issue with a timed out connection due to a busy server. I cannot output any result yet. Do you know if it is a server memory issue?

Best Regards.

Tantely.

On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Here is a sample query that will give you what I think you want:


select dv.sourceid as organisationunitid,

ou.name, ou.code as facilitycode, de.name, ‘Vaccination’ as datasetname,

pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv

inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)

inner join dataelement de on (dv.dataelementid=de.dataelementid)

inner join period pe on (dv.periodid=pe.periodid)

inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)

where dv.dataelementid in

(select dataelementid from datasetmembers where datasetid in (

select datasetid from dataset where name=‘Vaccination’))


Just replace ‘Vaccination’ with the name of your dataset. You can also create a variable for the dataset name of your SQL view in the newer versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

Hi Randy,

The SQL script is running fine after putting “limit 100” at the end of the query. Thanks! This script is answering to my question and I would like to customize it further. I am using SSH to connect to the remote server and would like to ask you where should be located the postgres config file for me to make some change on the size of the query limitation?

Best Regards.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

···

On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Are you limiting the data to a single data set at a time? You might try to limit the result set by putting “limit 100” at the end of the query. That way you can at least see if the query is working. There are often limits set on the postgres server for the number of records that can be returned, especially on cloud servers. You might need to change a parameter in the config.

Randy

On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy and Knut for the scripts. While I am running the sample script and customizing with the dataset name, I am facing another issue with a timed out connection due to a busy server. I cannot output any result yet. Do you know if it is a server memory issue?

Best Regards.

Tantely.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Here is a sample query that will give you what I think you want:


select dv.sourceid as organisationunitid,

ou.name, ou.code as facilitycode, de.name, ‘Vaccination’ as datasetname,

pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv

inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)

inner join dataelement de on (dv.dataelementid=de.dataelementid)

inner join period pe on (dv.periodid=pe.periodid)

inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)

where dv.dataelementid in

(select dataelementid from datasetmembers where datasetid in (

select datasetid from dataset where name=‘Vaccination’))


Just replace ‘Vaccination’ with the name of your dataset. You can also create a variable for the dataset name of your SQL view in the newer versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

I think the settings are in postgresql.conf.

You could check the following:

statement_timeout - set it to 0 to disable it.

I can’t seem to find the command limited the number of records a query can return. I suppose it varies with the size of the record.

Randy

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

···

On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Hi Randy,

The SQL script is running fine after putting “limit 100” at the end of the query. Thanks! This script is answering to my question and I would like to customize it further. I am using SSH to connect to the remote server and would like to ask you where should be located the postgres config file for me to make some change on the size of the query limitation?

Best Regards.

On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Are you limiting the data to a single data set at a time? You might try to limit the result set by putting “limit 100” at the end of the query. That way you can at least see if the query is working. There are often limits set on the postgres server for the number of records that can be returned, especially on cloud servers. You might need to change a parameter in the config.

Randy

On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy and Knut for the scripts. While I am running the sample script and customizing with the dataset name, I am facing another issue with a timed out connection due to a busy server. I cannot output any result yet. Do you know if it is a server memory issue?

Best Regards.

Tantely.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Here is a sample query that will give you what I think you want:


select dv.sourceid as organisationunitid,

ou.name, ou.code as facilitycode, de.name, ‘Vaccination’ as datasetname,

pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv

inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)

inner join dataelement de on (dv.dataelementid=de.dataelementid)

inner join period pe on (dv.periodid=pe.periodid)

inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)

where dv.dataelementid in

(select dataelementid from datasetmembers where datasetid in (

select datasetid from dataset where name=‘Vaccination’))


Just replace ‘Vaccination’ with the name of your dataset. You can also create a variable for the dataset name of your SQL view in the newer versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

Thank you Randy. I am going to check this postgresql.conf file to troubleshoot this issue.

Best Regards.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

···

On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy rwilson@msh.org wrote:

I think the settings are in postgresql.conf.

You could check the following:

statement_timeout - set it to 0 to disable it.

I can’t seem to find the command limited the number of records a query can return. I suppose it varies with the size of the record.

Randy

On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Hi Randy,

The SQL script is running fine after putting “limit 100” at the end of the query. Thanks! This script is answering to my question and I would like to customize it further. I am using SSH to connect to the remote server and would like to ask you where should be located the postgres config file for me to make some change on the size of the query limitation?

Best Regards.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Are you limiting the data to a single data set at a time? You might try to limit the result set by putting “limit 100” at the end of the query. That way you can at least see if the query is working. There are often limits set on the postgres server for the number of records that can be returned, especially on cloud servers. You might need to change a parameter in the config.

Randy

On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy and Knut for the scripts. While I am running the sample script and customizing with the dataset name, I am facing another issue with a timed out connection due to a busy server. I cannot output any result yet. Do you know if it is a server memory issue?

Best Regards.

Tantely.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Here is a sample query that will give you what I think you want:


select dv.sourceid as organisationunitid,

ou.name, ou.code as facilitycode, de.name, ‘Vaccination’ as datasetname,

pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv

inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)

inner join dataelement de on (dv.dataelementid=de.dataelementid)

inner join period pe on (dv.periodid=pe.periodid)

inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)

where dv.dataelementid in

(select dataelementid from datasetmembers where datasetid in (

select datasetid from dataset where name=‘Vaccination’))


Just replace ‘Vaccination’ with the name of your dataset. You can also create a variable for the dataset name of your SQL view in the newer versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Tantely Raminosoa

Data Officer
Management Sciences for Health
Antananarivo Madagascar

Mobile: 0321180040

E-mail: traminosoa@mikolo.org

Skype: traminosoa

Stronger health systems. Greater health impact.

www.msh.org

Hi there.

In general, if the query is taking such a long time that it times out, then there may be something wrong with the query or the level of performance of your server. Disabling that time query time out, could result in queries which take an extremely long time to complete, leading to the server being overwhelmed. How many records does your data value table have? I would be surprised if Postgres would time out unless the size of the database is very large (many tens of millions of rows) or the server is under-resourced. Another possibility is that if you are seeing a timeout from the web (via DHIS2) then the reverse proxy may be timing out. You can (but should be careful again) to increase the time out. You can increase this with

proxy_read_timeout 600;
···

Also, please have a look at our section on Postgres performance tuning. I have seen very large decreases in query time by performing tweaking the default setup to suit DHIS2 better.

http://dhis2.github.io/dhis2-docs/master/en/implementer/html/ch08s03.html#d5e464

Regards,

Jason

On Mon, Apr 4, 2016 at 3:45 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy. I am going to check this postgresql.conf file to troubleshoot this issue.

Best Regards.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy rwilson@msh.org wrote:

I think the settings are in postgresql.conf.

You could check the following:

statement_timeout - set it to 0 to disable it.

I can’t seem to find the command limited the number of records a query can return. I suppose it varies with the size of the record.

Randy


Tantely Raminosoa

Data Officer
Management Sciences for Health
Antananarivo Madagascar

Mobile: 0321180040

E-mail: traminosoa@mikolo.org

Skype: traminosoa

Stronger health systems. Greater health impact.

www.msh.org

On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Hi Randy,

The SQL script is running fine after putting “limit 100” at the end of the query. Thanks! This script is answering to my question and I would like to customize it further. I am using SSH to connect to the remote server and would like to ask you where should be located the postgres config file for me to make some change on the size of the query limitation?

Best Regards.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Are you limiting the data to a single data set at a time? You might try to limit the result set by putting “limit 100” at the end of the query. That way you can at least see if the query is working. There are often limits set on the postgres server for the number of records that can be returned, especially on cloud servers. You might need to change a parameter in the config.

Randy

On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy and Knut for the scripts. While I am running the sample script and customizing with the dataset name, I am facing another issue with a timed out connection due to a busy server. I cannot output any result yet. Do you know if it is a server memory issue?

Best Regards.

Tantely.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Here is a sample query that will give you what I think you want:


select dv.sourceid as organisationunitid,

ou.name, ou.code as facilitycode, de.name, ‘Vaccination’ as datasetname,

pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv

inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)

inner join dataelement de on (dv.dataelementid=de.dataelementid)

inner join period pe on (dv.periodid=pe.periodid)

inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)

where dv.dataelementid in

(select dataelementid from datasetmembers where datasetid in (

select datasetid from dataset where name=‘Vaccination’))


Just replace ‘Vaccination’ with the name of your dataset. You can also create a variable for the dataset name of your SQL view in the newer versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Hi Jason,

Thnak you for you message. The data value table has about 1,100,000 records. In addition to checking the postgreSQL configuration file, this time-out might also be related to the server configuration. Could you please provide a link for the JVM/Tomcat performance tuning according to the amount of server RAM?

Best Regards,

Tantely.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

···

On Tue, Apr 5, 2016 at 3:57 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi there.

In general, if the query is taking such a long time that it times out, then there may be something wrong with the query or the level of performance of your server. Disabling that time query time out, could result in queries which take an extremely long time to complete, leading to the server being overwhelmed. How many records does your data value table have? I would be surprised if Postgres would time out unless the size of the database is very large (many tens of millions of rows) or the server is under-resourced. Another possibility is that if you are seeing a timeout from the web (via DHIS2) then the reverse proxy may be timing out. You can (but should be careful again) to increase the time out. You can increase this with

proxy_read_timeout 600;

Also, please have a look at our section on Postgres performance tuning. I have seen very large decreases in query time by performing tweaking the default setup to suit DHIS2 better.

http://dhis2.github.io/dhis2-docs/master/en/implementer/html/ch08s03.html#d5e464

Regards,

Jason

On Mon, Apr 4, 2016 at 3:45 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy. I am going to check this postgresql.conf file to troubleshoot this issue.

Best Regards.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy rwilson@msh.org wrote:

I think the settings are in postgresql.conf.

You could check the following:

statement_timeout - set it to 0 to disable it.

I can’t seem to find the command limited the number of records a query can return. I suppose it varies with the size of the record.

Randy


Tantely Raminosoa

Data Officer
Management Sciences for Health
Antananarivo Madagascar

Mobile: 0321180040

E-mail: traminosoa@mikolo.org

Skype: traminosoa

Stronger health systems. Greater health impact.

www.msh.org

On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Hi Randy,

The SQL script is running fine after putting “limit 100” at the end of the query. Thanks! This script is answering to my question and I would like to customize it further. I am using SSH to connect to the remote server and would like to ask you where should be located the postgres config file for me to make some change on the size of the query limitation?

Best Regards.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Are you limiting the data to a single data set at a time? You might try to limit the result set by putting “limit 100” at the end of the query. That way you can at least see if the query is working. There are often limits set on the postgres server for the number of records that can be returned, especially on cloud servers. You might need to change a parameter in the config.

Randy

On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy and Knut for the scripts. While I am running the sample script and customizing with the dataset name, I am facing another issue with a timed out connection due to a busy server. I cannot output any result yet. Do you know if it is a server memory issue?

Best Regards.

Tantely.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Here is a sample query that will give you what I think you want:


select dv.sourceid as organisationunitid,

ou.name, ou.code as facilitycode, de.name, ‘Vaccination’ as datasetname,

pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv

inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)

inner join dataelement de on (dv.dataelementid=de.dataelementid)

inner join period pe on (dv.periodid=pe.periodid)

inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)

where dv.dataelementid in

(select dataelementid from datasetmembers where datasetid in (

select datasetid from dataset where name=‘Vaccination’))


Just replace ‘Vaccination’ with the name of your dataset. You can also create a variable for the dataset name of your SQL view in the newer versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Could you provide more information about your setup?

How much RAM does your server or servers have?

What is the query you are attempting to execute?

Where is the timeout occurring (through the Web or from the database itself)?

What does your “About DHIS2” say?

Have you performed and of the suggested performance upgrades in the implementation manual?

Regards

···

On Tue, Apr 5, 2016 at 3:57 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi there.

In general, if the query is taking such a long time that it times out, then there may be something wrong with the query or the level of performance of your server. Disabling that time query time out, could result in queries which take an extremely long time to complete, leading to the server being overwhelmed. How many records does your data value table have? I would be surprised if Postgres would time out unless the size of the database is very large (many tens of millions of rows) or the server is under-resourced. Another possibility is that if you are seeing a timeout from the web (via DHIS2) then the reverse proxy may be timing out. You can (but should be careful again) to increase the time out. You can increase this with

proxy_read_timeout 600;

Also, please have a look at our section on Postgres performance tuning. I have seen very large decreases in query time by performing tweaking the default setup to suit DHIS2 better.

http://dhis2.github.io/dhis2-docs/master/en/implementer/html/ch08s03.html#d5e464

Regards,

Jason

On Mon, Apr 4, 2016 at 3:45 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy. I am going to check this postgresql.conf file to troubleshoot this issue.

Best Regards.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy rwilson@msh.org wrote:

I think the settings are in postgresql.conf.

You could check the following:

statement_timeout - set it to 0 to disable it.

I can’t seem to find the command limited the number of records a query can return. I suppose it varies with the size of the record.

Randy


Tantely Raminosoa

Data Officer
Management Sciences for Health
Antananarivo Madagascar

Mobile: 0321180040

E-mail: traminosoa@mikolo.org

Skype: traminosoa

Stronger health systems. Greater health impact.

www.msh.org

On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Hi Randy,

The SQL script is running fine after putting “limit 100” at the end of the query. Thanks! This script is answering to my question and I would like to customize it further. I am using SSH to connect to the remote server and would like to ask you where should be located the postgres config file for me to make some change on the size of the query limitation?

Best Regards.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Are you limiting the data to a single data set at a time? You might try to limit the result set by putting “limit 100” at the end of the query. That way you can at least see if the query is working. There are often limits set on the postgres server for the number of records that can be returned, especially on cloud servers. You might need to change a parameter in the config.

Randy

On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy and Knut for the scripts. While I am running the sample script and customizing with the dataset name, I am facing another issue with a timed out connection due to a busy server. I cannot output any result yet. Do you know if it is a server memory issue?

Best Regards.

Tantely.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Here is a sample query that will give you what I think you want:


select dv.sourceid as organisationunitid,

ou.name, ou.code as facilitycode, de.name, ‘Vaccination’ as datasetname,

pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv

inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)

inner join dataelement de on (dv.dataelementid=de.dataelementid)

inner join period pe on (dv.periodid=pe.periodid)

inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)

where dv.dataelementid in

(select dataelementid from datasetmembers where datasetid in (

select datasetid from dataset where name=‘Vaccination’))


Just replace ‘Vaccination’ with the name of your dataset. You can also create a variable for the dataset name of your SQL view in the newer versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Hi Jason,

The DHIS2 2.19 is installed on a Linux OS running inside an Amazon web services. The server has in total 30GB RAM. The amount of memory allocated to JVM is now 2GB. The query I am trying to execute is a simple SQL select which intend to pull out the data values for a specified period of one data set. The timeout is occurring through the web, as I can access to the DHIS2 instance after killing the browser process. SQL view is giving the output if the SQL script is limited to show only 1000 records for example.

Regards

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

···

On Tue, Apr 5, 2016 at 9:23 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Could you provide more information about your setup?

How much RAM does your server or servers have?

What is the query you are attempting to execute?

Where is the timeout occurring (through the Web or from the database itself)?

What does your “About DHIS2” say?

Have you performed and of the suggested performance upgrades in the implementation manual?

Regards

On Tue, Apr 5, 2016, 11:02 Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Hi Jason,

Thnak you for you message. The data value table has about 1,100,000 records. In addition to checking the postgreSQL configuration file, this time-out might also be related to the server configuration. Could you please provide a link for the JVM/Tomcat performance tuning according to the amount of server RAM?

Best Regards,

Tantely.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

On Tue, Apr 5, 2016 at 3:57 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi there.

In general, if the query is taking such a long time that it times out, then there may be something wrong with the query or the level of performance of your server. Disabling that time query time out, could result in queries which take an extremely long time to complete, leading to the server being overwhelmed. How many records does your data value table have? I would be surprised if Postgres would time out unless the size of the database is very large (many tens of millions of rows) or the server is under-resourced. Another possibility is that if you are seeing a timeout from the web (via DHIS2) then the reverse proxy may be timing out. You can (but should be careful again) to increase the time out. You can increase this with

proxy_read_timeout 600;

Also, please have a look at our section on Postgres performance tuning. I have seen very large decreases in query time by performing tweaking the default setup to suit DHIS2 better.

http://dhis2.github.io/dhis2-docs/master/en/implementer/html/ch08s03.html#d5e464

Regards,

Jason

On Mon, Apr 4, 2016 at 3:45 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy. I am going to check this postgresql.conf file to troubleshoot this issue.

Best Regards.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy rwilson@msh.org wrote:

I think the settings are in postgresql.conf.

You could check the following:

statement_timeout - set it to 0 to disable it.

I can’t seem to find the command limited the number of records a query can return. I suppose it varies with the size of the record.

Randy


Tantely Raminosoa

Data Officer
Management Sciences for Health
Antananarivo Madagascar

Mobile: 0321180040

E-mail: traminosoa@mikolo.org

Skype: traminosoa

Stronger health systems. Greater health impact.

www.msh.org

On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Hi Randy,

The SQL script is running fine after putting “limit 100” at the end of the query. Thanks! This script is answering to my question and I would like to customize it further. I am using SSH to connect to the remote server and would like to ask you where should be located the postgres config file for me to make some change on the size of the query limitation?

Best Regards.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Are you limiting the data to a single data set at a time? You might try to limit the result set by putting “limit 100” at the end of the query. That way you can at least see if the query is working. There are often limits set on the postgres server for the number of records that can be returned, especially on cloud servers. You might need to change a parameter in the config.

Randy

On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Thank you Randy and Knut for the scripts. While I am running the sample script and customizing with the dataset name, I am facing another issue with a timed out connection due to a busy server. I cannot output any result yet. Do you know if it is a server memory issue?

Best Regards.

Tantely.


Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org wrote:

Hi Tantely,

Here is a sample query that will give you what I think you want:


select dv.sourceid as organisationunitid,

ou.name, ou.code as facilitycode, de.name, ‘Vaccination’ as datasetname,

pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv

inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)

inner join dataelement de on (dv.dataelementid=de.dataelementid)

inner join period pe on (dv.periodid=pe.periodid)

inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid)

where dv.dataelementid in

(select dataelementid from datasetmembers where datasetid in (

select datasetid from dataset where name=‘Vaccination’))


Just replace ‘Vaccination’ with the name of your dataset. You can also create a variable for the dataset name of your SQL view in the newer versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring knutst@gmail.com wrote:

Hi Tantely,

You are probably looking for the datasetmembers table and the datasetsource table (confusingly and historically, orgunits used to be referred to as sources, and this is still reflected in a few places).

When you say data elements, I assume you mean data values? If so, then something along the lines of the query below (though this didnt work for me right now

SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Randy Wilson
*Team Leader: *Knowledge Management, Data Use and Research

Rwanda Health System Strengthening Activity

Management Sciences for Health

Rwanda-Kigali

Direct: +250 788308835

E-mail: rwilson@msh.org

Skype: wilsonrandy_us

Stronger health systems. Greater health impact.

www.msh.org

On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear DHIS2 users,

I would like to export all data elements related to all organization units linked to a data set (questionnaire) on a specific period through the SQL View. Does anyone have some idea of on which table (/tables) should I make the SQL select script in DHIS2? The reason is that I have already exported the data through the “data export” (csv) but it returned me data values along columns and I need to transpose in Excel each Organization Unit to have data values aligned by row.

Thank you for your suggestions.

Best Regards,

Tantely

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Tantely Raminosoa

Data Officer
Management Sciences for Health
Antananarivo Madagascar

Mobile: 0321180040

E-mail: traminosoa@mikolo.org

Skype: traminosoa

Stronger health systems. Greater health impact.

www.msh.org