(no subject)

jason · 5 November 2014 10:00

Hi Devs,
I am trying to get some data from the analytics API, but running into some conceptual challenges I think. Here is the basic request

http://localhost:8080/dhis/api/analytics.xml?dimension=dx:EpkTS0PpUcd;ec2OE0NY0kp;YUQ5C7adVF4;JvQI5pknk5g;crfET066vWt;kOtCKrGhIgc;apH1D8RRoRW;hGYnVYquaaq;dS4aPkM4oPe;r4QXthzMIKe;cppU5xf9uaN;Ua5k0OjE08z;mytGJmQ1w9S;dZFsIFFpncs;e9ngG0Hydka;LamweRCLRCW;iFJ7wFgfrJB;nvWsTc2cwh8;RjZzPr2swK8;NrT7XMFRdqE;ePiFDUQ6buf;oq5ZbscH3wz;UXafjnLhfYt;jL5ybWARRzf;mcplaq7cMwz;zs6XMzE1OTv;LyCsXbryff3;XZ27rp4Q4i0;cfvFxYyATtu;F4srgCgdqRH;sYClaae1ZdS;S5fakEDQKLJ;ZadGDLyyu28;Onb0wiYGAit;a9ffeImRMo7;Ykkqv58aEfM;ilvdUOXLdsR;b3Ahd82CwKH;CGPjVxvQSVq;MqGmwTnyi7H;keuAhg8E1eK;AstyowWpwhp;w9x1oWNmve9;WhF6siECUP7;XQEuWn7ZFDE;KkYiGM7Exxr;WXMorZ4yKsi;UkFJsUrmmLZ;LM2SXCSqGoj;G5vOWCzuoz9;MTC3rfRrQnq;C6ajbjMlDA3;KGZR00ccKaz;J0CGjGCwW88;PbnCJTUFzIJ;QfksRSvzbBD;lGrPN3URJDL;Kac6byVa1Fk;FVY88gSfco1&dimension=pe:201407&filter=ou:LEVEL-5;s5DPBsdoE8b

So, basically, we have about 60 data elements, a single time period (201407), for all level 5 orgunits (about 40,000 of them). None of the orgunits at level 5 (except for 1) actually has any data. So, what comes back from the analytics, when it works, is a huge map of orgunits, with of course no data.

