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.
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.
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).
- 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?