Showcase your achievements in the Maveryx Community by submitting a Success Story now!
SUBMISSION INSTRUCTIONS
Overview of Use Case
Juniper Networks, Inc. is an American multinational corporation that develops and markets networking products, including routers, switches, network management software, network security products, and software-defined networking technology. The Juniper Networks Go-To-Market Analytics team is using Alteryx to manage and implement their Snowflake database and build dashboards around that integration. This use case will show the steps that were taken to connect Alteryx & Snowflake.
Describe the business challenge or problem you needed to solve
Our team's goal is to put analytics at the heart of our Go-To-Market strategy and Juniper Networks at large. As part of that initiative, we created one main Go-To-Market sales Tableau dashboard. This dashboard displayed key numbers including product, customer hierarchies, sales and sales opportunities as they stand today compared to historical numbers. For our business leaders, this data is crucial to tracking the progress of our quarterly sales and help predict where we may end up.
At the time, this dashboard was pulling from over 10 data sources with over 30,000,000 rows of data. And we had to ensure the data is accurate. Especially since the dashboard goes all the way up to the CEO. We eventually settled on a three-step process that leverages Alteryx all throughout the way and the final output fed our key sales Tableau dashboard and eventually other dashboards as well. In the last six months or so, we started integrating Snowflake to scale our process even further.
Describe your working solution
We use three key platforms. Alteryx, Tableau, and Snowflake to drive analytics forward at Juniper Networks. And as for our data, we initially started with our own Go-To-Market data sources like sales, sales opportunities, and product pricing.
Our first of three steps was to put 13 Excel files of our sales quarterly data in one data lake, and since this is a large amount of data, we pull it from our source system and quarterly snapshots. But with Alteryx, you can easily unify all the data together into a single Alteryx yxdb output.
For our second step, we take each of these files in the data swamp, clean and augment them using Alteryx and put them as certified data sources. In this workflow example, we want to find all unique combinations of our product hierarchy. So we can leverage this data as a product lookup table in future workflows. So we can take our product master data, clean it up, and create some new fields. Filter some of the data, union it back together, and then we finish by summarizing the data based on only the fields that we need.
And the third and final step is to combine our certified data sources and output Tableau extracts for our dashboards. We ensure the field names are the same and we union everything together. Now the output is a single Tableau extract file which can be used in one or more dashboards. So once this overall process is complete, we can refresh our dashboards and share them with our users.
This is how our team connected Alteryx and Snowflake. The most common way to connect the two systems is using Open Database Connectivity or ODBC for short. This application was programmed to be a standard way to access database management systems. And both Alteryx and Snowflake provide this connection. These are the general steps to setting up your ODBC connection. This link will take you to an Alteryx community post with more details about getting it set up in Alteryx and connecting to Snowflake.
When one connects to a database like Snowflake using the ODBC connection, this opens up a query Designer user interface in Alteryx. This UI provides a list of tables in the system of visual query builder to drag and drop fields and a SQL editor to enter your own SQL query and test it out. After a connection was made and a query is successful in selecting data, one can use any of the remaining in database tools in your workflows at that point. Now these tools, in some ways, are similar to their corresponding regular tools but they can also be vastly different. This is, again, because these tools are SQL based and so there can be some limitations in how the tools operate.
Another way to connect the two systems is by building a macro in Alteryx that uses SnowSQL and a Run Command tool to push code through to Snowflake. SQL is a command-line client for connecting to Snowflake to execute SQL queries and perform all DDL and DML operations. In this example, the Alteryx macro has been designed to roughly mimic the bulk load of data into Snowflake.
(Click Here for to see a detailed depiction of setting up your own SnowSQL connection.)
Now finally, the Run Command tool is configured to run SnowSQL.exe as the external program. It will pass through the previous inputs as command arguments and by using a temporary bulk load dot SQL file, its content will be over written each time the macro runs. And it will load these inputs into that run command window. When configured correctly in a workflow, this part of the macro will pop up a command prompt window like this black screen you see here. And run through all the SQL code one by on.
The final step once our macro is created, is to make a workflow that will utilize this macro. So this is a basic workflow we put together and there's five main parts to it. First, we clean up the fields in the first step. And this tends to be things like fixing dates, limiting some of the string fields,and then there's the occasional UTF-8 issues.
We can easily use Alteryx to create a new table in Snowflake that will include these selected field names and the field types. This one only has to be run once to create the table the first time and can be disabled on subsequent runs. We then create a CSV file out of our cleaned up data. And then this is the actual file that we will use to put into the stage and then copy it into the Snowflake table. And then step four, which is the formula tool, is where we input Snowflake details and make sure that we have the fields and values for all the control parameters. Those values will feed the macro and you have to make sure that the macro inputs match the parameters. So if you name them all similar names, this will make it much easier. And then when you're done, you can run the workflow and it'll load your data up into Snowflake.
Now if you remember from before, our goal is to refresh our Go-To-Market sales Tableau dashboardf or our CEO and our business leaders. Here's our new four step approach that incorporates both Alteryx and Snowflake. The icons show places where we're leveraging Alteryx and Snowflake features that were previously highlighted. If you remember from before, we still need to fill up our data swamp and collect our ports and put all of our ports there. However, our second step is now to use Alteryx to load data into our data lake in Snowflake. And since we're doing a little bit of cleaning up this time, our swamp now officially becomes a lake, yes. It turns out there are two main approaches one can take to loading in data. One can either transform the data first and then load it into Snowflake or one can load in the Snowflake first and then transform the data.
Once the data is in Snowflake from step two, we use either Alteryx's In-Database tools or SQL in Snowflake to prep and augment the data for our certified data sources which is step three and for our Tableau extracts which is step four.
Describe the benefits you have achieved
Thanks for sharing, Ben. I just watched your webinar and it was a great example of a modern analytics stack using Snowflake + Alteryx + Tableau (some refer to this as the "SALT" stack). Flagging it here for others who are interested in more - https://www.alteryx.com/on-demand-webinar/juniper-networks-is-going-big-with-big-data
Thank you for this valorous article. 🚀