1
00:00:00,860 --> 00:00:06,740
In the first chapter, we use the relationship tools with an Excel plus some Excel formula magic to

2
00:00:06,740 --> 00:00:08,570
build our first Power Pivot report.

3
00:00:09,290 --> 00:00:15,050
In this chapter, we'll get into the meat of power, pivot and rely mostly, at least on power pivots,

4
00:00:15,050 --> 00:00:17,970
internal tools to rebuild and improve that report.

5
00:00:18,530 --> 00:00:24,170
To that end, I've reverted my data file to one without relationships or data model settings for us

6
00:00:24,170 --> 00:00:24,980
to start from.

7
00:00:25,880 --> 00:00:31,130
My first step will be to add data to the data model, the easiest way to do this is with the add to

8
00:00:31,130 --> 00:00:33,320
data model button on the power pivot ribbon.

9
00:00:33,920 --> 00:00:39,170
Selecting any cell within each table and pressing that button will add the table directly to the data

10
00:00:39,170 --> 00:00:40,060
model for use.

11
00:00:40,790 --> 00:00:45,350
Each time I do this, the power pivot window will open and I can simply close it.

12
00:00:45,350 --> 00:00:46,460
Since I don't need it yet.

13
00:00:47,130 --> 00:00:52,610
I'll do this for all three of my data tables plus my stores dimensional table quite quickly.

14
00:00:54,150 --> 00:00:59,940
Note that the stores table is manually defined using the remove duplicates command, the best approach

15
00:00:59,940 --> 00:01:03,540
for this would have been to use power query and have it load to the data model.

16
00:01:03,720 --> 00:01:05,550
But that's for a more advanced course.

17
00:01:06,900 --> 00:01:10,690
My next step is to create the date table in the first version.

18
00:01:10,710 --> 00:01:15,090
I did this manually inside Excel with formulas in this.

19
00:01:15,090 --> 00:01:19,590
I'm going to flip through my tables really quickly to see what date range I need in order to handle

20
00:01:19,590 --> 00:01:20,160
my data.

21
00:01:20,430 --> 00:01:26,310
Though this isn't strictly necessary, the labor table tells me that I need one one two thousand nineteen

22
00:01:26,310 --> 00:01:28,350
through eight thirty one two thousand nineteen.

23
00:01:29,160 --> 00:01:33,930
Now I only need a few mouse clicks to create this table on the desired tab.

24
00:01:33,930 --> 00:01:37,650
I can go to the date table dropdown and click on New Immediately.

25
00:01:37,650 --> 00:01:43,170
I have a table with most of the date information I need clicking on that date dropdown again, I can

26
00:01:43,170 --> 00:01:48,850
check the range to see that it has chosen one one twenty nineteen through twelve thirty one twenty nineteen.

27
00:01:49,710 --> 00:01:52,800
So at this point I'm ready to create my relationships.

28
00:01:53,610 --> 00:01:58,590
I'll use the create relationship button while on my calendar table to create these relationships.

29
00:01:59,010 --> 00:02:04,740
What I click on this, a new window shows up with a preview of my calendar table, the top and a dropdown

30
00:02:04,740 --> 00:02:06,300
about halfway down the screen.

31
00:02:06,720 --> 00:02:12,000
This dropdown will let me pick the other table that I want the relationship created for so I can click

32
00:02:12,000 --> 00:02:14,130
on Labor to create the relationship to it.

33
00:02:15,180 --> 00:02:20,640
When creating this relationship, I want to pick the column along the top and the column along the bottom

34
00:02:20,640 --> 00:02:21,900
tables that match.

35
00:02:22,230 --> 00:02:24,270
In this case, that's the date field.

36
00:02:25,170 --> 00:02:28,440
Since they're named appropriately, the selection is made automatically.

37
00:02:29,280 --> 00:02:34,890
Once I'm ready to create the relationship, I can press OK, I need to create the state relationship

38
00:02:34,890 --> 00:02:37,380
two more times for sales and purchase orders.

39
00:02:37,740 --> 00:02:41,520
And as you can see, doing so takes only a few seconds.

40
00:02:41,910 --> 00:02:46,950
I'll also need to do the same thing for my stores table and the process will be identical.

41
00:02:47,640 --> 00:02:52,830
I'll select the stores table along the bottom of my data model window and then I'll go to the create

