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 <[email protected]> > Interetesting .... with a release candidate coming in just one week: > 2009-12-17 > > > On Fri, Dec 11, 2009 at 5:57 PM, Bob Jolliffe <[email protected]>wrote: > >> Looks like 3.2 is the "performance" targetted version. [ >> http://wiki.services.openoffice.org/wiki/Features] Including DataPilot. >> >> >> 2009/12/11 Bob Jolliffe <[email protected]> >> >>> 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 <[email protected]> >>> >>> 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 <[email protected]> >>>> >>>>> 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 <[email protected]> >>>>> Date: 2009/12/11 >>>>> Subject: Re: data pilot >>>>> To: ASLAM RAFFEE <[email protected]> >>>>> Cc: Jørn Braa <[email protected]>, Stephan Schaefer < >>>>> [email protected]>, Michael Brauer - Sun Germany - ham02 - >>>>> Hamburg <[email protected]> >>>>> >>>>> >>>>> 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 <[email protected]> >>>>> >>>>>> 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 [email protected] >>>>>> 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 [email protected] >>>>>> >> http://www.sun.com >>>>>> > >>>>>> > >>>>>> > -- >>>>>> > Michael Brauer, Technical Architect Software Engineering >>>>>> > StarOffice/OpenOffice.org >>>>>> > Sun Microsystems GmbH Nagelsweg 55 >>>>>> > D-20097 Hamburg, Germany [email protected] >>>>>> > 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 : [email protected] >>>>> 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 : [email protected] >> 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 Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
