Thanks all for the quick supportive advices,i think if we do 1) aggregation
level District and also removing zero values will help.
Dear Ola please publish this section , it will be helpfull to us.
Thanks.
________________________________
Muhire Andrew
HMIS/Ministry of Health
First say to yourself what you would be; and then do what you have to do..
________________________________
From: Ola Hodne Titlestad <olati@ifi.uio.no>
To: Jason Pickering <jason.p.pickering@gmail.com>
Cc: Muhire Andrew <muhireandrew@yahoo.com>; Lars Helge Øverland
<larshelge@gmail.com>; Bob Jolliffe <bobjolliffe@gmail.com>; Knut Staring
<knutst@gmail.com>; "dhis2-users@lists.launchpad.net"
<dhis2-users@lists.launchpad.net>
Sent: Monday, May 7, 2012 10:07 AM
Subject: Re: [Dhis2-users] Mydatamart issue?
Hi Andrew,
Do you know how many rows that is returned from your pivot source query?
The mydatmart tool is designed for what Jason describes in option 1) in his
email.
Also note that we require Excel 2007 or newer for pivot tables to work
properly with mydatamart (on metatdata updates and re-linking of mydatamart
files and excel).
The user selects its orgunit and then the analysis level (the lowest level
of the orgunit hierarchy needed for analysis). Typically you will not need
to look at facility level for general data analysis at the national level,
and in stead you can probably pick district level.
At the districts the users can still select facility as the analysis level
and download facility level data to their local mydatamart files and load
that into Excel.
To facilitate this difference in analysis level in mydatamart/Excel you
typically will need to use at least two different Excel files (pivot table
template files), one for national level users with pivot tables that pull
district level data (e.g. the pivotsource_routinedata_ou2_m query), and
another excel file for district users with facility level data (using
pivotsource_routinedata_ou4_m - where facility level = level 4).
Note that Excel will automatically aggregate the data for you in the pivot
tables, so you can easily look at district totals also in the pivot table
with facility level data. The difference is how deep you can drill down,
what the lowest level of analysis is; the district-level pivot table will
never be able to show facility data.
When you have data for more than 1 year you can also consider breaking up
the pivot table by either calendar or financial year by inserting custom sql
queries in your mydatamart file that filter data rows on the periods.
In my experience the pivot tables start to get really slow when you get
above 7-800 000 rows, but that of course also depends on your hardware.
I have promised to write up a section on setting up pivot tables with
mydatamart to the user documentation, so look out for commits to the
documentation branch in the next few weeks if you're interested in more
details on this topic.
And. to make your life a lot easier, I would consider getting rid of most of
those zero values Lars just referred to.... you probably only require that
for a few selected data elements.
Ola
-----
----------------------------------
Ola Hodne Titlestad (Mr)
HISP
Department of Informatics
University of Oslo
Mobile: +47 48069736
Home address: Vetlandsvn. 95B, 0685 Oslo, Norway. Googlemaps link
On 7 May 2012 09:32, Jason Pickering <jason.p.pickering@gmail.com> wrote:
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@yahoo.com> > 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: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help