The SQL (not produced by DHIS2) which gives me what I want (SELECT de,uidlevel5,monthly,value from analytics_2014\n where de in (‘EpkTS0PpUcd’,‘ec2OE0NY0kp’,‘YUQ5C7adVF4’,‘JvQI5pknk5g’,‘crfET066vWt’,‘kOtCKrGhIgc’,‘apH1D8RRoRW’,‘hGYnVYquaaq’,‘dS4aPkM4oPe’,‘r4QXthzMIKe’,‘cppU5xf9uaN’,‘Ua5k0OjE08z’,‘mytGJmQ1w9S’,‘dZFsIFFpncs’,‘e9ngG0Hydka’,‘LamweRCLRCW’,‘iFJ7wFgfrJB’,‘nvWsTc2cwh8’,‘RjZzPr2swK8’,‘NrT7XMFRdqE’,‘ePiFDUQ6buf’,‘oq5ZbscH3wz’,‘UXafjnLhfYt’,‘jL5ybWARRzf’,‘mcplaq7cMwz’,‘zs6XMzE1OTv’,‘LyCsXbryff3’,‘XZ27rp4Q4i0’,‘cfvFxYyATtu’,‘F4srgCgdqRH’,‘sYClaae1ZdS’,‘S5fakEDQKLJ’,‘ZadGDLyyu28’,‘Onb0wiYGAit’,‘a9ffeImRMo7’,‘Ykkqv58aEfM’,‘ilvdUOXLdsR’,‘b3Ahd82CwKH’,‘CGPjVxvQSVq’,‘MqGmwTnyi7H’,‘keuAhg8E1eK’,‘AstyowWpwhp’,‘w9x1oWNmve9’,‘WhF6siECUP7’,‘XQEuWn7ZFDE’,‘KkYiGM7Exxr’,‘WXMorZ4yKsi’,‘UkFJsUrmmLZ’,‘LM2SXCSqGoj’,‘G5vOWCzuoz9’,‘MTC3rfRrQnq’,‘C6ajbjMlDA3’,‘KGZR00ccKaz’,‘J0CGjGCwW88’,‘PbnCJTUFzIJ’,‘QfksRSvzbBD’,‘lGrPN3URJDL’,‘Kac6byVa1Fk’,‘FVY88gSfco1’) and monthly = ‘201407’

returns of course data for only orguntis which actually have data.

Is there a similar method for the analytics, to return only data for where it exists, but not to return the entire list of orgunits at level 5?

Regards,

Jason

···

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

Lars · 6 November 2014 09:06

Hi Jason,

I checked the docs, it seems you can do this:

?skipMeta=true

Lars

···

On Nov 5, 2014 11:00 AM, “Jason Pickering” jason.p.pickering@gmail.com wrote:

Hi Devs,
I am trying to get some data from the analytics API, but running into some conceptual challenges I think. Here is the basic request

http://localhost:8080/dhis/api/analytics.xml?dimension=dx:EpkTS0PpUcd;ec2OE0NY0kp;YUQ5C7adVF4;JvQI5pknk5g;crfET066vWt;kOtCKrGhIgc;apH1D8RRoRW;hGYnVYquaaq;dS4aPkM4oPe;r4QXthzMIKe;cppU5xf9uaN;Ua5k0OjE08z;mytGJmQ1w9S;dZFsIFFpncs;e9ngG0Hydka;LamweRCLRCW;iFJ7wFgfrJB;nvWsTc2cwh8;RjZzPr2swK8;NrT7XMFRdqE;ePiFDUQ6buf;oq5ZbscH3wz;UXafjnLhfYt;jL5ybWARRzf;mcplaq7cMwz;zs6XMzE1OTv;LyCsXbryff3;XZ27rp4Q4i0;cfvFxYyATtu;F4srgCgdqRH;sYClaae1ZdS;S5fakEDQKLJ;ZadGDLyyu28;Onb0wiYGAit;a9ffeImRMo7;Ykkqv58aEfM;ilvdUOXLdsR;b3Ahd82CwKH;CGPjVxvQSVq;MqGmwTnyi7H;keuAhg8E1eK;AstyowWpwhp;w9x1oWNmve9;WhF6siECUP7;XQEuWn7ZFDE;KkYiGM7Exxr;WXMorZ4yKsi;UkFJsUrmmLZ;LM2SXCSqGoj;G5vOWCzuoz9;MTC3rfRrQnq;C6ajbjMlDA3;KGZR00ccKaz;J0CGjGCwW88;PbnCJTUFzIJ;QfksRSvzbBD;lGrPN3URJDL;Kac6byVa1Fk;FVY88gSfco1&dimension=pe:201407&filter=ou:LEVEL-5;s5DPBsdoE8b

So, basically, we have about 60 data elements, a single time period (201407), for all level 5 orgunits (about 40,000 of them). None of the orgunits at level 5 (except for 1) actually has any data. So, what comes back from the analytics, when it works, is a huge map of orgunits, with of course no data.

The SQL (not produced by DHIS2) which gives me what I want (SELECT de,uidlevel5,monthly,value from analytics_2014\n where de in (‘EpkTS0PpUcd’,‘ec2OE0NY0kp’,‘YUQ5C7adVF4’,‘JvQI5pknk5g’,‘crfET066vWt’,‘kOtCKrGhIgc’,‘apH1D8RRoRW’,‘hGYnVYquaaq’,‘dS4aPkM4oPe’,‘r4QXthzMIKe’,‘cppU5xf9uaN’,‘Ua5k0OjE08z’,‘mytGJmQ1w9S’,‘dZFsIFFpncs’,‘e9ngG0Hydka’,‘LamweRCLRCW’,‘iFJ7wFgfrJB’,‘nvWsTc2cwh8’,‘RjZzPr2swK8’,‘NrT7XMFRdqE’,‘ePiFDUQ6buf’,‘oq5ZbscH3wz’,‘UXafjnLhfYt’,‘jL5ybWARRzf’,‘mcplaq7cMwz’,‘zs6XMzE1OTv’,‘LyCsXbryff3’,‘XZ27rp4Q4i0’,‘cfvFxYyATtu’,‘F4srgCgdqRH’,‘sYClaae1ZdS’,‘S5fakEDQKLJ’,‘ZadGDLyyu28’,‘Onb0wiYGAit’,‘a9ffeImRMo7’,‘Ykkqv58aEfM’,‘ilvdUOXLdsR’,‘b3Ahd82CwKH’,‘CGPjVxvQSVq’,‘MqGmwTnyi7H’,‘keuAhg8E1eK’,‘AstyowWpwhp’,‘w9x1oWNmve9’,‘WhF6siECUP7’,‘XQEuWn7ZFDE’,‘KkYiGM7Exxr’,‘WXMorZ4yKsi’,‘UkFJsUrmmLZ’,‘LM2SXCSqGoj’,‘G5vOWCzuoz9’,‘MTC3rfRrQnq’,‘C6ajbjMlDA3’,‘KGZR00ccKaz’,‘J0CGjGCwW88’,‘PbnCJTUFzIJ’,‘QfksRSvzbBD’,‘lGrPN3URJDL’,‘Kac6byVa1Fk’,‘FVY88gSfco1’) and monthly = ‘201407’

returns of course data for only orguntis which actually have data.

Is there a similar method for the analytics, to return only data for where it exists, but not to return the entire list of orgunits at level 5?

Regards,

Jason

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

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

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

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

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

jason · 6 November 2014 09:10

Yeah, I tried that as well. (I read the docs actually after this post! )

I get some results, but compared to the amount of time which the query takes, it is REALLY slow, like milliseconds versus minutes.

Regards,

Jason

···

On Thu, Nov 6, 2014 at 10:06 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Jason,

I checked the docs, it seems you can do this:

?skipMeta=true

Lars

On Nov 5, 2014 11:00 AM, “Jason Pickering” jason.p.pickering@gmail.com wrote:

Hi Devs,
I am trying to get some data from the analytics API, but running into some conceptual challenges I think. Here is the basic request

http://localhost:8080/dhis/api/analytics.xml?dimension=dx:EpkTS0PpUcd;ec2OE0NY0kp;YUQ5C7adVF4;JvQI5pknk5g;crfET066vWt;kOtCKrGhIgc;apH1D8RRoRW;hGYnVYquaaq;dS4aPkM4oPe;r4QXthzMIKe;cppU5xf9uaN;Ua5k0OjE08z;mytGJmQ1w9S;dZFsIFFpncs;e9ngG0Hydka;LamweRCLRCW;iFJ7wFgfrJB;nvWsTc2cwh8;RjZzPr2swK8;NrT7XMFRdqE;ePiFDUQ6buf;oq5ZbscH3wz;UXafjnLhfYt;jL5ybWARRzf;mcplaq7cMwz;zs6XMzE1OTv;LyCsXbryff3;XZ27rp4Q4i0;cfvFxYyATtu;F4srgCgdqRH;sYClaae1ZdS;S5fakEDQKLJ;ZadGDLyyu28;Onb0wiYGAit;a9ffeImRMo7;Ykkqv58aEfM;ilvdUOXLdsR;b3Ahd82CwKH;CGPjVxvQSVq;MqGmwTnyi7H;keuAhg8E1eK;AstyowWpwhp;w9x1oWNmve9;WhF6siECUP7;XQEuWn7ZFDE;KkYiGM7Exxr;WXMorZ4yKsi;UkFJsUrmmLZ;LM2SXCSqGoj;G5vOWCzuoz9;MTC3rfRrQnq;C6ajbjMlDA3;KGZR00ccKaz;J0CGjGCwW88;PbnCJTUFzIJ;QfksRSvzbBD;lGrPN3URJDL;Kac6byVa1Fk;FVY88gSfco1&dimension=pe:201407&filter=ou:LEVEL-5;s5DPBsdoE8b

So, basically, we have about 60 data elements, a single time period (201407), for all level 5 orgunits (about 40,000 of them). None of the orgunits at level 5 (except for 1) actually has any data. So, what comes back from the analytics, when it works, is a huge map of orgunits, with of course no data.

The SQL (not produced by DHIS2) which gives me what I want (SELECT de,uidlevel5,monthly,value from analytics_2014\n where de in (‘EpkTS0PpUcd’,‘ec2OE0NY0kp’,‘YUQ5C7adVF4’,‘JvQI5pknk5g’,‘crfET066vWt’,‘kOtCKrGhIgc’,‘apH1D8RRoRW’,‘hGYnVYquaaq’,‘dS4aPkM4oPe’,‘r4QXthzMIKe’,‘cppU5xf9uaN’,‘Ua5k0OjE08z’,‘mytGJmQ1w9S’,‘dZFsIFFpncs’,‘e9ngG0Hydka’,‘LamweRCLRCW’,‘iFJ7wFgfrJB’,‘nvWsTc2cwh8’,‘RjZzPr2swK8’,‘NrT7XMFRdqE’,‘ePiFDUQ6buf’,‘oq5ZbscH3wz’,‘UXafjnLhfYt’,‘jL5ybWARRzf’,‘mcplaq7cMwz’,‘zs6XMzE1OTv’,‘LyCsXbryff3’,‘XZ27rp4Q4i0’,‘cfvFxYyATtu’,‘F4srgCgdqRH’,‘sYClaae1ZdS’,‘S5fakEDQKLJ’,‘ZadGDLyyu28’,‘Onb0wiYGAit’,‘a9ffeImRMo7’,‘Ykkqv58aEfM’,‘ilvdUOXLdsR’,‘b3Ahd82CwKH’,‘CGPjVxvQSVq’,‘MqGmwTnyi7H’,‘keuAhg8E1eK’,‘AstyowWpwhp’,‘w9x1oWNmve9’,‘WhF6siECUP7’,‘XQEuWn7ZFDE’,‘KkYiGM7Exxr’,‘WXMorZ4yKsi’,‘UkFJsUrmmLZ’,‘LM2SXCSqGoj’,‘G5vOWCzuoz9’,‘MTC3rfRrQnq’,‘C6ajbjMlDA3’,‘KGZR00ccKaz’,‘J0CGjGCwW88’,‘PbnCJTUFzIJ’,‘QfksRSvzbBD’,‘lGrPN3URJDL’,‘Kac6byVa1Fk’,‘FVY88gSfco1’) and monthly = ‘201407’

returns of course data for only orguntis which actually have data.

Is there a similar method for the analytics, to return only data for where it exists, but not to return the entire list of orgunits at level 5?

Regards,

Jason

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

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

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

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

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

–

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

Lars · 6 November 2014 09:15

Yup I know. There is a weakness in the query planner here in the sense that it uses an IN (uids…) criteria rather than joining on _orgunitstructure level. This is something we need to improve as dhis org unit hierarchies grow bigger.

Lars

···

On Thu, Nov 6, 2014 at 10:06 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Jason,

I checked the docs, it seems you can do this:

?skipMeta=true

Lars

On Nov 5, 2014 11:00 AM, “Jason Pickering” jason.p.pickering@gmail.com wrote:

Hi Devs,
I am trying to get some data from the analytics API, but running into some conceptual challenges I think. Here is the basic request

http://localhost:8080/dhis/api/analytics.xml?dimension=dx:EpkTS0PpUcd;ec2OE0NY0kp;YUQ5C7adVF4;JvQI5pknk5g;crfET066vWt;kOtCKrGhIgc;apH1D8RRoRW;hGYnVYquaaq;dS4aPkM4oPe;r4QXthzMIKe;cppU5xf9uaN;Ua5k0OjE08z;mytGJmQ1w9S;dZFsIFFpncs;e9ngG0Hydka;LamweRCLRCW;iFJ7wFgfrJB;nvWsTc2cwh8;RjZzPr2swK8;NrT7XMFRdqE;ePiFDUQ6buf;oq5ZbscH3wz;UXafjnLhfYt;jL5ybWARRzf;mcplaq7cMwz;zs6XMzE1OTv;LyCsXbryff3;XZ27rp4Q4i0;cfvFxYyATtu;F4srgCgdqRH;sYClaae1ZdS;S5fakEDQKLJ;ZadGDLyyu28;Onb0wiYGAit;a9ffeImRMo7;Ykkqv58aEfM;ilvdUOXLdsR;b3Ahd82CwKH;CGPjVxvQSVq;MqGmwTnyi7H;keuAhg8E1eK;AstyowWpwhp;w9x1oWNmve9;WhF6siECUP7;XQEuWn7ZFDE;KkYiGM7Exxr;WXMorZ4yKsi;UkFJsUrmmLZ;LM2SXCSqGoj;G5vOWCzuoz9;MTC3rfRrQnq;C6ajbjMlDA3;KGZR00ccKaz;J0CGjGCwW88;PbnCJTUFzIJ;QfksRSvzbBD;lGrPN3URJDL;Kac6byVa1Fk;FVY88gSfco1&dimension=pe:201407&filter=ou:LEVEL-5;s5DPBsdoE8b

So, basically, we have about 60 data elements, a single time period (201407), for all level 5 orgunits (about 40,000 of them). None of the orgunits at level 5 (except for 1) actually has any data. So, what comes back from the analytics, when it works, is a huge map of orgunits, with of course no data.

The SQL (not produced by DHIS2) which gives me what I want (SELECT de,uidlevel5,monthly,value from analytics_2014\n where de in (‘EpkTS0PpUcd’,‘ec2OE0NY0kp’,‘YUQ5C7adVF4’,‘JvQI5pknk5g’,‘crfET066vWt’,‘kOtCKrGhIgc’,‘apH1D8RRoRW’,‘hGYnVYquaaq’,‘dS4aPkM4oPe’,‘r4QXthzMIKe’,‘cppU5xf9uaN’,‘Ua5k0OjE08z’,‘mytGJmQ1w9S’,‘dZFsIFFpncs’,‘e9ngG0Hydka’,‘LamweRCLRCW’,‘iFJ7wFgfrJB’,‘nvWsTc2cwh8’,‘RjZzPr2swK8’,‘NrT7XMFRdqE’,‘ePiFDUQ6buf’,‘oq5ZbscH3wz’,‘UXafjnLhfYt’,‘jL5ybWARRzf’,‘mcplaq7cMwz’,‘zs6XMzE1OTv’,‘LyCsXbryff3’,‘XZ27rp4Q4i0’,‘cfvFxYyATtu’,‘F4srgCgdqRH’,‘sYClaae1ZdS’,‘S5fakEDQKLJ’,‘ZadGDLyyu28’,‘Onb0wiYGAit’,‘a9ffeImRMo7’,‘Ykkqv58aEfM’,‘ilvdUOXLdsR’,‘b3Ahd82CwKH’,‘CGPjVxvQSVq’,‘MqGmwTnyi7H’,‘keuAhg8E1eK’,‘AstyowWpwhp’,‘w9x1oWNmve9’,‘WhF6siECUP7’,‘XQEuWn7ZFDE’,‘KkYiGM7Exxr’,‘WXMorZ4yKsi’,‘UkFJsUrmmLZ’,‘LM2SXCSqGoj’,‘G5vOWCzuoz9’,‘MTC3rfRrQnq’,‘C6ajbjMlDA3’,‘KGZR00ccKaz’,‘J0CGjGCwW88’,‘PbnCJTUFzIJ’,‘QfksRSvzbBD’,‘lGrPN3URJDL’,‘Kac6byVa1Fk’,‘FVY88gSfco1’) and monthly = ‘201407’

returns of course data for only orguntis which actually have data.

Is there a similar method for the analytics, to return only data for where it exists, but not to return the entire list of orgunits at level 5?

Regards,

Jason

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

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

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

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

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

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