dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

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

> 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
>>
>
sorry, and these will then be for OU4 and OU5 based on my change to use
these levels in stead.


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