dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olatitle@xxxxxxxxx>]

Hi,

Some background:

Recently we've added quite a few so called resource tables to make it easier
to analyse DHIS data with various dimensions in external tools.
These include tables with group sets/groups/elements for data elements,
indicator and orgunits as well as a table with all categories and options
for every catoptioncombo.
(you can generate them from the DHIS UI under Data Adminstration->resource
table)
For excel pivot tables I use predefined views to extract data, and in these
views I e.g. join the aggregateddatavalue table with the resource tables to
add more columns with dimensional information.
The data load process in Excel then typically becomes a select * from
<view>, which makes it a lot easier for non-techies to create pivot tables.
These views are manually created in postgres and need to be in the database
when you create or refresh the pivot tables.

The problem:
A problem I just noticed is related to updates of the resource tables. I
created a few new group sets and groups and needed to re-generate a resource
table in order to get the new information into the pivot table. This
triggered a drop table statement on the resource table that was not allowed
since I had created a view referencing that table. Would it be possible to
update the resource tables without having to drop them first, e.g. how we do
with datamart table like aggregateddatavalue?

If not (and perhaps anyway) could we maybe look into automatically
generating these pivot views from the DHIS as part of the resource tables?
That would mean we could drop the views and re-create them from the code if
necessary.

To give you an example of how I use the resource table here is a view to get
OU3 level data from the datamart together with categories (on columns) and
categoryoptions (on rows), as well as group sets (on columns) and groups (on
rows). In excel I can then filter out any category or groupset I don't need
for the specific table or report.

(The two resource tables used here are _categorystructure and
_dataelementgroupsetstructure )
-----------------------------------

CREATE OR REPLACE VIEW pivotsource_routinedata_ou3_dim AS
 SELECT
organisationunit_1.name AS orgunit1,
organisationunit_1.shortname AS ou1,
organisationunit_2.name AS orgunit2,
organisationunit_2.shortname AS ou2,
organisationunit_3.name AS orgunit3,
organisationunit_3.shortname AS ou3,
dataelementgroup.name AS degroup,
dataelement.name AS dataelement,
dataelement.shortname AS deshort,
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,
aggregateddatavalue.value, aggregateddatavalue.level,
_categorystructure.*,
_dataelementgroupsetstructure.*

FROM
dataelementgroupmembers dataelementgroupmembers, _categorystructure,
dataelement dataelement, dataelementgroup,
_dataelementgroupsetstructure,dataelementgroup,
 period period, periodtype periodtype,
orgunitstructure orgunitstructure, organisationunit organisationunit_1,
organisationunit organisationunit_2, organisationunit organisationunit_3,
aggregateddatavalue aggregateddatavalue

WHERE
dataelement.dataelementid = aggregateddatavalue.dataelementid AND
dataelementgroupmembers.dataelementid = dataelement.dataelementid AND
dataelementgroupmembers.dataelementgroupid =
dataelementgroup.dataelementgroupid AND
aggregateddatavalue.categoryoptioncomboid =
_categorystructure.categoryoptioncomboid AND
_dataelementgroupsetstructure.dataelementid =
aggregateddatavalue.dataelementid AND
period.periodtypeid = periodtype.periodtypeid AND
periodtype.name::text = 'Monthly'::text AND
period.periodid = aggregateddatavalue.periodid AND
aggregateddatavalue.organisationunitid = orgunitstructure.organisationunitid
AND
orgunitstructure.idlevel1 = organisationunit_1.organisationunitid AND
orgunitstructure.idlevel2 = organisationunit_2.organisationunitid AND
orgunitstructure.idlevel3 = organisationunit_3.organisationunitid AND
aggregateddatavalue.level = 3
ORDER BY period.startdate;
ALTER TABLE pivotsource_routinedata_ou3_dim OWNER TO dhis;
--------------------------------------


Ola Hodne Titlestad |Technical Officer|
Health Metrics Network (HMN) | World Health Organization
Avenue Appia 20 |1211 Geneva 27, Switzerland | Email: titlestado@xxxxxxx|Tel:
+41 788216897
Website: www.healthmetricsnetwork.org

Better Information. Better Decisions. Better Health.