On Wed, Feb 24, 2010 at 12:09 PM, Ola Hodne Titlestad <[email protected]>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: [email protected]|Tel: > +41 788216897 > Website: www.healthmetricsnetwork.org > > Better Information. Better Decisions. Better Health. > > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-devs > Post to : [email protected] > Unsubscribe : https://launchpad.net/~dhis2-devs > More help : https://help.launchpad.net/ListHelp > >
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
