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.
