Brents IT Blog

Random thoughts by an IT GOAT

NAVIGATION - SEARCH

Eclinicalworks, EHR, and the pitfalls of sucessful databases - A Word of Caution!

DISCLAIMER:  I have not spoken directly to ECW about these issues(though i hear they are aware), there may be good reasons as to why these issues exist, i just dont know them, the fast pace growth is the only logical reason i can come up with.  This is for informational purposes only and i applaud ECW because they were willing to use an open source database system(MySQL) and for making their database as open as it is.

Just recently I attended the ECW conference in Phoenix, Arizona.  It was a nice conference with a few thousand people in attendance.  It had all the aspects of a standard conference with its sessions, exhibitor hall and great interactions with other users.  Some of those collaborations can be quite fruitful and some quite enlightening.  Unfortunately it was at this conference when I started to piece together a significant issue with the ECw EHR product... its database which is the main reason for many complaints from users.

I’ve been building databases for 10 years.  While it’s not the lynch pin of my job duties, it does occur enough to where I think I know how to construct and deploy a successful database.  When I started to deep dive into the MySQL table structure of Eclinicalworks, I started having palpitations.  Their structure is that of a beginner’s database design, table after table created to fulfill a need but no fore thought placed on database growth, efficiency and data integrity.  As anyone who has ever tried to pull a report from their first database has figured out, you cannot just have text based data floating around, it needs to be referential, otherwise it’s impossible to account for the many ways to spell or misspell a state name(for instance).  Of course your database works in the end and theirs does, so it’s not a total loss; it’s just that you cannot report on that data quickly, efficiently or consistently. 

For me, realizing that their database was this bad, really made me re-evaluate my own knowledge of databases and try to figure out how they got soo big, without ever adding any basic features of truly efficient integrity based database.  It just blows my mind.  For those of you still reading... I am the preverbal canary in the cage here, this is my cautionary note to those who are listening, if you have the product, push hard for them to fix it and if you don’t, and are considering it, make sure to take into account these issues.  Of course not matter what product you are condsidering, make as much effort as possible to ensure your other choices do not have the same issues.  Course you may not be able to find out since some of them have closed databases(Intergy for one), but you could always demand they show your DB guy, after all its your money.  For small practices(under 10 providers), most of this is a non-factor.

Here is what a database should have:
1. Database integrity - this means that I cannot delete data or move it if other data relies on said data.  They don’t have this and it scares the crap out of me.  How do I know a patients record is 100% complete, nothing was deleted or altered that is incorrectly applied to this patient?  EHRs were created to help prevent medical mistakes as well as ensure data integrity... 
2. Data integrity – I should not be able to create a new 27 hour time zone.  I should not be able to make a new year format nor should I be able to enter data for years that haven’t happened yet!  This one is the scariest one to me.  Its soo freaking basic, it’s almost laughable that they do not have this in their database.  Letters are not allowed in date fields!  No letters in social security fields! <this is soo basic>.
3. Reference tables - there are many ways to spell and misspell names, places, things, etc.  Having it once in the database means that if it changes, it can be changed back.  It also means that there are not 50 different versions of the same thing.  This makes reporting slow and inconsistent.  BTW, not having reference tables makes your database much larger than it needs to be(more so as it increases in size.... exponentially).
4. No addition of tables without a good reason - compounded by not having reference tables, this ensures you end up with piles of redundant data that ensure queries will take minutes instead of seconds to run.
5. Good, Tested Query structure - Most of their queries are ok, but some just leave you scratching your head.  Soo many select intos your head will spin, they are mostly inclusive rather than exclusive, a major hindrance on reporting and data access.  Ever wonder why your users are sitting there staring at the screen waiting for feedback from the program?  THIS IS WHY!
6. Auditing - there is some auditing in ECw.... so not a total loss here.  They need more of it, in fact, they should have a separate database in which that’s all it does is audit the primary.  You should also be able to restore from this audit database.  Log files do work as an auditing measure, but in reality, if everything takes place in the database, then everything should be logged from it.

