BI: Building the Data Warehouse Lesson 5

Writing the ETL SQL Script
Korry Kelley | Lesson 5

 

 

The image above illustrates the steps I took to plan the ETL solution

I used the SQL script below to implement the ETL solution:

/**************************************************************

 Create the Data Warehouse

*************************************************************/

 

--****************** [DWNorthwindLite] *********************--

-- This file contains ETL code for use with the

-- [DWNorthwindLite] database.

--****************** KorryK Version ***************************--

 

USE [DWNorthwindLite]

Go

 

--********************************************************************--

-- 1) Drop the FOREIGN KEY CONSTRAINTS and Clear the tables

--********************************************************************--

Alter Table [DWNorthwindLite].dbo.FactOrders Drop Constraint [FK_FactOrders_DimProducts]

Go

Alter Table [DWNorthwindLite].dbo.FactOrders Drop Constraint [FK_FactOrders_DimDates]

Go

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/1999'

Declare @EndDate Datetime ='01/01/2004'

 

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))

 ,YEAR(@DateInProcess)+''+CAST(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 [dbo].[FactOrders]  WITH CHECK ADD  CONSTRAINT [FK_FactOrders_DimDates] FOREIGN KEY([OrderDateKey])

REFERENCES [dbo].[DimDates] ([DateKey])

GO

ALTER TABLE [dbo].[FactOrders] CHECK CONSTRAINT [FK_FactOrders_DimDates]

GO

 

ALTER TABLE [dbo].[FactOrders]  WITH CHECK ADD  CONSTRAINT [FK_FactOrders_DimProducts] FOREIGN KEY([ProductKey])

REFERENCES [dbo].[DimProducts] ([ProductKey])

GO

ALTER TABLE [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]