hi guys today I'm going to show you how to design
a dimensional data model which is also referred to as the Ralph Kimball
methodology for data modeling at the end of this tutorial, you
will be able to know the differences between
dimensional data model the differences between dimensions
facts and fact tables i will as well walk you through the whole dimensional
design process in order to obtain a star schema
and we are going to do that based on a case study in the mining
industry for uh for a good exploration project. I'll do my best to explain some
of these terminologies but I highly recommend that you watch my
previous videos for detailed explanations. So what is a
data model? it's the process of organizing data to
be logically stored in a database and dimensional
data modeling is one of the techniques for data modeling.
Dimensional data modeling is important because
it ensures that quality and accurate data are stored within a database
it helps us to easily define relationships among tables
using the primary and foreign key relationship
and lastly, it helps and provides an optimized database for
faster queries
dimensional data model is a denormalization technique
whereby you have a fact table surrounded by multiple dimensions
and constrained by primary and foreign keys
as you can see in the diagram we have a fact table in the center
and multiple dimensions surrounding this fact table
the arrows pointing from the dimensions to the fact table
you can think of them as the primary and foreign key relationships
and this diagram is often called the star schema. For a dimensional data
model there are some steps that we need to follow in order to come up with this
design and the first one will be for us to
identify our business process which is the operational activity that
we are modeling and here our case study
is going to tell us what operational activity we are going to measure
which is an exploration activity second you need to declare your grain
the grain is the lowest level of information
you want to capture within your data model or your database
the third is to identify your dimensions, dimensions are the descriptive entities
of the data model more like the "who" aspect who could be the person
you are collecting information about and the "where" location. So
if you have these descriptive entities you need to identify them as your
dimension tables. Next, you need to identify what
are the numeric measurements that you you are going to measure from the
business process and these are known as the Fact so in a
dimensional model you do not miss the numeric measurements
with the descriptive attributes you need to differentiate
them and the numeric measurements you call them
Facts and the descriptive entities you call them
dimensions. We need to create what we call a fact table and a fact
table should contain our fact which is the numerical
measurements and foreign keys of the associated dimensions
because we want to link all the dimensions to the fact in order to
obtain a star schema. If you have degenerate dimensions you
need to include them in your fact table as well as your audit information
which is the date/time stamp. So guys, this is our case study for the
exploration project and i've provided a copy of the
case study below this video so feel free to refer to it as you
follow along. I will show you how to design the star
schema from the conceptual level to the logical
level on an Excel sheet. I'll be making a follow-up video in future
and showing you how to design the physical model within
sql server management studio okay so let's dive on to
our excel sheet and start working on this case study
as i mentioned earlier for a dimensional model we need to identify
our business process so first i'm going to write down the business process what is
it going to be
and we need to identify our grain which is
the lowest level of information we want to capture
in our model, then next we're going to list all the dimensions
and Facts, identify our fact table and list all our facts out. We're going
to obtain all this information by reading through the question
and again the question is below this video so refer to it and follow along.
Okay so as I mentioned earlier our business
process is for an exploration drilling
activities, so I'm going to call this business process exploration drilling.
okay and the grain we are going to read through the question in order to obtain
the grain and here the question says that budget is
already in place to develop a database that will provide the number
of holes drilled per concession a day. This first sentence tells me
that my grain is going to be holes drilled per employee per
concession per day that's what i'm going to choose
as my grain so
holes drilled
per employee per concession
per day. okay We're going to read through the questions again
to identify what are our dimensions, what are our descriptive
entities so budget is already in place to develop a database that will provide
the number of drill holes per concession per day
so I believe that drill hole is one of my
my dimensions and concession should also be
a dimension and day should give me a date
table or date dimension. Alright, moving on it says that management will
require reports that provides the total amount charged per drilling company
per concession okay management wants to leverage the
database to set target for each employee
and to identify the potential drilling issues I'll call this drilling issues.
Alright so how many dimensions do I have so far six
so these are the dimensions that I can identify from the question
however there should be a location dimension because
hey, every concession needs to have a location.
Alright moving on to the fact table what is our fact table
well the question tells me that there should be one
fact which is um we are measuring the total amount
for the drilling project so I'm going to call this fact table
fact drilling activity or drilling operations
okay and the measure is the total
amount. Alright so we can call this one as
the conceptual model and i'm going to go ahead to design a star
schema using shapes so you can see the high level
representation of these entities and the relationships between them
so watch me as I create these, use these rectangles to represent my
entities and my dimensions and my fact table and the relationship
between them okay so I have seven dimensions.
Okay, I have less space here,
I hope this is gonna work
okay so i'm gonna give this fact a different color and call it
Fact drilling activity
and all of these dimensions will be linking to the
fact table
okay
okay so this dimension is going to be called dimension
drill hole. okay, this is my dimension - concession
alright so guys, this is my final product for my
high-level conceptual star schema, high level conceptual
dimensional model so my business process exploration drilling um the grain
the dimensions that we listed and the fact
and I represent all these dimensions surrounding
the fact table constrained by high level relationship between the dimensions
and the fact. Moving on I'm going to design a logical
model of the star schema and at the logical model
you want to have your dimensions and your facts
and which are the entities you also want to
identify your attributes which will be the fields or the columns
and then the primary and foreign keys need to be
identified at this stage so i'm going gonna go on to a new
spreadsheet and paste my dimensions and fact here
here i'm just gonna paste special and transpose okay i'm just gonna paste
my fact here okay and
just give it a little a little bit of design
I like colors so I can differentiate between my dimensions and my fact table
okay so these are my entities and i need to
list my attributes all of these entities need to have
primary keys so i'll go ahead and do that.
For start, drill hole id, concession id
date key so these are my IDs for all the dimensions and the fact table
and i'm going to just go ahead and list a few of
the attributes. For the drill hole I'm gonna have
like a drill type, and I'm gonna have let's say maximum depth of a drill
hole we can also have first and last name as attributes for the
employee okay
okay so i'm just gonna go ahead and populate these
tables as we move along okay so moving on to our fact table
we realize that from the question we need to put what is the fact
and the question says that total amount is a numeric measurement and that is our
fact a fact table should contain the numeric measurements
and the primary keys or the foreign keys of all the associated
dimensions, so the foreign keys of all the associated dimensions I'm just
gonna paste them here okay it needs to have a degenerate
dimension if you have it please list it and time and date stamp
so we're gonna have this date stamp in our fact table and then we are going
to use shapes again to represent our star schema for this
um, logical model, this is what I have as my final product for my
logical model as you can see, I have my fact table being surrounded by all
these dimensions and the constraints which is primary key
within each dimension being represented in the fact table as a foreign key and
that is the relationship between my dimensions and my fact
thank you guys so much for watching and a quick recap as i promised you
at the beginning of this video, we know
the importance of a dimensional data model
we know the differences between dimensions fact and fact table
we have also walked through the whole dimensional design process to obtain
a logical model star schema based on our case study i highly recommend
that you watch my previous videos for detailed explanations of some of the
terminologies that were used here. I am going to follow up
with another video of how to design a physical model within sql server
management studio and that will be in my next video so
stay tuned. I also intend to show you how to populate
the date dimension in Excel so you can use it for your future date dimensions
as well. Thank you so much for watching this
video and have a great day bye bye