Okay. So with materialized view you are referring to a plain db table, right? I thought you meant creating a view with sql something like “create materialized view” but I can see that has not been implemented directly in postgres yet.
Anyway I think creating a dhis sql view per table is a reasonable work-around. It requires you only to create 20 objects, one time.
One alternative would be to create some sort of dhis sql report. But it would be very similar in nature and require some development for 2.11. Another alternative would be just to expose database tables through the web api but that does not sound very nice or restful to me.
So if you are willing to create a sql view per table you can achieve 1.
Re 2 you can simply point directly to the sql views through the web api, for instance to the demo like this, for xml:
http://apps.dhis2.org/demo/api/sqlViews/dI68mLkP1wN/data.xml
for csv:
http://apps.dhis2.org/demo/api/sqlViews/dI68mLkP1wN/data.csv
I have committed and backported to 2.10 representations for Excel and HTML, you can reach them after upgrading by using the extensions .xls and .html .
For web pages you can use plain links like this:
CSV | etc
For 3, yes you can easily include these as resources. Just add a new one, give it a name, select “external URL” as type and point to the sql view URLs above.
For authentication you might get some tips from here:
http://dhis2.org/doc/snapshot/en/implementer/html/ch08s02.html#d5e600
You can download 2.10 latest from here in half an hour:
···
On Thu, Dec 20, 2012 at 1:58 PM, Wilson,Randy rwilson@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:
Is there a way to expose other tables in the API?
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.
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)
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