Tutorial: Analyzing Medicare Payment Data in Tableau – Part 1

In this tutorial I will take you through the steps to load and analyze Medicare payment data in tableau. For this example, we want to look at variation in hospital charges across the country. To do this, we will create a few worksheets. Our end goal is to create an interactive dashboard.

 

 

Capture

load the data:
Step one is to load the data. For this example I am using the 2012 Medicare payment file that lists the top 100 DRG payments for all Medicare hospitals. You can get this file from data.gov. The most current file is 2012 but in October the previous year’s version should be available. The file I use is the original CMS data, but I added a few extra data fields that may (or may not ) be relevant to a charge analysis. These fields are hospital ownership, Bed size, whether the hospital is a sole community provider, and whether the hospital is system member or independent.
Open tableau and start a new workbook. If you are not already in the Connect to Data Screen, you can either 1) Click on the database icon in top right side of your screen or 2) click on “Connect to Data” on the left hand side of your Tableau screen near the top. The data file for this example is in Microsoft Excel format to select that from the “Connect to Data Menu”.

The dialog box will prompt you with some options:

We want to import a single table, and that single table is the tab called “top_100_drg_with_hospital_info. The first row does have names in it, so leave that selected.
Your next choice is whether you want tableau to import all the data image024from your excel spreadsheet into Tableau or leave it in the spreadsheet and access it when it needs it.

Presumably, Importing all the data will help Tableau feel faster so I usually select that option unless I am working with a huge database that I don’t want stored on my workstation. In that case I choose “Connect Live”. This example isn’t all that big (about 156,000 records) so we choose “import all data”. It will take a moment for tableau to pull all the data in and save it to a new file.

When it is done importing the data, it will bring you to the main workbook screen. Let’s take a moment to look at the Data on the left. A dimension is a String, name, discrete, or categorical data type, and a measure is a continuous numeric data type. Note that Geographic data identified by a world icon, cna be either Dimension or measures depending on the data. At the top there is a panel called Dimensions and at the bottom is a panel called Measures. All of the data elements are listed in either Dimensions or Measures panels.

Before we get to dragging an dropping data to create great looking charts, I want to add a calculated data element that is not in the original data. In this case I want to calculate and display the variance of the average covered charges.

Create a calculated field
To create the calculated field, go to the Analysis menu and select “Create Calculated field”. A little calculated field editor will pop up and you can either select your data and functions from the menu, or type them in. Ill call this variable Measure “Charge Variance”.

Before you leave the Calculated filed editor take a moment too check out the other functions available.

Creating your first chart (or sheet)
The first chart will be looking at the charge variance measure by DRG. IF you don’t already have a blank worksheet open, you can create a new one by clicking on the button. We want to see DRGs down the rows and some summary values across the columns. First, drag the “DRG Definition” dimension field onto the Rows space of the worksheet.

Next, drag “Measure Names” from the Dimensions panel to the columns place. Then you can drag “Measure Values” on to the field square.
And voila. You should have a cool-looking table with your measures across the top and DRGs down the rows. Go ahead and delete the measure you are not interested by clicking them in the Measure Values panel and pressing delete. Also, the aggregate default in the Tableau is SUM. If you would rather see Average , Median, or some other statistic then click on the little dropdown arrow next to the name in the Measure Values panel and change the “Measure(Sum) to something else. Finally change the Sheet name to something descriptive, like DRG Values. After deleting the ones I don’t care about, mine looks like this:

Creating a Pie Chart.
Next lets create a pie chart that tells us the makeup of the hospital ownership/control. In other words, how many privately owned are there compared to non-profits, or federal. Create a new sheet and name it Hospital Control. On this report we want the rows to be Hospital Control and the values to be Total Discharges. Drag the CNTRL Dimension to the rows and the Total Discharges measure to the data field (middle). To convert this table to a pie chart click on the pie chart icon on the right of tableau.

Creating a Map.
First create a new work sheet and name it Map of Variance. We want to see a filled map (one with shaded areas) of the US that represents states’ variance for hospital charges. In other words, the map would show which states had more or less variance compared to others. To make it easy, were are going to use the state field, but you could use zip or longitude and latitude. Drag the “Provider State” on to the “Detail” button of the Marks panel .

Next drag Charge Variance on to the Color button of the Marks Card.

I don’t mind making the map a little more condensed by only showing the contiguous US(no offense Hawaii and Alaska). To do this, select the lower 48 by dragging your mouse over them, right click, and select’ Keep only’.

jon.

Dr. Jonathan P. DeShazo is an expert on health information technology, clinical data, and consumer informatics. He is currently an Assistant Professor in the Department of Health Adminstration and serves as Scientific Director of the Biomedical Informatics Core at Virginia Commonwealth University.

Leave a Reply

Your email address will not be published. Required fields are marked *