1 00:00:00,710 --> 00:00:06,130 As you can see, we're back to Excel and away from PowerPoint for these last two lessons in this video, 2 00:00:06,140 --> 00:00:11,000 I'm going to show you how to take your dimensional data and create custom hierarchies and then also 3 00:00:11,000 --> 00:00:12,710 how to edit your date hierarchy. 4 00:00:13,900 --> 00:00:18,580 Going way back to the beginning of the course, I introduced our grocery data set and we created our 5 00:00:18,580 --> 00:00:23,650 data relationships, but we skipped our department's table since they appeared to be incongruent. 6 00:00:25,950 --> 00:00:30,660 Today, we will not only bring them into alignment, but will also create a series of groupings that 7 00:00:30,660 --> 00:00:33,220 will function similarly to how our dates work. 8 00:00:33,660 --> 00:00:38,220 We're going to update our date hierarchy after that so that it includes our quarter field in between 9 00:00:38,220 --> 00:00:41,520 our year and month drill downs to start with us. 10 00:00:41,550 --> 00:00:45,190 Let's go and brainstorm what our department dimensional table should look like. 11 00:00:45,720 --> 00:00:51,390 My approach to this for a new data set is to align the unique values up from each of the tables and 12 00:00:51,390 --> 00:00:54,110 compare them excels, remove duplicates. 13 00:00:54,120 --> 00:00:55,920 Function is very useful for this. 14 00:00:56,370 --> 00:01:00,240 Once I've done this for all three of our tables, we can make some observations. 15 00:01:00,690 --> 00:01:05,850 Departments with specialized labor seem to have specific labor line items, while the labor costs for 16 00:01:05,850 --> 00:01:09,590 the other departments appear to be shared within a general department. 17 00:01:10,470 --> 00:01:13,290 With this in mind, we can begin structuring the data. 18 00:01:13,950 --> 00:01:18,450 The departments from sales and purchase orders line up nicely and at a detailed level. 19 00:01:18,450 --> 00:01:23,190 So I'm going to define a new table with a field call department that contains all of those. 20 00:01:23,910 --> 00:01:26,340 Next, I'm going to create a field called Section. 21 00:01:26,640 --> 00:01:31,140 I don't have a better name for it and I'm going to populate it with butcher and bakery for the butcher 22 00:01:31,140 --> 00:01:32,370 and bakery sections. 23 00:01:32,820 --> 00:01:36,180 And then I'm going to specify the rest as being the general section. 24 00:01:36,510 --> 00:01:41,490 In addition to creating this whole general section, I'm going to add a new RO that uses the general 25 00:01:41,700 --> 00:01:44,610 for both the department detail and our section. 26 00:01:45,210 --> 00:01:50,430 What we're doing here is creating this hierarchy of values and we need to use some specialized measures 27 00:01:50,430 --> 00:01:51,840 to allocate it to the lowest level. 28 00:01:52,050 --> 00:01:54,360 But I'm going to back burner that issue for now. 29 00:01:54,930 --> 00:01:59,310 Before I add this to the data model, let's create one more field that I'll call top. 30 00:01:59,730 --> 00:02:04,680 I'm going to group the butcher and bakery into a specialized group and then the rest into the general 31 00:02:04,680 --> 00:02:04,950 group. 32 00:02:04,950 --> 00:02:05,550 Yet again. 33 00:02:06,120 --> 00:02:08,730 This will give us a three tier hierarchy just for fun. 34 00:02:08,970 --> 00:02:13,800 And now I can add this table to our data model and start working with it in our data model. 35 00:02:13,830 --> 00:02:18,800 I'll quickly create the relationships between each of our supporting tables and this department's table. 36 00:02:19,380 --> 00:02:24,540 The reason we needed to add General to the department's detailed Section two is because we needed to 37 00:02:24,540 --> 00:02:29,100 ensure that there was a one to many relationship between this table and the other tables. 38 00:02:29,340 --> 00:02:33,840 Otherwise we would have tried to create a many to many relationship since there would have been many 39 00:02:33,840 --> 00:02:36,150 general values in this table to connect to. 40 00:02:37,050 --> 00:02:42,210 With these relationships created, we can go and use our department's table just like any other in our 41 00:02:42,210 --> 00:02:47,520 pivot table, but I don't want users throwing department in and seeing the arresting results that we 42 00:02:47,520 --> 00:02:48,070 expect. 43 00:02:48,420 --> 00:02:51,360 So let's move to our diagram for you in the diagram. 44 00:02:51,450 --> 00:02:51,600 You. 45 00:02:51,630 --> 00:02:55,950 There are some buttons that I skipped over early in the course, hidden in each of our tables. 46 00:02:56,580 --> 00:02:57,780 They appear at the top, right? 47 00:02:57,780 --> 00:03:03,780 When you mouse over a table, the left button is create hierarchy while the right buttons maximize window. 48 00:03:04,230 --> 00:03:06,930 Clicking on the maximize window button is pretty straightforward. 49 00:03:06,930 --> 00:03:10,820 It makes the window big and we can click on it again to shrink it back down. 50 00:03:11,400 --> 00:03:17,190 What we're interested in now is the create hierarchy button and we to our department's table and click 51 00:03:17,190 --> 00:03:18,860 on the Create Hierarchy button for it. 52 00:03:19,680 --> 00:03:25,500 It's going to create a new entry in our table with a little folder hierarchy icon and it'll ask us to 53 00:03:25,500 --> 00:03:26,250 input a name. 54 00:03:26,790 --> 00:03:28,650 I'll call this department hierarchy. 55 00:03:29,580 --> 00:03:33,300 Once I've done this, there's a little drag field's indicator showing to the right. 56 00:03:34,140 --> 00:03:39,870 I can create this custom hierarchy by doing exactly as it says and dragging fields onto this. 57 00:03:40,270 --> 00:03:45,690 I'll start with the topmost level and drag it onto the department hierarchy item and it'll get added 58 00:03:46,260 --> 00:03:51,360 that I'll drag section down below it and you can see where the black line shows up to indicate where 59 00:03:51,360 --> 00:03:52,260 it's going to drop. 60 00:03:53,070 --> 00:03:59,310 Finally, I'll try the department field below the section area and now we have a three tiered hierarchy. 61 00:03:59,970 --> 00:04:04,530 Once I've done this, I'm going to right click on each of the fields that are outside of the hierarchy 62 00:04:04,710 --> 00:04:07,000 and choose to hide them from the client tools. 63 00:04:07,350 --> 00:04:12,240 This will prevent our users from using them in the pivot table, thereby forcing them to use the department 64 00:04:12,240 --> 00:04:12,840 hierarchy. 65 00:04:13,500 --> 00:04:18,590 I'll close the Excel data model and we go to the pivot table and start to see our hierarchy in action. 66 00:04:18,960 --> 00:04:23,640 I'm going to shrink our data down and then remove store from the pivot table to limit the number of 67 00:04:23,640 --> 00:04:24,540 moving pieces. 68 00:04:25,080 --> 00:04:30,300 Once I find the department's table and the listing, I can select the department hierarchy and add it. 69 00:04:30,570 --> 00:04:34,800 Since it's the only thing available anyways, I'll drag it to the rows. 70 00:04:34,800 --> 00:04:39,330 And now we have a split between the general and specialized high level department sections. 71 00:04:40,020 --> 00:04:43,920 I'll start by drilling into specialized and we can see the butcher and bakery values. 72 00:04:44,910 --> 00:04:51,210 Once I in the general, we see one more general level, but drilling into that again will give us the 73 00:04:51,210 --> 00:04:52,710 detail department components. 74 00:04:53,660 --> 00:04:59,420 As we see it, this detailed granularity and as we expected, our profit calculations don't seem to 75 00:04:59,420 --> 00:05:05,000 make any sense, since the wages are only not zero for the general field and it's zero for all others. 76 00:05:05,340 --> 00:05:08,010 So we'll aggregate this back to the general level for now. 77 00:05:08,750 --> 00:05:12,020 With that, we created a brand new hierarchy for our departments. 78 00:05:12,150 --> 00:05:13,640 But what about the date hierarchy? 79 00:05:13,650 --> 00:05:14,680 We wanted to edit it. 80 00:05:15,590 --> 00:05:19,130 We have the quarter field that we created and we've used it manually. 81 00:05:19,700 --> 00:05:25,340 So let's go to the data model, go back to our diagram for you and look at the calendar table. 82 00:05:26,890 --> 00:05:33,250 This is as easy as finding quarter in this table and dragging it all the way down into the data hierarchy 83 00:05:33,250 --> 00:05:39,220 area and dropping it between year and month, once I've done this, I can close power of it yet again, 84 00:05:39,730 --> 00:05:44,830 go to our years and expand one to see that we have a quarter split now rather than the monthly split 85 00:05:44,830 --> 00:05:51,820 from before hierarchy's like these are exceptionally good tools for creating very useful aggregations 86 00:05:52,000 --> 00:05:57,050 and splits like the one we used for our departments and also works well for geography's. 87 00:05:57,380 --> 00:06:02,590 Imagine having state, city and address levels of detail if you're working with data spread across the 88 00:06:02,590 --> 00:06:03,100 globe. 89 00:06:03,670 --> 00:06:07,660 And obviously it works well for tiered structures like dates and times. 90 00:06:08,730 --> 00:06:13,770 Further, as you've been able to see, the hardest part about setting up a hierarchy might actually 91 00:06:13,770 --> 00:06:19,020 be naming the hierarchy fields themselves, since all the rest is drag and drop once you've created 92 00:06:19,020 --> 00:06:21,870 your relationships and your proper dimensional table. 93 00:06:22,880 --> 00:06:27,980 The next lesson will close out our educational portion of this course with a short lesson on how to 94 00:06:27,980 --> 00:06:33,860 define key performance indicators, after which you'll be given a data set and a full assignment to 95 00:06:33,860 --> 00:06:39,050 apply everything from this course to followed, of course, by a guided walk through of how you could 96 00:06:39,050 --> 00:06:39,590 have done it.