 Hello and welcome back to SuperDataScience Custom Chart Tutorial Series.
 This video is going to be part one of our Sunburst tutorial. A Sunburst chart
 is an outgrowth of a tree map. A tree map is a very familiar built-in chart type
 that's built in with Tableau. Used have multiple dimensions and you start out at
 the highest level and it drills into the lower levels as you could walk down that
 dimension. So for this one we have Region going to Segment, Product and then Brand
 all sized and shown by sales. A Sunburst takes that chart and makes it into a
 radial or a circular view. That's why this Sunburst Chart is actually also
 called a Radial Tree Map, at times. But for us we're going to stick with
 Sunburst at this point. And it's also worth mentioning that the man who was
 behind his technique is Bora Beran. He has a fantastic blog here and we're going to
 be using some of his techniques, as well, as some modifications that we've made to
 try and make it a little bit easier for you. This is going to be part 1 of this
 tutorial because this involves a fair amount of data prep and we wanted to
 show that to you to give you an idea of how to go through it. And then part 2
 will be actually putting the chart together. So if we come in and we look at
 our data right here we've kept it a nice small easy data set to work with. 
It's just retail data from a store showing sales. We have Region we have the Segment
 of that the sale is in. The Product Category of the sale and if available we
 have Brand. And then we have the individual sales meticles with that.
 So if you look at this row, we have our East Region and the Grocery Segment and our
 Product Category of vegetables and the sale would happen with it. And our East
 Region for technology we actually have a little bit more information showing the
 brand of cell phones that were shown. Now the trick with the Sunburst data is you
 actually need to have your data look like so. If we go back and look at our
 chart you'll notice that we have a ring or a circle for every single level that
 we're looking at. So right here we have our Region then we have our Segment
 Product is in a Brand and when we come back to our data we actually need data
 on every single one of those levels. So here you'll see we have our East region
 and we have the sales and then we have a level there on then we have our East
 Region for grocery, East region for technology. If you summ up these two
 values, sorry, summ up these two values, done, hey notice we have the 51 186
 that we get at level one. So level twos sum up to level one level threes
 some up to level two, level fourths to three and so on and so forth. There are
 many ways that you can automate this data, automate this preparation step.
 The best way would be some sort of method using script. Using R, using Python you
 can even do it using custom SQL. But in this case we're going to show a quick
 example that actually uses pivot tables for those of you who may not have access
 to those higher-level technologies or more advanced coding skills. So if you
 come here and you say: "insert pivot table", come in, you drop in your columns: Region
 Segment, Product, Category, Brand and then sales. Now we'll come in and we'll make
 this layout a little bit different we'll show in an outline form and then we're
 going to repeat all the items. You'll now see that we have the data that looks
 very similar to this, except we have something showing up is a little bit
 different. We have this vegetables column that's showing up as blank. What's
 happening there is is looking to our data, finding out there's no value there
 but still filling in a blank. We actually don't need that so we're going to go
 through and wrap up our data for any time that we see link. Now I recognize
 that this is a bit of a manual process and really only works well on small data
 sets but like I said it's just an example to show you a way to get to the
 data if you don't have those more advanced tools. So once you've hidden all
 the blanks you come down here avoiding the grand total row, come down, copy your
 data, make a new sheet and then paste values. See right here the data is
 looking really good. We have, we match the example Sunburst data really well, except
 now we just need to come in and create this level field. The level really needs
 to just look here and say okay give me the last area that has a value. You can
 do this using a match formula but we're just going to use a series of ifs. So
 we'll do if not is blank. So "is blank" looks to sell returns true if it's blank
 returns false if it isn't. So by looking at the not it's going to tell
 us it's going to return a true when there actually is a value there. So there
 is a value we want to say "4" because it'll be a level one two three four. If
 not we'll go and look at the next value that is blank; then we'll look to see two;
 so the 3 is the value there if there isn't then we'll do our last is blank
 look to be 2 if this value there it's 2 and then if there's no value there after
 that we know it's got to be in the first column so it'll be 1. So coming here fill
 that down we now have our level, all the way down 1 2 3 & 4 depending on where
 your sales are. And that we'll just call our Sunburst data and we will have
 everything set to connect Tableau. We're not quite finished yet because we have
 some more manipulation into inside of Tableau but that covers the preparation
 from our sales data step to our Sunburst data. Now going into Tableau we're going
 to connect the data and we're going to connect to our excel file and when we
 connect we're going to go in and we're going to use the legacy connection.
This is needed because we're going to use some custom SQL techniques.
 This option is not available for Mac users it's also only available for PC users
 who have the appropriate driver it's going to get a bit technical I apologize
 but the legacy connection uses a dot jet connection and that only exists in
 32-bit systems for Microsoft Office. It is a driver that has to do with
 Microsoft Access and so if you do have 32-bit system you should be fine if you
 don't you can go to Microsoft's website and download the driver. Or you can use a
 different technique which we'll talk about in a little bit. So if you connect with a
 legacy connection and have the proper drivers you can come here you have your
 data brought in and what we're going to need to do is we're going to need to
 prepare it for some data densification. Data densification takes your data, we
 have 50 rows right now, it makes a copy of it and then basically doubles it and
 adds in a column 1 with a value at your starting point and a value at your
 ending point. So to do that using custom SQL we'll copy this say Union all and
 then paste so if you look at our results right there all we've done effectively
 is just double the size of our data set. Now that we double the data set we need
 to add in one column that marks whether it came from the first copy or from the
 second copy and then give that a value that will then be used in the data
 densification process. So we'll come here to our custom SQL query edit it.
For those who don't know SQL need to add a column between a comma
 between each of your columns. And then we'll call this one, name it
 ToPad, another name for data densification is data padding and so you'll see why
 it's named ToPad in a little bit. Then for your second one you do a comma and
 you'll say 203 as ToPad; hit OK and notice for the first copy of our data we
 now have ToPad which says 1 for the second copy it now says 203. What data
 densification does is it goes into your data and it in effect creates rows that
 weren't there before. We'll walk through this technique more in the second video.
 But you need to have enough rows created by this process that you'll be able to
 draw smooth curves for a circle it has to be drawn many many times so 203 is a
 number that's been shown to be used that works well Bora Beran was the one who
 came up with a 203 it really could be any number that we pick. But for this
 case we're going to leave it from 1 to 203. Now that we have that set if we come
 in and look at our data we now have 100 rows, we have our Brand a Product
 Category our Region our Segment we have a level everything's on we have the
 number of sales associated with that level and then we have our padding from
 1 to 203. So now that we have that all set up that is going to be the end of
 part one that's all the data prep that's needed. We've gone in and done our
 preparation in Excel we've done our preparation in Tableau. As a quick note
 if you wanted to skip the custom SQL step if you don't have a legacy
 connector or if you rather do it in Excel what you can do is just come in
 copy your data copy just the values, sorry, and then paste it at the bottom of
 your table and then come back and add in that one and then add in the 203. That's
 it affects what we did with a custom SQL just made that new column this
 would have been called ToPad but instead we did it using the custom
 SQL because that is a little bit less manual and if you end up adding new rows
 to your data set the custom sequel will comprehend that and it will be a little
 bit more scalable in the long run. But anyway so now we have our Sunburst data
 we have our data ready to go here in Tableau and we'll get set for part two
 where we actually get to build a sunburst chart we'll see you in part two.
