dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

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