We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

live reporting sqldatamart



  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Hi Richard

    Glad to hear you got the scripts working.

    Tables, views and matviews are usually in the same namespace and are
    referenced in the same way. If you're using the accessdatamart database, a
    minor edit is required, which is described below. If you're starting with a
    fresh database I've also got a description of that below.

    If using the control panel form in the accessdatamart database to create
    linked tables, a minor edit is required in the vba module
    'mod_make_locallinked_table'. At line 23, a query is constructed which
    retrieves a list of tables in the schema:

    ml_table_list_sql = "SELECT pg_tables.tablename FROM pg_tables " _
    & "WHERE pg_tables.schemaname=" & Chr(39) & ml_study_name_clean & Chr(39)

    But now there are matviews, not tables. Fortunately, there is a pg_catalog
    view that lists all the materialized views. So change the above to:

    ml_table_list_sql = "SELECT pg_matviews.matviewname as tablename FROM
    pg_matviews " _
    & "WHERE pg_matviews.schemaname=" & Chr(39) & ml_study_name_clean &

    Once this is done, you should be able to then put the study name in the
    'study to import / link' text box, click the 'Import / Link tables' button,
    and choose the matviews to import or link.

    This is another update I'll need to add to the repo, probably as a new
    version of the Access database with the table processing code removed.

    If you are trying to link in a new database or something:

    To make an ODBC linked table, you'll need to either use VBA (in the
    accessdatamart see 'mod_make_locallinked_table' line 69-75), or make a DSN
    then use the ODBC link wizard.

    If you go with a DSN, I'll try to save you a headache by saying that if you
    have 64bit windows and 32bit office, the DSN will need to be made with the
    psqlODBC 32bit driver, using the windows 32bit driver manager, which is
    conveniently buried in 'C:\Windows\SysWOW64\odbcad32.exe'. Meanwhile the
    64bit driver manager (shown in 'Administrative Tools') is located at
    'C:\Windows\System32\odbcad32.exe'. Sadly, MS used the same name for both

    Anyway, you can also create remote queries by choosing the "Pass-through"
    query type, and in the query properties specifying an ODBC connection
    string. Make sure 'Returns records' is set to 'Yes'.

    For example:
    SQL: SELECT pg_matviews.matviewname as tablename FROM pg_matviews WHERE
    ODBC Connect Str: ODBC;DRIVER={PostgreSQL

    Best regards,

    On 18 June 2014 17:38, richard.brookes
  • richard.brookesrichard.brookes Posts: 54
    Hi Lindsay,

    I made the changes you suggested and works OK now.

    I did have any issue with the study name. The study has a space in the name e.g. "study name". This create the namespace study_name in the reporting db. However the process_text module strips the _. I had to remove this line and it worked fine.


  • richard.brookesrichard.brookes Posts: 54
    Hi Lindsay,

    I see that you have made some changes to the sqldatamart. 

    I thought I would take look, do you have any tips on setting it up without the security model you have specified.
    It's nice but over the top for testing it out.


  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    I'm guessing you mean without the certificates and SSPI. That part is 100%
    postgres so you can use whatever server-server and server-client
    authentication suits. The main reason for put all that in there is so I
    remember how I deployed it.

    I'm still working on the code and docs, planning on having them ready to
    launch at OC15. I'll try and incorporate a pathway for someone trying to
    check it out, or at least set it up with minimal security.
  • huamanzhuhuamanzhu Posts: 51
    Hi Lindsay,
    I tried your script,but I faild,because it is point a meesage ,"psql :fatal error: user ' ' password auth-failure ",I check all the message,it is ok.is it let me the database authority?
    kind regards,
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Have you set all the batch script parameters? There are two passwords
    involved, the postgres superuser password and the foreign server ocdm_fdw
    user password.

    Which setup steps have been completed? Running the batch script is one of
    the last steps.
  • huamanzhuhuamanzhu Posts: 51
    yes,I set up steps have been completed,and what do you mean the foreign server ocdm_fdw?the database password or others?
    user password
  • huamanzhuhuamanzhu Posts: 51
    I set the foreign server ocdm_fdw user password is my login the foreign server password,is it right?
  • huamanzhuhuamanzhu Posts: 51
    the script can support oc version 3.3? waht is it support oc version?
  • rogerogerogeroge Posts: 16

    Please can you help.

    I used the old version of slqmart [2014 ed] fine

    I have now moved onto the latest from github. I have sorted out the permissions and have managed to produce the database and materialised views on my target 9.3 system but cannot get the study specific digests to work. 

    There is no enrty for TABLE dm.build_new_study nor reference to it in the scripts on github.

    Running the TABLE dm.refresh.xx work fine and TABLE dm.build_study_functions; in psql throws an error line 28 wahol.clinicaldata does not exist [wahol is my study id]

    Help please!

This discussion has been closed.