# Exporting data through SQL View

**URL:** <https://community.dhis2.org/t/exporting-data-through-sql-view/3377>\
**Category:** Support - Assistance technique\
**Created:** [1 April 2016 07:29 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377 "2016-04-01T07:29:39Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Raminosoa\_Rabemanant](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/r/ed8c4c/32.png) [@Raminosoa\_Rabemanant](https://community.dhis2.org/u/Raminosoa_Rabemanant)\
**Post date:** [1 April 2016 07:29 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/1 "2016-04-01T07:29:39Z")

</div>

_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

---

<div class="post-metadata">

**Author:** ![Knut\_Staring](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/knut_staring/32/32549_2.png) [@Knut\_Staring](https://community.dhis2.org/u/Knut_Staring)\
**Post date:** [1 April 2016 20:40 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/2 "2016-04-01T20:40:08Z")

</div>

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](http://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](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> –
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](http://dhis2.org)

---

<div class="post-metadata">

**Author:** ![Randy\_Wilson](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/randy_wilson/32/154_2.png) [@Randy\_Wilson](https://community.dhis2.org/u/Randy_Wilson)\
**Post date:** [2 April 2016 05:39 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/3 "2016-04-02T05:39:40Z")

</div>

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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> –
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)

---

<div class="post-metadata">

**Author:** ![Raminosoa\_Rabemanant](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/r/ed8c4c/32.png) [@Raminosoa\_Rabemanant](https://community.dhis2.org/u/Raminosoa_Rabemanant)\
**Post date:** [4 April 2016 03:04 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/4 "2016-04-04T03:04:02Z")

</div>

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](mailto: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](http://ou.name), ou.code as facilitycode, [de.name](http://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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](http://dhis2.org)
> 
> –

---

<div class="post-metadata">

**Author:** ![Randy\_Wilson](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/randy_wilson/32/154_2.png) [@Randy\_Wilson](https://community.dhis2.org/u/Randy_Wilson)\
**Post date:** [4 April 2016 12:32 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/5 "2016-04-04T12:32:40Z")

</div>

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](mailto: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](mailto: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](http://ou.name), ou.code as facilitycode, [de.name](http://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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)

---

<div class="post-metadata">

**Author:** ![Raminosoa\_Rabemanant](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/r/ed8c4c/32.png) [@Raminosoa\_Rabemanant](https://community.dhis2.org/u/Raminosoa_Rabemanant)\
**Post date:** [4 April 2016 13:10 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/6 "2016-04-04T13:10:30Z")

</div>

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](mailto: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](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy [rwilson@msh.org](mailto: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](http://ou.name), ou.code as facilitycode, [de.name](http://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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](http://dhis2.org)
> 
> –

---

<div class="post-metadata">

**Author:** ![Randy\_Wilson](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/randy_wilson/32/154_2.png) [@Randy\_Wilson](https://community.dhis2.org/u/Randy_Wilson)\
**Post date:** [4 April 2016 13:42 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/7 "2016-04-04T13:42:07Z")

</div>

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](mailto: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](mailto: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](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy [rwilson@msh.org](mailto: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](http://ou.name), ou.code as facilitycode, [de.name](http://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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)

---

<div class="post-metadata">

**Author:** ![Raminosoa\_Rabemanant](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/r/ed8c4c/32.png) [@Raminosoa\_Rabemanant](https://community.dhis2.org/u/Raminosoa_Rabemanant)\
**Post date:** [4 April 2016 13:45 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/8 "2016-04-04T13:45:26Z")

</div>

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](mailto: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](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy [rwilson@msh.org](mailto: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](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy [rwilson@msh.org](mailto: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](http://ou.name), ou.code as facilitycode, [de.name](http://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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](http://dhis2.org)
> 
> –
> 
> **Tantely Raminosoa**
> 
> _Data Officer_  
> Management Sciences for Health  
> Antananarivo Madagascar
> 
> Mobile: 0321180040
> 
> E-mail: [traminosoa@mikolo.org](mailto:traminosoa@mikolo.org)
> 
> Skype: traminosoa
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)

---

<div class="post-metadata">

**Author:** ![jason](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/jason/32/32739_2.png) [@jason](https://community.dhis2.org/u/jason)\
**Post date:** [5 April 2016 00:57 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/9 "2016-04-05T00:57:31Z")

</div>

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

```auto
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](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](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> –
> 
> On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy [rwilson@msh.org](mailto: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](mailto:traminosoa@mikolo.org)
> 
> Skype: traminosoa
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy [rwilson@msh.org](mailto: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](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy [rwilson@msh.org](mailto: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](http://ou.name), ou.code as facilitycode, [de.name](http://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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](http://dhis2.org)
> 
> –
> 
> Jason P. Pickering  
> email: [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)  
> tel:+46764147049

---

<div class="post-metadata">

**Author:** ![Raminosoa\_Rabemanant](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/r/ed8c4c/32.png) [@Raminosoa\_Rabemanant](https://community.dhis2.org/u/Raminosoa_Rabemanant)\
**Post date:** [5 April 2016 02:02 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/10 "2016-04-05T02:02:45Z")

</div>

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](mailto: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
> 
> > 
> 
> > ```auto
> > 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](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](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> –
> 
> Jason P. Pickering  
> email: [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)  
> tel:+46764147049
> 
> On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy [rwilson@msh.org](mailto: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](mailto:traminosoa@mikolo.org)
> 
> Skype: traminosoa
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy [rwilson@msh.org](mailto: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](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy [rwilson@msh.org](mailto: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](http://ou.name), ou.code as facilitycode, [de.name](http://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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](http://dhis2.org)
> 
> –

---

<div class="post-metadata">

**Author:** ![jason](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/jason/32/32739_2.png) [@jason](https://community.dhis2.org/u/jason)\
**Post date:** [5 April 2016 06:23 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/11 "2016-04-05T06:23:11Z")

</div>

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](mailto: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
> 
> > 
> 
> > ```auto
> > 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](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](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> –
> 
> Jason P. Pickering  
> email: [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)  
> tel:+46764147049
> 
> On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy [rwilson@msh.org](mailto: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](mailto:traminosoa@mikolo.org)
> 
> Skype: traminosoa
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy [rwilson@msh.org](mailto: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](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy [rwilson@msh.org](mailto: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](http://ou.name), ou.code as facilitycode, [de.name](http://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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](http://dhis2.org)
> 
> –

---

<div class="post-metadata">

**Author:** ![Raminosoa\_Rabemanant](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/r/ed8c4c/32.png) [@Raminosoa\_Rabemanant](https://community.dhis2.org/u/Raminosoa_Rabemanant)\
**Post date:** [14 April 2016 08:24 UTC](https://community.dhis2.org/t/exporting-data-through-sql-view/3377/12 "2016-04-14T08:24:40Z")

</div>

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](mailto: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](mailto: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](mailto: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
> 
> > 
> 
> > ```auto
> > 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](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](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> –
> 
> Jason P. Pickering  
> email: [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)  
> tel:+46764147049
> 
> On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy [rwilson@msh.org](mailto: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](mailto:traminosoa@mikolo.org)
> 
> Skype: traminosoa
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy [rwilson@msh.org](mailto: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](mailto: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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy [rwilson@msh.org](mailto: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](http://ou.name), ou.code as facilitycode, [de.name](http://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](mailto: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](http://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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](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](mailto:rwilson@msh.org)
> 
> Skype: wilsonrandy\_us
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)
> 
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely [traminosoa@mikolo.org](mailto: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](https://launchpad.net/~dhis2-users)
> > 
> > Post to : [dhis2-users@lists.launchpad.net](mailto:dhis2-users@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-users](https://launchpad.net/~dhis2-users)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> Knut Staring
> 
> Dept. of Informatics, University of Oslo
> 
> Norway: +4791880522
> 
> Skype: knutstar
> 
> [http://dhis2.org](http://dhis2.org)
> 
> –
> 
> **Tantely Raminosoa**
> 
> _Data Officer_  
> Management Sciences for Health  
> Antananarivo Madagascar
> 
> Mobile: 0321180040
> 
> E-mail: [traminosoa@mikolo.org](mailto:traminosoa@mikolo.org)
> 
> Skype: traminosoa
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/1/b1ca6bf754b4b6682eee8aa873c02bb0f30a3993.jpeg)](http://www.msh.org/)
> 
> Stronger health systems. Greater health impact.
> 
> [![](https://dhis2.b-cdn.net/uploads/default/original/3X/e/b/eb07b38419b0df31a5d64ed154a6e6d64d57c884.jpeg)](https://www.facebook.com/ManagementSciencesForHealth) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/2/f/2f9be196db44bdf662321608b9ee89d2d3323652.jpeg)](https://twitter.com/MSHHealthImpact) [![](https://dhis2.b-cdn.net/uploads/default/original/3X/b/3/b311388b00d976b7d685368c7fc76c70a2bcbae1.jpeg)](https://www.youtube.com/user/MSHHealthImpact)
> 
> [www.msh.org](http://www.msh.org/)