Let me clearly state this encase people are actually reading and comprehending the severity of these problems….. these problems are why NO AMOUNT OF HARDWARE YOU PROVIDE WILL FIX THE PROBLEM NOR WILL YOUR REPORTING BE SPOT ON!

Don’t get me wrong, this is not a death knell for the product, but it should be a wakeup call for those of you who need to report accurate data in order to receive money from outside entities(grants, research, medicaid/medicare, CDC, etc ).  The fix for now is to use a product like Bridge-it which can clean up the database data and make accurate reporting a possibility(course you have to deal with the access front end(MSSQL backend)).  EBO may be good reporting product, but its reporting on crap data... so as they say “Garbage in, Garbage out”.

In ECW's defence, the product grew quickly and was pushed by doctors for small doctors offices.  Its very friendly for doctors to use and staff generally like it.  Unfortunately, as you get bigger, people start to look under the hood when things start to go wrong.  So please make the effort to fix the database, clean up customers data that resulted in the poor design and you will see customer satisfaction rise to new levels(i really mean this).  If you want to be the best, the database must mature.  Be a good time to add this to the list to get done for version 10, after all we are "improving healthcare together" includes user experience.

My next worry for EHR systems is data security.  While most have mechanisms for securely sending data to and from the client as well as other input devices(ecw uses SSL), no EHRs seems to make an effort to secure the data at rest or in motion on the database server or local server structure accessing that data(quick note, you really should secure your backend data network anyways).  Now of course, we all assume our data center is secure and someone sticking a network monitor on your server network is very unlikely, but lets not forget that even accidental breaches are wrought with heavy fines.


Comments (6) -

United States Lloyd Levine

You wouldn't happen to have a schema for ECW ?

Reply

I do have their schema, but i cannot share it with anyone.

Reply

Hi Brent,
I am building a report application off the data from ECW, but I don't know how to extract the data. Can I get some pointers from you?
I want to extract the data and use Tableau to find insights in the data.
Thanks.
Kazeem

Reply

Kazeem,

As i noted before, i cannot share the schema i have and i am pretty sure the Schema is out of date by now.  They offer a product called Cognos which can extract data for you, then you could use that raw data with Tableau( a great product ).

If you are hosting your own data, then there is also a third party company called BridgeIT which can get you in touch with someone who has the current Schema and their product can pull raw data out as well.  They have a bunch of data pulls already created, so you could probably get off the ground a bit quicker and for less money than Cognos.

Another benefit to hosting it is that their database is plain SQL, so if you have access to the database, you could tool around to see what is what and possibly extract the data you need.  Of course the schema comes in handy when doing this.

Best of luck

Reply

Hi Brent, are you still active with this Blog.? I just started working with ECW and EBO.  I am currently trying to understand IBM Cognos and the data elements within inside the EBO.  I have never used this type of reporting tool but I'm a quick learner who understands relational databases.  So right now, I am just jumping right into it.  I have had query experience in the past and I will be taking a SQL course this fall.  I am very determined to learn the EBO reporting tool.  I have used Allscripts and NextGen reporting tools.  I am also quite efficient with the following BI Tools from the Advisory Board Compass, Navigant Vital Stats, and Navigant Direct Access.  Do you have any suggestions or can you advise on any material that I can purchase to be successful in using the EBO?  Also, is there a way that I can stay in contact with you?  The EBO team will be providing me some training at the end of June and I want to be ready with my questions.

Reply

I cant really comment on EBO as i dont directly use it any more.  Or more precisely, i am not directly involved with its use anymore since we switched to MSSQL as our database engine.  What i can say is that you must get ECW to create any custom reports you want, which is probably why we still use BridgeIT internally.  You also have to be careful with the reports as some can request so much data they can take down your application servers.  It seems that they pull then filter from the dataset, which is bad when you have a 700GB database.  May want to look into getting a separate report server to pull from since they drop the EBO instance on your primary SQL server for MSSQL.  Of course, i believe they suggest you have a separate tomcat server for EBO.  I should note that reporting is generally a heavy duty process anyhow, so not much of this should be a surprise.  

Reply

Add comment