42
00:02:52,830 --> 00:02:58,500
relationship and create a relationship between the stores table and each of my three data tables, just

43
00:02:58,500 --> 00:02:59,760
as I did with the dates.

44
00:03:00,480 --> 00:03:06,600
Once I've finished with this again, after only a few seconds, I go to my diagram view via the home

45
00:03:06,600 --> 00:03:09,660
tab of my ribbon to see what my data model looks like.

46
00:03:10,470 --> 00:03:14,130
Much, much easier than creating a complicated service function.

47
00:03:14,280 --> 00:03:20,220
Right from here, I can go to the home tab and choose the pivot table dropdown and create a pivot table

48
00:03:20,220 --> 00:03:22,050
immediately on a new worksheet.

49
00:03:22,860 --> 00:03:28,590
I drag some fields here, I apply some formatting there and suddenly we're looking at an almost completed

50
00:03:28,590 --> 00:03:29,100
report.

51
00:03:29,370 --> 00:03:33,570
I'm still missing the profit computation, but we'll save that for one of our next lessons.

52
00:03:34,200 --> 00:03:39,240
I've recreated the basic structure here, so let's pop into our power pivot window and briefly review

53
00:03:39,240 --> 00:03:39,840
the setup.

54
00:03:40,140 --> 00:03:45,930
The diagram shows how the tables connect together at a very high level, much like the entity relationship

55
00:03:45,930 --> 00:03:48,720
diagram we created on the design tab.

56
00:03:48,720 --> 00:03:54,150
I can access the managed relationships button to pull up the list of relationships like an Excel.

57
00:03:54,720 --> 00:04:00,060
But in this window I have much more detail about the type of relationship, the table names and the

58
00:04:00,060 --> 00:04:00,720
field names.

59
00:04:00,720 --> 00:04:07,080
All the clients editing any single one will bring up the same relationship creation window that we used

60
00:04:07,080 --> 00:04:13,050
before with the table previews and the selection of the appropriate column in each section for specifying

61
00:04:13,050 --> 00:04:13,950
the relationship.

62
00:04:15,110 --> 00:04:21,140
Let's go look at our calendar table again, I could modify the range of this calendar table by going

63
00:04:21,140 --> 00:04:28,070
to the design tab, going to the date table, dropdown and choosing the update range option here.

64
00:04:28,320 --> 00:04:33,740
There's a little calendar pop up next to the field that you could use to select the specific date I

65
00:04:33,740 --> 00:04:35,520
could navigate to and select.

66
00:04:35,540 --> 00:04:37,220
January twenty twenty two.

67
00:04:37,220 --> 00:04:43,550
Just because to give us a little bit of future proof, other future updates could be made just as easily,

68
00:04:43,850 --> 00:04:50,060
making the state table far, far easier to manage and use than the Excel based one we created earlier.

69
00:04:50,750 --> 00:04:54,380
As an additional bonus, it has another beneficial side effect.

70
00:04:54,710 --> 00:05:00,380
So let's pop back out to our table and learn some more about the special date hierarchy field.

71
00:05:01,300 --> 00:05:06,400
Let's remove the month field from the Rose section and take the date hierarchy field from the calendar

72
00:05:06,580 --> 00:05:13,000
and move it there instead, this date hierarchy is basically a fully formed set of date dependencies.

73
00:05:13,180 --> 00:05:18,430
Imagine if you had dragged year, month and day all into your Rollerball section.

74
00:05:18,430 --> 00:05:21,190
It'd be huge, but you'd have all of your information at hand.

75
00:05:21,820 --> 00:05:24,540
Date hierarchy does that with a foldable container.

76
00:05:24,910 --> 00:05:31,060
So by adding it, I can expand out here and then I can expand out back to see individual day results.

77
00:05:32,020 --> 00:05:37,780
With that, we've recreated one portion of our original report, we've abused one of its cells, pivot

78
00:05:37,780 --> 00:05:43,240
table mechanics, to get around using any DACS to create measures by creating what are called implicit

79
00:05:43,240 --> 00:05:46,030
measures using the values field of our pivot table.

80
00:05:47,200 --> 00:05:53,110
In the next lesson, I will introduce you to two different concepts the concept of a measure and the

81
00:05:53,110 --> 00:05:57,610
concept of a calculated column from which we'll begin to learn some basic tax.