About two months ago, I received a few files that contained patient hospitalization data from a group of hospitals. For this, I will be forever grateful because I’ve had a lot of fun working with the data. After giving me the files, one senior analyst told me: “Good luck to you because no one has ever been able to make sense of our data in under two years!”. With those words in early February, the challenge began.
If you like this article and would like to see more of what I write, please subscribe to my blog by taking 5 seconds to enter your email address below. It is free and it motivates me to continue writing, so thanks!
Challenges, challenges, and more challenges
The data file contains nearly a half-a million patient records with over 500 fields per record, for a cool 200+ million pieces of data. For Tableau, this is no sweat. For me, it took some sweat to assimilate, process and understand this data.
The first challenge was to decide which fields to use in our analysis. Secondly, I had to do some serious magic on the flat ASCII file to convert the data into a format that was compatible with Tableau. I could write a treatise on how this was done but suffice it to say that I learned a few new tricks along the way, some of which I have written in the past few blog posts.
The third challenge had to do with understanding principal diagnoses and MS-DRGs codes (thousands of these) as well as principal and secondary procedure codes (thousands of these). Little did I realize that I would be going to medical school for the past couple of months! After getting acquainted with the data and proving that I could reproduce client-published data on key measures, the real fun began thanks to Tableau.
If I had been given this task a few years ago, I would have started the project by writing a computer program to accomplish the multitude of goals that we set forth for this project. I estimate that such a program would have taken me many months to complete and it still would not offer the analysis versatility that I get by simply using Tableau.
I am inching ever closer to believing that literally anything is possible in Tableau. Well, maybe that is a bit of an overstatement since I cannot simulate groundwater flow and contaminant transport in Tableau, but I surely can visualize the results of those models!
Accomplishments in Under Two Months
Imagine that you are the CEO of a hospital system that sees thousands of patients a month. You employee many doctors, nurses, and other staff in an attempt to help people recover from illnesses and to return to their lives after accidents and other mishaps. Your goal is to save everyone and to be efficient in your work, thereby returning people to their families as soon as possible and hopefully keeping them from returning to your hospital for repeat visits.
Now imagine that years have gone by while you were on the job. Diagnostic technologies have changed, new clinical techniques have emerged and some have ended, and new patients continue to arrive all the time. Imagine the complexity of the situations encountered at all of your hospitals. Think of the thousands of combinations of treatment technologies used on the patient population. Do you think it is possible for your staff to be doing everything right all the time?
What is the likelihood that your best techniques are being uniformly employed at all your hospitals, considering the personality and experience differences that exist in your staff at the various hospitals? Wouldn’t it be nice to have a tool that is based on historical data (actual patient data) that can actually tell you what techniques have lead to the best patient outcomes? Would you also like to know which techniques have not lead to successful outcomes for the patients?
This week, I accomplished building this tool using Tableau. The results are stunning, insightful, and simply represent the best and most creative work I have accomplished in a while. I was so excited at what I developed, that last night I actually tried to tell my wife about the work. She just look at me and said something like: “OK, now I’m sure you are a dork. Can we talk about something else now?”
In any event, when we unleash this tool in the coming weeks, the potential exists for the hospitals and patients to be helped in so many ways that we cannot even predict the improvements. It is possible that lives can be extended, money saved, and future hospitalizations might be avoided. The lessons learned will be significant and will occur in ways that we are just beginning to understand.
The development of this tool was only possible because of the absolute power of Tableau and my never-ending quest for truth in data. Tableau has been my daily companion for nearly eight years and all those uses have allowed me to break on through to the other side of insight and understanding in the highly complicated and complex U.S. medical system. Hopefully, future blog posts can be dedicated to explaining how this was done, but for now the information is proprietary. I feel like having Ric Savage yell out a big “Boom Baby” for me (click here to hear it for yourself)!
Another Year Has Gone Bye
After a full year of research and development, this job had to be re-done due to a data update. All I can say is that I spent a couple of weeks writing a series of Alteryx workflows that replaced months of single-use data joining and preparation operations. Alteryx absolutely kicked this job into the stratosphere. I wish I could say more about this, but I can’t. I wish I could show more, but I can’t. This work is the best professional accomplishment I have ever achieved. The potential for this analytical platform is monumental.