# Aggregating values

**URL:** <https://community.dhis2.org/t/aggregating-values/8450>\
**Category:** Support - Assistance technique\
**Created:** [11 July 2010 14:27 UTC](https://community.dhis2.org/t/aggregating-values/8450 "2010-07-11T14:27:50Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [11 July 2010 14:27 UTC](https://community.dhis2.org/t/aggregating-values/8450/1 "2010-07-11T14:27:50Z")

</div>

I have a dataelement collected at a Health Centre level (eg: ats\_posfem) in one province and only in some particular districts. In unpredictible future at National level we will be collecting on every health centre.

Now, my ignorance: How can I obtain the provincial total on Report Table? I have been battled with DHIS so far and nothing.

Thanks guys  
Caveman

---

<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:** [11 July 2010 15:03 UTC](https://community.dhis2.org/t/aggregating-values/8450/2 "2010-07-11T15:03:20Z")

</div>

A very common mistake is not having generated the resource tables,  
namely "orgunitstructure". Consult the documentation for more details  
on how to generate these tables.

Regards,  
Jason

> **···**
>
> On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto \<orvaquim@gmail.com\> wrote:
> 
> > I have a dataelement collected at a Health Centre level (eg: ats\_posfem) in  
> > one province and only in some particular districts. In unpredictible future  
> > at National level we will be collecting on every health centre.
> > 
> > Now, my ignorance: How can I obtain the provincial total on Report Table? I  
> > have been battled with DHIS so far and nothing.
> > 
> > Thanks guys  
> > Caveman
> > 
> > \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > Post to : dhis2-devs@lists.launchpad.net  
> > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> --  
> --  
> Jason P. Pickering  
> email: jason.p.pickering@gmail.com  
> tel:+260968395190

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [11 July 2010 15:36 UTC](https://community.dhis2.org/t/aggregating-values/8450/3 "2010-07-11T15:36:50Z")

</div>

Thanks!!!

Caveman

> **···**
>
> On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> 
> > A very common mistake is not having generated the resource tables,
> > 
> > namely “orgunitstructure”. Consult the documentation for more details
> > 
> > on how to generate these tables.
> > 
> > Regards,
> > 
> > Jason
> 
> > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> > 
> > > I have a dataelement collected at a Health Centre level (eg: ats\_posfem) in
> > 
> > > one province and only in some particular districts. In unpredictible future
> > 
> > > at National level we will be collecting on every health centre.
> > 
> > > 
> > 
> > > Now, my ignorance: How can I obtain the provincial total on Report Table? I
> > 
> > > have been battled with DHIS so far and nothing.
> > 
> > > 
> > 
> > > Thanks guys
> > 
> > > Caveman
> > 
> > > 
> > 
> > >
> 
> > > * * *
> > 
> > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > 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:+260968395190

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [11 July 2010 19:28 UTC](https://community.dhis2.org/t/aggregating-values/8450/4 "2010-07-11T19:28:39Z")

</div>

I did not have success. My logs are:

> **···**
>
> ===========================================================
> 
> - ERROR 21:24:31,306 Error while executing action (ExceptionInterceptor.java [http-8081-6])  
> java.lang.RuntimeException: Failed to flush BatchHandler
> 
> ==================================================================
> 
> This seems to come again from database thing.
> 
> Caveman
> 
> On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> 
> > A very common mistake is not having generated the resource tables,
> > 
> > namely “orgunitstructure”. Consult the documentation for more details
> > 
> > on how to generate these tables.
> > 
> > Regards,
> > 
> > Jason
> 
> > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> > 
> > > I have a dataelement collected at a Health Centre level (eg: ats\_posfem) in
> > 
> > > one province and only in some particular districts. In unpredictible future
> > 
> > > at National level we will be collecting on every health centre.
> > 
> > > 
> > 
> > > Now, my ignorance: How can I obtain the provincial total on Report Table? I
> > 
> > > have been battled with DHIS so far and nothing.
> > 
> > > 
> > 
> > > Thanks guys
> > 
> > > Caveman
> > 
> > > 
> > 
> > >
> 
> > > * * *
> > 
> > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > 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:+260968395190

---

<div class="post-metadata">

**Author:** ![Lars](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/lars/32/34166_2.png) [@Lars](https://community.dhis2.org/u/Lars)\
**Post date:** [12 July 2010 08:41 UTC](https://community.dhis2.org/t/aggregating-values/8450/5 "2010-07-12T08:41:38Z")

</div>

Generating any of the resource tables don't affect the aggregation /  
report tables...

> **···**
>
> On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto \<orvaquim@gmail.com\> wrote:
> 
> > Thanks!!!
> > 
> > Caveman
> > 
> > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \<jason.p.pickering@gmail.com\> wrote:
> > 
> > > A very common mistake is not having generated the resource tables,  
> > > namely "orgunitstructure". Consult the documentation for more details  
> > > on how to generate these tables.
> > > 
> > > Regards,  
> > > Jason
> > > 
> > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto \<orvaquim@gmail.com\> \>\> wrote:  
> > > \> I have a dataelement collected at a Health Centre level (eg: ats\_posfem)  
> > > \> in  
> > > \> one province and only in some particular districts. In unpredictible  
> > > \> future  
> > > \> at National level we will be collecting on every health centre.  
> > > \>  
> > > \> Now, my ignorance: How can I obtain the provincial total on Report  
> > > \> Table? I  
> > > \> have been battled with DHIS so far and nothing.  
> > > \>  
> > > \> Thanks guys  
> > > \> Caveman  
> > > \>  
> > > \>  
> > > \> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > \> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \> Post to : dhis2-devs@lists.launchpad.net  
> > > \> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > \>  
> > > \>
> > > 
> > > --  
> > > --  
> > > Jason P. Pickering  
> > > email: jason.p.pickering@gmail.com  
> > > tel:+260968395190
> > 
> > \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > Post to : dhis2-devs@lists.launchpad.net  
> > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [12 July 2010 08:41 UTC](https://community.dhis2.org/t/aggregating-values/8450/6 "2010-07-12T08:41:39Z")

</div>

Guys I found why.

It is because generating organitstructure takes a long time. (Mytop monitored this query: INSERT INTO orgunitstructure …)

The default transaction waiting time on MySQL is 50seconds.  
I increased this innodb\_lock\_wait\_timeout to 600seconds and it runs.

Please someone can help me. I am get a sensation that the DHIS community is abandoning MySQL support. Is it that true?

Caveman

> **···**
>
> On Sun, Jul 11, 2010 at 9:28 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> 
> > I did not have success. My logs are:
> > 
> > ===========================================================
> > 
> > - ERROR 21:24:31,306 Error while executing action (ExceptionInterceptor.java [http-8081-6])  
> > java.lang.RuntimeException: Failed to flush BatchHandler
> > 
> > ==================================================================
> > 
> > This seems to come again from database thing.
> > 
> > Caveman
> 
> > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > 
> > > A very common mistake is not having generated the resource tables,
> > > 
> > > namely “orgunitstructure”. Consult the documentation for more details
> > > 
> > > on how to generate these tables.
> > > 
> > > Regards,
> > > 
> > > Jason
> 
> > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> > > 
> > > > I have a dataelement collected at a Health Centre level (eg: ats\_posfem) in
> > > 
> > > > one province and only in some particular districts. In unpredictible future
> > > 
> > > > at National level we will be collecting on every health centre.
> > > 
> > > > 
> > > 
> > > > Now, my ignorance: How can I obtain the provincial total on Report Table? I
> > > 
> > > > have been battled with DHIS so far and nothing.
> > > 
> > > > 
> > > 
> > > > Thanks guys
> > > 
> > > > Caveman
> > > 
> > > > 
> > > 
> > > >
> 
> > > > * * *
> > > 
> > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > 
> > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > > 
> > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > 
> > > > 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:+260968395190
> 
> >

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [12 July 2010 08:49 UTC](https://community.dhis2.org/t/aggregating-values/8450/7 "2010-07-12T08:49:37Z")

</div>

I need to have a repo/table that aggregates data as I asked before. I did not find it on docs.

Someone told me that can happens if a generate those resource tables. I try to generate those tables and I get stuck on that error I reported.

Good for me if I do not need to generate such tables and ask what I should do?

Caveman

> **···**
>
> 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com)
> 
> > Generating any of the resource tables don’t affect the aggregation /
> > 
> > report tables…
> 
> > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> > 
> > > Thanks!!!
> > 
> > > 
> > 
> > > Caveman
> > 
> > > 
> > 
> > > 
> > 
> > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \> \> [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > 
> > > >
> > 
> > > > A very common mistake is not having generated the resource tables,
> > 
> > > > namely “orgunitstructure”. Consult the documentation for more details
> > 
> > > > on how to generate these tables.
> > 
> > > >
> > 
> > > > Regards,
> > 
> > > > Jason
> > 
> > > >
> > 
> > > >
> > 
> > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\> wrote:
> > 
> > > > > I have a dataelement collected at a Health Centre level (eg: ats\_posfem)
> > 
> > > > > in
> > 
> > > > > one province and only in some particular districts. In unpredictible
> > 
> > > > > future
> > 
> > > > > at National level we will be collecting on every health centre.
> > 
> > > > >
> > 
> > > > > Now, my ignorance: How can I obtain the provincial total on Report
> > 
> > > > > Table? I
> > 
> > > > > have been battled with DHIS so far and nothing.
> > 
> > > > >
> > 
> > > > > Thanks guys
> > 
> > > > > Caveman
> > 
> > > > >
> > 
> > > > >
> > 
> > > > > * * *
> > 
> > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > 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:+260968395190
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > * * *
> > 
> > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> > 
> > > 
> > 
> > >

---

<div class="post-metadata">

**Author:** ![Lars](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/lars/32/34166_2.png) [@Lars](https://community.dhis2.org/u/Lars)\
**Post date:** [12 July 2010 09:11 UTC](https://community.dhis2.org/t/aggregating-values/8450/8 "2010-07-12T09:11:59Z")

</div>

Hi Caveman, we are definitely not dropping mysql support, its just  
that most of the devs are using postgres so a function not being  
mysql-compliant might sneak in from time to time. I tested most of the  
application on mysql recently without trouble.

A common problem with aggregation earlier was the way the orgunit  
hierarchy was represented and stored, this is changed in 2.0.5  
snapshot and is way less error-prone now. So please build yourself a  
war-file from trunk and test with that.

If that doesn't work please send me a database dump and I will have a  
look. (On a sidenote a main reason for choosing postgres is the way  
dumps are restored - mysql uses single regular sql insert statements  
which might take days for very large databases while postgres uses  
more "native" copy statements which never takes more than minutes).

cheers, Lars

> **···**
>
> 2010/7/12 Orvalho Augusto \<orvaquim@gmail.com\>:
> 
> > I need to have a repo/table that aggregates data as I asked before. I did  
> > not find it on docs.
> > 
> > Someone told me that can happens if a generate those resource tables. I try  
> > to generate those tables and I get stuck on that error I reported.
> > 
> > Good for me if I do not need to generate such tables and ask what I should  
> > do?
> > 
> > Caveman
> > 
> > 2010/7/12 Lars Helge Øverland \<larshelge@gmail.com\>
> > 
> > > Generating any of the resource tables don't affect the aggregation /  
> > > report tables...
> > > 
> > > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto \<orvaquim@gmail.com\> \>\> wrote:  
> > > \> Thanks!!!  
> > > \>  
> > > \> Caveman  
> > > \>  
> > > \>  
> > > \> On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \>\> \> \<jason.p.pickering@gmail.com\> wrote:  
> > > \>\>  
> > > \>\> A very common mistake is not having generated the resource tables,  
> > > \>\> namely "orgunitstructure". Consult the documentation for more details  
> > > \>\> on how to generate these tables.  
> > > \>\>  
> > > \>\> Regards,  
> > > \>\> Jason  
> > > \>\>  
> > > \>\>  
> > > \>\> On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto \<orvaquim@gmail.com\> \>\> \>\> wrote:  
> > > \>\> \> I have a dataelement collected at a Health Centre level (eg:  
> > > \>\> \> ats\_posfem)  
> > > \>\> \> in  
> > > \>\> \> one province and only in some particular districts. In unpredictible  
> > > \>\> \> future  
> > > \>\> \> at National level we will be collecting on every health centre.  
> > > \>\> \>  
> > > \>\> \> Now, my ignorance: How can I obtain the provincial total on Report  
> > > \>\> \> Table? I  
> > > \>\> \> have been battled with DHIS so far and nothing.  
> > > \>\> \>  
> > > \>\> \> Thanks guys  
> > > \>\> \> Caveman  
> > > \>\> \>  
> > > \>\> \>  
> > > \>\> \> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > \>\> \> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\> \> Post to : dhis2-devs@lists.launchpad.net  
> > > \>\> \> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\> \> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > \>\> \>  
> > > \>\> \>  
> > > \>\>  
> > > \>\>  
> > > \>\>  
> > > \>\> --  
> > > \>\> --  
> > > \>\> Jason P. Pickering  
> > > \>\> email: jason.p.pickering@gmail.com  
> > > \>\> tel:+260968395190  
> > > \>  
> > > \>  
> > > \>  
> > > \>  
> > > \> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > \> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \> Post to : dhis2-devs@lists.launchpad.net  
> > > \> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > \>  
> > > \>

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [12 July 2010 10:40 UTC](https://community.dhis2.org/t/aggregating-values/8450/9 "2010-07-12T10:40:33Z")

</div>

Thanks!!

That of restoring a database of huge size is not everyday you do.

I need people with databases of Postgres with sizes above 5GB. And tell us how their postgres behave, specifically tell us how they address the vaccum data thing.

Please is not a war.

Caveman

> **···**
>
> 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com)
> 
> > Hi Caveman, we are definitely not dropping mysql support, its just
> > 
> > that most of the devs are using postgres so a function not being
> > 
> > mysql-compliant might sneak in from time to time. I tested most of the
> > 
> > application on mysql recently without trouble.
> > 
> > A common problem with aggregation earlier was the way the orgunit
> > 
> > hierarchy was represented and stored, this is changed in 2.0.5
> > 
> > snapshot and is way less error-prone now. So please build yourself a
> > 
> > war-file from trunk and test with that.
> > 
> > If that doesn’t work please send me a database dump and I will have a
> > 
> > look. (On a sidenote a main reason for choosing postgres is the way
> > 
> > dumps are restored - mysql uses single regular sql insert statements
> > 
> > which might take days for very large databases while postgres uses
> > 
> > more “native” copy statements which never takes more than minutes).
> > 
> > cheers, Lars
> > 
> > 2010/7/12 Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com):
> 
> > > I need to have a repo/table that aggregates data as I asked before. I did
> > 
> > > not find it on docs.
> > 
> > > 
> > 
> > > Someone told me that can happens if a generate those resource tables. I try
> > 
> > > to generate those tables and I get stuck on that error I reported.
> > 
> > > 
> > 
> > > Good for me if I do not need to generate such tables and ask what I should
> > 
> > > do?
> > 
> > > 
> > 
> > > Caveman
> > 
> > > 
> > 
> > > 
> > 
> > > 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com)
> > 
> > > >
> > 
> > > > Generating any of the resource tables don’t affect the aggregation /
> > 
> > > > report tables…
> > 
> > > >
> > 
> > > >
> > 
> > > >
> > 
> > > > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\> wrote:
> > 
> > > > > Thanks!!!
> > 
> > > > >
> > 
> > > > > Caveman
> > 
> > > > >
> > 
> > > > >
> > 
> > > > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \> \>\> \> [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > 
> > > > > >
> > 
> > > > > > A very common mistake is not having generated the resource tables,
> > 
> > > > > > namely “orgunitstructure”. Consult the documentation for more details
> > 
> > > > > > on how to generate these tables.
> > 
> > > > > >
> > 
> > > > > > Regards,
> > 
> > > > > > Jason
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\> \>\> wrote:
> > 
> > > > > > > I have a dataelement collected at a Health Centre level (eg:
> > 
> > > > > > > ats\_posfem)
> > 
> > > > > > > in
> > 
> > > > > > > one province and only in some particular districts. In unpredictible
> > 
> > > > > > > future
> > 
> > > > > > > at National level we will be collecting on every health centre.
> > 
> > > > > > >
> > 
> > > > > > > Now, my ignorance: How can I obtain the provincial total on Report
> > 
> > > > > > > Table? I
> > 
> > > > > > > have been battled with DHIS so far and nothing.
> > 
> > > > > > >
> > 
> > > > > > > Thanks guys
> > 
> > > > > > > Caveman
> > 
> > > > > > >
> > 
> > > > > > >
> > 
> > > > > > > * * *
> > 
> > > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > > 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:+260968395190
> > 
> > > > >
> > 
> > > > >
> > 
> > > > >
> > 
> > > > >
> > 
> > > > > * * *
> > 
> > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> > 
> > > > >
> > 
> > > > >
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > >

---

<div class="post-metadata">

**Author:** ![Lars](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/lars/32/34166_2.png) [@Lars](https://community.dhis2.org/u/Lars)\
**Post date:** [12 July 2010 12:48 UTC](https://community.dhis2.org/t/aggregating-values/8450/10 "2010-07-12T12:48:12Z")

</div>

> Thanks!!
> 
> That of restoring a database of huge size is not everyday you do.

Well that depends 🙂

> I need people with databases of Postgres with sizes above 5GB. And tell us  
> how their postgres behave, specifically tell us how they address the vaccum  
> data thing.

Vacuum and analyze jobs are run as a daemon by default as far as I know.

> From the postgres docs:

Maximum Database Size Unlimited  
Maximum Table Size 32 TB  
Maximum Row Size 1.6 TB  
Maximum Field Size 1 GB  
Maximum Rows per Table Unlimited  
Maximum Columns per Table 250 - 1600 depending on column types  
Maximum Indexes per Table Unlimited

So 5 GB should not be a problem..

> Please is not a war.

Definitely not 🙂

> **···**
>
> 2010/7/12 Orvalho Augusto \<orvaquim@gmail.com\>:
> 
> > Caveman
> > 
> > 2010/7/12 Lars Helge Øverland \<larshelge@gmail.com\>
> > 
> > > Hi Caveman, we are definitely not dropping mysql support, its just  
> > > that most of the devs are using postgres so a function not being  
> > > mysql-compliant might sneak in from time to time. I tested most of the  
> > > application on mysql recently without trouble.
> > > 
> > > A common problem with aggregation earlier was the way the orgunit  
> > > hierarchy was represented and stored, this is changed in 2.0.5  
> > > snapshot and is way less error-prone now. So please build yourself a  
> > > war-file from trunk and test with that.
> > > 
> > > If that doesn't work please send me a database dump and I will have a  
> > > look. (On a sidenote a main reason for choosing postgres is the way  
> > > dumps are restored - mysql uses single regular sql insert statements  
> > > which might take days for very large databases while postgres uses  
> > > more "native" copy statements which never takes more than minutes).
> > > 
> > > cheers, Lars
> > > 
> > > 2010/7/12 Orvalho Augusto \<orvaquim@gmail.com\>:  
> > > \> I need to have a repo/table that aggregates data as I asked before. I  
> > > \> did  
> > > \> not find it on docs.  
> > > \>  
> > > \> Someone told me that can happens if a generate those resource tables. I  
> > > \> try  
> > > \> to generate those tables and I get stuck on that error I reported.  
> > > \>  
> > > \> Good for me if I do not need to generate such tables and ask what I  
> > > \> should  
> > > \> do?  
> > > \>  
> > > \> Caveman  
> > > \>  
> > > \>  
> > > \> 2010/7/12 Lars Helge Øverland \<larshelge@gmail.com\>  
> > > \>\>  
> > > \>\> Generating any of the resource tables don't affect the aggregation /  
> > > \>\> report tables...  
> > > \>\>  
> > > \>\>  
> > > \>\>  
> > > \>\> On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto \<orvaquim@gmail.com\> \>\> \>\> wrote:  
> > > \>\> \> Thanks!!!  
> > > \>\> \>  
> > > \>\> \> Caveman  
> > > \>\> \>  
> > > \>\> \>  
> > > \>\> \> On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \>\> \>\> \> \<jason.p.pickering@gmail.com\> wrote:  
> > > \>\> \>\>  
> > > \>\> \>\> A very common mistake is not having generated the resource tables,  
> > > \>\> \>\> namely "orgunitstructure". Consult the documentation for more  
> > > \>\> \>\> details  
> > > \>\> \>\> on how to generate these tables.  
> > > \>\> \>\>  
> > > \>\> \>\> Regards,  
> > > \>\> \>\> Jason  
> > > \>\> \>\>  
> > > \>\> \>\>  
> > > \>\> \>\> On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto \>\> \>\> \>\> \<orvaquim@gmail.com\> \>\> \>\> \>\> wrote:  
> > > \>\> \>\> \> I have a dataelement collected at a Health Centre level (eg:  
> > > \>\> \>\> \> ats\_posfem)  
> > > \>\> \>\> \> in  
> > > \>\> \>\> \> one province and only in some particular districts. In  
> > > \>\> \>\> \> unpredictible  
> > > \>\> \>\> \> future  
> > > \>\> \>\> \> at National level we will be collecting on every health centre.  
> > > \>\> \>\> \>  
> > > \>\> \>\> \> Now, my ignorance: How can I obtain the provincial total on Report  
> > > \>\> \>\> \> Table? I  
> > > \>\> \>\> \> have been battled with DHIS so far and nothing.  
> > > \>\> \>\> \>  
> > > \>\> \>\> \> Thanks guys  
> > > \>\> \>\> \> Caveman  
> > > \>\> \>\> \>  
> > > \>\> \>\> \>  
> > > \>\> \>\> \> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > \>\> \>\> \> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\> \>\> \> Post to : dhis2-devs@lists.launchpad.net  
> > > \>\> \>\> \> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\> \>\> \> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > \>\> \>\> \>  
> > > \>\> \>\> \>  
> > > \>\> \>\>  
> > > \>\> \>\>  
> > > \>\> \>\>  
> > > \>\> \>\> --  
> > > \>\> \>\> --  
> > > \>\> \>\> Jason P. Pickering  
> > > \>\> \>\> email: jason.p.pickering@gmail.com  
> > > \>\> \>\> tel:+260968395190  
> > > \>\> \>  
> > > \>\> \>  
> > > \>\> \>  
> > > \>\> \>  
> > > \>\> \> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > \>\> \> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\> \> Post to : dhis2-devs@lists.launchpad.net  
> > > \>\> \> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\> \> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > \>\> \>  
> > > \>\> \>  
> > > \>  
> > > \>  
> > > \>  
> > > \>

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [12 July 2010 13:31 UTC](https://community.dhis2.org/t/aggregating-values/8450/11 "2010-07-12T13:31:48Z")

</div>

Good!

Hope will not be a problem said like that.

Anyway we need people using it. It is not a doubt. We know that on India there are MySQL with more than 5GB.

For vaccum yes postgres guys have daemonized the vaccum data clean. You are right.

Caveman

> **···**
>
> 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com)
> 
> > 2010/7/12 Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com):
> 
> > > Thanks!!
> > 
> > > 
> > 
> > > That of restoring a database of huge size is not everyday you do.
> > 
> > >
> 
> > Well that depends 🙂
> > 
> > > I need people with databases of Postgres with sizes above 5GB. And tell us
> > 
> > > how their postgres behave, specifically tell us how they address the vaccum
> > 
> > > data thing.
> > 
> > >
> 
> > Vacuum and analyze jobs are run as a daemon by default as far as I know.
> > 
> > > From the postgres docs:
> > 
> > Maximum Database Size Unlimited
> > 
> > Maximum Table Size 32 TB
> > 
> > Maximum Row Size 1.6 TB
> > 
> > Maximum Field Size 1 GB
> > 
> > Maximum Rows per Table Unlimited
> > 
> > Maximum Columns per Table 250 - 1600 depending on column types
> > 
> > Maximum Indexes per Table Unlimited
> > 
> > So 5 GB should not be a problem…
> > 
> > > Please is not a war.
> > 
> > >
> 
> > Definitely not 🙂
> 
> > > Caveman
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com)
> > 
> > > >
> > 
> > > > Hi Caveman, we are definitely not dropping mysql support, its just
> > 
> > > > that most of the devs are using postgres so a function not being
> > 
> > > > mysql-compliant might sneak in from time to time. I tested most of the
> > 
> > > > application on mysql recently without trouble.
> > 
> > > >
> > 
> > > > A common problem with aggregation earlier was the way the orgunit
> > 
> > > > hierarchy was represented and stored, this is changed in 2.0.5
> > 
> > > > snapshot and is way less error-prone now. So please build yourself a
> > 
> > > > war-file from trunk and test with that.
> > 
> > > >
> > 
> > > > If that doesn’t work please send me a database dump and I will have a
> > 
> > > > look. (On a sidenote a main reason for choosing postgres is the way
> > 
> > > > dumps are restored - mysql uses single regular sql insert statements
> > 
> > > > which might take days for very large databases while postgres uses
> > 
> > > > more “native” copy statements which never takes more than minutes).
> > 
> > > >
> > 
> > > > cheers, Lars
> > 
> > > >
> > 
> > > >
> > 
> > > >
> > 
> > > > 2010/7/12 Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com):
> > 
> > > > > I need to have a repo/table that aggregates data as I asked before. I
> > 
> > > > > did
> > 
> > > > > not find it on docs.
> > 
> > > > >
> > 
> > > > > Someone told me that can happens if a generate those resource tables. I
> > 
> > > > > try
> > 
> > > > > to generate those tables and I get stuck on that error I reported.
> > 
> > > > >
> > 
> > > > > Good for me if I do not need to generate such tables and ask what I
> > 
> > > > > should
> > 
> > > > > do?
> > 
> > > > >
> > 
> > > > > Caveman
> > 
> > > > >
> > 
> > > > >
> > 
> > > > > 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com)
> > 
> > > > > >
> > 
> > > > > > Generating any of the resource tables don’t affect the aggregation /
> > 
> > > > > > report tables…
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\> \>\> wrote:
> > 
> > > > > > > Thanks!!!
> > 
> > > > > > >
> > 
> > > > > > > Caveman
> > 
> > > > > > >
> > 
> > > > > > >
> > 
> > > > > > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \> \>\> \>\> \> [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > 
> > > > > > > >
> > 
> > > > > > > > A very common mistake is not having generated the resource tables,
> > 
> > > > > > > > namely “orgunitstructure”. Consult the documentation for more
> > 
> > > > > > > > details
> > 
> > > > > > > > on how to generate these tables.
> > 
> > > > > > > >
> > 
> > > > > > > > Regards,
> > 
> > > > > > > > Jason
> > 
> > > > > > > >
> > 
> > > > > > > >
> > 
> > > > > > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto \> \> \>\> \>\> \>\> [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\> \>\> \>\> wrote:
> > 
> > > > > > > > > I have a dataelement collected at a Health Centre level (eg:
> > 
> > > > > > > > > ats\_posfem)
> > 
> > > > > > > > > in
> > 
> > > > > > > > > one province and only in some particular districts. In
> > 
> > > > > > > > > unpredictible
> > 
> > > > > > > > > future
> > 
> > > > > > > > > at National level we will be collecting on every health centre.
> > 
> > > > > > > > >
> > 
> > > > > > > > > Now, my ignorance: How can I obtain the provincial total on Report
> > 
> > > > > > > > > Table? I
> > 
> > > > > > > > > have been battled with DHIS so far and nothing.
> > 
> > > > > > > > >
> > 
> > > > > > > > > Thanks guys
> > 
> > > > > > > > > Caveman
> > 
> > > > > > > > >
> > 
> > > > > > > > >
> > 
> > > > > > > > > * * *
> > 
> > > > > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > > > > 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:+260968395190
> > 
> > > > > > >
> > 
> > > > > > >
> > 
> > > > > > >
> > 
> > > > > > >
> > 
> > > > > > > * * *
> > 
> > > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> > 
> > > > > > >
> > 
> > > > > > >
> > 
> > > > >
> > 
> > > > >
> > 
> > > > >
> > 
> > > > >
> > 
> > > 
> > 
> > > 
> > 
> > >

---

<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:** [12 July 2010 17:39 UTC](https://community.dhis2.org/t/aggregating-values/8450/12 "2010-07-12T17:39:26Z")

</div>

Hi Lars,  
Well, yes and no. If the resource tables (namely orgunitstructure) is  
not generated, it would not be possible to assign any orgunits to a  
given data mart/report table? Likewise, if the orgunit levels have not  
been defined, it is also not possible to assign any orgunits to a  
given report table/datamart, or perhaps I am just hallucinating ? I  
have run into this problem several times actually when creating new  
databases. Ideally, the orgunitstructure table should be regenerated  
when there have been changes to the orgunit hierarchy and users should  
be warned that it is not possible to create a report table/datamart  
without first defining orgunit level names.

Regards,  
Jason

> **···**
>
> 2010/7/12 Lars Helge Øverland \<larshelge@gmail.com\>:
> 
> > Generating any of the resource tables don't affect the aggregation /  
> > report tables...
> > 
> > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto \<orvaquim@gmail.com\> wrote:
> > 
> > > Thanks!!!
> > > 
> > > Caveman
> > > 
> > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \>\> \<jason.p.pickering@gmail.com\> wrote:
> > > 
> > > > A very common mistake is not having generated the resource tables,  
> > > > namely "orgunitstructure". Consult the documentation for more details  
> > > > on how to generate these tables.
> > > > 
> > > > Regards,  
> > > > Jason
> > > > 
> > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto \<orvaquim@gmail.com\> \>\>\> wrote:  
> > > > \> I have a dataelement collected at a Health Centre level (eg: ats\_posfem)  
> > > > \> in  
> > > > \> one province and only in some particular districts. In unpredictible  
> > > > \> future  
> > > > \> at National level we will be collecting on every health centre.  
> > > > \>  
> > > > \> Now, my ignorance: How can I obtain the provincial total on Report  
> > > > \> Table? I  
> > > > \> have been battled with DHIS so far and nothing.  
> > > > \>  
> > > > \> Thanks guys  
> > > > \> Caveman  
> > > > \>  
> > > > \>  
> > > > \> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > > \> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > > \> Post to : dhis2-devs@lists.launchpad.net  
> > > > \> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > > \> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > > \>  
> > > > \>
> > > > 
> > > > --  
> > > > --  
> > > > Jason P. Pickering  
> > > > email: jason.p.pickering@gmail.com  
> > > > tel:+260968395190
> > > 
> > > \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > Post to : dhis2-devs@lists.launchpad.net  
> > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> 
> --  
> --  
> Jason P. Pickering  
> email: jason.p.pickering@gmail.com  
> tel:+260968395190

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [13 July 2010 06:17 UTC](https://community.dhis2.org/t/aggregating-values/8450/13 "2010-07-13T06:17:40Z")

</div>

I recompiled DHIS2.05 yesterday with help of a magician Jason yesterday. And the first thing I tried was this aggregation thing.

So I started redoing the orgunitstructure. It gave the same error.

While it was running I used the

show full processlist  
to find out that it is a verry long insert to be run at once. I have 1664 orgunits. So it sounds like indexing trouble.

As there is a faith and “antimysql fight” I am moving my data to postgres. I have to do it manually.

I have to do it manually because DHIS does not keep de ids fixed (organisationunid, dataelementid etc). That affects our mapping data for data importation from other systems. So if someone could suggest a good strategy is welcomed.

Thanks for your patience guys  
Caveman

> **···**
>
> 2010/7/12 Jason Pickering [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)
> 
> > Hi Lars,
> > 
> > Well, yes and no. If the resource tables (namely orgunitstructure) is
> > 
> > not generated, it would not be possible to assign any orgunits to a
> > 
> > given data mart/report table? Likewise, if the orgunit levels have not
> > 
> > been defined, it is also not possible to assign any orgunits to a
> > 
> > given report table/datamart, or perhaps I am just hallucinating ? I
> > 
> > have run into this problem several times actually when creating new
> > 
> > databases. Ideally, the orgunitstructure table should be regenerated
> > 
> > when there have been changes to the orgunit hierarchy and users should
> > 
> > be warned that it is not possible to create a report table/datamart
> > 
> > without first defining orgunit level names.
> > 
> > Regards,
> > 
> > Jason
> > 
> > 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com):
> 
> > > Generating any of the resource tables don’t affect the aggregation /
> > 
> > > report tables…
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> > 
> > > > Thanks!!!
> > 
> > > >
> > 
> > > > Caveman
> > 
> > > >
> > 
> > > >
> > 
> > > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \> \>\> [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > 
> > > > >
> > 
> > > > > A very common mistake is not having generated the resource tables,
> > 
> > > > > namely “orgunitstructure”. Consult the documentation for more details
> > 
> > > > > on how to generate these tables.
> > 
> > > > >
> > 
> > > > > Regards,
> > 
> > > > > Jason
> > 
> > > > >
> > 
> > > > >
> > 
> > > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\>\> wrote:
> > 
> > > > > > I have a dataelement collected at a Health Centre level (eg: ats\_posfem)
> > 
> > > > > > in
> > 
> > > > > > one province and only in some particular districts. In unpredictible
> > 
> > > > > > future
> > 
> > > > > > at National level we will be collecting on every health centre.
> > 
> > > > > >
> > 
> > > > > > Now, my ignorance: How can I obtain the provincial total on Report
> > 
> > > > > > Table? I
> > 
> > > > > > have been battled with DHIS so far and nothing.
> > 
> > > > > >
> > 
> > > > > > Thanks guys
> > 
> > > > > > Caveman
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > > * * *
> > 
> > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > 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:+260968395190
> > 
> > > >
> > 
> > > >
> > 
> > > >
> > 
> > > >
> > 
> > > > * * *
> > 
> > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > 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:+260968395190

---

<div class="post-metadata">

**Author:** ![bobj](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/bobj/32/33033_2.png) [@bobj](https://community.dhis2.org/u/bobj)\
**Post date:** [13 July 2010 09:55 UTC](https://community.dhis2.org/t/aggregating-values/8450/14 "2010-07-13T09:55:41Z")

</div>

> I recompiled DHIS2.05 yesterday with help of a magician Jason yesterday. And  
> the first thing I tried was this aggregation thing.
> 
> So I started redoing the orgunitstructure. It gave the same error.
> 
> While it was running I used the  
> show full processlist  
> to find out that it is a verry long insert to be run at once. I have 1664  
> orgunits. So it sounds like indexing trouble.
> 
> As there is a faith and "antimysql fight" I am moving my data to postgres. I  
> have to do it manually.

There is no mysql fight. Just more people using postgres I guess.

> I have to do it manually because DHIS does not keep de ids fixed  
> (organisationunid, dataelementid etc). That affects our mapping data for  
> data importation from other systems. So if someone could suggest a good  
> strategy is welcomed.

How are you doing mapping data from other systems? If you tell me a  
bit about these systems and the data they have maybe we can make a  
plan 🙂

Cheers  
Bob

> **···**
>
> On 13 July 2010 07:17, Orvalho Augusto \<orvaquim@gmail.com\> wrote:
> 
> > Thanks for your patience guys  
> > Caveman
> > 
> > 2010/7/12 Jason Pickering \<jason.p.pickering@gmail.com\>
> > 
> > > Hi Lars,  
> > > Well, yes and no. If the resource tables (namely orgunitstructure) is  
> > > not generated, it would not be possible to assign any orgunits to a  
> > > given data mart/report table? Likewise, if the orgunit levels have not  
> > > been defined, it is also not possible to assign any orgunits to a  
> > > given report table/datamart, or perhaps I am just hallucinating ? I  
> > > have run into this problem several times actually when creating new  
> > > databases. Ideally, the orgunitstructure table should be regenerated  
> > > when there have been changes to the orgunit hierarchy and users should  
> > > be warned that it is not possible to create a report table/datamart  
> > > without first defining orgunit level names.
> > > 
> > > Regards,  
> > > Jason
> > > 
> > > 2010/7/12 Lars Helge Øverland \<larshelge@gmail.com\>:  
> > > \> Generating any of the resource tables don't affect the aggregation /  
> > > \> report tables...  
> > > \>  
> > > \>  
> > > \>  
> > > \> On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto \<orvaquim@gmail.com\> \>\> \> wrote:  
> > > \>\> Thanks!!!  
> > > \>\>  
> > > \>\> Caveman  
> > > \>\>  
> > > \>\>  
> > > \>\> On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \>\> \>\> \<jason.p.pickering@gmail.com\> wrote:  
> > > \>\>\>  
> > > \>\>\> A very common mistake is not having generated the resource tables,  
> > > \>\>\> namely "orgunitstructure". Consult the documentation for more details  
> > > \>\>\> on how to generate these tables.  
> > > \>\>\>  
> > > \>\>\> Regards,  
> > > \>\>\> Jason  
> > > \>\>\>  
> > > \>\>\>  
> > > \>\>\> On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto \<orvaquim@gmail.com\> \>\> \>\>\> wrote:  
> > > \>\>\> \> I have a dataelement collected at a Health Centre level (eg:  
> > > \>\>\> \> ats\_posfem)  
> > > \>\>\> \> in  
> > > \>\>\> \> one province and only in some particular districts. In unpredictible  
> > > \>\>\> \> future  
> > > \>\>\> \> at National level we will be collecting on every health centre.  
> > > \>\>\> \>  
> > > \>\>\> \> Now, my ignorance: How can I obtain the provincial total on Report  
> > > \>\>\> \> Table? I  
> > > \>\>\> \> have been battled with DHIS so far and nothing.  
> > > \>\>\> \>  
> > > \>\>\> \> Thanks guys  
> > > \>\>\> \> Caveman  
> > > \>\>\> \>  
> > > \>\>\> \>  
> > > \>\>\> \> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > \>\>\> \> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\>\> \> Post to : dhis2-devs@lists.launchpad.net  
> > > \>\>\> \> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\>\> \> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > \>\>\> \>  
> > > \>\>\> \>  
> > > \>\>\>  
> > > \>\>\>  
> > > \>\>\>  
> > > \>\>\> --  
> > > \>\>\> --  
> > > \>\>\> Jason P. Pickering  
> > > \>\>\> email: jason.p.pickering@gmail.com  
> > > \>\>\> tel:+260968395190  
> > > \>\>  
> > > \>\>  
> > > \>\>  
> > > \>\>  
> > > \>\> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > \>\> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\> Post to : dhis2-devs@lists.launchpad.net  
> > > \>\> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > \>\> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > \>\>  
> > > \>\>  
> > > \>
> > > 
> > > --  
> > > --  
> > > Jason P. Pickering  
> > > email: jason.p.pickering@gmail.com  
> > > tel:+260968395190
> > 
> > \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > Post to : dhis2-devs@lists.launchpad.net  
> > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)

---

<div class="post-metadata">

**Author:** ![Lars](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/lars/32/34166_2.png) [@Lars](https://community.dhis2.org/u/Lars)\
**Post date:** [13 July 2010 15:48 UTC](https://community.dhis2.org/t/aggregating-values/8450/15 "2010-07-13T15:48:47Z")

</div>

> Hi Lars,
> 
> Well, yes and no. If the resource tables (namely orgunitstructure) is
> 
> not generated, it would not be possible to assign any orgunits to a
> 
> given data mart/report table?

The orgunitstructure table is just a resource table intended to be used by 3rd party tools which is not used internally in the system (maybe for the databrowser after you and Hieu changed it?).

> Likewise, if the orgunit levels have not
> 
> been defined, it is also not possible to assign any orgunits to a
> 
> given report table/datamart, or perhaps I am just hallucinating ?

That is true, but I wouldn’t call the orgunit levels a resource table per se, it has a belonging object in the model and is used internally (even if you very well might use it in query).

> I
> 
> have run into this problem several times actually when creating new
> 
> databases. Ideally, the orgunitstructure table should be regenerated
> 
> when there have been changes to the orgunit hierarchy and users should
> 
> be warned that it is not possible to create a report table/datamart
> 
> without first defining orgunit level names.

Agree that we could have some sort of integrated “required setup guide” in the system…

> **···**
>
> 2010/7/12 Jason Pickering [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)
> 
> > Regards,
> > 
> > Jason
> > 
> > 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com):
> 
> > > Generating any of the resource tables don’t affect the aggregation /
> > 
> > > report tables…
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> > 
> > > > Thanks!!!
> > 
> > > >
> > 
> > > > Caveman
> > 
> > > >
> > 
> > > >
> > 
> > > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \> \>\> [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > 
> > > > >
> > 
> > > > > A very common mistake is not having generated the resource tables,
> > 
> > > > > namely “orgunitstructure”. Consult the documentation for more details
> > 
> > > > > on how to generate these tables.
> > 
> > > > >
> > 
> > > > > Regards,
> > 
> > > > > Jason
> > 
> > > > >
> > 
> > > > >
> > 
> > > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\>\> wrote:
> > 
> > > > > > I have a dataelement collected at a Health Centre level (eg: ats\_posfem)
> > 
> > > > > > in
> > 
> > > > > > one province and only in some particular districts. In unpredictible
> > 
> > > > > > future
> > 
> > > > > > at National level we will be collecting on every health centre.
> > 
> > > > > >
> > 
> > > > > > Now, my ignorance: How can I obtain the provincial total on Report
> > 
> > > > > > Table? I
> > 
> > > > > > have been battled with DHIS so far and nothing.
> > 
> > > > > >
> > 
> > > > > > Thanks guys
> > 
> > > > > > Caveman
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > > * * *
> > 
> > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > > > > 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:+260968395190
> > 
> > > >
> > 
> > > >
> > 
> > > >
> > 
> > > >
> > 
> > > > * * *
> > 
> > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > > 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:+260968395190

---

<div class="post-metadata">

**Author:** ![Lars](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/lars/32/34166_2.png) [@Lars](https://community.dhis2.org/u/Lars)\
**Post date:** [13 July 2010 15:50 UTC](https://community.dhis2.org/t/aggregating-values/8450/16 "2010-07-13T15:50:35Z")

</div>

> > I recompiled DHIS2.05 yesterday with help of a magician Jason yesterday. And
> 
> > the first thing I tried was this aggregation thing.
> 
> > 
> 
> > So I started redoing the orgunitstructure. It gave the same error.
> 
> > 
> 
> > While it was running I used the
> 
> > show full processlist
> 
> > to find out that it is a verry long insert to be run at once. I have 1664
> 
> > orgunits. So it sounds like indexing trouble.
> 
> > 
> 
> > As there is a faith and “antimysql fight” I am moving my data to postgres. I
> 
> > have to do it manually.

> There is no mysql fight. Just more people using postgres I guess.
> 
> > 
> 
> > I have to do it manually because DHIS does not keep de ids fixed
> 
> > (organisationunid, dataelementid etc). That affects our mapping data for
> 
> > data importation from other systems. So if someone could suggest a good
> 
> > strategy is welcomed.

Database internal ids should not really be used to map anything between databases… Also postgres is using a sequence meaning auto-generated values are globally unique, while auto-generated values in mysql starts at 1 for each table.

> **···**
>
> On Tue, Jul 13, 2010 at 11:55 AM, Bob Jolliffe [bobjolliffe@gmail.com](mailto:bobjolliffe@gmail.com) wrote:
> 
> > On 13 July 2010 07:17, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> 
> > 
> 
> > How are you doing mapping data from other systems? If you tell me a
> > 
> > bit about these systems and the data they have maybe we can make a
> > 
> > plan 🙂
> > 
> > Cheers
> > 
> > Bob
> 
> > > 
> > 
> > > Thanks for your patience guys
> > 
> > > Caveman
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 2010/7/12 Jason Pickering [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)
> > 
> > > >
> > 
> > > > Hi Lars,
> > 
> > > > Well, yes and no. If the resource tables (namely orgunitstructure) is
> > 
> > > > not generated, it would not be possible to assign any orgunits to a
> > 
> > > > given data mart/report table? Likewise, if the orgunit levels have not
> > 
> > > > been defined, it is also not possible to assign any orgunits to a
> > 
> > > > given report table/datamart, or perhaps I am just hallucinating ? I
> > 
> > > > have run into this problem several times actually when creating new
> > 
> > > > databases. Ideally, the orgunitstructure table should be regenerated
> > 
> > > > when there have been changes to the orgunit hierarchy and users should
> > 
> > > > be warned that it is not possible to create a report table/datamart
> > 
> > > > without first defining orgunit level names.
> > 
> > > >
> > 
> > > > Regards,
> > 
> > > > Jason
> > 
> > > >
> > 
> > > >
> > 
> > > > 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com):
> > 
> > > > > Generating any of the resource tables don’t affect the aggregation /
> > 
> > > > > report tables…
> > 
> > > > >
> > 
> > > > >
> > 
> > > > >
> > 
> > > > > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\> \> wrote:
> > 
> > > > > > Thanks!!!
> > 
> > > > > >
> > 
> > > > > > Caveman
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \> \>\> \>\> [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > 
> > > > > > >
> > 
> > > > > > > A very common mistake is not having generated the resource tables,
> > 
> > > > > > > namely “orgunitstructure”. Consult the documentation for more details
> > 
> > > > > > > on how to generate these tables.
> > 
> > > > > > >
> > 
> > > > > > > Regards,
> > 
> > > > > > > Jason
> > 
> > > > > > >
> > 
> > > > > > >
> > 
> > > > > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\> \>\>\> wrote:
> > 
> > > > > > > > I have a dataelement collected at a Health Centre level (eg:
> > 
> > > > > > > > ats\_posfem)
> > 
> > > > > > > > in
> > 
> > > > > > > > one province and only in some particular districts. In unpredictible
> > 
> > > > > > > > future
> > 
> > > > > > > > at National level we will be collecting on every health centre.
> > 
> > > > > > > >
> > 
> > > > > > > > Now, my ignorance: How can I obtain the provincial total on Report
> > 
> > > > > > > > Table? I
> > 
> > > > > > > > have been battled with DHIS so far and nothing.
> > 
> > > > > > > >
> > 
> > > > > > > > Thanks guys
> > 
> > > > > > > > Caveman
> > 
> > > > > > > >
> > 
> > > > > > > >
> > 
> > > > > > > > * * *
> > 
> > > > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > > > > > > 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:+260968395190
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > > * * *
> > 
> > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > > > > 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:+260968395190
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > * * *
> > 
> > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> > 
> > > 
> > 
> > > 
> > 
> > * * *
> > 
> > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)
> > 
> > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [13 July 2010 20:28 UTC](https://community.dhis2.org/t/aggregating-values/8450/17 "2010-07-13T20:28:37Z")

</div>

Ok.

> > I recompiled DHIS2.05 yesterday with help of a magician Jason yesterday. And
> 
> > the first thing I tried was this aggregation thing.
> 
> > 
> 
> > So I started redoing the orgunitstructure. It gave the same error.
> 
> > 
> 
> > While it was running I used the
> 
> > show full processlist
> 
> > to find out that it is a verry long insert to be run at once. I have 1664
> 
> > orgunits. So it sounds like indexing trouble.
> 
> > 
> 
> > As there is a faith and “antimysql fight” I am moving my data to postgres. I
> 
> > have to do it manually.

> There is no mysql fight. Just more people using postgres I guess.
> 
> > 
> 
> > I have to do it manually because DHIS does not keep de ids fixed
> 
> > (organisationunid, dataelementid etc). That affects our mapping data for
> 
> > data importation from other systems. So if someone could suggest a good
> 
> > strategy is welcomed.

> How are you doing mapping data from other systems? If you tell me a
> 
> bit about these systems and the data they have maybe we can make a
> 
> plan 🙂  
> Basically these systems are:  
> The dataset (the DHIS concept) is represented by a table (in EpiInfo 6.06, DBF or MSAccess) where each record should have a month (or week or quarter), year, province, district and health center columns - sometimes they place the name of the association or a special sector of the health center - then the other columns define a dataelement for DHIS.

So what we were doing is:

1. Migrate the data from anywhere (EpiInfo, MSAccess and DBF) to MySQL.

2. On MySQL we have tables where the columns names of those tables mapping to the dataelementid (which Lars recommend not be like this)

3. There is a script on Python made to run routinely to process this data and insert the new one on DHIS 2 tables.  
When I say routinely, I mean each month or whenever I want it.

The objective were to use solely DHIS for report and one day for data entry.

Caveman

> **···**
>
> On Tue, Jul 13, 2010 at 11:55 AM, Bob Jolliffe [bobjolliffe@gmail.com](mailto:bobjolliffe@gmail.com) wrote:
> 
> > On 13 July 2010 07:17, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> 
> > Cheers
> > 
> > Bob
> 
> > > 
> > 
> > > Thanks for your patience guys
> > 
> > > Caveman
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > 2010/7/12 Jason Pickering [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)
> > 
> > > >
> > 
> > > > Hi Lars,
> > 
> > > > Well, yes and no. If the resource tables (namely orgunitstructure) is
> > 
> > > > not generated, it would not be possible to assign any orgunits to a
> > 
> > > > given data mart/report table? Likewise, if the orgunit levels have not
> > 
> > > > been defined, it is also not possible to assign any orgunits to a
> > 
> > > > given report table/datamart, or perhaps I am just hallucinating ? I
> > 
> > > > have run into this problem several times actually when creating new
> > 
> > > > databases. Ideally, the orgunitstructure table should be regenerated
> > 
> > > > when there have been changes to the orgunit hierarchy and users should
> > 
> > > > be warned that it is not possible to create a report table/datamart
> > 
> > > > without first defining orgunit level names.
> > 
> > > >
> > 
> > > > Regards,
> > 
> > > > Jason
> > 
> > > >
> > 
> > > >
> > 
> > > > 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com):
> > 
> > > > > Generating any of the resource tables don’t affect the aggregation /
> > 
> > > > > report tables…
> > 
> > > > >
> > 
> > > > >
> > 
> > > > >
> > 
> > > > > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\> \> wrote:
> > 
> > > > > > Thanks!!!
> > 
> > > > > >
> > 
> > > > > > Caveman
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \> \>\> \>\> [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > 
> > > > > > >
> > 
> > > > > > > A very common mistake is not having generated the resource tables,
> > 
> > > > > > > namely “orgunitstructure”. Consult the documentation for more details
> > 
> > > > > > > on how to generate these tables.
> > 
> > > > > > >
> > 
> > > > > > > Regards,
> > 
> > > > > > > Jason
> > 
> > > > > > >
> > 
> > > > > > >
> > 
> > > > > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \>\> \>\>\> wrote:
> > 
> > > > > > > > I have a dataelement collected at a Health Centre level (eg:
> > 
> > > > > > > > ats\_posfem)
> > 
> > > > > > > > in
> > 
> > > > > > > > one province and only in some particular districts. In unpredictible
> > 
> > > > > > > > future
> > 
> > > > > > > > at National level we will be collecting on every health centre.
> > 
> > > > > > > >
> > 
> > > > > > > > Now, my ignorance: How can I obtain the provincial total on Report
> > 
> > > > > > > > Table? I
> > 
> > > > > > > > have been battled with DHIS so far and nothing.
> > 
> > > > > > > >
> > 
> > > > > > > > Thanks guys
> > 
> > > > > > > > Caveman
> > 
> > > > > > > >
> > 
> > > > > > > >
> > 
> > > > > > > > * * *
> > 
> > > > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > > > 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:+260968395190
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > >
> > 
> > > > > > * * *
> > 
> > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > > > > 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:+260968395190
> > 
> > > 
> > 
> > > 
> > 
> > > 
> > 
> > > * * *
> > 
> > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > 
> > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > 
> > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> > 
> > > 
> > 
> > >

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [14 July 2010 04:35 UTC](https://community.dhis2.org/t/aggregating-values/8450/18 "2010-07-14T04:35:34Z")

</div>

I have changed the database (from MySQL to Postgres)… and used DHIS tools to export and import the data.

Well, those resource tables are being built in seconds on Postgres now. So I think there is a bug with MySQL. And I want to help to correct it.

One verry bad consequence of not importing the dataelementids is: The entering forms does not work no more. We have to redesign the forms after an import.

Caveman

> **···**
>
> On Tue, Jul 13, 2010 at 10:28 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> 
> > Ok.
> > 
> > On Tue, Jul 13, 2010 at 11:55 AM, Bob Jolliffe [bobjolliffe@gmail.com](mailto:bobjolliffe@gmail.com) wrote:
> 
> > > On 13 July 2010 07:17, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> > > 
> > > > I recompiled DHIS2.05 yesterday with help of a magician Jason yesterday. And
> > > 
> > > > the first thing I tried was this aggregation thing.
> > > 
> > > > 
> > > 
> > > > So I started redoing the orgunitstructure. It gave the same error.
> > > 
> > > > 
> > > 
> > > > While it was running I used the
> > > 
> > > > show full processlist
> > > 
> > > > to find out that it is a verry long insert to be run at once. I have 1664
> > > 
> > > > orgunits. So it sounds like indexing trouble.
> > > 
> > > > 
> > > 
> > > > As there is a faith and “antimysql fight” I am moving my data to postgres. I
> > > 
> > > > have to do it manually.
> 
> > > There is no mysql fight. Just more people using postgres I guess.
> > > 
> > > > 
> > > 
> > > > I have to do it manually because DHIS does not keep de ids fixed
> > > 
> > > > (organisationunid, dataelementid etc). That affects our mapping data for
> > > 
> > > > data importation from other systems. So if someone could suggest a good
> > > 
> > > > strategy is welcomed.
> 
> > > How are you doing mapping data from other systems? If you tell me a
> > > 
> > > bit about these systems and the data they have maybe we can make a
> > > 
> > > plan 🙂
> 
> > Basically these systems are:  
> > The dataset (the DHIS concept) is represented by a table (in EpiInfo 6.06, DBF or MSAccess) where each record should have a month (or week or quarter), year, province, district and health center columns - sometimes they place the name of the association or a special sector of the health center - then the other columns define a dataelement for DHIS.
> > 
> > So what we were doing is:
> > 
> > 1. Migrate the data from anywhere (EpiInfo, MSAccess and DBF) to MySQL.
> > 
> > 2. On MySQL we have tables where the columns names of those tables mapping to the dataelementid (which Lars recommend not be like this)
> > 
> > 3. There is a script on Python made to run routinely to process this data and insert the new one on DHIS 2 tables.  
> > When I say routinely, I mean each month or whenever I want it.
> > 
> > The objective were to use solely DHIS for report and one day for data entry.
> > 
> > Caveman
> 
> > > Cheers
> > > 
> > > Bob
> 
> > > > 
> > > 
> > > > Thanks for your patience guys
> > > 
> > > > Caveman
> > > 
> > > > 
> > > 
> > > > 
> > > 
> > > > 
> > > 
> > > > 
> > > 
> > > > 
> > > 
> > > > 2010/7/12 Jason Pickering [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)
> > > 
> > > > >
> > > 
> > > > > Hi Lars,
> > > 
> > > > > Well, yes and no. If the resource tables (namely orgunitstructure) is
> > > 
> > > > > not generated, it would not be possible to assign any orgunits to a
> > > 
> > > > > given data mart/report table? Likewise, if the orgunit levels have not
> > > 
> > > > > been defined, it is also not possible to assign any orgunits to a
> > > 
> > > > > given report table/datamart, or perhaps I am just hallucinating ? I
> > > 
> > > > > have run into this problem several times actually when creating new
> > > 
> > > > > databases. Ideally, the orgunitstructure table should be regenerated
> > > 
> > > > > when there have been changes to the orgunit hierarchy and users should
> > > 
> > > > > be warned that it is not possible to create a report table/datamart
> > > 
> > > > > without first defining orgunit level names.
> > > 
> > > > >
> > > 
> > > > > Regards,
> > > 
> > > > > Jason
> > > 
> > > > >
> > > 
> > > > >
> > > 
> > > > > 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com):
> > > 
> > > > > > Generating any of the resource tables don’t affect the aggregation /
> > > 
> > > > > > report tables…
> > > 
> > > > > >
> > > 
> > > > > >
> > > 
> > > > > >
> > > 
> > > > > > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \> \> \>\> \> wrote:
> > > 
> > > > > > > Thanks!!!
> > > 
> > > > > > >
> > > 
> > > > > > > Caveman
> > > 
> > > > > > >
> > > 
> > > > > > >
> > > 
> > > > > > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \> \> \> \>\> \>\> [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > > 
> > > > > > > >
> > > 
> > > > > > > > A very common mistake is not having generated the resource tables,
> > > 
> > > > > > > > namely “orgunitstructure”. Consult the documentation for more details
> > > 
> > > > > > > > on how to generate these tables.
> > > 
> > > > > > > >
> > > 
> > > > > > > > Regards,
> > > 
> > > > > > > > Jason
> > > 
> > > > > > > >
> > > 
> > > > > > > >
> > > 
> > > > > > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \> \> \>\> \>\>\> wrote:
> > > 
> > > > > > > > > I have a dataelement collected at a Health Centre level (eg:
> > > 
> > > > > > > > > ats\_posfem)
> > > 
> > > > > > > > > in
> > > 
> > > > > > > > > one province and only in some particular districts. In unpredictible
> > > 
> > > > > > > > > future
> > > 
> > > > > > > > > at National level we will be collecting on every health centre.
> > > 
> > > > > > > > >
> > > 
> > > > > > > > > Now, my ignorance: How can I obtain the provincial total on Report
> > > 
> > > > > > > > > Table? I
> > > 
> > > > > > > > > have been battled with DHIS so far and nothing.
> > > 
> > > > > > > > >
> > > 
> > > > > > > > > Thanks guys
> > > 
> > > > > > > > > Caveman
> > > 
> > > > > > > > >
> > > 
> > > > > > > > >
> > > 
> > > > > > > > > * * *
> > > 
> > > > > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > 
> > > > > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > > 
> > > > > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > 
> > > > > > > > > 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:+260968395190
> > > 
> > > > > > >
> > > 
> > > > > > >
> > > 
> > > > > > >
> > > 
> > > > > > >
> > > 
> > > > > > > * * *
> > > 
> > > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > 
> > > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > > 
> > > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > 
> > > > > > > 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:+260968395190
> > > 
> > > > 
> > > 
> > > > 
> > > 
> > > > 
> > > 
> > > > * * *
> > > 
> > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > 
> > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > > 
> > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > 
> > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> > > 
> > > > 
> > > 
> > > >
> 
> >

---

<div class="post-metadata">

**Author:** ![Orvalho\_Augusto](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/o/8491ac/32.png) [@Orvalho\_Augusto](https://community.dhis2.org/u/Orvalho_Augusto)\
**Post date:** [14 July 2010 06:57 UTC](https://community.dhis2.org/t/aggregating-values/8450/19 "2010-07-14T06:57:09Z")

</div>

And even do those resource tables on Postgres it does not aggregate.

Caveman

> **···**
>
> On Wed, Jul 14, 2010 at 6:35 AM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> 
> > I have changed the database (from MySQL to Postgres)… and used DHIS tools to export and import the data.
> > 
> > Well, those resource tables are being built in seconds on Postgres now. So I think there is a bug with MySQL. And I want to help to correct it.
> > 
> > One verry bad consequence of not importing the dataelementids is: The entering forms does not work no more. We have to redesign the forms after an import.
> > 
> > Caveman
> 
> > On Tue, Jul 13, 2010 at 10:28 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> > 
> > > Ok.
> > > 
> > > On Tue, Jul 13, 2010 at 11:55 AM, Bob Jolliffe [bobjolliffe@gmail.com](mailto:bobjolliffe@gmail.com) wrote:
> 
> > > > On 13 July 2010 07:17, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) wrote:
> > > > 
> > > > > I recompiled DHIS2.05 yesterday with help of a magician Jason yesterday. And
> > > > 
> > > > > the first thing I tried was this aggregation thing.
> > > > 
> > > > > 
> > > > 
> > > > > So I started redoing the orgunitstructure. It gave the same error.
> > > > 
> > > > > 
> > > > 
> > > > > While it was running I used the
> > > > 
> > > > > show full processlist
> > > > 
> > > > > to find out that it is a verry long insert to be run at once. I have 1664
> > > > 
> > > > > orgunits. So it sounds like indexing trouble.
> > > > 
> > > > > 
> > > > 
> > > > > As there is a faith and “antimysql fight” I am moving my data to postgres. I
> > > > 
> > > > > have to do it manually.
> 
> > > > There is no mysql fight. Just more people using postgres I guess.
> > > > 
> > > > > 
> > > > 
> > > > > I have to do it manually because DHIS does not keep de ids fixed
> > > > 
> > > > > (organisationunid, dataelementid etc). That affects our mapping data for
> > > > 
> > > > > data importation from other systems. So if someone could suggest a good
> > > > 
> > > > > strategy is welcomed.
> 
> > > > How are you doing mapping data from other systems? If you tell me a
> > > > 
> > > > bit about these systems and the data they have maybe we can make a
> > > > 
> > > > plan 🙂
> 
> > > Basically these systems are:  
> > > The dataset (the DHIS concept) is represented by a table (in EpiInfo 6.06, DBF or MSAccess) where each record should have a month (or week or quarter), year, province, district and health center columns - sometimes they place the name of the association or a special sector of the health center - then the other columns define a dataelement for DHIS.
> > > 
> > > So what we were doing is:
> > > 
> > > 1. Migrate the data from anywhere (EpiInfo, MSAccess and DBF) to MySQL.
> > > 
> > > 2. On MySQL we have tables where the columns names of those tables mapping to the dataelementid (which Lars recommend not be like this)
> > > 
> > > 3. There is a script on Python made to run routinely to process this data and insert the new one on DHIS 2 tables.  
> > > When I say routinely, I mean each month or whenever I want it.
> > > 
> > > The objective were to use solely DHIS for report and one day for data entry.
> > > 
> > > Caveman
> 
> > > > Cheers
> > > > 
> > > > Bob
> 
> > > > > 
> > > > 
> > > > > Thanks for your patience guys
> > > > 
> > > > > Caveman
> > > > 
> > > > > 
> > > > 
> > > > > 
> > > > 
> > > > > 
> > > > 
> > > > > 
> > > > 
> > > > > 
> > > > 
> > > > > 2010/7/12 Jason Pickering [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com)
> > > > 
> > > > > >
> > > > 
> > > > > > Hi Lars,
> > > > 
> > > > > > Well, yes and no. If the resource tables (namely orgunitstructure) is
> > > > 
> > > > > > not generated, it would not be possible to assign any orgunits to a
> > > > 
> > > > > > given data mart/report table? Likewise, if the orgunit levels have not
> > > > 
> > > > > > been defined, it is also not possible to assign any orgunits to a
> > > > 
> > > > > > given report table/datamart, or perhaps I am just hallucinating ? I
> > > > 
> > > > > > have run into this problem several times actually when creating new
> > > > 
> > > > > > databases. Ideally, the orgunitstructure table should be regenerated
> > > > 
> > > > > > when there have been changes to the orgunit hierarchy and users should
> > > > 
> > > > > > be warned that it is not possible to create a report table/datamart
> > > > 
> > > > > > without first defining orgunit level names.
> > > > 
> > > > > >
> > > > 
> > > > > > Regards,
> > > > 
> > > > > > Jason
> > > > 
> > > > > >
> > > > 
> > > > > >
> > > > 
> > > > > > 2010/7/12 Lars Helge Øverland [larshelge@gmail.com](mailto:larshelge@gmail.com):
> > > > 
> > > > > > > Generating any of the resource tables don’t affect the aggregation /
> > > > 
> > > > > > > report tables…
> > > > 
> > > > > > >
> > > > 
> > > > > > >
> > > > 
> > > > > > >
> > > > 
> > > > > > > On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \> \> \> \> \>\> \> wrote:
> > > > 
> > > > > > > > Thanks!!!
> > > > 
> > > > > > > >
> > > > 
> > > > > > > > Caveman
> > > > 
> > > > > > > >
> > > > 
> > > > > > > >
> > > > 
> > > > > > > > On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \> \> \> \> \> \> \>\> \>\> [jason.p.pickering@gmail.com](mailto:jason.p.pickering@gmail.com) wrote:
> > > > 
> > > > > > > > >
> > > > 
> > > > > > > > > A very common mistake is not having generated the resource tables,
> > > > 
> > > > > > > > > namely “orgunitstructure”. Consult the documentation for more details
> > > > 
> > > > > > > > > on how to generate these tables.
> > > > 
> > > > > > > > >
> > > > 
> > > > > > > > > Regards,
> > > > 
> > > > > > > > > Jason
> > > > 
> > > > > > > > >
> > > > 
> > > > > > > > >
> > > > 
> > > > > > > > > On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto [orvaquim@gmail.com](mailto:orvaquim@gmail.com) \> \> \> \> \> \> \>\> \>\>\> wrote:
> > > > 
> > > > > > > > > > I have a dataelement collected at a Health Centre level (eg:
> > > > 
> > > > > > > > > > ats\_posfem)
> > > > 
> > > > > > > > > > in
> > > > 
> > > > > > > > > > one province and only in some particular districts. In unpredictible
> > > > 
> > > > > > > > > > future
> > > > 
> > > > > > > > > > at National level we will be collecting on every health centre.
> > > > 
> > > > > > > > > >
> > > > 
> > > > > > > > > > Now, my ignorance: How can I obtain the provincial total on Report
> > > > 
> > > > > > > > > > Table? I
> > > > 
> > > > > > > > > > have been battled with DHIS so far and nothing.
> > > > 
> > > > > > > > > >
> > > > 
> > > > > > > > > > Thanks guys
> > > > 
> > > > > > > > > > Caveman
> > > > 
> > > > > > > > > >
> > > > 
> > > > > > > > > >
> > > > 
> > > > > > > > > > * * *
> > > > 
> > > > > > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > > 
> > > > > > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > > > 
> > > > > > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > > 
> > > > > > > > > > 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:+260968395190
> > > > 
> > > > > > > >
> > > > 
> > > > > > > >
> > > > 
> > > > > > > >
> > > > 
> > > > > > > >
> > > > 
> > > > > > > > * * *
> > > > 
> > > > > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > > 
> > > > > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > > > 
> > > > > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > > 
> > > > > > > > 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:+260968395190
> > > > 
> > > > > 
> > > > 
> > > > > 
> > > > 
> > > > > 
> > > > 
> > > > > * * *
> > > > 
> > > > > Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > > 
> > > > > Post to : [dhis2-devs@lists.launchpad.net](mailto:dhis2-devs@lists.launchpad.net)
> > > > 
> > > > > Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)
> > > > 
> > > > > More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
> > > > 
> > > > > 
> > > > 
> > > > >
> 
> > >
> 
> >

---

<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:** [14 July 2010 07:03 UTC](https://community.dhis2.org/t/aggregating-values/8450/20 "2010-07-14T07:03:03Z")

</div>

Hi Caveman,

Can you please post a stack trace of your application (use  
pastebin.com) from startup until you start the datamart/report table  
process?

Do you have any external views that are linked to the report table you  
are attempting to aggregate?

Regards,  
Jason

> **···**
>
> On Wed, Jul 14, 2010 at 8:57 AM, Orvalho Augusto \<orvaquim@gmail.com\> wrote:
> 
> > And even do those resource tables on Postgres it does not aggregate.
> > 
> > Caveman
> > 
> > On Wed, Jul 14, 2010 at 6:35 AM, Orvalho Augusto \<orvaquim@gmail.com\> wrote:
> > 
> > > I have changed the database (from MySQL to Postgres)... and used DHIS  
> > > tools to export and import the data.
> > > 
> > > Well, those resource tables are being built in seconds on Postgres now. So  
> > > I think there is a bug with MySQL. And I want to help to correct it.
> > > 
> > > One verry bad consequence of not importing the dataelementids is: The  
> > > entering forms does not work no more. We have to redesign the forms after an  
> > > import.
> > > 
> > > Caveman
> > > 
> > > On Tue, Jul 13, 2010 at 10:28 PM, Orvalho Augusto \<orvaquim@gmail.com\> \>\> wrote:
> > > 
> > > > Ok.
> > > > 
> > > > On Tue, Jul 13, 2010 at 11:55 AM, Bob Jolliffe \<bobjolliffe@gmail.com\> \>\>\> wrote:
> > > > 
> > > > > On 13 July 2010 07:17, Orvalho Augusto \<orvaquim@gmail.com\> wrote:  
> > > > > \> I recompiled DHIS2.05 yesterday with help of a magician Jason  
> > > > > \> yesterday. And  
> > > > > \> the first thing I tried was this aggregation thing.  
> > > > > \>  
> > > > > \> So I started redoing the orgunitstructure. It gave the same error.  
> > > > > \>  
> > > > > \> While it was running I used the  
> > > > > \> show full processlist  
> > > > > \> to find out that it is a verry long insert to be run at once. I have  
> > > > > \> 1664  
> > > > > \> orgunits. So it sounds like indexing trouble.  
> > > > > \>  
> > > > > \> As there is a faith and "antimysql fight" I am moving my data to  
> > > > > \> postgres. I  
> > > > > \> have to do it manually.
> > > > > 
> > > > > There is no mysql fight. Just more people using postgres I guess.
> > > > > 
> > > > > \>  
> > > > > \> I have to do it manually because DHIS does not keep de ids fixed  
> > > > > \> (organisationunid, dataelementid etc). That affects our mapping data  
> > > > > \> for  
> > > > > \> data importation from other systems. So if someone could suggest a  
> > > > > \> good  
> > > > > \> strategy is welcomed.
> > > > > 
> > > > > How are you doing mapping data from other systems? If you tell me a  
> > > > > bit about these systems and the data they have maybe we can make a  
> > > > > plan 🙂
> > > > 
> > > > Basically these systems are:  
> > > > The dataset (the DHIS concept) is represented by a table (in EpiInfo  
> > > > 6.06, DBF or MSAccess) where each record should have a month (or week or  
> > > > quarter), year, province, district and health center columns - sometimes  
> > > > they place the name of the association or a special sector of the health  
> > > > center - then the other columns define a dataelement for DHIS.
> > > > 
> > > > So what we were doing is:  
> > > > 1. Migrate the data from anywhere (EpiInfo, MSAccess and DBF) to MySQL.
> > > > 
> > > > 2. On MySQL we have tables where the columns names of those tables  
> > > > mapping to the dataelementid (which Lars recommend not be like this)
> > > > 
> > > > 3. There is a script on Python made to run routinely to process this data  
> > > > and insert the new one on DHIS 2 tables.  
> > > > When I say routinely, I mean each month or whenever I want it.
> > > > 
> > > > The objective were to use solely DHIS for report and one day for data  
> > > > entry.
> > > > 
> > > > Caveman
> > > > 
> > > > > Cheers  
> > > > > Bob
> > > > > 
> > > > > \>  
> > > > > \> Thanks for your patience guys  
> > > > > \> Caveman  
> > > > > \>  
> > > > > \>  
> > > > > \>  
> > > > > \>  
> > > > > \>  
> > > > > \> 2010/7/12 Jason Pickering \<jason.p.pickering@gmail.com\>  
> > > > > \>\>  
> > > > > \>\> Hi Lars,  
> > > > > \>\> Well, yes and no. If the resource tables (namely orgunitstructure) is  
> > > > > \>\> not generated, it would not be possible to assign any orgunits to a  
> > > > > \>\> given data mart/report table? Likewise, if the orgunit levels have  
> > > > > \>\> not  
> > > > > \>\> been defined, it is also not possible to assign any orgunits to a  
> > > > > \>\> given report table/datamart, or perhaps I am just hallucinating ? I  
> > > > > \>\> have run into this problem several times actually when creating new  
> > > > > \>\> databases. Ideally, the orgunitstructure table should be regenerated  
> > > > > \>\> when there have been changes to the orgunit hierarchy and users  
> > > > > \>\> should  
> > > > > \>\> be warned that it is not possible to create a report table/datamart  
> > > > > \>\> without first defining orgunit level names.  
> > > > > \>\>  
> > > > > \>\> Regards,  
> > > > > \>\> Jason  
> > > > > \>\>  
> > > > > \>\>  
> > > > > \>\> 2010/7/12 Lars Helge Øverland \<larshelge@gmail.com\>:  
> > > > > \>\> \> Generating any of the resource tables don't affect the aggregation  
> > > > > \>\> \> /  
> > > > > \>\> \> report tables...  
> > > > > \>\> \>  
> > > > > \>\> \>  
> > > > > \>\> \>  
> > > > > \>\> \> On Sun, Jul 11, 2010 at 5:36 PM, Orvalho Augusto \>\>\>\> \>\> \> \<orvaquim@gmail.com\> \>\>\>\> \>\> \> wrote:  
> > > > > \>\> \>\> Thanks!!!  
> > > > > \>\> \>\>  
> > > > > \>\> \>\> Caveman  
> > > > > \>\> \>\>  
> > > > > \>\> \>\>  
> > > > > \>\> \>\> On Sun, Jul 11, 2010 at 5:03 PM, Jason Pickering \>\>\>\> \>\> \>\> \<jason.p.pickering@gmail.com\> wrote:  
> > > > > \>\> \>\>\>  
> > > > > \>\> \>\>\> A very common mistake is not having generated the resource  
> > > > > \>\> \>\>\> tables,  
> > > > > \>\> \>\>\> namely "orgunitstructure". Consult the documentation for more  
> > > > > \>\> \>\>\> details  
> > > > > \>\> \>\>\> on how to generate these tables.  
> > > > > \>\> \>\>\>  
> > > > > \>\> \>\>\> Regards,  
> > > > > \>\> \>\>\> Jason  
> > > > > \>\> \>\>\>  
> > > > > \>\> \>\>\>  
> > > > > \>\> \>\>\> On Sun, Jul 11, 2010 at 4:27 PM, Orvalho Augusto \>\>\>\> \>\> \>\>\> \<orvaquim@gmail.com\> \>\>\>\> \>\> \>\>\> wrote:  
> > > > > \>\> \>\>\> \> I have a dataelement collected at a Health Centre level (eg:  
> > > > > \>\> \>\>\> \> ats\_posfem)  
> > > > > \>\> \>\>\> \> in  
> > > > > \>\> \>\>\> \> one province and only in some particular districts. In  
> > > > > \>\> \>\>\> \> unpredictible  
> > > > > \>\> \>\>\> \> future  
> > > > > \>\> \>\>\> \> at National level we will be collecting on every health centre.  
> > > > > \>\> \>\>\> \>  
> > > > > \>\> \>\>\> \> Now, my ignorance: How can I obtain the provincial total on  
> > > > > \>\> \>\>\> \> Report  
> > > > > \>\> \>\>\> \> Table? I  
> > > > > \>\> \>\>\> \> have been battled with DHIS so far and nothing.  
> > > > > \>\> \>\>\> \>  
> > > > > \>\> \>\>\> \> Thanks guys  
> > > > > \>\> \>\>\> \> Caveman  
> > > > > \>\> \>\>\> \>  
> > > > > \>\> \>\>\> \>  
> > > > > \>\> \>\>\> \> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > > > \>\> \>\>\> \> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > > > \>\> \>\>\> \> Post to : dhis2-devs@lists.launchpad.net  
> > > > > \>\> \>\>\> \> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > > > \>\> \>\>\> \> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > > > \>\> \>\>\> \>  
> > > > > \>\> \>\>\> \>  
> > > > > \>\> \>\>\>  
> > > > > \>\> \>\>\>  
> > > > > \>\> \>\>\>  
> > > > > \>\> \>\>\> --  
> > > > > \>\> \>\>\> --  
> > > > > \>\> \>\>\> Jason P. Pickering  
> > > > > \>\> \>\>\> email: jason.p.pickering@gmail.com  
> > > > > \>\> \>\>\> tel:+260968395190  
> > > > > \>\> \>\>  
> > > > > \>\> \>\>  
> > > > > \>\> \>\>  
> > > > > \>\> \>\>  
> > > > > \>\> \>\> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > > > \>\> \>\> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > > > \>\> \>\> Post to : dhis2-devs@lists.launchpad.net  
> > > > > \>\> \>\> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > > > \>\> \>\> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > > > \>\> \>\>  
> > > > > \>\> \>\>  
> > > > > \>\> \>  
> > > > > \>\>  
> > > > > \>\>  
> > > > > \>\>  
> > > > > \>\> --  
> > > > > \>\> --  
> > > > > \>\> Jason P. Pickering  
> > > > > \>\> email: jason.p.pickering@gmail.com  
> > > > > \>\> tel:+260968395190  
> > > > > \>  
> > > > > \>  
> > > > > \>  
> > > > > \> \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
> > > > > \> Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > > > \> Post to : dhis2-devs@lists.launchpad.net  
> > > > > \> Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/~dhis2-devs)  
> > > > > \> More help : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)  
> > > > > \>  
> > > > > \>
> 
> --  
> --  
> Jason P. Pickering  
> email: jason.p.pickering@gmail.com  
> tel:+260968395190

[Next page](https://community.dhis2.org/t/aggregating-values/8450.md?page=2)