Part I: Supporting The GDR User

1. Bertrand Russell (1872-1970) described his philosophy as a kind of “logical atomism”, by which he meant to endorse both a metaphysical view and a certain methodology for doing philosophy. The metaphysical view amounts to the claim that the world consists of a plurality of independently existing things exhibiting qualities and standing in relations. According to logical atomism, all truths are ultimately dependent upon a layer of atomic facts, which consist either of a simple particular exhibiting a quality, or multiple simple particulars standing in a relation.The methodological view recommends a process of analysis, whereby one attempts to define or reconstruct more complex notions or vocabularies in terms of simpler ones. According to Russell, at least early on during his logical atomist phase, such an analysis could eventually result in a language containing only words representing simple particulars, the simple properties and relations thereof, and logical constants, which, despite this limited vocabulary, could adequately capture all truths. Russell's logical atomism had a profound influence on analytic philosophy in the first half of the 20th century; indeed, it is arguable that the very name "analytic philosophy" derives from Russell's defense of the method of analysis.Stanford Encyclopedia of Philosophyhttp://plato.stanford.edu/entries/logical-atomism/

2. Part I: Supporting The GDR User Learn how to help solve a site’s data problems using the GDR in just one hour!

3. Our Advanced Objective: • Make Meaningful Modules

4. End with SQL • Everyone’s First SQL lesson • In Structured Query Language (SQL) we: • SELECT A comma separated list of columnsFROM A comma separated list of ‘tables’WHERE Two columns are equal (joins) or Some fact about a column is true

5. End with SQL: SELECT • Comma separated list of columns • Practical English: the columns of data which need to show in the report • Advanced: Columns, always including PATID, which make a useful module • Patid, name, mrn, dob • Patid, first fever time • Patid, delay code, case type (from peri-operative note)

6. End with SQL: FROM • Comma separated list of ‘tables’ • Practical English: Essentris data sources, usually identified in part by FULLNAME, Note Name, or else documented in the GDR manual • Advanced: Any of the the above plus any of the user-defined or CCI-defined GDR code modules • FAQ: where is ____ data in the GDR?

7. End with SQL: WHERE • Two columns are equal (joins) or • Some fact about a column is true • FAQ: How are __ and __ related to each other in the GDR? • A: Depends… • PATID: same patient, almost always part of a join • KEY: same ‘time’, part of a join when concurrency is an issue • MINORIT:same row in Order Entry or on Order Flowsheets

8. Start with Essentris • We already know our way around Essentris • Identify GDR related details in Essentris (look for “SELECT…FROM..WHERE…” patterns) • Admit Notes • Timed Notes • Vitals Flowsheets • Orders and Order Flowsheets • Admission/Discharge/Transfer functions

9. Admit Notes • User defined template document • Only one copy stored on the patient record • Has labels, checkboxes and text fields • In EDIT mode, right-click checkboxes and text fields, select “describe item”, then double click the Label “Item Edit History” • Text fields can be of two types • DATABASE: • Reference the DBI FULLNAME • NOTE: • Reference the TAG String (ex: !207.ABC^5.10)

10. Image: Notes Describe Item

11. Image: Notes Describe Item

12. Admit Notes tangent: Consider just One DBI View in the GDR • 13,000 DBI Views are all the same in these ways: • GDR Database Item VIEWNAME = FULLNAME + ‘_’ + IT# (kind of) • Columns • KEY • MINORIT • PATID • DATA • OLID & NLID

13. Image: Single DBI Data

14. GDR DBI View Columns: KEY • “Time of the data” • ADMIT note: KEY = admit datetime • Timed/Prompt Note: KEY = “Note Time” • Flowsheet: KEY = time column • KEY  LOGTIME (almost always…)

15. GDR DBI View Columns:MINORIT • In Essentris components which have dynamic or changing rows, MINORIT is like a row number • Order Entry • Order Flowsheet

16. GDR DBI View Columns:PATID • Internally generated encounter number • Unique across installations • Column that joins a single patient’s data • NOT visible in Essentris • Column that is almost never shown in Reports

17. GDR DBI View Columns:DATA • DATA column shows the value that was stored in Essentris. • Almost always defined as varchar2(4000) • All dates and numbers have to go through a datatype conversion from text in order to perform any math on the data • Sometimes defined as Number datatype

18. GDR DBI View Columns:OLID & NLID • Literally: Old Link ID and New Link ID • The Triple: PATID, OLID, NLID uniquely identify a charting event in Essentris • All data stored by one person at one time will have the same triple. • Reference the triple in the LOGTABLE to find the details of the charting event

19. Admit Notes: One DBI example SQL • FULLNAME = Name • VIEWNAME = NAME_517 • (refer to site/master files, or A_ITCONTRAST in the GDR) SELECT patid, dataFROM name_517

20. Image Name_517 Data

21. Admit Notes: Three DBI example SQL (Patient Details) #1 • VIEWNAMES: NAME_517, MRN_518, DOB_519

22. Admit Notes: Three DBI example SQL (Patient Details) #1 • VIEWNAMES: NAME_517, MRN_518, DOB_519 • SELECT NAME_517.patid, NAME_517.data, MRN_518.data, DOB_519.dataFROM NAME_517, MRN_518, DOB_519WHERE NAME_517.patid = MRN_518.patid and NAME_517.patid = DOB_519.patid

23. Image: 3 DBIs #1

24. Admit Notes: Three DBI example SQL (Patient Details) #2 • SELECT name.patid, name.data, mrn.data, dob.dataFROM NAME_517 name, MRN_518 mrn, DOB_519 dobWHERE name.patid = mrn.patid and name.patid = dob.patid

