Hi all this is Mina and welcome to my channel I'm excited about today's video
because it is the first video i'm making on how to extract data from source systems
transform them and load them into SQL server. This ETL process will be completed using SSIS
for this tutorial I obtained preliminary data set on confirmed cases of COVID 19
from the statistics canada website and here is the link for you to get a data set as well
at the end of this video you will be able to understand the purpose of
staging environment, during data modelling and data warehousing design process, you
will be able to migrate data from source systems into staging environment using SSIS
SSIS simply stands for SQL server integration services and the third objective will be to help
you create a source to target mapping document that you can use to track all of your ETL processes
you would also be able to create an enterprise data warehouse star schema in SQL server based on
the Ralph Kimball methodology and lastly you would have a data warehouse that is populated with data
from staging environment to the data warehouse using SSIS.
Before we move on to explore the data, I would like to walk you through a brief introduction
to what data warehouse is all about and what is ETL. So as you all know, we have data from several
different sources and in order for us to get them into a central data warehouse, we need to grab
all of the data from wherever they are situated whether on the cloud or on our local computer
we need to extract that data, transform it and then load it into a temporary environment here in
this case it's referred to as staging database so that's a temporary environment that holds the data
and then from there, you can again extract the data from the staging environment
transform it and load into the data warehouse. once in the data warehouse business users can explore
the data and create visualizations and reports on it using power bi tableau excel or SSRS
in some companies, they prefer to rather create a data mart which is a simple form of data warehouse
focused on just a single department so let's say they would create a data mart from the
data warehouse just for the sales department. so that would contain only sales information and the
sales guys can develop reports from it and also they can create another one for the HR
to also report on that as well. they can have several different data marts but you can also find
most of the time business users going directly into the data warehouse to create reports as well
so moving on to explore our data set, I got this data set from the statistics Canada website
as I've already talked about and here is the definition of all of the columns they did so
well to define all of the columns on the metadata sheet and we have about 29 columns in that table
it's basically about COVID 19 patients and um whether they were hospitalized or not
the week in which they started experiencing symptoms and if their um symptoms
or their infection led to death or not it also recalls the year that the incident happened and
also personal a little bit of personal information regarding the patient such as the gender
and the age group of the patient so this is the metadata and this is the data itself, um 29 columns
so from this data i created a source to target mapping document if you watched my previous video on
how to create dimensional data model i showed you how to create a source to target mapping document
which is essentially um metadata that you can use to track all of your source tables
source columns all the way through the ETL process so you can use it to keep
track of your source tables and the tables and columns that end up in the staging environment
as well as um the tables and columns that you will specify as your dimensions when you are creating
the data warehouse as well as the fact information. So from this source to target mapping document
i have a column called source system so here i specify where the file is located on my local
computer if it's in the cloud you can put that information under the source system or database
here and the source table within this location is called COVID 19 data
these are the different columns in a table that i showed you previously
so from this source data I'm going to create a staging environment which is essentially a database
table you can say a database table within SQL server and I'm going to call that table COVID 19
and the columns I'm going to have to rename all of these columns to into a meaninful column names
and i'm gonna perform a little bit of transformation on some of the columns so
i have modified the data a little bit so that we can transform it so
um if you look at the data I have removed the death field as well as the
transmission field. I want to use SSIS to migrate all of the source data into the SQL server staging
environment and I'm going to use SSIS to change all of the column names as well as
in introduce these two new columns that are intentionally omitted from the source data
so this is going to be how i'm going to structure the star schema enterprise data warehouse and
for all of these columns, I've been able to identify one, two, three, four, five, and six. I'll say
six dimensions from um the source system so I identified the dimension for the region which is
essentially containing all the region or the location of the COVID 19 patient and in this
table I'm just going to have the region code and the definition of the origin itself and because
this is a dimension table i'm going to introduce a surrogate key which is an auto incrementing um
identity, primary key field auto-incrementing means that um I'm going to set it to start from one
increment by one. So in this field, I'm just gonna have one, two, three, four, five and it's
a primary key because it will uniquely identify all of the region information within that table
all right and I'm also going to have the year week table um this
is a little different from our typical date dimension that we know
because the data was collected at the weekly level so that is the grain
of the data so it's aggregated to weak level and i do not have granular information as to the day,
or the minute or the hour the the incident occurred so I'm also going to create um
a year week table that has just a year and a week so in this table i would create um a year week id
which will essentially be a combination of these two fields and that's going to be my primary key
there is also the Dim patient details table and that has all of the personal information
regarding the patient such as the gender and the age group and the occupation of the patient
in this table, I'm going to create a patient id field to serve as my primary or surrogate key
and this is also auto-incrementing as well of my primary keys will be auto-incrementing
all right there is the dim transmission table that will contain the translation code
which is more like a business key business key means that it's just a code so if we go into
the metadata, let me show you. transmission code so it's just like one stands for domestic acquisition
contact of COVID 19 case and then two so it's just one, two, nine in that field and the transmission is
the description of the code however I'm still going to have a surrogate or primary key to
uniquely identify this field in case the code we usually create this primary key so that in case
the business key or the code information changes it does not affect the uniqueness of our table
all right there is also the dim hospital status table that will contain the hospital status code
and the hospital status and hospital status id field will be the primary key
I'm going to put Asymptomatic, Resolve and Death information into the junk table because
all of them contain Yes' and Nos. I'm not gonna create three tables here. I'm gonna create just
one and then i would create a role playing dimension that would link them to the fact
table and this will be the primary key of this table and moving on to the fact information
this COV ID field is coming from the source so that will serve as my business key
in that case, i need to create a primary surrogate key which i will call Fact_COVID19_ID
and then a typical fact table needs to have the foreign keys of all the associated dimensions which
means that all all of the primary keys within these tables need to also be represented in the
fact table so that the connections can be created between the dimensions and the fact table
I do not have any metric information on this fact table so that renders this Fact table as a Factless
fact table. this is the name for a fact table that does not have any metric information in it. In all
of these columns, there is no column that serves as a degenerate dimension so in this fact table
there will be no degenerate dimension. Last but not the least I will introduce a load date field to serve
as the date stamp for when the records are loaded into the fact table so here I'm going to just draw
a logical model for you to get an overview of how the star schema is going to look like
so this is going to be my fact table in the center and the dimension tables surrounding it
now
so i'm just drawing the logical model so you can have an idea of how the star schema data warehouse
is going to look like so I like to represent everything in the form of the diagram
so here we go, region links to the fact table and the way they can link to the fact table is
when I introduce their IDs or primary keys into the fact table. So because I have RegionID
here and RegionID here, I can link with both tables all right and last but not the least
this is how our star schema is going to look like. For the junk table, I'm going to have
three
connections to the fact table. Yes each one representing Asymptomatic, Resolved and Death
information
all right let me know in the comment section if you have any questions regarding this model
and since i'll be covering a lot of topics in this video please feel free to pause the video
to gain more clarity on anything you might miss along the way. Also due to the high level of
questions from my viewers i now have a dedicated team who will answer all the practical questions
and also offer mentorship on these topics. So if you are interested feel free to send us an
email so right now we are going to move on to visual studio and we are going to create
a new project using the integration service project that's SSIS. SQL server integration
services in visual studio you can identify it as integration service project. So I'll
click on that and go to next. Now I have to give a name to my project I'm going to call it COVID19
ETL
okay I need to
save this in my C drive. I'm just gonna navigate to my C drive
and save it in this folder. Now I can click on create and this begins my ETL creation process