-: In this lesson, we are going to dive
just a little bit deeper off of the last lesson
and we are going to talk about understanding dimensions.
So what we're going to do is we're going to take a look
at fact tables and fact table grains,
and then we're going to dive slightly deeper
into database dimensions
and talk about that a little bit more.
Then we'll finish up by talking about
relationships between fact and dimension tables.
So with that, let's dive on in.
So let's start off by talking about fact tables
and fact table grains.
And for this analogy, I'm going to take us into a
grocery store and we're going to talk about everything
in the terms of what we would find in a grocery store.
So let's start off with our fact table.
Now, if you remember fact tables contain numeric data.
So we could be looking at a store profits
or we could look at the product sales that are going across
as they beep through the register.
And we could look at sales by register.
These are all kinds of things
that could lie in that fact table.
And so each row is going to represent a single event.
For instance, it might represent a product sale.
And so each piece of information helps us to measure.
So for instance, we could be looking at
how successful we are, how many products did we sell?
So those individual product sales help us
to measure how profitable we are
or how many sales we have of a particular product.
And so then that takes us to the fact table grain.
Now the fact table grain helps us to identify exactly
what's inside of the fact table record.
Good way to think about it is like the business description
of what's going to be in that record.
So it communicates the level of detail
that we're going to have.
And so for this case, we could look at it
and we could say that our main fact table is sales.
And so all of the data that we we have in that fact table
is going to describe the sales of our particular store.
So then looking at our dimensions.
Dimensions then are going to add additional description
to those fact tables.
So they're not just going to be numeric data,
it's going to be all kinds of things.
For instance, I could have the day, month,
year of the product sales, or I could have the brand name
of the chips that we're selling,
or the type of chips, so barbecue or whatever, right?
So those dimension tables are then going to describe
the sales going on in the fact table.
And they're going to provide us details
about each instance of an object, so our product detail.
And the level of detail that we include
is incredibly important because if we add too much detail
it's going to bog down our queries
and make it very hard to find the data that we need.
If we don't add enough detail
then we're going to have a hard time getting the reports
that we want at a business level.
So now let's jump in
and let's talk a little bit about the relationship
between those fact and dimension tables.
So a fact and a dimension table have a relationship
and the relationship is defined by a primary key.
So this is a unique data column that helps us
to define relationships.
So basically let's say that we have our sales example again
and we have all of the sales in our fact table
and then we have a description describing the products
in a dimension table.
Well, we could have an item key for instance
that could tie together the fact table
and the dimension table.
That key builds that relationship.
Or another example, let's say that we have a student ID.
So we're looking at grades that a student gets.
So we have our grades
and we'll have multiple grades of columns
and then we'll have an average there as a final column.
We could use a student ID, the first column there
to be a relationship builder
to help us to tie in to additional tables.
Now, beyond my primary key, I can also have
a foreign key that can provide an additional link
between data in 2 tables.
So for instance,
I could have a class table that provides a list of classes
and then it provides the grades in those classes
and then has a teacher ID.
Now that teacher ID could be linked to another table
that provides the first and last name of that teacher.
So I could actually link those 2 tables together
by that teacher ID.
So the important thing to remember here is fact
and dimension tables have a relationship
and that relationship is defined by the keys that you use.
So let's tie all of this together here
and talk about what we need to know for the DP-203.
So we talked about fact tables and fact table grains.
Fact table grains can absolutely make or break a design.
You have to make sure that the grain that you're using
matches the business description and you have to make sure
that the data in there is relevant to that grain.
So that's very important to make sure that you don't get
a table that gets too busy or doesn't provide enough clarity
or information to get the data that you need out.
How many dimension levels do you need?
This is another critical point to think about.
You can have a star table with 1 dimension level
or you could have a snowflake table
with 20 dimension levels.
The levels that you add is going to add complexity
to your database.
So make sure that you're defining your dimension levels
and the data in a way that is appropriate
and provides the detail needed
to get the data that you need.
It all comes back to that query
and making sure that you're matching the business design
and what the table or database really needs to be.
Finally, the key is the key.
Make sure that your foreign and primary keys make sense
and that you can tie those together to pull all
the tables together to get the data that you need.
So for the DP-203,
what you really need to understand is the basics.
You need to understand the basics about keys.
You need to understand the basics
about fact and dimension tables
and just a little bit of database design.
If you've got that down,
you should be good to move on into the next lesson.
So with that, we'll stop talking about schemas
and we'll move on.