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

Hi all

First draft of the live reporting sqldatamart is up on my github [1]. There are instructions in the readme if someone wants to try it out.

The OpenClinica live server can be postgres 8.4. The reporting server must be postgres 9.3. Everything happens in postgres now, so no more Access required (unless using it as a study client). A full refresh on an instance with 400k item_data rows takes about 2 minutes.

Also, Linas sent some updates for his powershell/xsl files, to deal with Unicode characters in R. The commit can be viewed at [2].

Best regards,

[1] https://github.com/lindsay-stevens-kirby/openclinica_scripts/tree/master/sqldatamart
[2] https://github.com/lindsay-stevens-kirby/openclinica_scripts/commit/46a7f98e1e3871be9bc8231c8a664a8b0534857f


  • sadigasadiga Posts: 17
    Hi Lindsay,

    Is there any documentation or screenshots of the reporting UI?

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

    The scripts automate the process of establishing a reporting database. The
    idea is that it saves having to figure out all the ETL stuff - either
    making sense of the OpenClinica database, or transforming XML, or reading
    via REST - so you can point your reporting tool of choice at it and go from
    there. I currently use Access for reports but it could be anything else.

    So, there isn't a UI. I have added a screen shot of pgAdmin [1] though, in
    case that helps illustrate the output. There are 7 studies in the instance,
    one of which is expanded to show it's views. The views prefixed with 'ig_'
    correspond to OpenClinica item groups.


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

    I've run the scripts, however the item group views are not created in the study schema (in the reporting db) as expected.

    Any idea what the issue may be?

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

    Thanks for trying it out.

    If you can attach your postgresql log it would help diagnose the issue. In
    Windows you can find them under "C:\Program

    Otherwise, if the other parts have run OK, my first guess is that the item
    group creation fails because at least one column name is duplicated.

    The script uses the same naming convention as the accessdatamart, which
    assumes item names are 8 characters, allows 4 extra characters for
    appending a mutli-valued item's integer value (3 digits plus underscore),
    adds the first 45 characters of the item description (plus underscore),
    plus 6 characters for '_label' at the end if it is a value label column.
    This is so everything fits inside postgres' limit of 64 character column
    names (8+1+3+1+45+6).

    If that seems like it would be incompatible with your item naming
    conventions, you could try just using the item oid for the column names. In
    script 08 at line 76-86, replace 'lower(...) as item_name_hint' with
    'item_oid as item_name_hint'.

    In the metadata table, both item_name and item_oid get the multi-select
    value appended so doing this won't affect the expansion of multi-values to
    separate columns.

    My second guess is that it is related to null value flavours. In script 08
    at line 38 and 53, the null flavour of 'NI' is replaced with null so it
    doesn't collide with the date or numeric column types.

    'NI' is the only one present in our data, but for wider compatibility I'll
    add in the rest of the null value flavours, e.g. replacing at line 38 and
    53 'when item_value = 'NI'' with 'when item_value IN ('NI','NA'UNK')' etc.

    Best regards,

    On 12 June 2014 14:59, richard.brookes
  • richard.brookesrichard.brookes Posts: 54
    via Email
    Hi Lindsay,

    Here is log file.


  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    Emailing attachments doesn't seem to work - could you try attaching it via the forum website please?
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    via Email
    Hi Richard

    Thanks for uploading the log file. It seems that the item group script is
    failing because there is a group with an item name starting with a number.
    Column names starting with a number are not allowed in postgres (as well as
    SAS and Stata, I think).

    My previous suggestion about changing the item_name_hint to just use the
    item_oid (which starts with 'I_') should work, but I'll try and write a
    better fix for it in next few days. It will probably involve detecting such
    item names and prepending the letter i.

    Best regards,

    On 13 June 2014 19:00, richard.brookes
  • richard.brookesrichard.brookes Posts: 54
    I changed item_name_hint to item_oid and it worked fine.
  • richard.brookesrichard.brookes Posts: 54
    Lindsay, how do you link to a postgres view using ms access? I can only see tables.
This discussion has been closed.