Visualizing a company’s distribution network in Supply Chain Management is extremely powerful. Network maps can be used to show which distribution centers currently serve customers or smaller regional warehouses and whether there are inefficient flows. Alternatively, you may want to visualize a new strategy where different distribution centers flow product through the network.
Using a few steps in Tableau, you can quickly create meaningful network maps. I will walk through an example to produce a simple network map with some data from Texas.
Example Network Map
Part 1 – Prepare the Data
In this example we have six ZIP codes in Texas with Latitudes and Longitudes that is representative of customer demand, shown as blue circles in the final chart. Note as well that in column H, we have the Distribution Center (DC) that is assigned to these customer locations. There is one DC in Midland and another in Waco TX, which are represented by red circles in the final network map. I have attached the data we will be working with in this example if you want to recreate the analysis yourself.Download
We will go through a few steps to transform the data for Tableau. Below we will walk through each step, but what we will end up with is the below transformed data. You will see that Tableau needs a separate row for each destination(shaded in grey) and each origin (shaded in green), where the key that joins each destination and origin is the RouteID in column J.
1.1 Add in Origin-Dest and RouteID
Let’s walk through how to get the data in the correct format for Tableau using MS Excel. We start off by adding a column to show whether a point is an origin or a destination in Column I. I have labelled origin ’01_Orig’ and destination as ’02_Dest’. The reason I add a number before the label is to force a layer to come up on top when there are many data points – Tableau sorts layers alphabetically. I.e. a ’01_Orig’ dot will sit on top of a ’02_Dest’ dot. Next we add a RouteID column. This column tells Tableau that if there are two rows with the same RouteID, then Tableau is to draw a line between them. To create a Route ID, I concatenated columns H and A using the syntax in Excel =”DC_”&H2&”-“&A2
Add Orig-Dest and RouteID Columns
1.2 Copy All Rows And Paste Below
Next, copy all rows and paste them below. Delete the copied columns B,E and F as we will replace this text.
1.3 Fill in Lat and Long of the Origin
Start off by copying the origin cities from column H into column B. Next look up the Latitudes and Longitudes for these origin cities and fill them into columns E and F. You can use the tab in the attached excel to look up Latitudes and Longitudes based off ZIP codes.
Now that we have the data prep out of the way, we are now ready to enter Tableau.
Part 2 – Create a Network Map in Tableau
2.1 Connect the Data
Open up a new sheet in Tableau and add a new data connection. Connect to the excel sheet and use the tab ‘MapData’ if you are using the downloaded data set.
2.2 Add Latitude and Longitude
You next need to add in ‘Longitude’ in the Columns and ‘Latitude’ in the Rows of Tableau. Note that I used the Longitude and Latitudes that I have in my excel sheet and not the generated Longitude and Latitudes based off city names that Tableau calculates. When you drop the fields into Tableau make sure you select ‘dimension’ from the drop down (select the right corner of the green Longitude and Latitude). If you don’t change these fields to ‘Dimension’, Tableau will not plot the data on a map.
Add Longitude to Columns and Latitude to Rows
2.3 Add RouteID as Detail
Next change the Marks to ‘Line’ and drag the column ‘RouteID’ into ‘detail’ . You will then end up with a series of lines starting at the origin and connecting to the destinations. However, if you want to add in the origin and destination as circles, we need to add in a dual axis.
2.4 Add a Second Axis
Take the Latitude column and place another in the ‘Rows’. This will create a second chart below.
Add Second Latitude
Ensuring you have selected ‘Latitude (2)’, change the Marks to Circle and drop ‘Pop’ onto the Size to scale by population. You can also distinguish origin and destination colours by dragging ‘Orig-Dest’ into ‘Colour’.
Add Second Latitude with Circle and Colour
2.5 Change to Dual Axis
Click on the second Latitude and select Dual Axis. You should then end up with an awesome single completed Network Map with lines between origins and destinations, scaled by the population size.
Change Second Latitude to Dual Axis
Completed Network Map
When this network map is paired with some basic center of gravity or cluster analytics, you can create some really useful first stages of Supply Chain network design.