Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,620 --> 00:00:00,880
All right.
2
00:00:00,890 --> 00:00:05,950
Time to talk about snowflakes and I'm not talking about whether I'm talking about schemas.
3
00:00:06,050 --> 00:00:08,320
I consider this table structure here.
4
00:00:08,450 --> 00:00:13,550
This should look extremely familiar because this is the exact table structure that we're working with
5
00:00:13,940 --> 00:00:21,020
with our Adventure Works demo got one data table sales data table at the bottom and three product related
6
00:00:21,140 --> 00:00:25,730
look at tables product subcategories and categories.
7
00:00:25,730 --> 00:00:27,120
Now here's the thing.
8
00:00:27,170 --> 00:00:32,730
The sales data table can connect to products using that product key column.
9
00:00:32,930 --> 00:00:39,080
But we have no means of connecting the sales data directly to either subcategories or categories because
10
00:00:39,080 --> 00:00:43,720
we don't have any foreign key that can map to one of the fields in those tables.
11
00:00:44,060 --> 00:00:46,420
But fear not we've got an alternative.
12
00:00:46,670 --> 00:00:52,490
And what we're going to do here is actually connect products to products subcategories because both
13
00:00:52,490 --> 00:01:00,710
of those tables share products subcategory key and then by similar logic connects subcategories to categories
14
00:01:01,070 --> 00:01:04,390
because those two share product category key.
15
00:01:04,460 --> 00:01:10,780
And in doing so we've essentially connected sales data to each of those lookups in the chain.
16
00:01:11,000 --> 00:01:15,760
So when you create those actual table relationships it looks something like this.
17
00:01:15,830 --> 00:01:22,160
So little protip peer models that have chains of look up or dimension tables like this are often called
18
00:01:22,160 --> 00:01:28,850
snowflake's schemas whereas star schemas generally have a bunch of individual lookup tables surrounding
19
00:01:28,850 --> 00:01:30,700
one central data table.
20
00:01:31,010 --> 00:01:38,570
So let's hop into our relationship view and build out these product relationships case or back to our
21
00:01:38,570 --> 00:01:40,160
model in the relationships.
22
00:01:40,160 --> 00:01:47,720
You got these set up nicely to create that snowflake chain and all we need to do here is Connect the
23
00:01:47,720 --> 00:01:56,300
product subcategory key to the product subcategory key there and the category key to the category of
24
00:01:56,300 --> 00:01:57,560
key.
25
00:01:57,640 --> 00:01:58,330
And there you go.
26
00:01:58,330 --> 00:02:05,490
We've created our snowflake our chain set of lookups that connects all the way down to that sales table.
27
00:02:05,530 --> 00:02:11,410
That means we can filter or segment these order quantity values by fields in the product table or the
28
00:02:11,410 --> 00:02:14,300
subcategory table or the category table.
29
00:02:14,530 --> 00:02:19,660
Now interesting thing call out here is that you'll notice that a table like this product subcategory
30
00:02:20,290 --> 00:02:25,070
or the product lookup table for that matter contains both primary keys.
31
00:02:25,150 --> 00:02:31,000
In this case product key and foreign keys like the product subcategory key because in this case for
32
00:02:31,000 --> 00:02:38,050
products unique values for product key and multiple values for subcategory and the same thing holds
33
00:02:38,440 --> 00:02:40,610
in the subcategory table as well.
34
00:02:40,660 --> 00:02:45,500
It's got a subcategory key which is primary and a category key which is foreign.
35
00:02:45,520 --> 00:02:50,860
So just an interesting thing to call out there tables don't necessarily have to have only a primary
36
00:02:51,040 --> 00:02:52,220
or foreign key.
37
00:02:52,270 --> 00:02:54,190
They could potentially have both.
38
00:02:54,190 --> 00:02:55,060
So there you have it.
39
00:02:55,090 --> 00:03:01,150
We've created a little snowflake schema here and we've officially wired up all of the tables that currently
40
00:03:01,150 --> 00:03:02,590
exist in our model.
41
00:03:02,590 --> 00:03:08,210
So go ahead and give the file a save and then we'll move on to managing and editing these relationships.
4431
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.