September 20, 2017
As anyone who has read anything on my blog that relates to survey data knows, the number one impediment to success with Tableau is getting your data “just so.”
Until recently I recommended two different approaches. You can either use Alteryx and have a rock solid, robust, fully-featured, (and expensive) solution or you could have a somewhat rickety, cumbersome, free solution using cross-database joins in Tableau.
Thanks to the advice of fellow Zen Master Rob Radburn, I now know about a third alternative, EasyMorph. Based on my brief time working with the product, EasyMorph appears to be solid, robust, full-featured, and costs somewhere between free and not-at-all expensive.
In this blog, I’ll show you how to take the same data set I use in all my classes and get it setup so that it works perfectly with Tableau.
Working with EasyMorph
Using the same data set I discuss in the general “here’s how your data needs to be setup” blog post, let’s see how we’re going to get the data-as-numbers, the data-as-labels, and the helper file to all play nicely in EasyMorph.
Importing and Reshaping the Label Responses
Here’s the EasyMorph Start page. Let’s begin by importing the Label responses from Excel.
Here I indicate that I want to open the Excel workbook and then tell EasyMorph which tab in the workbook I want to use.
Clicking the Apply button will apply this “transformation”, where a transformation is just an action I want to have happen in my workflow. EasyMorph will then display the data in its main workspace window, as shown below.
We now need to specify which columns we want to leave intact and which we want to reshape. We can do this by adding an Unpivot transformation.
I’ll start by clicking Add New Transformation…
… then I’ll click the Advanced menu and select Unpivot from the list, as shown here.
We next need to indicate what we want to call the reshaped fields and which fields we want to leave intact. Here we indicate that we want to leave Gender Generation, and Location intact, but we also need to include RespID and Weight (we need to scroll down to see them.)
Next, we need only click the Apply button and we’ve got our demographics and Data Labels set up, “just so.”
Importing and Reshaping the Numeric Responses
We’re now ready to import the Numeric version of the data. We start by clicking Import/create table, indicate we want to import from a file, and select the same Excel file.
We’ll see the same options as before, but this time we’ll specify that we want to import the Data Numbers tab, as shown below.
Clicking Apply will import the data.
Before reshaping the numeric version of the data, we can remove redundant columns, in this case the “demographic” variables (Gender, Location, Generation, and Weight) as we already have them in the Label version of the data.
We can do this by selecting the columns and selecting Remove 4 columns from the context menu, as shown here. Note that this context menu approach is just a different way to create and apply a transformation.
We’ll now Unpivot the data, leaving RespID and renaming the columns, as shown below.
Joining the Two Reshaped Data Sources
We’re now ready to join the two reshaped data sources. We’ll need to make sure that every Quesiton ID for each RespID lines up so we will join the tables on both of these fields.
Start by clicking the first table (Imported Table 1), then select Add new transformation, then select Merge another table from the list of transformations, as shown below.
By default, EasyMorph suggests matching the data based on RespID and Question ID. Note that I only need to bring in the Values field from the second table, as shown here.
Clicking Apply will append the Values column from the second data source to the first data source.
Adding the Helper data to the mix
We’re now ready to import and join the “Helper” data. This will map each Question ID to its human readable form and groups related questions together.
Start by clicking Import/create table, then indicate that you want to import from a file, then select the same Excel file.
We’ll see the same options as before, but this time we’ll specify that we want to import the Question Helper sheet, as shown below.
We’re now ready to merge the Helper data with the combined numeric and label data.
We start first by selecting the main table (Imported Table 1) and selecting Add new transformation.
We then select Merge another table and indicate we want to add columns from Imported table 3, as shown below.
We now need to specify which fields to use for the join and which columns to combine. Note that the field names are different so EasyMorph didn’t automatically join on Question ID.
Clicking Apply will combine all the relevant data into the main table, as shown below.
Notice the icons along the top of the window indicating that we’ve performed four transformations on this table. Selecting an icon will show the settings you specified for the selected transformation.
Not convinced the data is “just so”? We can sort on RespID and you’ll see all the survey responses for a particular respondent (Just right-click the RespID column and select Sort.)
Exporting the Data to a Tableau Data Extract file (.TDE file)
We’re now ready to export the data so that we can visualize it using Tableau.
We’ll start by making sure the main table is selected and then click Add new transformation.
We then select Export and select Export to Tableau from the list of options, as shown below.
Clicking Export to Tableau will bring up the Export options, as shown below.
Before clicking Apply, indicate that RespID and Labels should be Text fields and not Number fields.
Okay, we’re just about finished; we just need to run the project by clicking the Run project button at the top of the screen.
Want to load the exported .TDE into Tableau? Just click the last transformation icon at the top of the screen, then click the Open file icon, as shown below.
Below we see what complete data flow looks like. Note that I added a transformation to filter out null values (It’s the second to last icon in the top table). Further note that you can rename each of the tables as you see fit.
But wait, there’s more!
Assuming your data is coded correctly you’ll probably want to remove null values and labels. No problem, you can just Add a transformation and indicate you want to remove <empty> values.
And what if your data isn’t coded correctly (e.g., check all apply questions are coded using 1s and blanks instead of 1s and 0s)? EasyMorph can handle that, too.
Easy morph was very easy to learn and you can’t beat the price. If you have 30 or fewer transformations in a project you can use the free download (our sample only has ten transformations). Need more than 30 transformations as well as more sophisticated features (e.g., automation and scripting) the price will range from a one-time license of $195 to $55 per month.
EasyMorph also appears to handle large survey data sources without a problem. I was able to convert a monster NPS survey with 3,500 columns and 2,200 rows into a trim ten columns and about 7M rows in just a matter of seconds.
So far, the only downside I’ve seen is that it won’t read SPSS files directly; you’ll need to export to two CSV files, one for numeric-coded data and one for label-coded data.
Other than that minor issue the tool has been terrific and I plan to incorporate it into my classes on visualizing survey data using Tableau.
If you need geospatial analysis and predictive analysis in addition to ETL then EasyMorph won’t meet your needs (this is where Alteryx shines). But if you just need solid ETL capabilities, this is a great tool, at a great price.