This position is responsible for writing and maintaining extract/transform/load routines to get data from institutional data sources for use by clinical researchers within the Johns Hopkins University School of Medicine, specifically the Department of Psychiatry, and their collaborators. The major systems with which the position interfaces are: Epic, EPR2020, Sunrise Clinical Manager, and DataMart/CaseMix.
This position will require a significant level of collaboration with other engineers on the Center for Clinical Data Analysis (CCDA) team, which assists researchers with accessing clinical data for research purposes across the Johns Hopkins medical institutions, and the MHiCARE team, which develops EPIC clinical and research integration tools for the Department of Psychiatry. It also requires working closely with CCDA Medical Director/Honest Broker on specific projects and techniques. This position requires working closely with our faculty to discern their needs and to understand and address changes that arise from end user acceptance testing. The engineer will participate in weekly team meetings to review all work and next steps. The engineer will be responsible for delivering all data in a secure manner, following all applicable institutional policies and State and Federal regulations for privacy, security, and the conduct of human subject research.
This position will work closely with users across the Department of Psychiatry, and may require some travel to different parts of the medical campus meet with faculty.
Use SQL Server to extract and transform data.
Full command of and extensive experience with complex SQL, including: DBA-level understanding of SQL Server database and table design and performance, including logs, fillfactor settings, optimization, SQL Server Agent, user permissions and schema, transaction management, iSQL, bcp, batch files.
Advanced DDL statements, including: CREATE/ALTER/DROP tables, constraints, indexes, referential integrity, primary and foreign keys, data typing, defaults, defaults, check constraints, rules, triggers, logging, sort orders and character sets, transaction logs, global and local temporary objects, locking and lock contention/escalation, killing spids.
Advanced DML statements, including: CASE statements, PATINDEX/CHARINDEX parsing, regular expressions, aggregate queries/scalar and vector aggregates, stored procedures, parameters, local and global variables, delimiters, CAST and CONVERT, datetime arithmetic, Boolean comparisons, while loops, cursors, RAISERROR, query optimization, SQL functions, system functions, querying system tables, query optimization. SQL Server Management Studio experience, including compiling and scripting stored procedures, database mail, log analysis, optimizing/analyzing indexes.
SQL Server Integration Services experience (both 2008 and 2010 versions, 32- and 64-bit), include project and package creation/design; data flow tasks and data conversion; OLE, flat file and Excel data sources/destinations and connection managers; scheduled jobs; monitoring; data transformation; email notification.
Basic command of clinical research methods and epidemiologic principles, including missing-data handling, imputation (and its perils), parametric vs. non-parametric analysis, skewed distributions, implications of continuous vs. categorical data, assessing measurement error, form design, PID validation, CPT data, and ICD9/ICD10 diagnosis categories and handling within Epic.
DESIGN AND DEVELOPMENT
Develop detailed tasks and project plans by analyzing project scope and milestones for complex projects in order to ensure product is delivered in a timely fashion according to software lifecycle standards.
Write functional/technical specifications from the complex system requirements, putting them into functional and technical descriptions for use by programmers and business analysts to develop technical solutions.
Develop/change data input, files/database structures, data transformation, algorithms, and data output by using appropriate computer language/tools to provide technical solutions for complex application development tasks.
Document code and associated processes by adhering to development methodologies, adding code comments and appropriate documentation to various knowledge-base system(s) to simplify code maintenance and to improve support.
Provide monitoring and guidance in application design and development to more junior staff.
Provide thought leadership in designing and developing innovative integrated solutions.
TESTING AND DOCUMENTATION
Create and document complex test scenarios using the appropriate testing tools to validate and verify application functionality.
Test all changes by using the appropriate complex test scenarios to ensure all delivered solutions work as expected and errors are handling in a meaningful way.
Author and maintain documentation by writing audience-appropriate materials to serve as technical and/or end-user references.
Mentor junior staff in testing tools and technologies by reviewing their work.
IMPLEMENTATION AND MAINTENANCE
Implement changes by adhering to the change management policies and procedures for any given project to communicate to all parties the nature, significance, and risk factors of the solution.
Monitor changes and resolve complex problems by responding as they occur, by reviewing all processing and output of the newly implemented solution, and by proactively ensuring the solution works successfully in order to satisfy the customer requirements and to provide a smooth transition to the new solution.
SUPPORT OF PRODUCTION USERS OF NEW APPLICATIONS
Provide support by investigating and resolving issues, including complex issues to ensure prompt, effective service.