dhis2-users team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

Randy if you really want to have the maximum database-flexible view of your
data then you might also consider just creating user accounts with read
only access to a copy of the production database.

Rather than having dhis transformed into a sort of phPgAdmin :-)

One possiblility which comes to mind ... it would be easy enough to have a
copy of the database running on your other machine (cheetah), refreshed
daily.  Then just create separate user accounts on that database and open
it up to your users to connect with excel, access or whatever other
horrible things they want to use.  And you can of course still run your
materialize views script.

Bob



On 21 December 2012 07:36, Wilson,Randy <rwilson@xxxxxxx> wrote:

>  Thanks to all of you..  ****
>
> ** **
>
> I now have some great short term fixes and look forward to thinking more
> about the best approach over the longer term.  I like the idea of using
> this function to create “true” views dynamically as well as the tables
> Jason has created.   From the user perspective, the nice thing is that they
> can use the DHIS web interface to create useful dataelement and indicator
> groups – and then the function creates the views for them automatically –
> no need to write SQL queries.  We might want to add a check box in the
> dataelement/indicator group definition screens that users can check if they
> want the a materialized view created for the group.  The default would be
> no.  Then we can change the syntax in the function to select only those
> groups that are checked before looping through and creating the
> tables/views. ****
>
> ** **
>
> As Jason notes the main issue that brought us to build these tables was
> that the SQL syntax in the function uses the resource tables – to create a
> more detailed orgunit view (all reporting entities with the names of the
> entire hierarchy above them (sector, sub-district, district, province).
> Many of the reports require these groupings of data and the pivot table
> drill-down/float-up capabilities are very helpful.  I’m not sure how we can
> get around the need to drop and rebuild these tables.****
>
> ** **
>
> Randy****
>
>  ****
>
> ** **
>
> *From:* Lars Helge Øverland [mailto:larshelge@xxxxxxxxx]
> *Sent:* Thursday, December 20, 2012 10:51 PM
> *To:* Jason Pickering
> *Cc:* Wilson,Randy; dhis2-users@xxxxxxxxxxxxxxxxxxx
> *Subject:* Re: [Dhis2-users] Help creating links to download materialized
> views****
>
> ** **
>
> 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<http://apps.dhis2.org/demo/api/sqlViews/dI68mLkP1wN/data>
> ****
>
> ** **
>
> 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:****
>
> ** **
>
> <a href="http://apps.dhis2.org/demo/api/sqlViews/dI68mLkP1wN/data.xml<http://apps.dhis2.org/demo/api/sqlViews/dI68mLkP1wN/data>">Download
> as XML</a> |****
>
> <a href="http://apps.dhis2.org/demo/api/sqlViews/dI68mLkP1wN/data.csv";>CSV</a>
> | 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:****
>
> ** **
>
>
> http://apps.dhis2.org/ci/job/dhis-web-2.10/lastSuccessfulBuild/artifact/dhis-2/dhis-web/dhis-web-portal/target/dhis.war
> ****
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> On Thu, Dec 20, 2012 at 5:28 PM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:****
>
> Hi Randy and Lars,****
>
> The fundemental problem is that view which they were using are linked to
> the resource tables. These tables need to be regenerated, and when they are
> regenerated, the tables are dropped and recreated. If views are linked to
> these tables, the resource table regeneration cannot take place, because
> the database forbids the table to be dropped, because there are linked
> views. DHIS2 fails silently and only says "Resource tables generating..."
> or something like that. I think we have described this problem on the list
> before and I am sure you are aware of it. ****
>
> ** **
>
> So, to get around this problem, we use stored procedures
> to materialize the views, and then as Randy indicates, users can get to
> them through ODBC. ****
>
> ** **
>
> I guess it could be done Randy by creating an SQL View of the materialized
> view. This does not really make a whole lot of sense, but it could be done.
> So, if you create an SQL view in DHIS2 of "SELECT * FROM _materalized
> view;" you will get a new materialized view available, as Lars points out,
> through the API. A bit wasteful really but certainly possible. ****
>
> ** **
>
> The "elegant" part which Randy mentions is that we do not define many
> seperate views, but dynamically define the tables based on data element
> groups. The views are very similar, they just differ in the data elements
> which should be presented to the users. So, instead of maintaining like
> 20 separate similar views, we have just done the same thing with one single
> stored procedure instead. Second option then would be to have
> these separate 20 (or whatever the number is) views as DHIS2 SQL views.
> Again, not very elegant. ****
>
> ** **
>
> Third option which I see (which I think is the best option) is to reflect
> SQL views out of DHIS2 without ever materializing them. I think there
> should be an option for "materialize" for each SQL view. If it is not
> checked, the view would be produced dynamically at the time of request.
> Again, not a very elegant solution for this problem, because we would still
> need 20 separate "SELECT * FROM _materialized_view1", "SELECT * FROM
> _materialized_view2", but at least we would not write the materalized view
> twice. ****
>
> ** **
>
> Fourth option (maybe a bit limiting) is to create the same SQL View type
> of functionality for tables, lets call it a table reflector, which would
> simply reflect a table as JSON, XML, CSV or whatever. The user could be
> presented with a list of tables in the system, and simply choose the one
> you want (in our case, one of the materialized views) and then they would
> be available somehow through the API. ****
>
> ** **
>
> Fifth option, is some external report. In Nigeria, we use R for this
> purpose actually, and it is relatively straightforward. Other things like
> PHP, Python or Birt could be used pretty easily as well. ****
>
> ** **
>
> Just a bit of a grab-bag, but maybe there will be some possible solution
> in there. ****
>
> ** **
>
> Regards,****
>
> Jason****
>
> ** **
>
> ** **
>
> ** **
>
> On Thu, Dec 20, 2012 at 1:58 PM, Wilson,Randy <rwilson@xxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp****
>
>  ** **
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp****
>
> ** **
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>
>