Extracting & Reporting
Roadmap & New Feature Ideas
Add-Ons & Code Contributions
Q & A
Installation & Setup
Study Build, Rules, and CRFs
Data Entry, Monitoring, and Data Management
Extracting & Reporting
Web Services & Integration
Internationalization and Localization (i18n / L10n)
Announcements & Updates
Announcements & Updates
Users Mailing List
Developer Mailing List
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
live reporting sqldatamart
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.
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.
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();
- missing semicolon at end of line 67 of build script
- moved sqldatamart to its own repo 
- moved manual into wiki attached to new repo
If you have issues or pull requests please submit to new repo
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
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.
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.
I update the oc postgresql the oc postgres host based authentication file,like you example,
# TYPE DATABASE USER ADDRESS METHOD
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.
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".
, 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.
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.