dhis2-users team mailing list archive

[Muhire Andrew <muhireandrew@xxxxxxxxx>]

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@xxxxxxxxxx>
To: Jason Pickering <jason.p.pickering@xxxxxxxxx> 
Cc: Muhire Andrew <muhireandrew@xxxxxxxxx>; Lars Helge Øverland <larshelge@xxxxxxxxx>; Bob Jolliffe <bobjolliffe@xxxxxxxxx>; Knut Staring <knutst@xxxxxxxxx>; "dhis2-users@xxxxxxxxxxxxxxxxxxx" <dhis2-users@xxxxxxxxxxxxxxxxxxx> 
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@xxxxxxxxx> 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@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
>>
>