Hi Randy, let me see if I get you correctly: The problem is mainly that you cannot drop your sql views in an arbitrary order since they depend on each other? Also, your views do not depend on any dhis resource table, and you completely maintain your views through the cron job.
Assuming this, would it solve the problem if we introduced a property on dhis-managed sql views which would allow you to specify whether it should be dropped and recreated by dhis (i.e. when rebuilding the resource tables) or not? For your sql views you could then set this to not to be dropped/created and you could control those sql views like you prefer. The advantage here is that we already have the functionality for producing dhis-managed sql views as json/xml/csv through the web api. regards, Lars On Thu, Dec 20, 2012 at 12:58 PM, Wilson,Randy <rwil...@msh.org> wrote: > In Rwanda we have found report tables and MyDatamart inadequate for the > needs of many of the central level health programs. What they want is a > dump of all the datavalues in *specific* *dataelement and indicator groups > * along with the related orgunit hierarchy. We originally were doing > this using SQL views, under Data Administration, however some of the views > are built on one another so when the Datamart and Resource table procedures > run they is unable to drop the views before re-creating them…. And > consequently neither process runs.**** > > ** ** > > Jason Pickering has helped us develop an elegant function that gets around > the view issue, and creates materialized views for all dataelement groups: > special tables that are refreshed every night through a chron job (just > like the datamart). This gives us exactly what the users want – they > generally link to the tables with ODBC connection using an excel pivot > table – and because they are only retrieving the dataelement groups they > want the file is not too big. Unfortunately, this only works on the local > area network – refreshing the pivot tables over the network is likely to be > too slow, and our security settings don’t enable a remote ODBC connection. > **** > > ** ** > > We are now beginning to explore the DHIS-2 API to create a portal for > users to access DHIS-2 objects. I see that SQL views are exposed through > this API, but for the reasons stated above we can’t rely on them. Here is > where we need help:**** > > **1. **Is there a way to expose other tables in the API? **** > > **2. **Does anyone have some sample HTML code that we could use to > list these special tables (they all start with _*view*_) in the portal > and download them as xls, csv or xml? Similar to what is shown in the > DHIS-2 portal demo for downloading tables.**** > > **3. **Could we include the links to these tables as resources in > users’ dashboards?**** > > ** ** > > *Randy Wilson***** > > Senior HMIS and Data Use Advisor**** > > Rwanda/IHSSP**** > > Management Sciences for Health**** > > BOX 371 > Kigali, Rwanda > +250788308835 (mobile) > Skype name (wilsonrandy_us)**** > > www.msh.org<http://www.msh.org/?utm_source=2012-10-15+MSH+internal+announce+email+signature&utm_campaign=iemailsig&utm_medium=email> > **** > > *Stronger health systems. Greater health impact.***** > > ** ** > > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-users > Post to : dhis2-users@lists.launchpad.net > Unsubscribe : https://launchpad.net/~dhis2-users > More help : https://help.launchpad.net/ListHelp > >
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-users Post to : dhis2-users@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
