dhis2-users team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Hi Lars,
I could not agree with you more. I think the ability of users to extract
out chunks of data from the data mart based on indicator or data element
groups would largely solve this problem of external views at least in
Rwanda. It is a great idea, and hope it can be implemented in future
releases.

Best regards,
Jason



On Fri, Dec 21, 2012 at 9:33 AM, Lars Helge Øverland <larshelge@xxxxxxxxx>wrote:

>
>
> On Fri, Dec 21, 2012 at 5:47 AM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> Yes, in this context, a materialized view is simply a table which is
>> produced from an SQL view. The advantage of course is that materialized
>> views can be indexed, etc and are normally much faster to serve than
>> dynamic SQL views.
>>
>> The one thing I did not mention, is the refreshment of the materialized
>> views. As Randy pointed out, we have scheduled the refresh of the views as
>> a cron job. Ideally, this would be something which DHIS2 would do. If we
>> were to represent the 20 or so views as normal DHIS2 SQL views, there is no
>> native way to refresh them, and this needs to occur each night after the
>> data mart runs. So, ideally, scheduling of the regeneration of the SQL
>> views (and resource tables) should be an option as well. There is a work
>> around using CURL to force the regeneration, but again ,this is a bit of a
>> kludge as well. Hope this can get included at some point in time.
>>
>>
>> I think the real fix in this case would be.
>>
>> 1) Implement scheduling of SQL views.
>> 2) If users want to use custom Postgresql functions, they could do this,
>> and they could be triggered by DHIS with "SELECT
>> my_custom_stored_procedure();" with the scheduler. This would eliminate the
>> need for a cron job from the system.
>> 3) Allow for users to choose certain tables which should be available as
>> resources, for instance, "_orgunitstructure"  (Bob's point) or
>> "_my_custom_table", which might be some external table or materialized
>> view. DHIS would not do anything other than to produce JSON, HTML, Excel or
>> whatever from this table. Not really sure why this is not RESTFUL? Isn't
>> this the same thing as what is being done with the SQL views themselves?
>>
>>
>>
>
> Okay. In my opinion the real fix would be to support data element and
> indicator group selection in mydatamart. Then one wouldn't have to do any
> of this. Anyway, scheduling of sql views and resource tables is already on
> the plan and will be done for 2.11. We can also extend the sql view
> functionality to support custom select statements like you suggest.
>
>
>
>
>