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
-
Add a Windows data source (administrative tools->data sources) that links to your database (odbc connection)
-
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
-
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@who.int|Tel: +41 788216897
Website: www.healthmetricsnetwork.org
Better Information. Better Decisions. Better Health.