dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

On Tue, Jan 26, 2010 at 4:35 PM, Ola Hodne Titlestad <olatitle@xxxxxxxxx>wrote:

> 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
> <view>, 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?
>
>
 What gets inconvenient with not dropping them is that the columns might
change, e.g. when adding more categories the category_structure table will
get more columns.


> 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.
>
>
This could clearly be done.

Lars