1 00:00:00,210 --> 00:00:06,000 In our last lesson, we visualize the data structure that links our information together by building 2 00:00:06,000 --> 00:00:12,810 out a database diagram in this lesson, we will learn how to add data to our data model and then impose 3 00:00:12,810 --> 00:00:15,340 our diagram and its structure onto that data. 4 00:00:15,600 --> 00:00:21,390 Typically, the first step to working with data inside the data model is to load it to the data model. 5 00:00:21,990 --> 00:00:25,410 To do this, we need to visit the power pivot tab on our ribbon. 6 00:00:25,890 --> 00:00:31,500 If you do not have the power pivot tab on your reben, go to the data tab and click on Manage the model. 7 00:00:31,500 --> 00:00:38,070 Once you'll ask you if you want to install the and simply say yes and you'll be ready to move on to 8 00:00:38,070 --> 00:00:39,860 added data table to the data model. 9 00:00:39,960 --> 00:00:42,790 You need to have a cell selected in the table of your choice. 10 00:00:43,140 --> 00:00:47,610 I'm starting with the labor table and I'm going to click on the add to data model button. 11 00:00:47,610 --> 00:00:53,760 And this table will be added to the data model and the data model management window will open for now, 12 00:00:53,760 --> 00:00:55,620 simply close out of the management window. 13 00:00:55,620 --> 00:01:01,710 Each time it opens, we're going to stick to excel this chapter and introduce the data model interface 14 00:01:01,710 --> 00:01:02,700 in the next chapter. 15 00:01:03,840 --> 00:01:09,030 Do this for each of the tables in the workbook that you want to include for this workbook that involves 16 00:01:09,030 --> 00:01:15,400 the sales purchase orders, stores and calendar tables, once uploaded each of them to the data model. 17 00:01:15,540 --> 00:01:16,680 I can begin using them. 18 00:01:17,220 --> 00:01:22,470 So let's see what our data model can build right now by creating a pivotal, just like I covered in 19 00:01:22,470 --> 00:01:23,320 the review lesson. 20 00:01:23,550 --> 00:01:29,790 This can be done by going to insert choosing pivot table and then specifying a location in this case. 21 00:01:29,790 --> 00:01:34,170 And in all cases in this course, we will use the workbooks data model as the source. 22 00:01:34,770 --> 00:01:39,480 If you think back to the introductory chapter, we were able to create a pivot table with total split 23 00:01:39,480 --> 00:01:40,680 by store and math. 24 00:01:41,130 --> 00:01:44,940 For now, let's try calculating the total sales by store to see what we get. 25 00:01:45,330 --> 00:01:50,700 Solvejg store for my stores table to the row section and the sales field from the sales table to my 26 00:01:50,700 --> 00:01:51,540 value section. 27 00:01:51,540 --> 00:01:52,540 And what do we get? 28 00:01:53,040 --> 00:01:58,800 Clearly this pivot table isn't working since it's given us the same value for each store and for the 29 00:01:58,800 --> 00:01:59,590 grand total. 30 00:02:00,000 --> 00:02:05,610 So what's going on in our review lesson also reminded you how to drill down into a pivot table with 31 00:02:05,610 --> 00:02:06,330 a double click. 32 00:02:06,570 --> 00:02:07,890 So let's do that right now. 33 00:02:08,610 --> 00:02:13,020 Double clicking on the Albertsons entry for sales gives us the data that looks like this. 34 00:02:13,540 --> 00:02:18,450 Our problem is immediately apparent while the entry on the pivot table says Albertsons. 35 00:02:18,600 --> 00:02:22,650 And the detail of the top tells us that this data is associated with Albertsons. 36 00:02:22,680 --> 00:02:27,000 The data very clearly includes Kroger, which means that Tom-Tom is probably here, too. 37 00:02:27,510 --> 00:02:29,310 When we did this in the example video. 38 00:02:29,340 --> 00:02:30,550 This just seemed to work. 39 00:02:30,570 --> 00:02:31,740 So what's so different? 40 00:02:32,620 --> 00:02:36,150 Let's go back to our pivot table and look at our pivot table fields again. 41 00:02:36,600 --> 00:02:42,600 There's a little note that says relationships between tables may be needed because we haven't defined 42 00:02:42,600 --> 00:02:45,570 a relationship between the stores table and the sales table. 43 00:02:45,930 --> 00:02:48,510 We have a cross joint relationship between the two. 44 00:02:49,020 --> 00:02:54,630 This means that whenever we choose Kroger, Tom-Tom or Albertsons from that store's table, we will 45 00:02:54,630 --> 00:02:57,210 get the entirety of the data from the sales table. 46 00:02:58,390 --> 00:03:03,460 We need to tell Power Pivot that the store value in the stores table and the store value in the sales 47 00:03:03,460 --> 00:03:07,050 table should match, and that's where our relationships will come into play. 48 00:03:07,630 --> 00:03:13,420 Defining relationships tells Parapet how to relate a field in one table to a field and another table. 49 00:03:14,020 --> 00:03:19,630 The pivot table fields pop out, even has quick links for auto detecting or creating relationships. 50 00:03:19,930 --> 00:03:25,270 So I could click on the create button here, but instead I want to open my relationships manager. 51 00:03:26,140 --> 00:03:29,980 This manager can be found on the data tab under the relationships button. 52 00:03:30,400 --> 00:03:33,910 When I click on this button, a new window will appear with its own commands. 53 00:03:34,390 --> 00:03:39,100 These commands are new detect, edit, activate, deactivate and delete. 54 00:03:39,640 --> 00:03:41,590 Let's start with the detect very briefly. 55 00:03:42,040 --> 00:03:46,960 This button is pretty amazing as it will automatically detect all of the relationships in a workbook. 56 00:03:47,140 --> 00:03:52,290 If the data is structured and named correctly, our next lesson will cover just how to do that. 57 00:03:52,510 --> 00:03:53,890 Next step is the new button. 58 00:03:54,340 --> 00:03:58,840 Clicking on the new button will bring up an interface for defining a relationship from scratch with 59 00:03:58,840 --> 00:04:03,520 some strange terms, notably the phone and primary terms in the column descriptions. 60 00:04:03,970 --> 00:04:09,070 These refer to whether the column key that is specified is a foreign key or a primary key. 61 00:04:09,730 --> 00:04:12,160 Don't worry about that terminology so much. 62 00:04:12,160 --> 00:04:17,860 Instead, understand that the top table should have the bigger data set, while the bottom table should 63 00:04:17,860 --> 00:04:19,660 have the unique set of values. 64 00:04:20,560 --> 00:04:25,750 For us, that means that the top table will be the sales table, since it has many stores and the bottom 65 00:04:25,750 --> 00:04:29,170 table will have the store tables and sets the listing of unique stores. 66 00:04:30,070 --> 00:04:36,400 As I press, OK, watch our pivot table update as soon as this relationship is created, our pivot table 67 00:04:36,610 --> 00:04:41,510 shows the different values for each Rollerball, while our grand total doesn't change. 68 00:04:42,100 --> 00:04:43,990 There's a lot going on here to unpack. 69 00:04:44,200 --> 00:04:46,600 So let's leave the relationships window for a moment. 70 00:04:47,560 --> 00:04:52,510 The first thing I'll do is drill into Albertson's data again with the double click, this time the data 71 00:04:52,510 --> 00:04:55,000 table only has Albertsons as the store name. 72 00:04:55,270 --> 00:04:56,890 There's no Kroger or Tom Thumb. 73 00:04:57,520 --> 00:05:03,010 The relationship is appropriately connected the store table to our sales table so it now understands 74 00:05:03,010 --> 00:05:05,920 how to filter and segregate the data pieces appropriately. 75 00:05:06,670 --> 00:05:12,550 As importantly, all of the computation and filtering of this data happens live in the background as 76 00:05:12,550 --> 00:05:13,060 you work. 77 00:05:13,270 --> 00:05:16,900 So there's not a little calculating time or in Excel that you will wait on. 78 00:05:17,290 --> 00:05:20,290 Those big changes can take a fair amount of time to process. 79 00:05:21,300 --> 00:05:27,840 Finally, and quite curiously, the grand total did not change, the grand total is disconnected from 80 00:05:27,840 --> 00:05:33,990 the individual cells in a sense, and it won't necessarily reflect the total of the pivot table so much 81 00:05:33,990 --> 00:05:36,220 as the total for the underlying data set. 82 00:05:36,690 --> 00:05:40,080 I'll look into this technicality with you later on in the course. 83 00:05:41,330 --> 00:05:45,540 For now, let's go back to our relationships manager and learn about the rest of our commands. 84 00:05:46,260 --> 00:05:48,240 The edit command is pretty straightforward. 85 00:05:48,480 --> 00:05:53,470 Selecting one of the relationships in the list and pressing edit will bring it up for editing. 86 00:05:53,880 --> 00:05:59,280 This can be useful if relationships have been set up incorrectly at some stage and need to be revised. 87 00:06:00,370 --> 00:06:06,100 The next two commands are activate and deactivate, if you look at the relationship window, there is 88 00:06:06,100 --> 00:06:08,020 a status field that says active. 89 00:06:08,410 --> 00:06:13,540 There are certain rules about what types of relationships can exist at a given time with a circular 90 00:06:13,540 --> 00:06:16,030 series of relationships being against those rules. 91 00:06:16,480 --> 00:06:23,350 However, it can be beneficial to have them all defined and then deactivate or activate specific links 92 00:06:23,350 --> 00:06:26,440 in the chain at certain times for certain reports. 93 00:06:28,510 --> 00:06:32,290 Clicking on deactivate will simply turn the relationship off temporarily. 94 00:06:32,680 --> 00:06:37,570 I can do this and you can watch the pivot table update to reflect the Agnon relationship. 95 00:06:38,350 --> 00:06:43,960 If I turn it on again just as easily by clicking the activate button, you can see it correct itself. 96 00:06:45,220 --> 00:06:48,520 The delete button is similar to deactivate, except it's more permanent. 97 00:06:48,760 --> 00:06:52,660 It completely removes the relationship from the model, as you can see here. 98 00:06:54,110 --> 00:06:59,630 Since we have a database diagram and we have most of our tables defined, I can go through and easily 99 00:06:59,630 --> 00:07:04,100 create the same relationships from the demonstration video, a few videos back. 100 00:07:04,820 --> 00:07:10,400 Once I do this and press OK, I can add the month value from the calendar table to the pivot table for 101 00:07:10,400 --> 00:07:11,570 a nice quick report. 102 00:07:13,360 --> 00:07:19,060 As you can see, the hardest part about understanding relationships was covered in the previous chapter 103 00:07:19,240 --> 00:07:21,770 when we define the data structure in the first place. 104 00:07:22,450 --> 00:07:27,370 Would it surprise you to know that this particular lesson covers the hard way of creating relationships? 105 00:07:27,970 --> 00:07:33,400 In our next lesson, I'll start from scratch with our data diagram and we'll learn about an easier way 106 00:07:33,400 --> 00:07:38,260 to have Excel creates relationships for you with just a little bit of tweaking from our end.