dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

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
>> >>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> >>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> >>> More help   : https://help.launchpad.net/ListHelp
>> >>
>> >
>> >
>> > _______________________________________________
>> > Mailing list: https://launchpad.net/~dhis2-devs
>> > Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> > Unsubscribe : https://launchpad.net/~dhis2-devs
>> > More help   : https://help.launchpad.net/ListHelp
>> >
>> >
>
>