dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

Thanks Ola.  I'll pass on this report to the openoffice team at sun and see
if they have any comment.

Regards
Bob

2010/1/5 Ola Hodne Titlestad <olatitle@xxxxxxxxx>

> Hi,
>
> Sorry for the late feedback on this.
>
> I've done some testing of datapilot using the new openoffice 3.2 RC1
> release on Windows XP with java 1.6.16:
>
> *Large datasets are problematic
> *The main issue I have encountered is the lack of support for large
> datasets. In DHIS we typically have data sets of more than 100 000 rows,
> sometimes more than a million rows. With openoffice I ended up having lots
> of java heap space errors with the larger datasets when loading the data
> into the datapilot (using jdbc connection to postgres) and did not manage to
> create them at all. Datasets with up to 20 000 rows seem to work ok,
> although a bit slower than what I am used to with Excel, and the 40 000 rows
> dataset I managed to set up is extremely slow when doing any manipulation. I
> can see that the wiki page on performance improvement on larger datasets
> that Bob linked to refers to a test dataset of 5000 rows, which is a very
> small dataset in the context of DHIS.
>
> For comparison I created Excel pivot tables for the 40 000 row
> IndicatorsOU3 table and a 300 000 row pivot for RoutineDataOU3 and these
> work just fine and much faster than the datapilots. This is especially
> noticeable when editing the pivot table layouts (start in data pilot and
> pivot table wizard in excel), but also when doing simpler manipulation of
> the tables such as moving a field from row to column or changing a filter.
>
> *Indicator values as calculated fields are not supported?
> *Another issue I have come across is the lack of support to set up what in
> Excel is called calculated fields,formulas based on other pivot fields. This
> is needed when dealing with indicators as the indicator value field is a
> calulated field set up as the formula numxfactor/denominatorvalue. Such a
> formula is needed when dealing with aggregation of percentages as numerators
> and denominators need to be summed up separately and not by simply averaging
> the percentage (the value) of all the orgunit children. It is possible that
> this is supported, but I haven't found it yet.
>
> *Captions for pivot fields are not supported?
> *I cannot find a place to change the caption of the pivot fields. We often
> use generic column names in the database such as orgunit1, orgunit2, but in
> a pivot table it looks much better if these names are replaced with more
> localised names e.g. country, district, chiefdom in the case of Sierra
> Leone.
>
> *General comments:
> *- Openoffice does not seem to store the data inside the spreadsheet file
> like Excel does and therefore will always need a functioning database
> connection. Excel only needs the database connection on refresh.
>
> - You need to create a new openoffice database using a jdbc connection to
> the existing postgres database.
>
> - To connect to the postgres database using jdbc you need to add the
> postgres jdbc driver to the java classpath in openoffice:
> Tools->Options->Java->Classpath
>
> - This openoffice database needs to be registered in openoffice in order to
> be available to the datapilot. This is an option during the setup of a new
> database, or you can do it later by opening Calc and press F4, then right
> click inside the Bibliography navigator window and select Registered
> Databases. Then click New and look up your openoffice database.
>
> - When creating the datapilot the pivot view queries in the dhis database
> should be treated as sheets (default) and not queries
> (all views in postgres are automatically displayed as tables in the
> openoffice database)
>
> Here are the files I have used:
> http://folk.uio.no/olati/filer/dhis2_sl_data_pilots.ods
> http://folk.uio.no/olati/filer/dhis2_sl_pivots.zip
> http://folk.uio.no/olati/filer/dhis2_sl.backup
>
> http://208.76.222.114/confluence/download/attachments/8096/PivotSourceViewsOU2-5.sql
>
> - the excel file can be used right away, but a refresh will needs an odbc
> connection 'dhis2_sl' to a database 'dhis2_sl' with the pivotsource views
> - the openoffice file will need a database dhis2_sl registered in
> openoffice with the pivotsource views
>
> Ola
> ----------
>
> 2009/12/11 Knut Staring <knutst@xxxxxxxxx>
>
> Interetesting .... with a release candidate coming in just one week:
>> 2009-12-17
>>
>>
>> On Fri, Dec 11, 2009 at 5:57 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>wrote:
>>
>>> Looks like 3.2 is the "performance" targetted version.  [
>>> http://wiki.services.openoffice.org/wiki/Features]  Including DataPilot.
>>>
>>>
>>> 2009/12/11 Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>>>
>>>> Sure.  This is just a background issue.  The OOo pivot table performance
>>>> has been a mess for some time now.  Just want to take advantage of the
>>>> opportunity to feed them some real requirements.
>>>>
>>>> Mind you I see IBM's Chinese dev team have been putting some work into
>>>> this already (
>>>> http://wiki.services.openoffice.org/wiki/Calc/Proposal_DataPilot_byIBM).
>>>> I wonder did this optimisation make itself into OOo yet?  I guess we'll find
>>>> out soon.
>>>>
>>>> Bob.
>>>>
>>>> 2009/12/11 Ola Hodne Titlestad <olatitle@xxxxxxxxx>
>>>>
>>>> Hi Bob,
>>>>>
>>>>> Can give you a data pilot (for the latest version of OO) with lots of
>>>>> data by Monday.
>>>>> Would that be soon enough?
>>>>>
>>>>> Ola
>>>>> _______
>>>>>
>>>>> 2009/12/11 Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>>>>>
>>>>>> Hi all
>>>>>>
>>>>>> I am in touch with Sun engineers on OpenOffice Calc regarding
>>>>>> datapilot performance.  I understand there have been performance issues with
>>>>>> pivot tables - in fact it is a well known openoffice problem.  Can anyone
>>>>>> send me a reasonably substantive excel worksheet with pivot tables which I
>>>>>> can send to Sun to see if we can't motivate improvement of openoffice calc
>>>>>> in this area.
>>>>>>
>>>>>> Regards
>>>>>> Bob
>>>>>>
>>>>>> ---------- Forwarded message ----------
>>>>>> From: Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>>>>>> Date: 2009/12/11
>>>>>> Subject: Re: data pilot
>>>>>> To: ASLAM RAFFEE <Aslam.Raffee@xxxxxxx>
>>>>>> Cc: Jørn Braa <jornbraa@xxxxxxxxx>, Stephan Schaefer <
>>>>>> Stephan.Schaefer@xxxxxxx>, Michael Brauer - Sun Germany - ham02 -
>>>>>> Hamburg <Michael.Brauer@xxxxxxx>
>>>>>>
>>>>>>
>>>>>> Sure.  Thanks.  I will try and get hold a realistic sample from the
>>>>>> field rather than a concoction.
>>>>>>
>>>>>> Regards
>>>>>> Bob
>>>>>>
>>>>>> 2009/12/11 ASLAM RAFFEE <Aslam.Raffee@xxxxxxx>
>>>>>>
>>>>>>> Bob,
>>>>>>>
>>>>>>>
>>>>>>> Could you provide Stephan the information needed so that he can
>>>>>>> assist.
>>>>>>>
>>>>>>>
>>>>>>> Regards,
>>>>>>>
>>>>>>> Aslam Raffee,
>>>>>>> Government Strategy and Global Communities, EMEA
>>>>>>> Sun Microsystems, Inc.
>>>>>>> Waterfall Edge, Waterfall Park, Bekker Street
>>>>>>> Midrand 1685 South Africa
>>>>>>> Phone +27 11 256-6360
>>>>>>> Mobile +27 82 312 6782
>>>>>>> Email aslam.raffee@xxxxxxx
>>>>>>> http://www.sun.com
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On 11 Dec 2009, at 2:42 PM, Michael Brauer - Sun Germany - ham02 -
>>>>>>> Hamburg wrote:
>>>>>>>
>>>>>>> > Hi Aslam,
>>>>>>> >
>>>>>>> > I have set Stephan Schäfer on copy who leads our OOo Calc team.
>>>>>>> >
>>>>>>> > Of cause we may have a look at the problem. But we need to know the
>>>>>>> version of OOo that is in use and one ore more sample documents that don't
>>>>>>> work as expected.
>>>>>>> >
>>>>>>> > Best regards
>>>>>>> >
>>>>>>> > Michael
>>>>>>> >
>>>>>>> > Am 11.12.09 10:44, ASLAM RAFFEE schrieb:
>>>>>>> >> Dear Michael,
>>>>>>> >> I am in discussions with Bob Jolliffe of the Health Information
>>>>>>> Systems Project (http://www.hisp.org/) on how Sun can add value to
>>>>>>> this project. One of the issues that has come up is that the project would
>>>>>>> like to use open office and ODF. Due to Data pilot performance issues (keeps
>>>>>>> crashing) they are forced to use excel pivot tables. Are we able to look
>>>>>>> into this problem and assist with the data pilot issues.
>>>>>>> >> Regards,
>>>>>>> >> Aslam Raffee,
>>>>>>> >> Government Strategy and Global Communities, EMEA
>>>>>>> >> Sun Microsystems, Inc.
>>>>>>> >> Waterfall Edge, Waterfall Park, Bekker Street
>>>>>>> >> Midrand 1685 South Africa
>>>>>>> >> Phone +27 11 256-6360
>>>>>>> >> Mobile +27 82 312 6782
>>>>>>> >> Email aslam.raffee@xxxxxxx
>>>>>>> >> http://www.sun.com
>>>>>>> >
>>>>>>> >
>>>>>>> > --
>>>>>>> > Michael Brauer, Technical Architect Software Engineering
>>>>>>> > StarOffice/OpenOffice.org
>>>>>>> > Sun Microsystems GmbH             Nagelsweg 55
>>>>>>> > D-20097 Hamburg, Germany          michael.brauer@xxxxxxx
>>>>>>> > http://sun.com/staroffice         +49 40 23646 500
>>>>>>> > http://blogs.sun.com/GullFOSS
>>>>>>> >
>>>>>>> > Sitz der Gesellschaft: Sun Microsystems GmbH, Sonnenallee 1,
>>>>>>> >          D-85551 Kirchheim-Heimstetten
>>>>>>> > Amtsgericht Muenchen: HRB 161028
>>>>>>> > Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
>>>>>>> > Vorsitzender des Aufsichtsrates: Martin Haering
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Mailing list: https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>>>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>>
>> --
>> Cheers,
>> Knut Staring
>>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
> More help   : https://help.launchpad.net/ListHelp
>
>