Key Concepts for Relating Data

Quick reference

• 00:05 In the regular world of pivot tables, in order to use multiple tables in
• 00:09 one pivot table, you need to flatten the data table.
• 00:12 Which is usually done by creating a bunch of vlookup statements to look up data
• 00:16 that exists in table two and bring it back to table one.
• 00:20 Power Pivot actually allows us to get rid of that concept
• 00:23 by linking different tables together using something called relationships.
• 00:27 And it's much more similar to database relationships
• 00:30 than what you would suggest and see in something like Excel normally.
• 00:35 Now there's a few different kinds of relationships in the world,
• 00:38 Power pivot supports a relationship style called many to one.
• 00:44 And what this means is many repeating values in one table
• 00:47 can be linked to a table of unique values in another.
• 00:50 And this typically is the way that you would actually think about a vlook up.
• 00:53 You've got multiple different repeating items, and
• 00:55 you look it up using an exact match in another table.
• 00:58 That has a unique column of entries,
• 01:00 In order to bring back the appropriate amount.
• 01:03 There's a another version of a many to one relationship, it's called a one to many.
• 01:08 Basically the only difference here is that the tables are on the other side
• 01:12 of each other.
• 01:12 So instead of looking from left to right, you're looking from right to left.
• 01:16 It always forms a one to many or
• 01:18 many to one relationship depending on how you look at the table.
• 01:21 There are two other types of relationships in data in the world,
• 01:26 unfortunately these are not supported by default in Power Pivot.
• 01:29 One of them is the one to one relationship,
• 01:32 this is where there's unique items on both sides of the keys.
• 01:35 Now Power Pivot can work with this,
• 01:36 it just doesn't support this as a native relationship type at this time.
• 01:42 There is also this thing called many to many relationships,
• 01:46 many to many are quite complex.
• 01:47 And they occur in the real world but they're not supported in Power Pivot.
• 01:51 But don't fret, we will show you this by the end of the course of
• 01:54 how to deal with this particular problem.
• 01:57 So what do these relationships actually look like?
• 02:00 Well we build relationships inside Power Pivot
• 02:03 using a graphical view by dragging fields from one table to another.
• 02:07 So you can see here we've got a chart of counts on the left-hand side,
• 02:10 there's an account department field.
• 02:12 And on the right-hand side, there's a departments field on the department table,
• 02:16 we just left-click and drag from one to the other.
• 02:19 What do these actually mean though?
• 02:21 Well here's the thing,
• 02:23 when you look at the data that's in these individual tables.
• 02:26 The chart of accounts has a list of a bunch of different accounts.
• 02:29 And as you might expect each department might have multiple different
• 02:32 accounts in it.
• 02:34 That's why when you look down the account department column that
• 02:37 value of 110 repeats many, many times.
• 02:40 You can have lots of duplicates in there,
• 02:42 that is what we call the many side of the relationship.
• 02:45 Meanwhile, the department table has a unique list of items.
• 02:50 What you see in the COA Table up top, that's not a limited list,
• 02:53 there's way more than five rows of data.
• 02:55 But down in the bottom table, department, there are only those five rows of data.
• 03:00 And that means that these values are unique,
• 03:02 there's only one instance of 110 in that column.
• 03:05 It cannot have another instance of 110, so this is where we get the term many to one.
• 03:11 You'll also notice that there is a diagram here,
• 03:14 where it draws a little arrow between the two tables on one side department.
• 03:18 It indicates which side has the unique entry, that's the one.
• 03:22 And on the other side there's a star next to the COA table, that's the many, okay?
• 03:27 So the one to many is the star, and
• 03:29 the arrow is the direction in which the relationship flows.
• 03:33 Now, if you're looking at it going holy cow how am I going to remember all that?
• 03:37 You don't actually really have to worry too much.
• 03:39 Because if you actually have a true one to many relationship and
• 03:43 you drag the arrow from one side to the other.
• 03:46 Power Pivot is incapable of getting this wrong, it will
• 03:49 only create the relationship in the correct direction, it's impossible not to.
• 03:54 So you can drag from the act department, over to department, or
• 03:58 from department to act department.
• 03:59 It doesn't matter it'll get it right immediately.
• 04:03 Now one of the big recommendations I have for you is when you're creating this
• 04:06 relationship view as soon as you drag your fields from one side to other.
• 04:11 We know now by looking at these two guides, the department and
• 04:14 account department are related.
• 04:16 And account department, the ACC department is on the many side,
• 04:19 because that's the side that has the star.
• 04:22 Always right-click and hide the field on the star side of the relationship.
• 04:26 And if you do that, you'll actually save yourself a lot of potential
• 04:28 problems in the future, I wish it did this for you by default.
• 04:32 Now I did say that one to one relationships are not supported, and
• 04:36 they're not.
• 04:38 But you can still create something similar in Power Pivot, it uses the one to many.
• 04:43 What does it mean?
• 04:45 Well it means this, you've got a table that looks like this.
• 04:48 Look at the POSChitHour column in both tables.
• 04:52 They're identical, they only have unique values.
• 04:54 There's unique values on the top and unique values on the bottom.
• 04:58 To create this kind of a relationship
• 05:01 it must be built as a Many to One inside Power Pivot.
• 05:04 The challenge is, this one Power Pivot can get backwards, so
• 05:08 you have to be a little careful on how you build it.
• 05:11 Now, the thing to remember about this when you're building your models is that these
• 05:15 arrows are kind of like one way streets.
• 05:18 You can't drive the wrong way on them.
• 05:20 So the challenge is you've got to remember to always go and make your arrows flow
• 05:24 the same direction from your dimensions all the way through to your fact tables.
• 05:28 But just to make this a little bit more complicated,
• 05:32 between Excel 2013 and 2016 they changed the direction.
• 05:34 So what you see here is in Excel 2016 view,
• 05:37 where all arrows point towards the fact table.
• 05:40 So notice units and amount are the columns we're gonna aggregate.
• 05:44 So your dimension starts at shift sort,
• 05:46 anything you filter is gonna flow through shifts and down to sales.
• 05:49 In Excel 2013, the arrows actually go the other way.
• 05:53 Instead of a star, you've got a big dot and the arrow will actually flow up.
• 05:58 There is no one, it just has an arrow that points towards the dimension table,
• 06:01 so there are gonna go the opposite direction.
• 06:04 Regardless, they still need to go the same way, so
• 06:06 you're gonna have a dot from sales up to pointing to shifts.
• 06:10 And then another dot from shifts pointing to shifts sort, so it makes it a little
• 06:14 bit more confusing, but at the end of the day it is consistent.

Lesson notes are only available for subscribers.