dhis2-users team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Hi Andrew,
I do not know all of the circumstances of your data, but in general,
Excel is limited by a certain number of rows and columns. I think
Excel 2003 is limited to around 65,000 rows and Excel 2007 to over 1
million rows. PivotTables are limited by the amount of available
memory on your machine. If your dataset has more than this, you can do
a few things.

1) Use a higher level of aggregation. Instead of pulling data at the
facility level for the entire country, use the district level (or
something higher) this will decrease the number of rows which must be
pulled into Excel. Similarly, looking at quarterly data, versus
monthly data will decrease the amount of data which needs to be
retrieved by Excel.

2) Filter the rows which are pulled from MyDatamart with some type of
filter in the pivot source query. This normally is done using a filter
on either the _dataelementgroupsetstructure or
_indicatorgroupsetstructure table of the MyDatamart database. This
will allow you to for instance, pull only PMTCT indicators into an
Excel sheet, without pulling unrelated indicators, and then having to
filter them in the Excel table. You will need to modify and install
some new SQL views in MyDatamart to use this approach.

3) Consider using other tools, such as R/SPSS/Stata for analysis which
do not have such limitations.

We regularly analyze rather largish datasets (for Excel anyway) with
several million rows of data, using these approaches. The best way
however is just to bring the data which you need for a specific
PivotTable, without returning everything with may not be relevant for
a particular analysis.

Best regards,
Jason



On Mon, May 7, 2012 at 9:16 AM, Muhire Andrew <muhireandrew@xxxxxxxxx> wrote:
> Hi to all,
>
> In Rwanda we have 4 months data in our DHIS2 database, And downloading data
> for the whole 4 months, Excel gives an overloading error or can even
> burst.......................Any advice on using mydatamart?   how are you
> running it over there? ......This issue happens when we download aggregated
> data from facility level................I would suggest to have a selection
> box in mydatamart where a user can select only the data elements or
> Indicators to be analysed insteady of having the whole staff.
>
> In Rwanda, users are used on row data from MYDATAMART,now 4 months its
> impossible to download to excel at the central level!!!! what will happen in
> upcoming months?
>
>
>
>
>
>
> ________________________________
>
>
> Muhire Andrew
> HMIS/Ministry of Health
> First say to yourself what you would be; and then do what you have to do..
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>