Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Maveryx Success Stories

Learn how Alteryx customers transform their organizations using data and analytics.
STORIES WANTED

Showcase your achievements in the Maveryx Community by submitting a Success Story now!

SUBMISSION INSTRUCTIONS

Leveraging Alteryx to Implement & Manage Juniper’s Snowflake Data Warehouse (INT)

cbalas
Alteryx Alumni (Retired)

clipboard_image_3.jpeg


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.

 

clipboard_image_11.png

 

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.

clipboard_image_5.png


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.

 

clipboard_image_6.png

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.

 

clipboard_image_7.png

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.

 
clipboard_image_8.png

 

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.

 

clipboard_image_9.png

(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.

clipboard_image_10.png

 

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.

 

clipboard_image_12.png

 

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.

 

clipboard_image_13.png

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

  1. Alteryx allowed me to showcase my abilities which helped me obtain a data analyst role and changed my life, so I want to give back to the Alteryx community and hopefully inspire others out there to continue to take your companies or your careers and Alteryx to new heights as well.
  1. Thanks to a lot of hard work, perseverance, and having a platform like Alteryx, our Go-To-Market team has succeeded in our objective. Now as it stands today, our Go-To-Market analyst team is one team lead and eight analysts strong and over time, our team became known for analytics at Juniper and our dashboards can now be found supporting over a 1,000 people throughout the company including the CEO. Alteryx is an amazing tool that is so flexible and powerful. It can handle practically any situation. Alteryx and this three-step approach allowed us to handle many different data sources and process large amounts of data every day. We were running over 20 workflows which amounted to over 12 hours of Alteryx server run time and by refreshing our dashboards every day, we were helping thousand users put data and insights at the center of their daily decision making. This amounted to a huge impact on how Juniper operates.
  1. Alteryx brings speed to the data transformation process. This is both an increased speed to discovering insights as well as faster approach to transforming our data. Second, the data prep is huge for our team. Alteryx continues to be versatile enough to handle just about anything you need to do to our data. Third, the ability to schedule workflows establishes our daily routine and this consistency, in turn, helps build trust in our team to deliver data everyday. And finally, all of Alteryx's data connections and the ability to load and unload data is definitely useful, especially with our increasing number of data sources.

 

  1. Ultimately using Alteryx in our initial approach allowed our Go-To-Market analytics team to handle tons of data, enhance our data prep, reduce errors, and allowed us to easily schedule and annotate our overall process. And ultimately, Alteryx transformed our approach to data. And if you are looking to supercharge Alteryx even further, you could consider adding a cloud based data warehouse like Snowflake to the mix. As our use case shows, combining the flexibility and power of Alteryx with the speed and concurrency of Snowflake can allow one to scale everything even further.
clipboard_image_14.png

 

 

 

Comments
swooledge
Alteryx Alumni (Retired)

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

 

rafadataengineer
5 - Atom

Thank you for this valorous article. 🚀