Solving Many-To-Many Joins with Composite Keys - Practice

Quick reference

• 00:04 For this example, we're gonna use a very stripped down example file.
• 00:08 You'll notice that we have a chart of accounts table with three different
• 00:12 account department combinations.
• 00:13 Six rows in our transactions table, and six rows in our budgets table.
• 00:18 None of these guys are linked yet, I've just created a nice little pivot table
• 00:22 that's summarizing the transactions and budget side by side, individually.
• 00:25 And they seem to be giving us decent tools, but
• 00:28 what I'd really like to be able to do is pull the account name onto rows, and
• 00:33 have these calculated correctly without getting this yellow message here.
• 00:37 So, I'm going to, right now, try and figure out exactly how to make that work.
• 00:42 So I'm just going to grab name and pull it back here, and what I need to do is,
• 00:46 I need to edit the queries that I'm using to feed the data model.
• 00:50 So I'm going to go and pull up the queries and connections pane, or
• 00:53 show queries, or show pane, depending on the version of Excel you're in,
• 00:56 to take a look at the queries that I'm actually using.
• 00:59 And I'm going to start with transactions.
• 01:02 I'm going to right-click on this guy and say edit.
• 01:07 So this will take me into the transactions query,
• 01:09 where I can see that I've got an account and department field.
• 01:12 What I'm going to do is, I'm going to hold down my control key and
• 01:15 grab the department column, as well as account.
• 01:19 And now, I'm going to go to transform, and I'm gonna choose to merge columns.
• 01:25 When I do this it comes up, and asks me what separator would you like.
• 01:29 I'm gonna say, you know, I would like to use a custom, I'd like to use a dash.
• 01:33 And I'm gonna call this one something that has some nomenclature that I'm
• 01:37 gonna remember.
• 01:39 So, for right now, we'll go with LNK, which is my shortcut for link, underscore.
• 01:44 And then I like to use a small abbreviation to describe what I'm actually
• 01:47 dealing with here, so, 'LNK_ACC' for account, DEPT for department.
• 01:53 And I'm going to say, okay, and you'll see that it tosses the original
• 01:57 column away and creates me a new link account department column.
• 02:01 Why can I toss the originals?
• 02:04 Because this is my amount column, which is going to be summed up.
• 02:08 That means that this is something from a fact table,
• 02:11 which means the only thing I need in this table is my fact, and
• 02:14 the keys that I'm gonna link to my dimension table.
• 02:17 So I don't really need to preserve any of the count or department information.
• 02:21 I just need the linking key.
• 02:24 Now, I'm gonna also modify these other guys, so if I open the queries pane,
• 02:28 I can look at budgets.
• 02:29 And with budgets, same thing, it's a fact table.
• 02:32 So I'm going to grab the department by holding down control.
• 02:36 And I'll show you what happens if I right click and merge columns.
• 02:41 And I do the same thing.
• 02:43 Custom, dash, we're gonna call this one the same thing, LNK_ActDept.
• 02:51 And we'll say OK.
• 02:52 And you'll notice that by right clicking on the header
• 02:55 I get the same transform operation.
• 02:59 Now I'm gonna go to the chart of accounts.
• 03:01 Now the chart of accounts I wanna play with a little differently,
• 03:03 because I might want to slice by account, and I might want to slice by department.
• 03:07 So in this case I want to actually merge these two guys together.
• 03:10 But I don't wanna make this column disappear, because I might need it for
• 03:13 something else.
• 03:15 So, this is important to not be on the transform tab, but
• 03:17 rather to be on the add column tab.
• 03:20 Remember, the right click operation gives me the same as the transform operation.
• 03:24 So, to add this deliberately, I must be on the add column tab.
• 03:28 And here's the same command, merge columns.
• 03:31 And it looks identical.
• 03:34 So, I can go in here, I can call these the same name.
• 03:40 And I can say, okay.
• 03:42 And at this point, I'm left with link account department.
• 03:46 But I also have the account department columns left alone behind.
• 03:50 So at this point I'm happy with the way everything looks.
• 03:53 These guys, these tables have already been loaded to the data model.
• 03:56 So I'm just gonna go back and say, let's close and load.
• 03:59 That will cause all three of these guys to refresh.
• 04:02 And here we go, everything is being written into the data model, so
• 04:05 that's great.
• 04:06 And once it's there, now I can go and
• 04:09 say, all right, let's go create the relationships.
• 04:11 So, manage data model, and then from here we'll find the Power Pivot tab.
• 04:16 Open up Power Pivot.
• 04:19 We'll go to Diagram View.
• 04:21 And now, where I couldn't drag account and department to make a relationship between
• 04:25 these two guys, what I can do now is I can say, hey, this is pretty cool.
• 04:30 Link account department, links from the budget, sort to the budgets here.
• 04:34 And like I say,
• 04:35 Power Pivot is incapable of getting a one-to-many relationship wrong.
• 04:38 So that's great.
• 04:39 Let's right-click Hide from Client Tools.
• 04:43 You'll notice that we could not link across these two tables,
• 04:47 because they have many repeating values in both budgets and transactions.
• 04:52 But, we can link these two guys and they'll work just fine.
• 04:56 Because, again, this is the unique key on the chart of accounts or COA table.
• 05:01 So right-click, Hide from Client Tools.
• 05:04 And at this point I'm gonna decide,
• 05:06 I don't think anybody's ever gonna use this key for anything.
• 05:09 It's just a key to link tables.
• 05:11 Let's go right-click and Hide from Client Tools here.
• 05:14 And now, when I drop back to Excel and we take a look at the field list, all of
• 05:18 those hidden fields, even though they're still there, don't show up in this area.
• 05:23 And this is one of the other reasons I like to hide things,
• 05:26 cuz it prevents you from putting the wrong things on and causing filtering problems.
• 05:29 Because, if I'm looking now for
• 05:32 anything to fit on these tables, I'm not gonna end up pulling the wrong one.
• 05:38 Let's put name on here.
• 05:40 Look at that, everything slices beautifully.
• 05:42 If I want to put my department underneath my name,
• 05:45 I can see that it's going to slice those things down nicely too.
• 05:48 So everything works out quite well.
• 05:50 So this is how we use a composite key.
• 05:51 We join the two together, and
• 05:53 then we can actually use them to make that relationship.
• 05:56 The secret to this is being able to identify what your composite key is.
• 06:01 And that's something unfortunate I can't help you with.
• 06:03 You need to actually study your data to figure out if that relationship
• 06:06 does exist or not.

Lesson notes are only available for subscribers.