dhis2-devs team mailing list archive

[johansa@xxxxxxxxxx]

Bob,

Ola gave a good description here. I'm working on a very detailed guide to
(MS Excel) pivot table creation, expanding the one Ola has put on
launchpad. Hopefully it will be ready in a few days, depending on how much
time I get to spend on it.

Calle, currently here in Geneva, says that OO was tested a while ago with
a 15-20 MB pivot from South Africa (that's small for them, regular pivot
size will be up to 200 MB). It did not work very well (did not start up in
15 minutes). Smaller pivots might work, but it seems like OO doesnt handle
load very well (and splitting the data up into 20 files takes away the
whole point of pivot tables)

Johan

> Hi Bob,
>
> I think maybe Johan can give some more input and possibly also help you to
> test this as he as lots of experience from using Excel pivots with DHIS.
>
> Some comments and backgrouns on the use of pivots with DHIS data:
>
> Normally a pivot table contains all data (data elements or indicators) for
> all months for a certain orgunit level.
> The reason for splitting them up based on orgunit level is to get much
> faster access to data at higher levels using the aggregated data as the
> data
> source base level for the pivot in stead of the lowest level (values in
> the
> datavalue table).
>
> With large amounts of data over many years it also makes sense to split
> the
> data into pivot tables according to calendar or financial years.
>
> Data element and indicator groups are often then used as filters within a
> pivot table to organise the data. With very large datasets you could also
> split up pivots based on data elements/indicator groups with one group per
> table, which you can set up in a view in the DHIS2 database.
>
> So based on this a datamart export for the SA sample db could be all data
> elements + all indicators + all districts + all district municipalities
> (OU
> level 4).
>
> This would then require 4 pivot tables:
> - routine data (data elements) for OU3
> - indicator data for OU3
> - routine data for OU4
> - indicator data for OU4
>
> All these values will be put in the two datamart tables called
> aggregateddatavalue and aggregatedindicatorvalue.
>
> Each of these pivot tables will have their corresponding view (with
> descriptive self-explanatory names) in the file Johan mentioned:
> http://208.76.222.114/confluence/download/attachments/8096/PivotSourceViewsOU2-5.sql
>
> The views have been set up on the DHIS 2 side to make the pivot table
> design
> easier. The views (queries) in the DHIS database combine the datamart
> tables
> (either data values or indicator values) with the orgunit hierarchy
> (orgunitstructure table) and replace id with readable names as
> identifiers.
> Groups and groupsets can also be joined in and filters on years or groups
> created in where clauses.
>
> In Excel the data source for the pivot table will then simply be an select
> *
> from pivot_view_XX which simplifies things a lot for the implementers or
> superusers defining their own pivot tables. Of course alos pivot table
> designs can be reused across contexts by manually editing the data
> connections in excel to replace database names, server URLs, and login
> info.
>
> A typical setup we have applied in both Sierra Leone and Tanzania recently
> is a district office with 1 DHIS server (preferrably running on linux) and
> multiple Windows+Office workstations in a local network. Each windows
> machine can then set up an odbc connection to the database running on the
> server and then use Excel pivot tables that retrieves data directly from
> the
> server. Excel keeps all its data in memory (in the excel file) and only
> requries server access when pivots are refreshed (typically once a month)
> so
> this works out quite well.
>
> best regards,
> Ola Hodne Titlestad
> HISP
> University of Oslo
>
>
> On Fri, Apr 24, 2009 at 1:13 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
> wrote:
>
>> Its very hard for me to test this functionality without a better
>> understanding of the data :-(   But I will give it a go.
>>
>> Currently I have a dummy South African data set from Lars here:
>> http://folk.uio.no/larshelg/files/dhis2sample.zip
>>
>> Can anyone suggest a meaningful datamart to set up with this
>> particular data to test with?
>>
>> Relating to a previous discussion on testing, is there a standard
>> dummy dataset which we can be sure everyone has access to and which is
>> reasonably representative of useful data?  Is this it?
>>
>> Regards
>> Bob
>>
>> 2009/4/24 Ola Hodne Titlestad <olati@xxxxxxxxxx>:
>> > Hi,
>> >
>> > We have tried to get pivot tables (data pilots) in OpenOffice to work
>> with
>> > DHIS data, but so far our experiences are that it doesn't work out
>> well.
>> >
>> > The main issue is performance as the data pilot operations (pivoting,
>> > zooming in/out) become very slow with average amounts of DHIS data.
>> >
>> > Setting them up works well and is just like in Excel (when you have
>> first
>> > registered the database).
>> >
>> > Personally I have not tested this for at least a year so maybe things
>> have
>> > changed, but at least I haven't heard anything about improvements in
>> this
>> > regard.
>> >
>> > Would be great if someone could test data pilot performance again on
>> the
>> > latest OO version and report back to the list.
>> >
>> > So far, properly working desktop pivot tables with DHIS data have been
>> > available only to MS Office users.
>> >
>> > The web based pivots (accessible from Reports menu in DHIS 2) are very
>> basic
>> > compared to the functionality available in Excel.
>> >
>> > best regards,
>> > Ola Hodne Titlestad
>> > HISP
>> > University of Oslo
>> >
>> >
>> > On Fri, Apr 24, 2009 at 12:46 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
>> > wrote:
>> >>
>> >> And here's a simple tutorial on doing pivot tables (Data Pilot) in
>> >> openoffice calc: http://www.learnopenoffice.org/CalcTutorial33.htm
>> >>
>> >> Regards
>> >> Bob
>> >>
>> >> 2009/4/24 Ola Hodne Titlestad <olati@xxxxxxxxxx>:
>> >> > If you want to play around with these views and make changes
>> without
>> >> > having
>> >> > to write the SQL I recommend using OpenOffice query editor which is
>> just
>> >> > like the interface in MS Access.
>> >> > You can register (link to) your postgres database in OpenOffice and
>> >> > create
>> >> > queries there.
>> >> >
>> >> >
>> >> > best regards,
>> >> > Ola Hodne Titlestad
>> >> > HISP
>> >> > University of Oslo
>> >> >
>> >> >
>> >> > On Fri, Apr 24, 2009 at 12:34 PM, Ola Hodne Titlestad <
>> olati@xxxxxxxxxx>
>> >> > wrote:
>> >> >>
>> >> >> Hi Johan,
>> >> >>
>> >> >> You're right, there is a bug there. I can see I have fixed it
>> locally,
>> >> >> but
>> >> >> didn't update the attachment on the wiki, sorry.
>> >> >>
>> >> >> Will upload and replace with my new version now.
>> >> >>
>> >> >> best regards,
>> >> >> Ola Hodne Titlestad
>> >> >> HISP
>> >> >> University of Oslo
>> >> >>
>> >> >>
>> >> >> On Fri, Apr 24, 2009 at 11:53 AM, <johansa@xxxxxxxxxx> wrote:
>> >> >>>
>> >> >>> Hi, I've downloaded and run the SQL creating views found here:
>> >> >>> https://answers.launchpad.net/dhis2/+faq/371
>> >> >>>
>> >> >>> I'm trying to understand the SQL here. Not an expert, but I don't
>> see
>> >> >>> how
>> >> >>> the indicatorgroup is retrieved by the following code, for
>> indicators
>> >> >>> OU2.
>> >> >>>
>> >> >>> I see that indicatorgroupmembers i mentioned in the FROM and
>> WHERE
>> >> >>> statements, but not in the SELECT. What should I add to include
>> the
>> >> >>> groups, which I want to pull through to the pivot tables? If
>> somehow
>> >> >>> the
>> >> >>> indicatorgroups are retrieved by this view, what SQL would I need
>> to
>> >> >>> add
>> >> >>> in the pivottable (using Pivot Play)
>> >> >>>
>> >> >>> Johan
>> >> >>>
>> >> >>>
>> >> >>> CREATE OR REPLACE VIEW pivotsource_indicator_ou2 AS
>> >> >>>
>> >> >>> SELECT indicator.name AS indicator, organisationunit_1.name AS
>> >> >>> orgunit1,
>> >> >>> organisationunit_1.shortname AS ou1, organisationunit_2.name AS
>> >> >>> orgunit2,
>> >> >>> organisationunit_2.shortname AS ou2, period.periodid,
>> periodtype.name
>> >> >>> AS
>> >> >>> periodtype, to_char(period.startdate::timestamp with time zone,
>> >> >>> 'YYYY'::text) AS year, to_char(period.startdate::timestamp with
>> time
>> >> >>> zone,
>> >> >>> 'Mon'::text) AS month, (rtrim(to_char(period.startdate::timestamp
>> with
>> >> >>> time zone, 'Mon'::text)) || '-'::text) ||
>> >> >>> to_char(period.startdate::timestamp with time zone, 'YY'::text)
>> AS
>> >> >>> period,
>> >> >>> aggregatedindicatorvalue.factor *
>> >> >>> aggregatedindicatorvalue.numeratorvalue
>> >> >>> AS numxfactor, aggregatedindicatorvalue.denominatorvalue,
>> >> >>> aggregatedindicatorvalue.annualized,
>> aggregatedindicatorvalue.level
>> >> >>>
>> >> >>> FROM indicator indicator, indicatorgroupmembers
>> indicatorgroupmembers,
>> >> >>> indicatorgroup indicatorgroup, organisationunit
>> organisationunit_1,
>> >> >>> organisationunit organisationunit_2, period period, periodtype
>> >> >>> periodtype,
>> >> >>> aggregatedindicatorvalue aggregatedindicatorvalue,
>> orgunitstructure
>> >> >>> orgunitstructure
>> >> >>>
>> >> >>> WHERE indicator.indicatorid =
>> aggregatedindicatorvalue.indicatorid
>> AND
>> >> >>> indicatorgroupmembers.indicatorid = indicator.indicatorid AND
>> >> >>> indicatorgroupmembers.indicatorgroupid =
>> >> >>> indicatorgroup.indicatorgroupid
>> >> >>> AND period.periodid = aggregatedindicatorvalue.periodid AND
>> >> >>> period.periodtypeid = periodtype.periodtypeid AND
>> >> >>> periodtype.name::text =
>> >> >>> 'Monthly'::text AND orgunitstructure.organisationunitid =
>> >> >>> aggregatedindicatorvalue.organisationunitid AND
>> >> >>> orgunitstructure.idlevel1
>> >> >>> = organisationunit_1.organisationunitid AND
>> orgunitstructure.idlevel2
>> >> >>> =
>> >> >>> organisationunit_2.organisationunitid AND
>> >> >>> aggregatedindicatorvalue.level
>> >> >>> =
>> >> >>> 2
>> >> >>>
>> >> >>> ORDER BY period.startdate;
>> >> >>>
>> >> >>> ALTER TABLE pivotsource_indicator_ou2 OWNER TO dhis;
>> >> >>>
>> >> >>>
>> >> >>> _______________________________________________
>> >> >>> 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
>> >> >
>> >> >
>> >
>> >
>>
>