yngdaveb wrote on Friday, January 14, 2011:
I’m looking for test data for the OpenEMR MySQl patient_data table. I’d like to import this data for testing purposes.
johnbwilliams wrote on Friday, January 14, 2011:
This is an important topic. I.e. How can patient records (de-identified or fake) be shared among OpenEMR instances for test purposes.
For example, many of us are involved with meaningful use development of OpenEMR, and we should be using NIST-defined and other patient test records. It would be inefficient for each collaborating developer to reproduce thier own NIST test patient records, not to mention the likely subtle errors introduced by different manual entries of the test data.
Moreover, adequate QA testing, requires a large amount of thougtfully designed test vectors (patient test records).
A tool to input, output and share multiple test vectors (patient test records) for OpenEMR is needed.
Any volunteers to create tool?
johnbwilliams wrote on Friday, January 14, 2011:
Continung my previous post, for lack of a better name, I will dub this tool the “record i/o” tool.
The volunteer developer sought to create this “record i/o” tool should have strong knowledge of the OpenEMR DB schema, and could develop this tool very quickly.
I’ll also dub the term “record pack” to be a file of OpenEMR patient records created by the “record I/O” tool A “record pack” file will be created by executing the “Record I/O” tool on an instance of OpenEMR. An instance of OpenEMR can be populated with patient records from a “record pack” using the “Record I/O” tool.
Beyond the initial use case (described in previous post) of enabling collaboration among OpenEMR developers, Record I/O can be extended in to other important uses.
For example, CCR/CCD QA. The first effort I would make to identify “low hanging” bugs in the new OpenEMR CCR/CCD Reporting module is to extend Record I/O to read a patient record from CCR (or CCD) XML file and then write the this record into an OpenEMR instance. Then I would create an OpenEMR CCR.xml from this patient record, and diff the two CCR files to identify & correct bugs. Repeating this with the many publically avaialble CCR and CCD test files will identify many if not most of the bugs in the OpenEMR CCR/CCD Reporting module.
Another example: New feature in OpenEMR to create or modify a patient record from from a CCR or CCD orginated by another provider. If an OpenEMR practicioner judges the CCR/CCD issuing provider to be trustworthy, AND the OpenEMR practictioner chooses to add the CCR/CCD patient record to the practitioner’s patient record in OpenEMR, this could be done. Today, many practitioners are wary of doing this but it will be come more and more frequent as providers become more comfortable with EHRs and HIE.
Another example: Medication reconciliation. An expected frequent activity in medication reconciliation is to compare a patient’s medication list in an OpenEMR patient record, to a patient medication list from another provider in the form of a CCR/CCD. A variation on Record I/O tool and the CCR/CCD QA tool, is to a) read a patient’s medication list from an OpenEMR record; b) read a patient’s medication list from a externally provided CCR/CCD; allow the OpenEMR practicioner to visually compare the two lists, and d) allow the OpenEMR practitioner to select all or individual medication records from the external CCR/CCD to be added to the patient’s record in OpenEMR. While the NIST stage 1 certification requirement for medication reconciliation does not require all of this functionality, I suggest that this is where they are going with it, probably in stage 2. (My understanding is NIST stage 1 includes only items a and b from above.
I could go on and on - there are numerous valuable applications involving reading and writing of patient records to/from OpenEMR, and reading and writing patient records to/from patient summary care documents (CCR/CCD).
johnbwilliams wrote on Friday, January 14, 2011:
Continuing my rant on OpenEMR database I/O tools (see two previous posts in this thread) …
I suggest a first step of defining the file format of the OpenEMR “Record Pack”. One possible format is XML. One could go all out and define an XML schema representation of the OpenEMR DB schema, but this is overkill for gettting started.
For getting started, a plain old XML file with attributes tags corresponding to OpenEMR DB tables and records will suffice for now.
Thoughts?
Anybody with a knowledge of the OpenEMR DB schema willing to whip up this xml file definition?
Thanks
John
aethelwulffe wrote on Friday, January 14, 2011:
Can we just export the table to a csv, then apply a scramble, replacement, or cypher scheme, then port the data back to a table? Alternately, how about just a “standard” fake patient pack that everyone will use, add to etc… for testing purposes as well as to demonstrate bugs/behavior? How many patients would it take after all? A dozen? 64,556? If we put a test db in a repository, and even have other things in a dev pack (perhaps other tools as well, like any sql report scripts that other developers find useful on occasion) would this not be smart?
If it is to be a more-than-one-use item, I will gladly plug in fake names and numbers to generate a test db.
yngdaveb wrote on Friday, January 14, 2011:
I use this combination of SQL statements:
‘ Script to initialize the OpenEMR patient_data table.
DELETE FROM patient_data;
‘ Script to insert into OpenEMR patient_data table, from the fakenames table.
INSERT INTO `patient_data` (`sex`, `fname`, `lname`,`mname`,`street`, `city`, `state`,`pid`)
(SELECT `gender`, `givenname`, `surname`, `middleinitial`, `streetaddress`, `city`, `state`, `pid` from `fakenames`);
‘ Script to create the fakenames table and populate it.
DROP TABLE `fakenames`;
CREATE TABLE `fakenames` (
`pid` int NOT NULL AUTO_INCREMENT,
`gender` varchar(6) NOT NULL,
`givenname` varchar(20) NOT NULL,
`middleinitial` varchar(1) NOT NULL,
`surname` varchar(20) NOT NULL,
`streetaddress` varchar(100) NOT NULL,
`city` varchar(100) NOT NULL,
`state` tinytext NOT NULL,
PRIMARY KEY (pid)
);
insert into fakenames values(’ ‘,‘female’,‘Cynthia’,‘O’,‘Stanley’,‘1840 Reeves Street’,‘Oconto Falls’,‘WI’);
insert into fakenames values(’ ',‘female’,‘Betty’,‘D’,‘Thornton’,‘2271 Colony Street’,‘Hamden’,‘CT’);
……. On for another 2,998 records……
I get my randomly generated data from various sites like
johnbwilliams wrote on Saturday, January 15, 2011:
aethelwulffe:
For the purpose of producing a tool as quick as possible, for exporting, transferring and importing OpenEMR patient recorrds among collaborative developers, your approach is right on. I suggest that the “scramble, replacement, or cypher scheme” is unnecessary as this tool is not to be used for exporting, transferring nor importing live patient data (PHI, IIHI) into OpenEMR
yngdaveb:
Thats a great start for writing records into oemr. What would CREATETABLE look like to write a complete patient record in OpemEMR?
Backing up, what is the definition of an OpenEMR patient record, for this purpose transfering patient test records among developers?
aethelwulffe wrote on Saturday, January 15, 2011:
I’m dribbling bits into a fake database right now. I’m trying to get facilities and everything set up very much in the way our “real” one is set up.
I have not decided if I should use fake or real insurance companies, x-12 partners, etc… as well. Fake calender, providers, appointments…you name it, I’m adding it. I would much rather have a fake database than and old backup copy of our data here (that has to be secured just like the up-to-date one) for testing and demonstration.
jcahn2 wrote on Saturday, March 19, 2011:
Ahoy Art.
Did you ever get this data set completed? Is it specifically compatible with 4.0.0? Can we get a link to it, maybe on the wiki?
Thanks, Jack