0:00Hey, it's Tim here. In today's video we're
0:04taking a slight tangent of functions to
0:06cover
0:06a topic that's going to be important over
0:09the next few days when I start covering LOD
0:12s.
0:12That concept is called granularity. Now
0:14granularity is really really important in a
0:16dataset because
0:17it decides pretty much how we handle our
0:19data and so it's really vital that this is
0:21a concept
0:22you understand and you really investigate
0:24whatever dataset you're working with. Today
0:27I'm going to show you how to do that. Let's
0:28get stuck in. Okay so we're here inside of
0:30Tableau.
0:31I'm actually going to open the second Super
0:32store sales here. This is the American
0:34version. Let me
0:35just go ahead and click on that and you'll
0:36see that it takes us straight into Tableau.
0:39Now one of the things here is we need to
0:41basically start investigating what we mean
0:43by level of
0:44detail. So I'm going to sort of challenge
0:46your perspective and by the way if you've
0:47been using
0:48Tableau for a long while this might be
0:49super basic for you but I still think it's
0:51a worthwhile
0:52refresher just to make sure that you're in
0:53tune with everything that we're going to be
0:55talking
0:55about over the next few days when we look
0:58at LODs. Now I'm just going to drag in a
1:01few rows of data
1:02in here. To do that I'm just going to drag
1:04order ID put it onto rows and then I'm
1:06going to drag
1:07sales put it onto where it says ABC and I'm
1:09going to leave it at that. Essentially what
1:12we can
1:12definitely agree on here is that this table
1:15is showing us the total sales for each
1:17order. There's
1:18no sort of contention there but if I was to
1:21ask you this question what level of gran
1:23ularity does
1:24the superstore's data set actually work on
1:27there's actually a slightly complicated
1:29answer. You see
1:31ever since 2020.2 that answer has become
1:33more complex because of something called
1:35the data model
1:36and so what we have to do is ask that
1:38question of each model in our data set.
1:41Essentially each
1:42logical layer will have a different level
1:44of granularity in our data set. Let's
1:46invest to get
1:46that a little bit further. So here I've
1:48dragged in order ID and what I'm going to
1:50do is you'll see
1:51this field here that says count now. The
1:54interesting thing is this says count of
1:56orders, orders count
1:57but it's actually slightly misleading
1:59because this name orders is actually
2:01derived from the name of
2:02the table so it doesn't necessarily count
2:04the number of orders it's just counting the
2:06number
2:07of rows inside of the orders table. So that
2:09's a really important thing to be aware of.
2:12So if I
2:12was to drag orders count into this table
2:15you'd see that I'm actually getting
2:17multiple rows of data
2:19in each order and essentially what's
2:20happening here is that when you make an
2:22order you don't just
2:23order one thing all the time you could
2:25actually order multiple things and so that
2:27's what we're
2:28seeing here in the data. Just to keep this
2:30simple and to keep this table easy to work
2:32with I'm going
2:32to go ahead and select a few of these you
2:34can select whichever ones you want you just
2:36need to
2:36make sure you select some with one rows two
2:38rows and four rows. So I'm going to select
2:40this one I'm
2:41going to hold command on a mac control on a
2:43windows machine and just select a few of
2:45these
2:46just to make sure we've got a variety of
2:48orders in our data set and once I've
2:50selected those I'm going
2:50to select keep only and just bring in a
2:53nice table. Now the next thing I'm going to
2:55do is I'm actually
2:56going to enable the summary window
2:58essentially if I go here to worksheet and
3:00then just show the
3:01summary window this is a nice little table
3:03that then appears here on the right hand
3:05side and what
3:06I want to do is drag that over to the left
3:08over here just above filters. Now I've got
3:11to admit I
3:12didn't know you could drag the summary
3:13window where you wanted until last week and
3:15I've been using
3:16Tableau for a long while I don't know why I
3:18didn't even try that myself but you can
3:20drag it to this
3:20particular location this is a good location
3:23for me because I don't really use the pages
3:24shelf so
3:25having it right there actually makes that
3:27of that space useful and I've still got a
3:29lot of vertical
3:30space to work with. Now this summary window
3:32is really cool because it actually reacts
3:34to what
3:34you're doing inside the Tableau let's say I
3:36want to know the total of these two cells I
3:38can just
3:39click on command and it will do something
3:41for me it will tell me the sum here but it
3:43also do the
3:44average the min the max and the median
3:46between those two values so it's a really
3:48nice way of
3:49having a calculator right in the view
3:51without having to go and get your
3:52calculator and do some
3:53math and it works with the values you see
3:55it's not working on the underlying data
3:57which makes it a
3:58really really practical tool when working
4:00with your data set. So now the next thing
4:03to do is to
4:03understand what's actually going on here so
4:06this particular data here has four so if I
4:09just click
4:09on this four and I go to this little icon
4:11here at the very end you'll see that it has
4:14a table icon
4:15if I click on that it actually loads a
4:16summary view for me and in there it shows
4:19the summary
4:19of what I'm seeing here in the view if I
4:21just drag this down you'll see that
4:23essentially this
4:25set of summary information corresponds to
4:28what we have here in our table so
4:31everything is really
4:31really nice and simple to to understand
4:34however if we go down to the detail here in
4:36the second tab
4:37you'll see that we actually get more
4:39information and looking at this data you
4:41can see that
4:41there's actually four products in this
4:43single order so they all share the same
4:45order id
4:46in this column here you can see they all
4:47have the same order id which means they all
4:49came as part of
4:50the same order but the person who was
4:52ordering this ordered things from different
4:54categories
4:55and they ordered it from the same city and
4:58place I assume that Houston, United States
5:01and customer
5:02related to the customer the manufacturer
5:04came from lots of different places the
5:07postal code I guess
5:08this is the customer's postal code but
5:10essentially we're not always sure so these
5:12are the kind of
5:12questions you'd really need to ask when you
5:15look at the granularity of data is the
5:16information
5:17operating at the same level or is it
5:19actually inherited from a higher or maybe
5:22even lower level
5:23okay but you can see here that the detail
5:26of this data set is actually at the product
5:28level
5:29so our entire superstore data set is
5:32actually based at the granularity of the
5:34product in every
5:36order not the order even though the table
5:38is called orders it's actually operating at
5:41the
5:41product level of detail okay what does this
5:44mean well if I close this window and I just
5:47do a few
5:48things here what I'm going to do is I'm
5:50going to drag sales into this view again
5:52and this time
5:53around I'm going to bring in the average so
5:54to do that I'm just going to put it here on
5:56the right
5:57hand side that puts it onto the detail pane
5:59and then I'm just going to go to the
6:01measure select
6:02average then drag it in here so we get a
6:04third item over here on the right hand side
6:06and now you
6:07can see there's an average being calculated
6:09and now that we have that average I'm
6:12actually going
6:12to go here to the analysis totals and show
6:15the column grand totals okay and so we get
6:18a range
6:19of numbers we get the sales the total sales
6:21this I believe is the correct figure and we
6:24also get the
6:25count of orders there's 15 and this is
6:27essentially telling us there's 15 products
6:30in all our orders
6:31and in our orders we've got eight orders
6:34here and so what I can do just to make sure
6:36this isn't so
6:37confusing is I can actually bring in a
6:39count distinct so let's go ahead and bring
6:41this on
6:42to detail and let's click on this little
6:44drop down go to measure select count
6:47distinct now that
6:48that's the calculation I want I'm now going
6:50to drag it here onto the measure values
6:51pane
6:52and now we can see what's going on so we've
6:54got eight rows here in the table because
6:56the
6:56grand total takes up a row but we've
6:58actually got seven orders okay so now you
7:00can start to see what
7:02we mean by granularity there's different
7:04things going on at different levels the
7:07only thing really
7:08going on at the same level of detail as our
7:10orders is actually the distinct count of
7:13orders in this
7:13particular table everything else is
7:15essentially an aggregation of some sort and
7:17we need to basically
7:18start to understand what that's doing now
7:22this 148 is an interesting number because
7:26you would
7:26think that this is actually the average of
7:29these cells and so averages always catch
7:31people out
7:31because people sometimes aren't necessarily
7:34clear about what they're doing so in this
7:36case you might
7:37see 148 but if I just hold command here and
7:40I just go and select each and every one of
7:42these
7:43um you can see that the average is actually
7:44108 and that's the average of these values
7:47now it's
7:48not good to do averages of averages but the
7:51key thing here is because I'm actually
7:53operating at
7:54the order level of id whatever I do in this
7:57data set tableau is always going to be
8:00actually
8:01calculating the average product value in my
8:04entire data set because all it's doing is
8:07taking the
8:08total value and dividing it by the number
8:10of rows in the entire data set in this
8:12particular case
8:14and so if you've ever thought that the
8:16average sales in any particular context is
8:18anything but
8:19the average product sales then that's
8:21technically not correct because the level
8:23of detail that we're
8:24operating here at is actually the product
8:27level of detail I can't drum that home
8:29enough it's really
8:30super important especially when you look at
8:32level of detail calculations because
8:34understanding that
8:35allows you to create calculations that
8:37solve this problem in lots of different
8:39ways okay now in
8:40order to demonstrate this I'm going to
8:42actually ask a slightly different question
8:44if I was to ask
8:45the question what is the average value of
8:48an order in this data set on screen you'd
8:50have to answer
8:51that in a slightly different way number one
8:53this sales column is actually where we know
8:56the value
8:57of each and every cell so the average value
9:00of an order in this data set on screen
9:02would actually be
9:04essentially this row of data divided by the
9:09number of orders which is seven and so the
9:12answer to that
9:13is actually here 317. 317 is the actual
9:17value of each and every order it is not the
9:20same as the
9:21average sales in this data set because the
9:23average sales is actually working at the
9:26product level of
9:27detail and so the complex thing here is
9:29when we start doing things like level of
9:31detail
9:32calculations we have to manipulate what's
9:34going on because essentially tableau is
9:36always going
9:37to be aggregating at the granularity that
9:39we've set in the data set and so with level
9:41of detail
9:42counts what we can start to do is start to
9:44direct it in the specific grouping or
9:46specific level of
9:47detail that we're trying to work at okay
9:50and so I just wanted to create this video
9:52to stress that
9:52point now just as a brief example what I'm
9:54going to do is I'm going to open up this
9:56data model
9:57connection here and you'll see that this is
9:59actually created for us when you connect to
10:00sample superstore cells and we've actually
10:04got three logical tables in the view now if
10:09I go to
10:09each one of these you'll see that this one
10:11is the orders table which is what we can
10:12see here and
10:13we've already figured out that this is the
10:15product level of granularity if I go to the
10:18people table
10:18you'll see that this operates a slightly
10:20different level now initially you'll look
10:22at this and you
10:23go how is this even working how is it
10:25creating a relationship to orders table it
10:27's because we
10:28haven't enabled the ability to show hidden
10:31fields if I just grab my arrow here you can
10:33just see that
10:34this box here isn't ticked so if we tick
10:36that we can see that the order id is
10:38actually hidden in
10:39fact the region is hidden because that's
10:41what it's actually creating the
10:42relationship on if I click
10:43on this line up here you can see that this
10:46is what the relationship is based on okay
10:48and so what this
10:49what's happened here is we've just hidden
10:51the column name so we don't have it dupl
10:53icated
10:53tablo has renamed it here to say that it's
10:55coming from the people table but that's
10:57just basically
10:58telling us that it's hidden okay and if we
11:00click on the returns table you'll see that
11:02the same is
11:03true actually with order id I'd actually
11:05enable this ahead of time so you can see
11:06that here that
11:07show hidden fields is already enabled
11:09because we just enabled it and so now you
11:11see it straight
11:12away but if I untick it this too would also
11:15be hidden okay and so this returns table is
11:18operating
11:18at the order level but the people table is
11:21actually operating at the regional level
11:23okay
11:24and then when we create this relationship
11:26tableau is doing the hard work of figuring
11:28out
11:28how this all comes together and then it
11:30will use that information to decide how
11:32things aggregate
11:33across the entire data set so that's pretty
11:35much it I just wanted to very very briefly
11:38touch on
11:38this concept of granularity because in
11:40subsequent videos I'm going to be touching
11:42on LODs and what
11:43I'd like to be able to do is reference this
11:45video multiple times in different videos so
11:48that you can
11:48get a little refresher on what is meant by
11:50granularity in a data set so there are a
11:53couple
11:53of ways this can be referred to sometimes
11:55it's called the grain of your data
11:56sometimes it's
11:57called the granularity of the data
11:59sometimes it's called the lowest level of
12:01detail in your data set
12:02sometimes people will just call it whatever
12:05they want but ultimately generally speaking
12:07when people
12:07are talking about what is the level of
12:09detail or what's the detail of your data
12:11set what's the
12:11grain they're talking about what is on each
12:14and every individual row and that's a
12:16really important
12:17concept to be aware of okay that's it for
12:19this video thanks for watching and tune in
12:22the rest
12:22of this week we're going to be taking on L
12:24ODs tomorrow I'm going to be touching on the
12:25concept
12:26of order of operations because that's the
12:28next key concept we need to tackle before
12:30we take on
12:30LODs so tune in tomorrow for that video
12:37thanks for watching and I'll catch you in
12:44the next one
Tableau | Data visualisation | Data prep