dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

On Wed, Feb 24, 2010 at 12:09 PM, Ola Hodne Titlestad <olatitle@xxxxxxxxx>wrote:

> 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
> -----------
>
>
Hi,

thanks for sharing this, very useful. Will be great to have a section in the
docs for this.

Lars


>
>
>
>
>
>
>
> 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.
>
> _______________________________________________
> 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
>
>