dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

On Fri, Apr 24, 2009 at 1:59 PM, Ola Hodne Titlestad <olati@xxxxxxxxxx>wrote:

> 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).
>

Small correction: All district municipalities (OU4) + all facilities (OU5)
is probably better as there is only one orgunit at OU3 level.

The sample database has been pruned down (by Calle, the DHIS 2 version is a
copy of the 1.4) to include only one District municipality so it might be to
small for proper testing.
But with data for all periods + all facilities you can still get a
reasonable amount of values I think. I'm on a very slow connection in
Zanzibar right now so I can't check on the demo, but if you go to data
administration and data browser you can quickly find out for which periods
you have good data coverage. I think its at least two years of data there.


> 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
>> >> >
>> >> >
>> >
>> >
>>
>
>