25. Image: 3 DBIs #2

26. Admit Notes: Three DBI example SQL (Patient Details) #3 • SELECT name.patid, name.data name, mrn.data mrn, dob.data dobFROM NAME_517 name, MRN_518 mrn, DOB_519 dobWHERE name.patid = mrn.patid and name.patid = dob.patid

27. Image: 3 DBIs #3

28. Admit Notes: Three DBI example SQL (Patient Details) #4 • Without specifying the columns names: • SELECT *FROM NAME_517 name, MRN_518 mrn, DOB_519 dobWHERE name.patid = mrn.patid and name.patid = dob.patid

29. Admit Notes: Note Items • Reference the TAG string for the Text Prompt (ex: !200.PER^6.2) • All sites use code like the following which comes from a template to get Note Item data from notes:

31. Admit Notes: Note Items SQL (Real Example: Peri-Operative data) • Code below yields one record to one patient • SELECT patid, key, max(decode(tag,497,data)) Delay_Code, max(decode(tag,2 ,data)) Case_Type, max(decode(tag,484,data)) DT_Univ_protocolFROM peri_operativenote_200perGROUP BYpatid, key

32. Image: P/O Note Data

33. Timed Notes: Note Items SQL (Generic Example) • Code is the same logically, but the result is many records to one patient • SELECT patid, key, max(decode(tag,1,data)) col_name_for_tag1, max(decode(tag,2,data)) col_name_for_tag2, max(decode(tag,3,data)) col_name_for_tag3FROM timed_note_viewGROUP BY patid, key

34. Essentris Flowsheets

35. Image: Flowsheet

36. Parameter Flowsheets (Vitals) • Time driven values • Database items • DBIs have a “many to one” relationship to patient • Most reports require we find a single value to report: • MIN/MAX(data or key), the value closest to a point in time, a single LIST of values, etc • Turn chronological DBI charting into Start and Stop times for treatment intervals: vent use, restraints, etc

37. Vitals Flowsheet Database items • Right-click, select “Describe Item” to find the site-defined FULLNAME • FULLNAME is the prefix of the GDR VIEWNAME for that data

38. Image: Flowsheet Describe Item

39. GDR Database Items • VIEWNAME = FULLNAME + ‘_’ + IT# • Columns • KEY • MINORIT • PATID • DATA • OLID & NLID

40. Flowsheet Database Item SQL example: TempF • FULLNAME: TempF(DegF) • VIEWNAME: TEMPF_DEGF__163 • SELECT patid, dataFROM tempf_degf__163WHERE data > 100

41. Image: tempf #1

42. Flowsheet Database Item SQL example: First Fever (a la TempF) per patient • SELECT patid, min(key) keyFROM tempf_degf__163WHERE data > 100GROUP BY patid

43. Image: tempf #2

44. - STOP - • We’ve already made three modules without even talking about what they are • GDR Modules are Reuseable Blocks of SQL code which accomplish a specific task • Module1: Patient Details • Module2: Peri Operative Data • Module3: Time of First Fever

45. Putting Modules to work: Easy! • Best Practices involves identifying a main or “driving list” (DL) • DL is verifiable, simple, and is the left side of all joins in the WHERE clause • We’ll use patient detail as the driving list • If this were easy, the code would be: • SELECT * from patient_details, peri_operative_data, first_fever_timeWHERE patient_details.patid = peri_operative_data.patid andpatient_details.patid = first_fever_time.patid

46. Putting Modules to Work: Easy… • With formatting: • SELECT * -- this * means: all the columnsFROM patient_details, peri_operative_data, first_fever_timeWHERE patient_details.patid = peri_operative_data.patid and patient_details.patid = first_fever_time.patid

47. Putting Modules to Work: Easy? • With “stand in” fake code to show the logic: • SELECT * FROM (select…from…where…) patient_details, (select…from…where…) peri_operative_data, (select…from…where…) first_fever_timeWHERE patient_details.patid = peri_operative_data.patid and patient_details.patid = first_fever_time.patid

48. Putting Modules to Work: Not Easy To Read • With REAL code to show the final resulting code: SELECT * FROM -- (SELECT name.patid, name.data name, mrn.data mrn, dob.data dob FROM NAME_517 name, MRN_518 mrn, DOB_519 dob WHERE name.patid = mrn.patid and name.patid = dob.patid )patient_details,-- (SELECT patid, key, max(decode(tag,497,data)) Delay_Code, max(decode(tag,2 ,data)) Case_Type, max(decode(tag,484,data)) DT_Univ_protocolFROM peri_operativenote_200per GROUP BY patid, key)peri_operative_data, --- (SELECT patid, min(key) key FROM tempf_degf__163 WHERE data > 100 GROUP BY patid)first_fever_time -- WHERE patient_details.patid = peri_operative_data.patid and patient_details.patid = first_fever_time.patid

49. Putting Modules to Work: Easy? • With “stand in” fake code to show the logic: • SELECT * FROM (select…from…where…) patient_details, (select…from…where…) peri_operative_data, (select…from…where…) first_fever_timeWHERE patient_details.patid = peri_operative_data.patid and patient_details.patid = first_fever_time.patid

50. Flash Back Slide- Admit Notes: Three DBI example SQL (Patient Details) #4 • Without specifying the columns names: • SELECT *FROM NAME_517 name, MRN_518 mrn, DOB_519 dobWHERE name.patid = mrn.patid and name.patid = dob.patid