Objective 1: Model the data - Tableau Tutorial

Alright, so let's get started with objective one, model the data. So for this objective, you've got four separate tasks to address, and as always, feel free to use any tool or approach you'd like. You could use Excel, Power BI, or others, but for this solution walkthrough, I'm going to be using Tableau Desktop. So the goal here is to review the data being used in our analysis, and join our data together to create a cohesive data model. We'll start with connecting to our raw data using the candy and zip file CSVs provided and we'll take a moment to review the information. Next we'll create relationships between our fact tables and two of our dimension files including products and factories. After that, we'll explore and profile the data. Now that we've covered our objective tasks at a high level, let's jump into Tableau Desktop and walk through our objective tasks. Okay, if you'd like to follow along, please open up a new Tableau desktop instance. Remember, the first task is to connect to our raw data using the Candy and Zip CSV files provided. We'll want to familiarize ourselves with the five different data sources and understand their data grain, dimensions, and measures. Let's start off with connecting to our Candy sales and US Zip data sources. I'm going to go over to text file, and I'm going to choose one of our data sources. Let's start with Candy Sales and choose Open. Okay, as you can see, it brought in all of our CSVs for reference on the left-hand side and brought Candy Sales CSV into the view. The Candy Sales data contains all of our customer and order-level sales data for several years across all factories within the Sugar Wagon's distribution network. As a first step, we want to join this data to a separate US Zips lookup file so that we can bring in zip-level latitude and longitudinal points. Later in this project, we'll be converting those coordinates into geospatial fields for use in our mapping effort. Now I'm going to perform a true join against this field. So I'm not going to use relationships here. So I'll double click on candy sales CSV. And I'll go ahead and bring in our US zips file. This will allow us to do a physical join, I'm going to go ahead and make this a left join. And I'll be joining on zip code. So on the left, I'll go over and I'll choose postal code. And on the right, I'll go over and choose Zip. We're using a left join so that we can maintain all historical sales records while bringing in the geospatial data we'll need for the records which match. If we take a quick look at the combined information, you can see now that we have our latitude and longitudinal data points for our zip codes joined into our primary sales and orders data set. Now that we have our historical sales data and matching geospatial information, we'll want to bring in our dimensional product and factories tables. These tables will be important reference points for our geospatial mapping and distance calculations for our network modeling work. So first, let's get out of our physical layer, and we're going to bring these in using relationships. So first, we're going to bring out candy products, and we'll drop it into the view. And we're going to join these on product ID. Bringing in candy product data allows us to bring in pricing and cost data in order to extrapolate our margin values. It also brings in which factory each product was produced at. You can see that here in our preview down on the bottom right hand side. Now we'll go ahead and bring in our candy factories table, and that will automatically join on factory from both the products table and the factory table. As you can see, that brings in our latitude and longitude for each of our factories, which we'll be plotting in our map. Now that we have our main data components integrated, let's add another small fact table to bring in some hard-coded manual goals to compare against our actual sales values. That comes from our candy targets. We'll go ahead and drag that and we'll put it where it says new base table to add a secondary fact table to our data model. And we're going to go ahead and join this to our candy products using division. You'll see that happened automatically. And now we can apply our candy targets against our candy sales at division level. Now that we've got all of our data modeling taken care of, let's take a step back and review what we have before we start visualizing our data. For our fourth task, we want to take a moment to explore and profile the data provided. When we're reviewing the data, let's try to answer a few questions. First, which factory produces which type of product? Let's go ahead and click on our factories table. And we can see where our factories are located. But if we want to see the mapping between factories and products, we can go to candy products. Looking at this, we can see that the Lots of Nuts and Wicked Chalkies factories produce most of the chocolate inside this distribution. And then if we go down, we can see most of the sugar branded products come from the sugar shack. And there's a few other smaller factories that provide other types of sweets. These points will be important when we're doing our distribution map so that we can see where customers are ordering products from so that we can better optimize either where our factories should be located, which factories should be shipping to which customers, or which production lines should be included in which factory type. All of these things are considerations that many supply chain analysts will look at when trying to optimize their distribution chain. Another interesting point looking at the candy products table is the differences in the price and margin of our different products. Some of them can be pretty substantial. And that will factor into our calculations when we're deciding how to ship which products to which customers from which factories. Obviously, for this use case, we're not including all parameters, which will be used in a network modeling exercise, you can imagine there are many different factors that will weigh into the costs and benefits of shipping products within our distribution. Alright, so that wraps up all the tasks for our first objective. Best of luck on Objective 2.