Query+Health+DIRECT+and+FlatFile+based+POC

include component="page" wikiName="siframework" page="Query Health Header" In an effort to reduce the complexity of the Query Health process, and to minimize development time and cost, we are providing a novel Direct-compliant process that uses MS Excel models and e-mail transport to execute the queries. On 3/20/12, we presented our new proof of concept, which follows ...
 * This wiki page describes Query Health DIrect proofs of concept using ReAsure HealthNode™ (RAHN™) technology from National Health Data Systems.**

= New Proof of Concept: Use of XML data files and Excel Power Pivot table = This section describes our simple, Direct-compliant Query Health process, which uses MS Excel to:
 * Create an Access database payload file from XML files generated by an EHR and then
 * Generate the query results by consuming and processing the Access file using a PowerPivot table (and regular Excel Pivot tables).

Viewing the Video
You can view a live video of or new proof of concept that deploys this process at: [|RAHN QueryHealth POC video] ([]) An excellent approach and one that derserves more attention by the WG.

Sending Query Requirements (Envelope) to Responder
Since the video does not show how the query requirements (query envelope) are sent from the Requestor to the Responder, I will now discuss ways to do it.The simplest way is to send the Responder a message saying something like:

//“We are [Requestor's name] and are requesting patient data for eMeasure NQF 59, which evaluates diabetic blood glucose control. We therefore need your EHR to generate standard CCR or CCD XML data files for patients who are between 18 and 75, specifically those who have a diagnosis of diabetes and take medications for the diabetes. //

//The easiest way to provide these data is to generate the CCR or CCD XML data files for all patients who at least 18 years of age on 1/1/2011 (measurement start date) and no older than 75 on 12/31/2012 (measurement end date). The files should include all data elements for the following data types (database fields): //
 * //Diagnosis //
 * //Medication //
 * //Encounter //
 * //Laboratory results //

//The RAHN Responder software will generate a database file from those XML files containing only the required data and send that file to the Requestor for analysis. The results will be returned to you promptly.” //

The Responder app will then extract from the XML files only the data elements it needs for analysis, as well as eliminate all null records (those lacking the pertinent data). I have other ideas about how this can be done, but what I just described currently appears to be the simplest.

In addition, all required policy information and HQMF header data can be sent as necessary in a structured or unstructured file format as an e-mail attachment via Direct.

CEDD Implementation Model Mapping
Section 5 of Query Health CEDD_Version 1 0.docx, provies implementation guidance "...to assist vendors and developers in ensuring that the clinical data source they wish to make available can be queried successfully. Analysis of the implementation models that the Query Health technical approach are used to provide developers and other technical stakeholders clear and tangible guidance on how to implement the conceptual/logical aspects of the Query Health CEDD within their own internal environments. The intent in using an implementation model-aligned approach is to encourage greater adoption of the Query Health technical approach, to support multiple implementation paths. Since much of the information generated by clinical workflow and usage of clinical data systems can be captured in clinical data sources, including an Electronic Health Record (EHR), the distributed query approach within Query Health attempts to leverage that data at the source, by outlining a conceptual design closely aligned to successful current approaches, such as i2b2, PopMedNet, and hQuery, as well as existing specifications such as HITSP C32, the Continuity of Care Document (CCD), and the Consolidated CDA Implementation Guide."

