Now that everyone knows where I stand with Alteryx (I like it), let’s talk about my favorite tool for workflows and the namesake of this blog, “Dynamic Input”.
It should be noted, that I don’t love AS400. But it exists in my life and life of so many others so we must deal with it. Alteryx helps me deal. So much so that they have told me that they are creating “In-DB” tools for DB2/AS400.
That’s a lie. I have been told many times that it’s not on the roadmap. But we are going to get around it by making ODBC connections.
One of the problems that is not specifically unique to my company is that we operate not only on AS400 but other SQL Server based tools. Alteryx allows us to blend data from disparate sources and sometimes that can be a huge ask because data across databases are structured differently, they can have different record counts, etc. So one of our biggest problems before Alteryx was that we have a few different operating systems that transfer data between them, but some are small (hundreds of thousands of records) and some are large (millions of records). So to compare every record in a range from a small data set would require us to select a larger range in a larger data set, and they wouldn’t always line up 100%. Do you follow? This output would create a large rigid data set that required the users to either change the range for both every time that you wanted to look at something different, or pull the entire table from data set 2. About as appealing as it sounds.
This was creating a few headaches.
First problem is that when creating apps on our Alteryx server, there were immediate limitations to what the consumer could pull because of how the workflow was structured. We would have to build tools for each way the user wanted to look at the same set of data. (Which is a serious bummer as the tools often got mixed up because people don’t want to read descriptions about what they are running and I don’t want to deal with calls about why the output does not look the way they think it should. *Sigh* Namaste…)
Second problem was run time. Our server was pulling 10M+ row tables and running joins and formulas on these huge data sets. Some reports were taking 15 to 20 minutes to run.
This created our third problem, which is that we only had a limited number of processes that could run at once, so these long running process queued up our server and creating a ton of congestion.
There is a guy who I work with named Jake that works with a tool called Kapow! which is a web scraping tool that visually, kind of works like Alteryx. Kapow! (I just enjoy typing that) has a tool that works almost exactly the same as the Dynamic Input tool so he just sat down in Alteryx one day and talked me through how it should work. No matter how smart you think you are, you always need really smart people in your life and on this day (and every day I use Dynamic Input), I was happy that I had Jake.
Let me show you how it works. If nothing I have said before or after this, correctly illustrates my problem, look at these pictures:
Venn Diagrams are neat:
Here is a workflow example:
As you can see on my two workflows above, I have the same amount of records joining at the end, however I have much fewer records coming into our right join on the second workflow. Now on the surface, this doesn’t seem incredibly unique, because you could use a normal input tool and add the same limiters into our “Where” statement for Data Set 2. The tool really starts to shine when the data passing through changes every time you run the report. You know, like when you have input that is always changing and you need your tools to be as dynamic as the consumer needs. (See how I highlighted the words that make up “Dynamic Input”? Here is a GIF about how I imagine you are reacting.)
(I’m a boy but it’s alright)
Analytic apps are a prime example of when you can use this tool.
In the example above we added a simple text input that would let the end user type in their customer’s code and with the correct output tool, it would show us the data for any correctly typed out customer code. This works because the dynamic input tool is changing the record IDs that are being queried from data set 2. If you need help on the App side, there are quite a few examples through the University of Google that can help you configure your analytic apps so go explore or email me with any questions about that.
It was suggested to me that I split this post up into two acts. The first being this entertaining look into the tool that helps us be better and the second which is a little more serious and instructional. You can read that here.
If you didn’t catch that I added a hyperlink to the word “here” above, the link is below.