Florida Healthcare Costs

If you read any Stephen Few for more than a minute, you’d realize he emphatically stresses clear and simple-looking visualizations.  Well, he does in the one O’Reilly book I have of his where he spends a considerable amount of time pointing out what NOT to do.

With this in mind and looking for an excuse to keep playing with Awesome Tableau Software, I decided to take another look at the Healthcare Costs Data previously talked about at Centers for Medicare and Medicaid Services’.

This time, I restricted the data to the state of Florida (I’m here) and decided to drop the map.  The hopeful intent of this visualization is to provide the needed information as fast as possible.  As before, I cannot embed here but provide the link instead.  Just click on the images to play with visual.

 

And for perspective, this is what Stephen did.

US Healthcare Costs

… and here is a quick and dirty graphical overview of the discrepancies in cost per diagnoses across the US.  This was done with Tableau and recently made available data shared on previous post.

Click on the image for visualization.  Free WordPress won’t let me embed!

Medical Provider Charge Database Download

Data is provided at Centers for Medicare & Medicaid Services here. This dataset is being both in Excel and CSV formats.

Data looks like this:

DRG Definition	039 – EXTRACRANIAL PROCEDURES W/O CC/MCC
Provider Id	10001
Provider Name	SOUTHEAST ALABAMA MEDICAL CENTER
Provider Street Address	1108 ROSS CLARK CIRCLE
Provider City	DOTHAN
Provider State	AL
Provider Zip Code	36301
Hospital Referral Region Description	AL – Dothan
Total Discharges	91
Average Covered Charges	32963.07692
Average Total Payments	5777.241758

Flex OLAP Cube – updated

I’ve added ability to consume URLs whose output is XML as a data-source for Flex OLAP Cube. Check it out 🙂

Going thru this exercise has helped me understand a bit about creating data cubes and the uses they serve. This is but one of the many interesting (to me) things I am exposed to at work. Doing this from scratch provides me with insight unattainable with a ‘shrink-wrap’ tool.

Note – I had originally fetched data from accross the web but had to store xml files internally for show and tell. Enjoy.

Movielens OLAP Cube Slicing And Dicing On Demand

I wanted to write the post ‘Slicing Your Own OLAP Cube’ but I am not there yet. From my last post, a recurring theme in my friends comments was that the dimensions and measures that can be inspected where set in stone. I thought I was doing fairly well but I can see their point. Having a cube and having it sliced in a way you don’t need is kinda useless.

Movielens – Movie Ratings Analysis with OLAP Cubes

For this post, I will describe how to use the previously provided database to create data cubes from the Movielens Dataset.  With these cubes, I will then create a few reports using Adobe Flex to illustrate the advantages of using data cubes for reporting instead of the more traditional ‘query and report’ practices from live databases, etc.

Movielens OLAP – Database Download – updated

UPDATE –
1. I have broken down mysql dump file to a set of individual files per table. I got some complaints on unreasonable file size.
2. I’ve now included 10 million movie ratings as well which I hadn’t because of size as well. Now its a file to itself and you can skip if you find it difficult to import.

I’ve finally gotten around to posting the database online to share. This olap database is a star schema of movie ratings and movie topic tags as described on previous posts (here and here).

The set can be downloaded from Infochimps here. I will post any updates there as well.

Real Time Dashboards 3 – Backend

For a quick refresh, just go back to the intro post where we state what we intend to do.

Or do this instead and get up to speed faster.  All components are linked to for if you do not have the software we’re using…

Real Time Dashboards 2 – update

Oops,  missed a field in log create script on my previous post.

You can pick proper file here -> log-revised-sql.  Create script fir step table is fine, file is here -> step.sql.

After running these two scripts (remember to rename to ‘.sql’), you should have the following tables in your database:

Next time, we’ll be setting up the Java part of the application…

Real Time Dashboards 2 – SQL layer

This is part 2 of Real Time Dashboards. Reading the previous introductory post (it is short) will put this post in context.

As previously mentioned, our monitor checks up on an ETL process which is itself updating many databases as it executes.

For this post series, we are going to randomly grab an existing workflow (thank you Mazda) to monitor. We will be monitoring a ‘100-point’ car inspection. Lets imagine a car inspector checking things off in his tricorder or some other digital device. This device is, of course, running a program that records everything to a simple database.

I know, one would actually wait in a lobby while technicians check a car out. Feel free to swap my workflow with your workflow; the point to take from this is that our monitor will work well regardless the process being monitored 🙂

Hoping to keep this part basic, I am constructing a table called Step with the following fields: id, label, parentId, workEffort. WorkEffort will be a numeric field, a hypothetical amount of work each respective step would take (could define time, hammers to the head, amount of cowbell, etc.). This basic structure will allow the definition of any number of main and sub processes in a tree hierarchy. This table can be used to describe any number of main steps, each with any number of sub steps. It is important to note that we would be assigning ‘workEffort’ values to the children of our main steps for as many levels as we may have. I.E. – only the children of these steps (the inner steps) will have workEffort… Aggregating these values would, in turn, yield the workEffort of its parent. Peachy.

We will have a second table, Log, where the status of each of these processes can be recorded. The log table will have the following fields: id, active, success, startTime, endTime. Both active and success will be booleans and will indicate which process is active and which process, if any, fails.

Simple but complete enough to represent many real life processes we could monitor. At work, our typical process consists of a series of main steps each comprised of many sub steps. The tables described here would even allow us to take this a bit further as long as we remain sensible in our expectations on performance and complexity.

I have included the scripts to create each of these tables step and log for you. Download and replace the doc extension for sql. WordPress, free and wonderful, wouldn’t allow me to upload .sql files. Bah!

For the next post I will be describing this ‘car inspection’ process and how we can both model and mimic realistically.