Since none of those approaches use Excel and PowerPivot tools, I constructed the following grid that corresponds to the one in the model mapping document, which describe the RAHN approach. In essence, it shows that Non-calculated and Calculated Columns in the Excel PowerPivot tables provide the data elements, value/code sets, and analytics to perform the queries. •MeasurementStartDate •MeasurementEndDate || e.g.: =DATE(2011,01,01) ||
 * ** S&I CEDD Defined Object ** || ** S&I CEDD Data Elements ** || ** RAHN Tables ** || ** RAHN Dimension Data Element ** || ** Implementation Guidelines ** ||
 * ** Time Period ** || n/a || Excel PowerPivot (XLPP) Table || Custom Measures:
 * ** Patient Information ** || Race || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Ethnicity || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Gender || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Age || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table ||   ||
 * ^  || Zip Code || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table ||   ||
 * ** Provider Information ** || Provider Name || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table ||  ||
 * ^  || Provider Location || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table ||   ||
 * ** Insurance Information ** || Insurance Coverage || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Insurance Type || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ** Encounter ** || Last Seen/Visit || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ** Diagnosis ** || Diagnosis Code || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ** Vital Signs ** || Alive (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || A value of Y, M, or N ||
 * ^  || Systolic/Diastolic Blood Pressure (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || HBA1C (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Eye Examination (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || BMI (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Smoking Status (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Foot Examination (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || LDL (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Microalbumin level (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Microalbumin result (part of Vital Sign Observation) || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ** Medication ** || Medication Name || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||
 * ^  || Medication Type || Excel PowerPivot (XLPP) Table || CCD or CCR flattened XML string in Non-calculated column in XLPP Table || Calculated Column(s) in XLPP Table contain all standard (and other) value/code set elements ||

Direct Project Implementation Video
For a seven minute video of a Direct implementation using the RAHN ™ pub/sub nodes to transmit patient data as encrypted e-mail attachments (via our referral manager app that we recently presented at an IEEE humanitarian technology conference), go to [].

=Previous Proof of Concept: Create and Deliver SQL Query=

The proof of concept (PoC) begins with a pair of MS Excel workbook apps, one for the Requestor and the other for the Responder. Each app uses VBA macros to process data in spreadsheet cells. An Access database from MS (which is included with Access purchase) is being used as the data store for this initial PoC since an EHR database is unavailable.

The Requestor app UI is used to define the SQL query parameters through a spreadsheet in which the end-user selects tables and fields, and then defines filtering and sorting criteria. In a subsequent version, Active-X user forms could be used to populate the spreadsheet. And while there are no joins in this first PoC query, and while it is limited to 1 table and 3 fields, subsequent versions could accommodate any number of query parameters.

After defining the parameters, the user clicks a button that executes a macro, which copies the parameters to another Excel spreadsheet file, encrypts it, and stores the “parameter definition” (PD) file in a folder ready to be shipped to the Responder (e.g., as an e-mail attachment). Note that the PD file does not have macro capabilities (it’s a .xlsx file), but it could just as easily been be any type of delimited text file. Note that while the encryption in the PoC is consistes of the native Excel workbook password protection; multiple levels of encryption are can be added (as we have done in other apps).

Upon receipt of the PD file, the Responder end-user enters the database path then clicks a button on his/her Excel app. This Active-X control then launches a macro that retrieves the file, decrypts it, and copies its contents to corresponding cells in one of the app’s own spreadsheets. The macro code then creates an SQL statement that incorporates the parameters from the data that was copied to the spreadsheet cells, and then executes the query against the local database. The results payload are automatically saved to another Excel file, encrypted, and stored in a folder ready to be shipped back to the Requester. The data also appears on the Responder’s spreadsheet for review. Note that if the query fails, a message box display the actual SQL statement for examination. While the returned data could be sent merely as numerators and denominators, the current PoC returns a flat file of the actual tuples.

Upon receipt of the payload file, the Requestor clicks a button on the app and the results are immediately displayed on a spreadsheet. These data are “live” and can be aggregated and/or otherwise processed by the Requestor using Excel and/or other tools.

Note that in addition to SQL, Excel can also utilize pivot table to query for aggregated data from OLAP-based data warehouses.

BTW, we can also demonstrate a Direct method for e-mailing the files back and forth in a pub/sub node-to-node (app-to-app) architecture. One reason for pub/sub is that the Requestors must know the schemas of all the Responder databases before sending the requests. Since there are many hundreds of approved EHRs, and since the schemas of any of them could change at any time, the pub/sub method enables the requisite “handshake.”

A video of this PoC is at []

<span style="font-family: Arial,Helvetica,sans-serif;">You can download the four PoC files (which includes the database) in a zip file from[| http://nhds.com/dp/queryhealthbyspreadsheet v1.7.zip] [newest version uploaded on 11/17/11] – Just unzip to any folder and following the instructions in the PDF file. Note that I’m prepared to demonstrate it live during our next meeting. Note that you need MS Excel 2007 or newer to run the PoC apps.

=Participants:=
 * Participants || Organization Name ||
 * Steve Beller, PhD || National Health Data Systems, Inc. ||
 * Sabatini Monatesti || COO, NHDS Inc. ||

include component="page" wikiName="siframework" page="space.template.inc_contentleft_end"