Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
On August 10 we hosted a live webinar with four of our esteemed ACEs:
Mark Frisch @MarqueeCrew, Patrick McAuliffe @patrick_mcauliffe, Jack Morgan @jack_morgan and Treyson Marks @Treyson.
You can listen the recording to the webinar in its entirety here. Topics covered in the webinar included:
- Creating an Analytics Culture
- Understanding Self-Service/Overcoming Misconceptions
- What is the cost and investment?
- How the Business and IT can work together
- Getting started with Self-Service
- Other tips, tricks and recommendations
As it typically happens when we have our ACEs speak about their experiences and expertise with Alteryx, we receive more questions than time will allow. The questions below are ones we didn’t have time to cover, and our ACEs were gracious enough to take the time to answer after the recording stopped.
As a new user and not an IT person I run into issues of not knowing if my inexperience is the problem or if I'm running into some IT issue, for example setting up a report to email out... do I need server I have scheduler and designer?
Mark Frisch:
Alteryx is designed for you (non-IT user). Take advantage of the Alteryx Community for training and for Q&A. Alteryx server provides for automation, collaboration, scalability and data governance. So if you are looking for automation, you might need server (though a desktop automation license can be purchased). Before putting those finishing touches on your process, first show the value of the process and the justification for expansion to the server may/will follow. Start small and gain momentum.
I too started with no tech background, I was a Biology major! your issues are created by lack of IT experience, they're created by lack of use of certain tools in Alteryx. Setting aside time to sandbox in Alteryx is key to being successful. One of the first projects I took on with Alteryx was replacing a billing system, which had a requirement for pdf statements...I thought i was going to go crazy. But I changed my tactic and found data and a pdf online that i could create with that data and pretty much "Gran-Prix'd" it until i got to the solution. As for the 2nd half of the question, if you want the email to go out at the same time every day and you don't want to run your workflow manually to do it, yes you would need server, or at least the add on scheduler option with designer. However, if you are at a company, DO NOT WASTE your money on a license with scheduling ability...Splurge for the server. Otherwise, if you don't mind running the process yourself at the same time everyday...you only need designer.
Siloed data resides across metro-urban-rural departments with varying degrees of expertise. How can Alteryx benefit my company?
Mark Frisch:
Become the change agent in your organization. The first experience with Alteryx should be a WHOA! That was FAST! Reaction. Eliminate the effort to get to your expected results. During this journey, you’ll likely find new data learnings (e.g. unmatched records) and possibly errors in your “legacy” approach. Now you’ll be ready to try things that were so out of reach, that you either made the decision not to go there or you couldn’t imagine. This is when you’ll find time to start working with other silos of data and with others in the organization. Alteryx along with collaboration amongst your silos will lead you to new company benefits.
How have you found Alteryx is different from other BI tools like Microsoft and Tableau?
Alteryx and Tableau...A lot of people think they're like comparing 2 brands of lets say...Peanut Butter; Skippy to Jif. This is not the case. The reality is, Alteryx and Tableau (or Power BI for that matter) are more like Peanut Butter and Jelly, they compliment each other. Any Tableau user at our company, I recommend also use Alteryx because without it, Tableau on your data is like putting lipstick on a pig!
Alteryx always leads to success. I can’t even compare it to Excel. If you have unlimited time and patience, along with a tolerance for not getting the results that you hope for, then Excel could be the right tool for you. I’ve tested Alteryx against SSIS and Alteryx could handle problems that SSIS choked on. As for Tableau, I love the visualizations that are created by it. I find many times that it requires the help of Alteryx on the data front-end and without it, the results are not readily repeatable across dashboards. Many end-users want to export the data and or create output in PDF or Excel/CSV formats.
Alteryx is an all-in-one tool suite that gets you from data to knowledge, but also can deliver action!
One of the key downside of a self service tool like Alteryx is lack of Governance and controls which would typically be present in a consolidated db , how can we convince stakeholders around this?
Databases allow users to write to and read from files dynamically. They provide for fast access to data. The database is a warehouse for facts. There is no intelligence derived from the storage of data.
Alteryx blends data, both structured and unstructured, and provides for analytics on the data. Alteryx helps the organization innovate. It works cooperatively with the governance of databases and other infrastructure to abide by the rules of engagement. Alteryx server adds additional governance features and now Alteryx Connect brings new capabilities to the table.
Using Alteryx won’t give you keys to the kingdom. It will however allow the Line of Business (LOB) to uncover new value and speed innovation. In closing, what data governance does the LOB standard tool, Excel provide you?
Alteryx is not, nor is it designed to be a database. Its a tool, like (but better) than Informatica that ENABLES you to access data. If there are governance issues, they're in your company's access and provisioning process, not in how Alteryx works
I've heard this mentioned a couple times - How do you blend 2 disparate data sets together without a relationship? Are you forcing the 2 together with some mashed up key like name, DOB, SSN, etc?
Data artistry comes into play when blending disparate data. I’ll describe a general approach to what I’d first try:
- JOIN: Get the exact matches out of the way.
- SORT & BROWSE: Look at the data and see with your eyes what looks like a next candidate.
- SUMMARIZE & GROUP BY: See what is unique and where opportunities for matching are.
Having these iterative steps ready, you’ll try and fail but more importantly, you’ll learn. It may be a combination of approaches that will lead you to success. Yes, there is a Fuzzy Matching tool to try, but first you’d have to understand your data and see which combination of fields to use and develop a weighting of how much importance to put on each attribute. Data quality will certainly be a part of this investigation.
This approach works with people, businesses, entertainment titles, receipt data and the like. For complex problems, you may benefit from pattern matching (RegEx) as well. I like the construction of hash keys to simplify matching later or to help you “cast a net” to see what possibly could match (e.g. ZIPCode & Last Name). Making a decision on the forced key is a limiting solution.
One more note: When approaching the blending & mashing challenge the looser the rules, the more over-matching will occur. Similarly, the tighter the rules, the more under-matching will occur. The art is to start at one end of the spectrum and move to the other until the gains of accuracy are out-weighed by the losses of errors. I start loose and tighten. When diminishing returns are seen, it may be time to move on.
As we know, Alteryx doesn't magically match data together that doesn't have a relationship. That being said, fields like names, in combination with other fields, CAN be used in Fuzzy Matching to create PKs in your data sets for joining, so I'd recommend that route for someone that has disparate data and has confidence that a combination of fields together can create a fuzzy primary key.
What Alteryx objects (tools) do you use the most to resolve data issues prior to visualizing these? Example: Imputation etc.
Data viz relies on LEAN, but robust data....terms that aren't usually used in the same sentence. Imputation is a good starting point but tagging data based on source and confidence can be good when working with disparate data. For me, i've had the greatest success when creating rows for missing data to create a more comprehensive data set. The only things worse that Pie Charts are timeline charts that have random date's between data points. Alteryx is stellar in painting in the missing pieces.
The best object is your eye! Within a browse tool, you’ll be able to see the color coding of data (Green is good and “Turd” is bad and grey is neutral). You’ll see leading and trailing spaces too. Common sense can also play a factor in the identification of data issues (e.g. negative sales amounts or quantities).
Here are tools that I recommend:
Browse: for reasons stated above. I don’t leave lots of browses in my workflow. I tend to use the browse everywhere feature.
Multi-Field Formula: I prefer to use this tool to make my updates over the use of the cleanse macro. I like to know exactly what’s being done and have the ability to customize the action.
Summarize: Both for imputing values as well as for looking at the data (e.g. State Code list or null/blank counts).
Find & Replace: Used to fix data
Join: Used to fix data
Alteryx is fantastic...but routinely we have to move in step with whatever version company has. How close is Alteryx to providing licensing model (lease, plan, etc.,)that's more affordable for a user who wants to stay locked to the latest and greatest?
Mark Frisch:
Alteryx is sold on a subscription basis. You always are entitled to the latest and greatest version. As for affordability, think in terms of value. I’ve got clients that started with 2 licenses and within a year jumped to 50+ licenses. I’m working with small and large companies and the value stories are similar in nature. A 1-year term license is $5,195 and a 3-year term license is $3,995 annually (https://www.alteryx.com/products/pricing). My first client saw enough time savings in their first use case (during the free 2-week trial) to purchase 5 licenses. The time savings (not to mention quality improvements) for the use case was equated to $50,000 each year. They happily purchased $20,000 worth of software.
We also had issues with our app distribution team wanting to keep Alteryx up to date. I proposed that we take over the leg work to get the newer versions packaged and UAT tested. Now all I have to do is submit a ticket, and as long as I do my part, within 3 weeks we have the newest version available.
I've been looking for documentation for using Cobol copybook definitions in Alteryx. Do you guys have any experience with mainframe data and using copybook definitions for ETL?
Mark Frisch:
Here is a flat file definition used by Alteryx (XML):
<flatfile version="1">
<file
path="out.asc"
eoltype="crlf"
/>
<fields>
<field name="TotalRecordCount" type="Int64" length="20"/>
<field name="ColumnName" type="String" length="22"/>
<field name="CountNonNull" type="Int64" length="20"/>
<field name="PCT_NonNull" type="FixedDecimal" scale="2" length="10"/>
</fields>
</flatfile>
You can construct this content by reading a copybook and save it so that Alteryx workflows can input the raw data and understand the contents automatically. If you need help with this, I would post the copybook along with some sample data to the community. I’d be surprised if you had to wait long to see a workflow posted with a solution.
As Mark mentions, turning on the xml of the workflow works for us, and seems to satisfy other teams that need to know how we're processing the data.
That's it for now. Big thanks to our ACEs for their insight, advocacy and their commitment to our diverse and vibrant Alteryx Community!
