dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

2010/1/26 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
>
>
Wouldn't some of the views you are developing be fairly general, like the
one combining aggregateddatavalue/indicatorvalue with dimensional data?
Would it be a good idea to provide certain "built-in" views so that other
people could use them straight out of the box (being re-created and
everything)?