Global Medical Safety Overview - Adverse Event Data Analysis
Collect, process, report, and analyze adverse event data in Global Medical Safety, focusing on mostly spontaneous cases with approximately 5000 cases per day. The sample patient narrative illustrates a detailed medical history and treatment journey, showcasing the importance of data analysis in medical safety practices.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
JMP + Oracle = A Happy Marriage Brian Jackson, Untitled 6 Artwork from Reflections Art in Health September 2022 Peter Mroz, Methods & Analysis Global Medical Safety pmroz@its.jnj.com
Janssen R&D Discover and develop innovative medicines and solutions that transform individuals lives and solve the most important unmet medical needs of our time Peter Mroz Global Medical Safety Protecting patients by driving robust medical safety excellence and benefit-risk assessment JMP user since 2007 Methods & Analysis Develop and implement analytic tools to increase efficiency and analytical capability to detect and evaluate safety signals 2
Disclaimer This presentation represents my views and does not imply any formal endorsement of any product by Janssen/J&J 3
Agenda Introduction ODBC Configuring Oracle client ODBC connection string Bringing data into JMP Writing data back to Oracle Fast data insertion Faster data loading Executing Oracle PL/SQL procedures Hiding the password Error trapping Building IN lists Miscellaneous tips (throughout) 4
Introduction Global Medical Safety Collect, process, report, analyze adverse event data Mostly spontaneous cases 5000 cases/day 5
Sample Patient Narrative Subject xxxxxx had cancer (disease needs to be specified), which was diagnosed in Jun 1998. The subject previously received treatment with vvvvvvvvv, ddddddddd, mmmmmmm, and ppppppppp. The subject s pertinent medical history included cardiac arrhythmia, myocardial infarction, deep vein thrombosis, hypercholesterolemia, acute renal failure, and hypertension. On 30 Apr 2001 (Cycle 1, Day 33; 15 days after completing Cycle 1 therapy), the subject was admitted to the hospital for treatment of acute renal insufficiency. The subject complained of a cough one week before admission, and was treated with a dose of pppppppp for Pneumocystis carinii pneumonia and reported decreased urine output since that time. A baseline 24-hour urine collection on 14 Mar 2001 revealed 75% lambda light chain Bence-Jones protein, a total protein of 2700 mg/24 hours, and a urine M-protein of 2025 mg/24 hours; the investigator considered that the increase in monoclonal proteins was related to a dental abscess. On 27 Mar 2001, his baseline creatinine was 1.9 mg/dL. On admission, the subject s creatinine was 4.9 mg/dL, BUN was 72 mg/dL, and potassium was 5.5 mEq/L. His cloudy yellow urine had 30 mg/dL protein and bacteria (cultured as coagulase-negative Staphylococcus). An abdominal ultrasound showed a slight increase in echogenicity and irregular renal cortices that were consistent with renal disease. A physical examination revealed right lung base crackles and a temperature of 38.1C. On the following day, 01 May 2001, the subject s creatinine was 4.9 mg/dL, BUN was 62 mg/dL, and potassium was 4.7 mEq/L. The subject was discharged on 4 May 2001 (Day 38) with a creatinine of 4.6 mg/dL and a BUN of 65 mg/dL, and the event was considered resolved with sequelae. The subject was given xxxxxxxx at discharge. A 24-hour urine collection on 24 May 2001 revealed 5.24 g/24 hours Bence-Jones protein, total protein of 5 mg/24 hours, urine M-protein of 4 mg/24 hours, and a creatinine of 4.8 g/24 hours. The subject was discontinued from the study because of progressive multiple myeloma on 29 May 2001. Concomitant medications included aaaaaaaa, bbbbbbbb, cccccccc, dddddddd, nnnnnnnn, gggggggg, eeeeeeee, kkkkkkkk, ooooooooooo, qqqqqqqqq, pppppppp, and ssssssss. In the opinion of the investigator, the Grade 3 acute renal insufficiency was unrelated to xyzuvwxy. 6
Introduction JMP: great at statistical analysis and visualization Oracle: great at data storage Users: analyze/visualize data from Oracle using JMP Tabular reports of safety data Patient narrative drilldowns Safety data visualization Trending Forest Plots JMP + Oracle = A Happy Marriage 7
ODBC Open DataBase Connectivity ODBC drivers access a database using SQL Allows JMP client software to communicate with Oracle database Install Oracle client on PC 8
Configuring Oracle Client (1) Define two environment variables ORACLE_HOME TNS_ADMIN ORACLE_HOME points to folder where client is installed Example: C:\Oracle\19.0.0\client_1 Include $ORACLE_HOME\bin in the PATH environment variable Example: C:\Oracle\19.0.0\client_1\bin TNS_ADMIN points to location of TNSNAMES.ORA Example: C:\Oracle\19.0.0\client_1\network\admin Hint: point TNS_ADMIN to a fileshare location Multiple users can all point to this file Easier to maintain one version of TNSNAMES.ORA 9
What is TNSNAMES.ORA? Configuration file Network service names mapped to connect descriptors Net service names mapped to listener protocol addresses Providing an alias for your database # Development database MYDEVDB = (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=myservername)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=MYDEVDB))) 10
Determine ODBC Connect String Click the Windows button and type odbc Select ODBC Data Sources (64-bit) for 64-bit JMP Select ODBC Data Sources (32-bit) for 32-bit JMP 11
ODBC Connection String Click Drivers tab Find the Oracle ODBC driver you wish to use Oracle in OraClient19Home1 12
ODBC Connection String This is the only connection string you will need: dsn_string = "Driver={Oracle in OraClient19Home1};Dbq=TNS_ALIAS;UID=USERNAME;PWD=PASSWORD;"; Driver: Depends on Oracle client version Dbq: Database alias as defined in TNSNAMES.ORA UID: Schema name PWD: Password 13
Example ODBC Connection String dsn_string = "DRIVER={Oracle in OraClient19Home1};DBQ=MYDEVDB;UID=MYUSERNAME;PWD=MYPASSWORD;"; 14
Bringing Data Into JMP Several ways to get Oracle data into JMP: Open Database() Execute SQL() New SQL Query() Query Builder (File > Database > Query Builder) This talk will focus on Execute SQL() create database connection() Execute several SQL commands with execute sql() close database connection() 15
Execute SQL() From the scripting index: invisible creates an invisible table tableName is equivalent to SELECT * from tablename outputTableName provides a name for the JMP dataset 16
Example // Oracle data retrieval example dsn_string = "DRIVER={Oracle in OraClient19Home1};DBQ=MYDEVDB;UID=MYUSERNAME;PWD=MYPASSWORD;"; dbc = create database connection(dsn_string); sql_statement = "SELECT e.id, e.row_version_number, e.row_key, e.rep_last_name, e.rep_first_name, e.rep_email, e.rep_role FROM eba_sales_salesreps e ORDER BY e.rep_last_name, e.rep_first_name"; dt = execute sql(dbc, sql_statement, "EBA Sales Reps"); close database connection(dbc); 17
Tip 1 Replace single quotes inside character strings with two single quotes // Will not work sql_statement = "SELECT * FROM my_table m WHERE m.name = 'O'Malley'"; // Replace single quotes with two single quotes sql_statement = "SELECT * FROM my_table m WHERE m.name = 'O''Malley'"; 18
Tip 2 Use column aliases for readability Use \[ ]\ to avoid the need for escape characters sql_statement = "\[SELECT e.id "ID", e.row_version_number "Version Number", e.row_key e.rep_last_name e.rep_first_name e.rep_email e.rep_role FROM eba_sales_salesreps e ORDER BY e.rep_last_name, e.rep_first_name]\"; "Row Key", "Last Name", "First Name", "Email", "Role" 19
Writing Back to Oracle Update existing record(s) sql_statement = "UPDATE eba_sales_salesreps e SET e.rep_last_name = 'Smith' WHERE e.rep_first_name = 'Sweed'"; execute sql(dbc, sql_statement); Insert new record sql_statement = "INSERT INTO eba_sales_salesreps (id, row_version_number, row_key, rep_last_name, rep_first_name, rep_email, rep_role) VALUES (21, 1, 'ABCD', 'Doe', 'Joseph', 'joseph.doe@fastmail.com', 3)"; execute sql(dbc, sql_statement); 20
Writing Back to Oracle Execute SQL() does an implicit COMMIT UPDATE INSERT 21
Fast data insertion Executing multiple INSERT statements can be slow Use INSERT ALL sql_statement = "INSERT ALL INTO tmp_data(sample_number) VALUES('12340500979') INTO tmp_data(sample_number) VALUES('12340502848') INTO tmp_data(sample_number) VALUES('12340502875') INTO tmp_data(sample_number) VALUES('12340503578') INTO tmp_data(sample_number) VALUES('12340503600') INTO tmp_data(sample_number) VALUES('12340503991') INTO tmp_data(sample_number) VALUES('12340504865') INTO tmp_data(sample_number) VALUES('12340504902') INTO tmp_data(sample_number) VALUES('12340504945') INTO tmp_data(sample_number) VALUES('12340505347') SELECT 1 FROM DUAL"; 22
INSERT ALL Comparison Insert 100 rows one by one Insert 100 rows all at once SQL Insert Examples.jsl "One at a time: 6.487534" "INSERT ALL: 0.136179" 23
Faster Data Loading Use SQL*Loader for faster data loading Data file (csv, tab-delimited, etc.) .ctl file: Control file Structure for the data file and the target table .cmd file: Runs SQL Loader in a command window Generate .ctl and .cmd files using JSL Use runprogram()to execute .cmd file 24
Tab-Delimited Data File test_name p_value ror ror05 ror95 rr AB-VALUE 0.04 1.6 1.3 1.7 2.6 CD-VALUE 0.11 2.5 2.1 2.8 3.1 DE-VALUE 0.23 0.4 0.35 0.56 0.7 FG-VALUE 0.02 0.55 0.5 0.8 0.6 25
//SQL Load Example.jsl ctl_directory = "c:\temp\"; import_filename = "test_import.txt"; dest_table = "TEST_IMPORT"; tns_alias = "MYDEVDB"; //------------------------------------------------------------------------------- // Create control file ctl_file_text = evalinsert( "\[LOAD DATA INFILE '^import_filename^' INTO TABLE ^dest_table^ fields terminated by "\t" optionally enclosed by '"' TRAILING NULLCOLS (TEST_NAME, P_VALUE, ROR, ROR05, ROR95, RR, date_loaded "sysdate", user_name_loaded "upper(trim(SYS_CONTEXT('USERENV','OS_USER')))" )]\"); ctl_file_name = ctl_directory || "test_import.ctl"; // Save control file to disk save text file(ctl_file_name, ctl_file_text, mode("replace")); 26
// SQL Load Example.jsl (continued) //------------------------------------------------------------------------------- // Create a command file to run SQL*Loader cmd_file_text = evalinsert( "rem Run SQL Loader to load data into Oracle instance\!N C:\!N cd ^ctl_directory^\!N sqlldr userid=myusername/mypassword@^tns_alias^ control=test_import.ctl skip=1 log=test_import.log \!N pause\!N "); cmd_file_name = ctl_directory || "test_import.cmd"; // Save command file to disk save text file(cmd_file_name, cmd_file_text, mode("replace")); //------------------------------------------------------------------------------- caption("Running SQL*Loader to load data file"); wait(0); // The /c option carries out the command before terminating run_sql_load = runprogram(executable("cmd.exe"), options("/c " || cmd_file_name), readfunction("text")); 27
// SQL Load Example.jsl (continued) //------------------------------------------------------------------------------- // Error checking caption("Checking load results..."); wait(0); if (!contains(run_sql_load, "Rows successfully loaded"), ew = new window("Error Loading", << modal, text box("Errors encountered loading file. Contact tech support"), text box(""), panel box("SQL Load output:", text box(run_sql_load, set width(800), set wrap(800), << Set Font( "Courier New" )), ) ); throw(); ); 28
// SQL Load Example.jsl (continued) //------------------------------------------------ // Display the log file, just for information log_text = load text file(ctl_directory || "test_import.log"); log_win = new window("Log File", panel box("SQL*Load log file for " || import_filename, tb = textbox(log_text, set width(800), set wrap(800), << Set Font( "Courier New" )), ), ); caption(remove); wait(0); 29
Large File Demo 6 columns 30,000 records 30
Executing Oracle PL/SQL Procedures Surround procedure call with BEGIN/END sql_statement = "BEGIN myusername.pkg_util.send_email('Success'); END;"; execute sql(dbc, sql_statement); Control will return to JMP when procedure is done 31
Hiding the Password Hide the ODBC connection string in result table pref(ODBC Hide Connection String(1)); Create connection in encrypted JSL function Database name Username Password Function returns database connection Default local ensures function variables are not visible 32
my_dbc Function // File Name: my_dbc_unencrypted.jsl // Description: Function to get database connection string my_dbc = function({}, {default local}, // Check that environment variables have been set ohome = get environment variable("ORACLE_HOME"); thome = get environment variable("TNS_ADMIN"); if (::g_ohome == "" | thome == "", nw = new window("Oracle not Configured", << modal(), textbox( "Oracle has not been configured properly on your computer. Please contact technical support.") ); throw("Oracle not configured"); ); // Connection string for Oracle ODBC dsn_string = "Driver={Oracle in OraClient19Home1};Dbq=MYDEVDB;UID=MYUSERNAME;PWD=MYPASSWORD;"; _dbc = create database connection(dsn_string); // Return the Oracle connection _dbc; ); 33
Encrypt Script Edit > Encrypt Script 34
To use encrypted script Save as my_dbc.jsl To use: include("c:\jsl\my_dbc.jsl"); dbc = my_dbc(); sql_statement = "SELECT * FROM eba_sales_salesreps"; dt = execute sql(dbc, sql_statement, "EBA Sales Reps"); close database connection(dbc); dbc from the log: Database( "Oracle in OraClient19Home1" ) 36
Error Trapping Oracle SQL Commands Function name: log_execute_sql Executes SQL and traps ODBC errors found in the log If any are found, display a warning message to the user If the global variable::g_ex_sql_debug is set to 1, display the SQL before executing it Very useful for developing/debugging SQL Uses log capture to inspect the log for errors string = log capture(expr); Evaluates the expr argument and captures the output that would have appeared in the JMP log window and returns it in a string instead 37
log_execute_sql: Arguments Argument Description caller Calling program. This will be included in the error message dbc Pointer to database created with create database connection call sql SQL statement to run invisible If 1, return an invisible table. If 0, returned table will be visible tbl_name Table name to give the returned results table 38
log_execute_sql: Examples pname = "Example log_execute_sql"; dbc = create database connection(dsn_string); // Working SQL sql1 = "SELECT SYSDATE FROM DUAL"; dt1 = log_execute_sql(pname, dbc, sql1, 0, "System Date"); // SQL with an error in it sql2 = "SELECT SYSDATE FROM DUALx"; dt2 = log_execute_sql(pname, dbc, sql2, 0, "System Date2"); close database connection(dbc); 39
log_execute_sql: Program Output sql1: sql2: 40
log_execute_sql: Debug Output // Show debug window ::g_ex_sql_debug = 1; sql3 = "SELECT t.id, t.row_version_number, t.row_key, t.rep_last_name, t.rep_first_name, t.rep_email, t.rep_role, t.created_by, t.created, t.updated_by, t.updated, t.flex_n08 FROM EBA_SALES_SALESREPS t WHERE t.rep_role = 1"; dt = log_execute_sql(pname, dbc, sql3, 0, "Sales Reps"); 41
//------------------------------------------------------------------------------//------------------------------------------------------------------------------ /* Function Name: log_execute_sql Description: Execute SQL and trap ODBC errors found in the log file. If any are found, display a warning message to the user. If the global variable global:g_ex_sql_debug flag is set to 1, display the SQL before executing it Arguments: _caller _dbc _sql _invisible _tbl_name Calling program. This will be included in the error message database connection created with create database connection call sql statement to run If 1, set the invisible flag. If 0, don't set it. If 2, make private (new 10-28-2020) Optional table name to give the returned results table Subprograms: execute sql batch interactive log capture Returns: pointer to dataset This preference is optional but good to use: pref(ODBC Hide Connection String(1));// Don't show connection string in dataset */ 42
log_execute_sql (continued) Example calls: 1. Retrieve data from a table into an invisible dataset pname = "Main Program"; dbc = Create Database Connection(::db_dsn);// ::db_dsn contains your database connection string // The two colons means this is a global variable btw // Get values from PARAMETERS table sql_statement = "SELECT rp.parameter_name, rp.parameter_value FROM myschema.my_parameters rp"; param_dataset = log_execute_sql(pname, dbc, sql_statement, 1, "My Parameters"); close database connection(dbc); 2. Execute an INSERT statement: dbc = Create Database Connection(::db_dsn);// ::db_dsn contains your database connection string sql_statement = evalinsert( "INSERT INTO myschema.my_activity_log (user_created, date_created, my_section, activity, parameter1, value1, parameter2, value2, parameter3, value3, parameter4, value4, parameter5, value5, parameter6, value6, parameter7, value7, parameter8, value8) VALUES ('^one_current_user_id^', CURRENT_DATE, 'DASHBOARD', 'REFRESH', 'Product Name', '^product_name_txt^', 'Owner', '^owner_txt^', 'Search Event Text', '^search_event_txt^', 'Date From', '^date_from_txt^', 'Date To', '^date_to_txt^', 'Alert Status', '^alert_status_txt^', 'Event Types', '^concat_event_types_list^', 'Alert Types', '^concat_alert_types_list^')"); // Run this SQL. No table is retrieved as it's a simple insert log_execute_sql(pname, dbc, sql_statement, 0, ""); Close Database Connection(dbc); 43
// log_execute_sql (continued) log_execute_SQL = function({_caller, _dbc, _sql, _invisible, _tbl_name}, {Default Local}, if (isempty(_dbc), _nw = new window("log_execute_sql: Empty Value", << modal, textbox("Error in log_execute_sql: The database connection is empty."), ); throw("Empty database connection string"); ); if (isempty(_sql) | _sql == "", _nw = new window("log_execute_sql: Empty Value", << modal, textbox("Error in log_execute_sql: The SQL statement is empty."), ); throw("Empty SQL string"); ); // Check the debug flag _debug = 0; if (! is empty(global:g_ex_sql_debug), _debug = global:g_ex_sql_debug; ); 44
// log_execute_sql (continued) if (_debug, _nw = new window("log_execute_sql DEBUG", << modal, lineup box(ncol(2), text box("_caller: "), text box(_caller), text box("_dbc: "), text box(char(_dbc)), text box("_invisible: "), text box(char(_invisible)), text box("_tbl_name: "), text box(_tbl_name), ), panel box("SQL", sql_teb = text edit box(_sql) ), hlistbox( _cb = check box("Turn off subsequent debug output"), _tb = text box(" "), _bb = button box("OK", is_checked = _cb << get(1); if (is_checked, global:g_ex_sql_debug = 0; ); ), ), sql_teb << set nlines(40) << set width(500); ); ); 45
// log_execute_sql (continued) // OK we're good to go now. batch interactive(1);// New 5-17-2018 force all errors to go to the log _log_contents = log capture( if (_invisible == 1, _dt = execute sql(_dbc, _sql, invisible, _tbl_name); , _dt = execute sql(_dbc, _sql, _tbl_name); ); ); batch interactive(0);// New 5-17-2018 errors will not be forced to go to the log // Check the log window for ODBC errors //_log_contents = get log(); _found_error = 0; crlf = hex to char("0D0A"); if (contains(_log_contents, "[Oracle][ODBC]") | contains(uppercase(_log_contents), "ERROR"),// Looking for errors // then _found_error = 1; _log_list = words(_log_contents, crlf); _error_msg = _log_list[1]; ); 46
// log_execute_sql (continued) if (_found_error, // then //print(_sql);// Write the SQL out to the log window _nw = new window("log_execute_sql: Oracle ODBC Error", << modal, textbox("An Oracle error occurred in " || _caller || ": "), panel box("Please email to technical support:", sql_teb = text edit box(evalinsert( "Calling Program: ^_caller^ Error Message: ^_error_msg^ SQL: ^_sql^"))), sql_teb << set nlines(30) << set width(500), ); ); // Return the table pointer _dt; );// end log_execute_sql 47
Example Debugging // Debugging SQL.jsl pname = "Debugging SQL.jsl"; dbc = my_dbc(); sql_statement = "SELECT t.id, t.row_version_number, t.row_key, t.rep_last_name, t.rep_first_name, t.rep_email, t.rep_manager_id, t.rep_eba_sales_username, t.rep_role, t.created_by, t.created, t.updated_by t.updated FROM eba_sales_salesreps t WHERE t.rep_role = 1"; dt = log_execute_sql("Test", dbc, sql_statement, 0, "Test"); close database connection(dbc); 48
Building IN Lists The Oracle IN operator determines whether a value matches any values in a list Example SELECT * FROM EBA_SALES_SALESREPS t WHERE t.rep_last_name IN ('Davidson', 'Raj', 'Sanjay', 'Jing'); Similar to JSL contains() function rep_last_name = "Raj"; pos = contains({"Davidson", "Raj", "Sanjay", "Jing"}, rep_last_name); show(pos); pos = 2; Limit of 1000 values 49
Oracle SQL IN List Function name: get_sql_in_list Build an IN list from the list provided. If there are more than 1000 items in the list, separate them into 1000- element chunks connected via UNION to avoid the limit of 1000 items in an Oracle IN list If the elements are of type String, single quotes will be replaced with two single quotes, and single quotes will be put around each item Arguments: item_list: List of items to create an IN list from preamble: SQL string to preface the IN list with 50
