My Data Toolkit Is Happier With Alteryx In It

I’ve been an Alteryx window shopper for a while now.  I’ve seen many positive tweets, and have read several blog posts (those by Ken Black and Chris Love, in particular) that argued how indispensable Alteryx was to any modern-day data warrior. But I’d never had a chance to use it. The 14-day trial didn’t seem long enough to really get a feel for it, and the price tag for a single license, while not exorbitant, was higher than I could justify without some concrete proof of its value. 

I recently initiated a 30-day guided trial with Alteryx (where they make themselves available to answer any questions you might have during the trial), and now have the concrete proof I was looking for. Here’s a flavor of what I’ve been able to accomplish in just a couple weeks, with only intermittent episodes of focused time with the software. 
TDE Creation
We have a ridiculous process for generating TDEs today.  We schedule extracts of data out of Business Objects (generally in the form of Excel files), union them together via Custom SQL and then wait (sometimes hours) for Tableau Server to finally generate the TDE.  
One of our TDEs is the result of a union of 9 different Excel files (ushered into existence by 1400 lines of SQL).  It contains roughly 2.5 million rows of data and provides us with a rich data set that we use to produce numerous dashboards and ad-hoc analyses.

Best case, when the entire process runs without incident, it takes about 2 hours from the time the Excel files are produced by Business Objects to the time when the TDE reflects the updated data.  That wait is painful enough, but when we have to make edits to the SQL (to add a field or fix an error), we go through an even more aggravating process, waiting for the SQL to read in the files, manually adjusting the SQL script (paying close attention to spelling and field order) and then hoping that no ambiguous errors get thrown as the modified TDE is generated.  If an error is thrown, we have to use trial and error to figure out what is wrong and then go through the whole waiting game again.

By contrast, this Alteryx workflow pulls in all of the same data files, performs all of the same field additions/conversions (plus more), creates a union of all of the files and then generates a TDE in under 10 minutes.  Not only is that so much faster, but each discrete step is clearly documented (making for much easier troubleshooting and maintenance later) and any errors that are thrown are actually descriptive and helpful.  And while it took me about 2 hours to build this workflow, those were literally the first 2 hours I spent with the tool.

A few particularly nice features that I appreciated during this TDE creation process:

  • The fact that Alteryx will automatically order the fields by name and then let you see where gaps might exist (e.g. a field you thought was in a file but isn’t) is so much easier than scrolling through hundreds of lines of SQL and hoping that you don’t screw something up with copy/paste.
  • Being able to easily create a secondary selection from a file and them use that across the TDE.  In the example below, I’m finding the week of quarter that corresponds to the last completed week and then creating a constant that I can use throughout the TDE.  This won’t be as necessary once we upgrade internally to Tableau 9 and I can use a FIXED LOD calculation to derive the same value, but it’s nice to know how easy it is to do this in an Alteryx workflow. 
  • The Workflow Dependencies settings let you make a global change that impacts all of the files you input into your workflow.  So if I have 9 different Input tools reading in 9 different files, I can create the workflow using files from one directory (e.g. a local folder on my laptop) and then switch to using a network directory where the refreshed files are deposited.  Simply awesome.
Striping a TDE with Salesforce.com User IDs
We’ve recently embarked upon a project to integrate Tableau views inside of Salesforce.com.  The goal is to embed some interactive dashboards into both the Desktop and the SF1 mobile application (POC example shown below) so that our sales reps don’t need to leave the Salesforce environment in order to get great analytics.  After the recent detour into the land of Excel tables, I heartily embraced this opportunity to demonstrate the power and versatility of Tableau.

Central to all of this is the requirement that each rep only see the data they’re supposed to see. And “supposed to see” is defined by the user account assignment table inside of Salesforce, where each user is associated with one or more Salesforce Account Ids, which in turn are associated with distinct customer records in our EDW (where the sales data resides).

So what we wanted to do was the following:

  • Filter the Salesforce user assignment list down to just those users that have a relationship to a customer found inside of our EDW (i.e. customers that actually have data we can show in Tableau).
  • Find all unique combinations of User ID and Customer (as defined in our EDW) and append the User ID to each relevant TDE row.
  • For all User ID / Customer combinations that mapped to a single Salesforce.com Account ID, incorporate the Account ID into the TDE as well (so that a user could link out to that Salesforce page from the Tableau view).  For all those User ID / Customer combinations that mapped to more than one Salesforce.com Account ID, we just null out the Account ID instead of arbitrarily selecting one.

While I know there are many ways to achieve this, I was able to build this workflow and generate the TDE striped with User IDs within an hour after finishing our team call discussing the requirement.  Some tweaking and enhancement is going to be necessary to get this fully realized, but getting this close to the final solution this quickly reminds me of my early days with Tableau where I was able to generate a prototype of a final dashboard in a fraction of the time it would have taken using more “traditional” tools.  And I’m still a novice user, having clocked maybe 3 full days with Alteryx thus far.

I remember waiting in line for the bus to the TCC15 party, overhearing some guys pooh-poohing Alteryx as an overrated piece of unnecessary software.  “My ETL guy can do everything that Alteryx can do” was essentially their argument.   And while this may be true, how many of us have a dedicated ETL guy?  I certainly don’t.  But more importantly, why spend time explaining what you need to an expensive resource when you can do it yourself via a much less expensive drag and drop tool?

My data world just got a lot cooler and a lot more enjoyable. 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s