Korry Kelley | Lesson 6
During this lesson I've taken a lite version of Northwinds DB and created a small Data Warehouse (DW) in which I then developed an ETL script in SQL MGMT Studio where I tested a successful run (Please See scripts below). In the image directly below this paragraph you can see my a graphical flow chart of the SSIS ETL package I developed in Visual Studio (VS) based on my working SQL ETL script.
Starting off I want to ensure I don't run into any errors right off the bat by utilizing a SQL Execution Task to reset my DW objects for testing purposes during the VS ETL development process. Please note: the correlating SQL command was inserted into each Task Item.
I then followed by outlining the different steps in the ETL process into bucketed Sequence Containers (Prepare, Extract, Load, Close)
The 1st step in the ETL process is to prepare the DW to be filled by removing any key constraints and truncating the data tables using VS Execute SQL Tasks. Please note: the correlating SQL command was inserted into each Task Item.
The 2nd step in the ETL process is to fill the DW dimension tables with the required information using Data Flow tasks for Database to DW house transactions and an Execute SQL tasks for the auto generated tables. Please note: the correlating SQL command was inserted into each Task Item.
The 3rd step in the ETL process is to fill the DW fact table being this table depends on the dimension table this comes later during the process. Please note: the correlating SQL command was inserted into each Task Item.
The 4th step in the ETL process is to reset the any required key constraints. Please note: the correlating SQL command was inserted into each Task Item.
USE [master]
GO
If Exists (Select * from Sysdatabases Where Name = 'DWNorthwindLite')
Begin
ALTER DATABASE [DWNorthwindLite] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [DWNorthwindLite]
End
GO
Create Database [DWNorthwindLite]
Go
--********************************************************************--
-- Create the Tables
--********************************************************************--
USE [DWNorthwindLite]
Go
/****** [dbo].[DimProducts] ******/
CREATE TABLE DWNorthwindLite.dbo.DimProducts(
ProductKey int IDENTITY NOT NULL
,ProductID int NOT NULL
,ProductName nVarchar(100) NOT NULL
,ProductCategoryID int NOT NULL
,ProductCategoryName nVarchar(100) NOT NULL
,StartDate int NOT NULL
,EndDate int NULL
,IsCurrent char(3) NOT NULL
CONSTRAINT PK_DimProducts PRIMARY KEY (ProductKey)
)
Go
/****** [dbo].[DimCustomers] ******/
CREATE TABLE DWNorthwindLite.dbo.DimCustomers(
CustomerKey int IDENTITY NOT NULL
,CustomerID nchar(5) NOT NULL
,CustomerName nVarchar(100) NOT NULL
,CustomerCity nVarchar(100) NOT NULL
,CustomerCountry nVarchar(100) NOT NULL
,StartDate int NOT NULL
,EndDate int NULL
,IsCurrent char(3) NOT NULL
CONSTRAINT PK_DimCustomers PRIMARY KEY (CustomerKey)
)
Go
/****** [dbo].[DimDates] ******/
CREATE TABLE DWNorthwindLite.dbo.DimDates(
DateKey int NOT NULL
,USADateName nVarchar(100) NOT NULL
,MonthKey int NOT NULL
,MonthName nVarchar(100) NOT NULL
,QuarterKey int NOT NULL
,QuarterName nVarchar(100) NOT NULL
,YearKey int NOT NULL
,YearName nVarchar(100) NOT NULL
CONSTRAINT PK_DimDates PRIMARY KEY (DateKey)
)
Go
/****** [dbo].[FactOrders] ******/
CREATE TABLE DWNorthwindLite.dbo.FactOrders(
OrderID int NOT NULL
,CustomerKey int NOT NULL
,OrderDateKey int NOT NULL
,ProductKey int NOT NULL
,ActualOrderUnitPrice money NOT NULL
,ActualOrderQuantity int NOT NULL
CONSTRAINT PK_FactOrders PRIMARY KEY (OrderID,CustomerKey,OrderDateKey,ProductKey)
)
Go
--********************************************************************--
-- Create the FOREIGN KEY CONSTRAINTS
--********************************************************************--
ALTER TABLE DWNorthwindLite.dbo.FactOrders
ADD CONSTRAINT FK_FactOrders_DimProducts
FOREIGN KEY (ProductKey) REFERENCES DimProducts(ProductKey)
ALTER TABLE DWNorthwindLite.dbo.FactOrders
ADD CONSTRAINT FK_FactOrders_DimCustomers
FOREIGN KEY (CustomerKey) REFERENCES DimCustomers(CustomerKey)
ALTER TABLE DWNorthwindLite.dbo.FactOrders
ADD CONSTRAINT FK_FactOrders_DimDates
FOREIGN KEY (OrderDateKey) REFERENCES DimDates(DateKey)
--********************************************************************--
-- Review the results of this script
--********************************************************************--
Select 'Database Created'
Select Name, xType, CrDate from SysObjects
Where xType in ('u', 'PK', 'F')
Order By xType desc, Name
/*
(STEP 3) Fill the DW using SQL and SSIS
*/
USE [DWNorthwindLite]
Go
--********************************************************************--
-- 1) Drop the FOREIGN KEY CONSTRAINTS and Clear the tables
--********************************************************************--
Alter Table [DWNorthwindLite].dbo.FactOrders Drop Constraint [FK_FactOrders_DimProducts]
Alter Table [DWNorthwindLite].dbo.FactOrders Drop Constraint [FK_FactOrders_DimDates]
Alter Table [DWNorthwindLite].dbo.FactOrders Drop Constraint [FK_FactOrders_DimCustomers]
Go
Truncate Table [DWNorthwindLite].dbo.DimProducts
Truncate Table [DWNorthwindLite].dbo.DimDates
Truncate Table [DWNorthwindLite].dbo.DimProducts
Truncate Table [DWNorthwindLite].dbo.FactOrders
go
--********************************************************************--
-- 2) FILL the Tables
--********************************************************************--
/****** [dbo].[DimProducts] ******/
INSERT INTO [DWNorthwindLite].dbo.DimProducts
SELECT
[ProductID] = Products.ProductID
,[ProductName] = CAST(Products.ProductName as nVarchar(100))
,[ProductCategoryID] = Products.CategoryID
,[ProductCategoryName] = CAST(Categories.CategoryName as nVarchar(100))
,[StartDate] = 20000101
,[EndDate] = Null
,[IsCurrent] = 'Yes'
FROM [NorthwindLite].[dbo].[Categories]
INNER JOIN [NorthwindLite].dbo.Products
ON Categories.CategoryID = Products.CategoryID;
Go
/****** [dbo].[DimCustomers] ******/
INSERT INTO [DWNorthwindLite].[dbo].DimCustomers
Select
[CustomerID] = Customers.CustomerID
,[CustomerName] = CAST(Customers.CompanyName as nvarchar(100))
,[CustomerCity] = CAST(Customers.City as nvarchar(100))
,[CustomerCountry] = CAST(Customers.Country as nvarchar(100))
,[StartDate] = 20000101
,[EndDate] = Null
,[IsCurrent] = 'Yes'
From [NorthwindLite].[dbo].[Customers]
Go
/****** [dbo].[DimDates] ******/
Declare @StartDate Datetime ='01/01/1995'
Declare @EndDate Datetime ='01/01/2000'
Declare @DateInProcess Datetime
Set @DateInProcess=@StartDate
--YEAR(@DateInProcess)+''+MONTH(@DateInProcess)+''+DAY(@DateInProcess)
While @DateInProcess<=@EndDate
Begin
Insert Into [DWNorthwindLite].[dbo].[DimDates]
([DateKey],[USADateName],[MonthKey],[MonthName],[QuarterKey],[QuarterName],[YearKey],[YearName])
Values
(
CAST(FORMAT(@DateInProcess, 'yyyyMMdd') as int)
,DATENAME(WEEKDAY, @DateInProcess)+', '+CAST(MONTH(@DateInProcess) as nvarchar(100))+'/'+DATENAME(DAY,@DateInProcess)+'/'+DATENAME(YEAR,@DateInProcess)
,CAST(FORMAT(@DateInProcess, 'yyyyMM') as int)
,DATENAME(MONTH,@DateInProcess)+','+CAST(Year(@DateInProcess) as nVarchar(100))
,CAST(CAST(YEAR(@DateInProcess) as varchar(4))+'0'+DATENAME(QUARTER,@DateInProcess) as int)
,'Quarter '+DATENAME(QUARTER,@DateInProcess)+', '+CAST(Year(@DateInProcess) as nVarchar(100))
,YEAR(@DateInProcess)
,CAST(Year(@DateInProcess) as nvarchar(100))
)
Set @DateInProcess=DATEADD(d,1,@DateInProcess)
End
Go
--SELECT * FROM [DWNorthwindLite].[dbo].[DimDates]
Go
/****** [dbo].[FactOrders] ******/
Insert Into FactOrders
Select
[OrderID] = [Orders].[OrderID]
,[CustomerKey] = [DimCustomers].[CustomerKey]
,[OrderDateKey] = CAST(FORMAT([Orders].[OrderDate], 'yyyyMMdd') AS INT)
,[ProductKey] = [OrderDetails].[ProductID]
,[AcualOrderPrice] = [OrderDetails].[UnitPrice]
,[ActualOrderQuantity] = [OrderDetails].[Quantity]
From [NorthwindLite].[dbo].[Orders]
Join [NorthwindLite].[dbo].[OrderDetails]
On [NorthwindLite].[dbo].[Orders].[OrderID] = [NorthwindLite].[dbo].[OrderDetails].[OrderID]
Join [DWNorthwindLite].[dbo].[DimCustomers]
On [NorthwindLite].[dbo].[Orders].[CustomerID] = [DWNorthwindLite].[dbo].[DimCustomers].[CustomerID]
Go
--********************************************************************--
-- 3) Re-Create the FOREIGN KEY CONSTRAINTS
--********************************************************************--
ALTER TABLE [DWNorthwindLite].[dbo].[FactOrders] WITH CHECK ADD CONSTRAINT [FK_FactOrders_DimCustomers] FOREIGN KEY([CustomerKey])
REFERENCES [dbo].[DimCustomers] ([CustomerKey])
GO
ALTER TABLE [dbo].[FactOrders] CHECK CONSTRAINT [FK_FactOrders_DimCustomers]
GO
ALTER TABLE [DWNorthwindLite].[dbo].[FactOrders] WITH CHECK ADD CONSTRAINT [FK_FactOrders_DimDates] FOREIGN KEY([OrderDateKey])
REFERENCES [dbo].[DimDates] ([DateKey])
GO
ALTER TABLE [DWNorthwindLite].[dbo].[FactOrders] CHECK CONSTRAINT [FK_FactOrders_DimDates]
GO
ALTER TABLE [DWNorthwindLite].[dbo].[FactOrders] WITH CHECK ADD CONSTRAINT [FK_FactOrders_DimProducts] FOREIGN KEY([ProductKey])
REFERENCES [dbo].[DimProducts] ([ProductKey])
GO
ALTER TABLE [DWNorthwindLite].[dbo].[FactOrders] CHECK CONSTRAINT [FK_FactOrders_DimProducts]
GO
--********************************************************************--
-- Review the results of this script
--********************************************************************--
Select * from [dbo].[DimProducts]
Select * from [dbo].[DimCustomers]
Select * from [dbo].[DimDates]
Select * from [dbo].[FactOrders]
