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.
