my attempt to rid the clinical world of excel › download › sde › rtp2016 › ... · my...
TRANSCRIPT
![Page 1: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/1.jpg)
MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL
MIKE MOLTER
DIRECTOR OF STATISTICAL PROGRAMMING AND
TECHNOLOGY
WRIGHT AVE
OCTOBER 27, 2016
![Page 2: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/2.jpg)
![Page 3: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/3.jpg)
Agenda
• Introduction to Study Designer
• Development tools
• Database
• Interface
• Brief demo (if time)
• Future of Study Designer
![Page 4: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/4.jpg)
Technologies in Use:
• Neo4j
• Cypher
• HTML, Javascript, CSS
• Python
• Jinja2
• Google App Engine
![Page 5: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/5.jpg)
Technologies NOT in Use:
• SAS
• Excel
![Page 6: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/6.jpg)
What is Study Designer?
Study Designer is a web-based tool used for entering metadata (i.e. programming specs and define.xml) for a study
Signature features
• Database of CDISC data models, implementation guides, and study metadata
• Web interface that guides the user through the development of metadata by way of a study design sequence
![Page 7: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/7.jpg)
Database
![Page 8: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/8.jpg)
Database
Sam 10/27/16
TEMP 99.5 F
NAME DATE TEST RESULT
UNIT
Sam 10/27/16
HEIGHT
73 in
Sam 10/27/16
WEIGHT
220 lb
Sam 10/27/16
SYSBP 118 mmHg
Sam 10/27/16
DIABP 75 mmHg
Tony 10/24/16
TEMP 99.1 F
Tony 10/24/16
HEIGHT
70 IN
Tony 10/24/16
DIABP 73 mmHg
Tony 10/24/16
SYSBP 122 mmHg
Observations(records)
Properties (variables or fields)
Keys
Table
![Page 9: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/9.jpg)
Traditional rectangular SQL database
![Page 10: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/10.jpg)
Name :SamDate :10/27/16Test : TEMPResult : 99.5Unit : F
node Properties
Observation (node)
:VSnode label
Neo4j – Graph Database
![Page 11: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/11.jpg)
:VS
:VS
:VS
:VS
:VS
:VS:VS
:VS
:VS:LB
:LB
:LB
:LB
:LB
:LB:LB
:LB
![Page 12: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/12.jpg)
FirstName : TomLastName: HanksBorn: 1956
: ACTOR
[:ACTEDIN]
[:DIRECTED]
Neo4j – Graph Database
[:ACTEDIN]
![Page 13: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/13.jpg)
Cypher – the query language of Neo4j
MATCH (tom:Person {name: "Tom Hanks"})-
[:ACTED_IN]->
(tomHanksMovies:Movie)
RETURN tom, tomHanksMovies
alternatively,RETURN tom.born, tomHanksMovies.tagline
see demo
![Page 14: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/14.jpg)
CDISC standards in Neo4j<ItemGroupDef
Name=“AE”
other attributes>
<ItemRef ItemOID=“IT.STUDYID” Order=“1” Mandatory=“Yes”/>
<ItemRef ItemOID=“IT.AESEV” Order=“15” Mandatory=“Yes”/>
One ItemRef for each variable in the dataset
</ItemGroupDef>
<ItemDef
Name=“STUDYID”
OID=“IT.STUDYID”
STUDYID attributes>
</ItemDef>
<ItemDef
Name=“STUDYID”
OID=“IT.AESEV”
AESEV attributes>
<CodeListRef CodeListOID=“CT.SEV”>
</ItemDef>
<CodeList
OID=“CT.SEV”
<EnumeratedItem CodedValue=“MILD”>
More EnumeratedItem elements
</CodeList>
![Page 15: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/15.jpg)
Name : AEClass : EventsPurpose:Tabulation
: ItemGroupDef
Order: 15Mandatory: Yes
: ContainsItem
CDISC standards in Neo4j
![Page 16: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/16.jpg)
CDISC standards in Neo4j
![Page 17: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/17.jpg)
CDISC standards in Neo4j
![Page 18: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/18.jpg)
CDISC standards in Neo4j
InstantiatesModel
![Page 19: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/19.jpg)
Using Python to interact with Neo4j
from py2neo import Graph
graph = Graph()
defaults to localhost:7474
results = graph.cypher.execute(‘match (:IG)-[]->
(igd:ItemGroupDef) return igd.name,igd.class,igd.purpose’)
name class purpose
CM Interventions Tabulation
VS Findings Tabulation
AE Events Tabulation
results (a Python object called a RecordList)
![Page 20: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/20.jpg)
Google App Engine
• A cloud computing platform for developing
and hosting web applications in Google-
managed data centers
• When downloaded and installed, a local
development server is created and listens
on port 8080.
• Comes with a Python package that allows
application developers to…
• associate web form URLs with code to
be executed
• capture and process web form input
from users
![Page 21: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/21.jpg)
app =
webapp2.WSGIApplication([
(‘URL1’,URL1_code_class),
(‘URL2’,URL2_code_class),
(‘/’,FrontPage)
], debug=True)
class FrontPage:
def get(self):
<pre-processing code>
<code to render web form>
see demo
![Page 22: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/22.jpg)
class FrontPage:
def get(self):
allstudies = <Cypher query to return all current
studies in the database>
<code to render web form>
self.render(webform.html, studylist=allstudies)
![Page 23: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/23.jpg)
Jinja 2
• Templating language used for
generating dynamic HTML
• HTML generated by what is found in
the database
• Think of the SAS macro language
• Supports …
• reference of parameters passed in
from application ({{x}})
• conditional logic ({% if condition
%})
• iterative logic ({% for x in list-
name %}
![Page 24: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/24.jpg)
HTML for dropdowns
<select name=“mydropdown”>
<option>Choose an Option</option>
<option>choice1</option>
<option>choice2</option>
<option>choice3</option>
</select>
self.render(webform.html,studylist=allstudies)
<select name=“mydropdown”>
<option>Choose a study</option>
{% for x in studylist %}
<option>{{x[0]}}</option>
{% endfor %}
</select>
![Page 25: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/25.jpg)
Capturing web form choices in Python
<select name=“mydropdown”>
<option>Choose a study</option>
{% for x in studylist %}
<option>{{x[0]}}</option>
{% endfor %}
</select>
HTML World…
Back in Python Land…
chosenstudy=self.get.request(“mydropdown”)
self.get.request returns the user choice from the dropdown
![Page 26: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/26.jpg)
USER:Enters application
URL into web browser
PYTHON:1. Execute code
based on URL (e.g. querying database)
2. Render a web form, passing parameters to build dynamic HTML
USER:1. Populates form
fields2. Clicks Submit
button
PYTHON:1. Execute code
based on URL (e.g. adding to database, querying database)
2. Render a web form, passing parameters to build dynamic HTML
Brief summary of the User input/Code Execution Cycle
![Page 27: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/27.jpg)
In Summary
In order to replace Excel, we need• a database to house standards• a user interface
• collects information from users• interacts with the database
To connect the dots• Python code to touch the database• Python code to pass parameters to dynamic
HTML (Jinja2 templates)• Python code to capture user choices• Google App Engine to pair Python code with
URLs and provide methods for capturing user choices
![Page 28: My Attempt to Rid the Clinical World of Excel › download › sde › rtp2016 › ... · MY ATTEMPT TO RID THE CLINICAL WORLD OF EXCEL MIKE MOLTER DIRECTOR OF STATISTICAL PROGRAMMING](https://reader033.vdocuments.us/reader033/viewer/2022042323/5f0d37de7e708231d4394160/html5/thumbnails/28.jpg)
The Current and Future State of Study Designer
• Custom domains
• Controlled Terminology+extensions
• define.xml
• spec.xml
• standards development
• Trial Design
• Visit Schedules
• Version control
• Dependency tracking
• protocol.xml extraction???