Limits for report table

Tom,

Copying this to our DHIS 2 development list, as it is something several people would like to be aware of.

  1. As we saw today, removing the restriction on dataelement shortname length works, but only up to 40 characters if you want to crosstab on them.

  2. Unfortunately, because of the limitiations of the underlying Postgres database, the datamart/report table generation cannot handle more than 1233 data elements. The GUI should probably check this and give you feedback, instead of just creating a hanging process as seems to be the case currently.

There are two ways to deal with this:

a) We can divide the operation into three parts (since you have 2813 data elements). Basically, just select a couple of your datasets at a time. And then the CSV files must be joined somehow. This probably means that OrgUnits (200 countries) should be on crosstabbed (to get the same columns in all three files)

b) We can create a separate “Export all” button, which does not use the crosstab functionality, but is basically just a dump of our datavalue table with names of orgunits, periods (years), and data elements in CSV format.

Cheers,

Knut

Yes, we could create a regular datavalue export in CSV format, will have a look.

···

On Tue, May 5, 2009 at 4:35 PM, Knut Staring knutst@gmail.com wrote:

Tom,

Copying this to our DHIS 2 development list, as it is something several people would like to be aware of.

  1. As we saw today, removing the restriction on dataelement shortname length works, but only up to 40 characters if you want to crosstab on them.
  1. Unfortunately, because of the limitiations of the underlying Postgres database, the datamart/report table generation cannot handle more than 1233 data elements. The GUI should probably check this and give you feedback, instead of just creating a hanging process as seems to be the case currently.

There are two ways to deal with this:

a) We can divide the operation into three parts (since you have 2813 data elements). Basically, just select a couple of your datasets at a time. And then the CSV files must be joined somehow. This probably means that OrgUnits (200 countries) should be on crosstabbed (to get the same columns in all three files)

b) We can create a separate “Export all” button, which does not use the crosstab functionality, but is basically just a dump of our datavalue table with names of orgunits, periods (years), and data elements in CSV format.

Yes, we could create a regular datavalue export in CSV format, will have a look.

Would id’s for dataelement, period, source be sufficient (joined in later) or do you need names?

Pretty sure we would need the names (and not need the IDs), as this is going to be imported into other applications, such as R and STATA.

Knut

···

2009/5/6 Lars Helge Øverland larshelge@gmail.com

Yes, we could create a regular datavalue export in CSV format, will have a look.

Would id’s for dataelement, period, source be sufficient (joined in later) or do you need names?

Hi there. I was just sitting here talking to Knut about this, and the
requirements for why someone would need such a large crosstab table
eludes me, but anyway, perhaps it is a valid requirement. Seems much
better to do this one the client side.

Anyway, what I had suggested to him was the possible inclusion of a
more generic functionlality, that would allow people with
administrative privileges to define a custom view, to their liking,
and then dump it to a particular fomat, like CSV, or use it for some
other purpose, like the creation of a report. For instance, in some of
the reports that I was working on, I need some other "view" for
support of the table. One gives me a listing of districts and which
provinces they belong to. I have been requested by some users to
provide a listing of districts and provinces, which I could implement
as a simple view, save it in a table in the DHIS2 database, and then
allow them to download this.

In essence what I would like to see would be the ability of an
administrator to add a custom SQL SELECT statement , have that view
saved in a table someplace, and then exectued when needed.
Essentially, this is what is being done with the "Create report table"
interface, which could really equally be accomplished through the use
of stored procedure to materialize a view.

This sort of opens up a pandora's box though, as it sort of
circumvents the security model of DHIS. Only administrators would be
allowed to add statements, and then would need to assign specific
security privileges to user groups to allow them to execute them.

We can discuss this more once you arrive in Geneva. :slight_smile:

Regards,
Jason

···

2009/5/6 Lars Helge Øverland <larshelge@gmail.com>:

On Tue, May 5, 2009 at 4:35 PM, Knut Staring <knutst@gmail.com> wrote:

Tom,
Copying this to our DHIS 2 development list, as it is something several
people would like to be aware of.
1) As we saw today, removing the restriction on dataelement shortname
length works, but only up to 40 characters if you want to crosstab on them.
2) Unfortunately, because of the limitiations of the underlying Postgres
database, the datamart/report table generation cannot handle more than 1233
data elements. The GUI should probably check this and give you feedback,
instead of just creating a hanging process as seems to be the case
currently.
There are two ways to deal with this:
a) We can divide the operation into three parts (since you have 2813 data
elements). Basically, just select a couple of your datasets at a time. And
then the CSV files must be joined somehow. This probably means that OrgUnits
(200 countries) should be on crosstabbed (to get the same columns in all
three files)
b) We can create a separate "Export all" button, which does not use the
crosstab functionality, but is basically just a dump of our datavalue table
with names of orgunits, periods (years), and data elements in CSV format.

Yes, we could create a regular datavalue export in CSV format, will have a
look.

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp