On the topic of pivot tables, the open source AribaWeb framework seems to come with them as a component: http://aribaweb.org/
Knut On Wed, Jan 6, 2010 at 11:09 AM, <[email protected]> wrote: > Thanks Ola, > this was interesting reading. I'm quite concerned about the need for > openoffice pivots to link directly to the database for any action, even as > simple as filtering. That means you would, for DHIS1.4, need DHIS on your > computer with the full database. For DHIS2 you need either that or a > stable connection to the server database. Many users will not have this. > Typically in Sierra Leone, with Excel, we refresh a pivot table and > distribute that file to end-users, which would then always work regardless > of connection. If the "always-refresh" feature of openoffice is the way it > is done, this is a serious impediment to share information with > less-connected users. > > Does anyone know if openoffice spreadsheets can be set to store the values > like Excel? > > Johan > > > 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 > > > > > > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-devs > Post to : [email protected] > Unsubscribe : https://launchpad.net/~dhis2-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
