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 , 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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org
Better Information. Better Decisions. Better Health.