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
    I think the docs need to be updated, dm.build_study_functions is the view
    name not dm.build_new_study.

    Could you post the full error message from dm.build_study_functions?

    Also, was the dm schema created, and if so what matviews does it contain?
    I'm assuming you've got a openclinica_fdw schema?

    I think I'll move sqldatamart to a new repo soon so that versions can be
    tagged, and issues / pull requests can happen more easily.
  • rogerogerogeroge Posts: 16


    Attached are the copies of your files I used to generate the DW [as you see I have stripped out the secure roles to be replaced by an account dmart which does everything for testing]. Note there is one place in the build script under create user mapping that references the username for the live OC system.

    The schema and tables from PGAdmin3 is also attached, as is clear the script has done most of the work fine.


  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    Setting different user names might be the problem. You've replaced "ocdm_fdw" with "clinica", and  "dm_admin" with "dmart".

    In the first case, I'd recommend against that because I suspect the "clinica" user is the OpenClinica "clinica" superuser. The "ocdm_fdw" role (or whichever role you make) should have read access to the openclinica database only.

    In the second case, the "dm_admin" name is present in some other functions so you'd need to change it there as well. See the functions "dm_create_study_schemas", "dm_grant_study_schema_access_to_study_role", "dm_reassign_owner_study_matviews". The first one is probably the cause of the build failure, since it is where the "wahol" schema (and "clinicaldata" matview) would be created.

    Also, from the psql screenshot it seems there are 7 matviews missing from the dm schema; there should be 16, not 9, as follows. If one of the missing matviews is "clinicaldata" then that's another reason why it might be failing.

    Line 53: SELECT dm_create_dm_response_sets();
    Line 57: SELECT dm_create_dm_metadata();
    Line 61: SELECT dm_create_dm_metadata_event_crf_ig();
    Line 63: SELECT dm_create_dm_metadata_crf_ig_item();
    Line 65: SELECT dm_create_dm_metadata_study();
    Line 69: SELECT dm_create_dm_clinicaldata();
    Line 76: SELECT dm_create_dm_subjects();
    Line 78: SELECT dm_create_dm_subject_event_crf_status();
    Line 80: SELECT dm_create_dm_subject_event_crf_expected();
    Line 82: SELECT dm_create_dm_subject_event_crf_join();
    Line 84: SELECT dm_create_dm_discrepancy_notes_all();
    Line 86: SELECT dm_create_dm_discrepancy_notes_parent();
    Line 88: SELECT dm_create_dm_subject_groups();
    Line 90: SELECT dm_create_dm_response_set_labels();
    Line 92: SELECT dm_create_dm_user_account_roles();
    Line 94: SELECT dm_create_dm_sdv_status_history();
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    More news:

    - missing semicolon at end of line 67 of build script
    - moved sqldatamart to its own repo [1]
    - moved manual into wiki attached to new repo

    If you have issues or pull requests please submit to new repo

    [1] https://github.com/lindsay-stevens-kirby/openclinica_sqldatamart
  • richard.brookesrichard.brookes Posts: 54
    Hi Lindsay,

    Great work on this BTW...

    I am getting stuck at a similar place as Roger. Have 9 of the 16 matviews.

    FYI I have gone off script a bit and setting it up on a single machine without the ssl. I removed line 27 & 28 from dm_build_commands.sql
        sslmode 'verify-full',
        sslrootcert 'root.crt'

    It seems to create the openclinica_fwd schema OK, falling over on the dm.

    Attached is a copy of the output when executing the setup bat. 

    Any help would be greatly appreciated.



  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    tldr: grab latest code from repo, follow "basic" setup in wiki.

    I've just merged a bunch of changes into master that seem to work now. I
    think the problem was that I'd committed some work that wasn't finished so
    probably you guys were using broken code. I'm using branching now so that
    shouldn't be a problem in future.

    Anyway, I've factored out the user names so you can set them as you like in
    the bat script. Same goes for sslmode; in fact the new way would let other
    foreign server settings be added without updating the build sql script.

    In terms of deployment, I've split the wiki setup section into 'basic' and
    'advanced'. Basic is the minimum possible. Advanced is the original setup
    with performance settings, logging, encryption and SSPI authentication.

    Whether you do basic or advanced, you should now only need to edit the bat

    There were a few other minor refinements, described in the commit messages.

    If you try the latest code, please let me know how you go. If you have
    problems with the setup or instructions please open an issue in github.
  • huamanzhuhuamanzhu Posts: 51
    I update the oc postgresql the oc postgres host based authentication file,like you example,
      hostssl openclinica ocdm_fdw ocdmIPAddress/32 md5
    but I can't connect the oc server by myocdm.why?
    if i set TYPE:host and I can connect the oc server.
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    That's a good point, thanks. I've updated the wiki accordingly.

    "hostssl" is only for advanced setup, it means "only accept this connection
    type if it uses SSL"

    "host" is the correct type for basic setup, it means "accept this
    connection whether it uses SSL or not".
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    @huamanzhu, I checked your attached files and it looks like you have the
    old code that will not work. Please get the latest version from github.
  • huamanzhuhuamanzhu Posts: 51
    yes.I try your the latest version and it worked,but it has 20 matviews ,not 22,can you tell me why?,and the other schema(defult_study,xxx,public) ,what does it mean?
This discussion has been closed.