← Back to team overview

dhis2-devs team mailing list archive

Working with pivot tables in DHIS2 - some changes due to the new dimensional model

 

Hi,

Just wanted to share some insights on how to work with pivot tables in the
latest version of DHIS2.

I will put this into the user manual shortly, but though it might be a good
idea to share here first, also to get some thoughts on improving the work
flow.

I have used the typical DHIS approach to pivot table integration, same as in
DHIS 1.4 with database views in the DHIS database providing everything you
need per table in Excel.

With the new dimensional model and the resource tables in the dhis database
we can easily get all group sets and categories over to Excel as pivot
fields, which is a major improvement to the data analysis process. Here is a
short how-to.

This is the current workflow:
*
First time setup:
*1) In DHIS2, Data Administration, Resource Tables, Tick all and generate
2) in DHIS2, export the data you need to the datamart
3) In database (e.g. pgAdmin), insert the pivot views
4) Add a Windows data source (administrative tools->data sources) that links
to your database (odbc connection)
5) In Excel use the Pivot table wizard, connect to your dhis database using
the odbc connection and do a select * from one of the pivot views
6) define the layout of the table (e.g. pick the dimensions you want to see)

*On data updates:
*1) Export the new data to datamart
2) Refresh the pivot tables

*On metatdata updates (new indicators, new categories, new groups etc.)
*1) Drop all the pivot views in your database
2) Re-generate all the resource tables
3) Put the views back in
4) Refresh your pivot table (possibly you have to go back to the pivot's
query designer to fetch the new columns)

I have already mentioned the inconvenience of having to drop and re-create
the pivot views every time there is a change to the resource tables. This is
not straight forward for the users and seems unnecessary when e.g. simply
adding a few new indicators. Ideally both resource tables and standard pivot
views should be taken care of by DHIS without bothering the users.

I will upload the pivot views to the resources folder in the code repository
shortly.

Ola
-----------








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.

Follow ups