dhis2-users team mailing list archive

["Wilson,Randy" <rwilson@xxxxxxx>]

Hi Lars and Jason,

I agree as well.  Remember the views are actually crucial for custom iReports as well - so a mydatamart only solution is not sufficient.  We have extended our system with tables to manage results based financing data (dataelement tariffs and storage of banking details for orgunits).  As a result we need to be able to create SQL views that include these foreign tables to be used as the datasources for standard reports.

Randy

From: Jason Pickering [mailto:jason.p.pickering@xxxxxxxxx]
Sent: Friday, December 21, 2012 12:05 PM
To: Lars Helge Øverland
Cc: Wilson,Randy; dhis2-users@xxxxxxxxxxxxxxxxxxx
Subject: Re: [Dhis2-users] Help creating links to download materialized views

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<mailto:larshelge@xxxxxxxxx>> wrote:

On Fri, Dec 21, 2012 at 5:47 AM, Jason Pickering <jason.p.pickering@xxxxxxxxx<mailto: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.