Homwork 05 - Creating A ETL Processing Script

In this homework, I created a SQL ETL processing script to take data from the NorthwindLite database, transform the data, and then load it into the DWNorthwindLite database.

First I completed the list of data transformations for each column and then placed the SQL transformation code in the Instructor's spreadsheet:

After, I used the starter ETL script to help test the SQL transformation code:

Finally once I completed the Starter script, I put it all together in a clean single ETL script that wipes the DWNorthwindLite database and then fills it back up from the NorthwindLite database:

Homework Session 04

For homework 4 I created a data warehouse based on the “NorthwindLite” database. Using the Project Manager’s Object Worksheet, I was able to construct the data warehouse with the specified data types that were requested. I created the database in SSMS designer and then used the generate scripts feature to generate the necessary SQL code to save as a script file.

Below is the diagram for the DWNorthwindLite database:

Below is the PM's worksheet, I changed the datatype for DimProducts.ProductCategoryName to nvarchar(100) as the example data did not match the proposed type of int.

Homework Session 3

For this homework I created documentation on how I would name a DataWarehouse based on the NorthwindLite Database. I have changed some of the data types in my Datawarehouse design to better represent the data that is being stored.

Below is the source table names and data types provided from the NorthwindLite DB:

 

Source	Source Type
NorthwindLite	Database
NorthwindLite.dbo.Orders	Table
NorthwindLite.dbo.Orders.OrderID	int
NorthwindLite.dbo.Orders.CustomerID	nchar(5)
NorthwindLite.dbo.Orders.OrderDate	datetime
NorthwindLite.dbo.OrderDetails	Table
NorthwindLite.dbo.OrderDetails.OrderID	int
NorthwindLite.dbo.OrderDetails.ProductID	int
NorthwindLite.dbo.OrderDetails.UnitPrice	money
NorthwindLite.dbo.OrderDetails.Quantity	smallint
NorthwindLite.dbo.Categories	Table
Generated	na
NorthwindLite.dbo.Categories.CategoryID	int
NorthwindLite.dbo.Categories.CategoryName	nvarchar(15)
NorthwindLite.dbo.Products	Table
Generated	na
NorthwindLite.dbo.Products.ProductID	int
NorthwindLite.dbo.Products.ProductName	nvarchar(40)
NorthwindLite.dbo.Products.CategoryID	int
NorthwindLite.dbo.Customers	Table
Generated	na
NorthwindLite.dbo.Customers.CustomerID	nchar(5)
NorthwindLite.dbo.Customers.CompanyName	nvarchar(40)
NorthwindLite.dbo.Customers.ContactName	nvarchar(30)
NorthwindLite.dbo.Customers.Address	nvarchar(60)
NorthwindLite.dbo.Customers.City	nvarchar(15)
NorthwindLite.dbo.Customers.Country	nvarchar(15)

Below, you can see that I changed all of the nvarchar data types to a single type of nvarchar(40). This was done to simplify the amount of different data types in the datawarehouse. As the type is nvarchar, the database will reduce the amount of space used to store the data depending on the length. I have also changed the OrderDate from “datetime” to “date” as the source database was not actually recording any time other than midnight. Doing this will save a significant amount of space in the datawarehouse. I am also storing the Generated columns in the dimension tables to a Key column as a data type of int.

Initial datawarehouse design for the NorthwindLite database:

Object Name	Description	Destination Type
DWNorthwindLite	Data Warehouse	Database
DWNorthwindLite.dbo.FactOrders	Fact Table	Table
DWNorthwindLite.dbo.FactOrders.OrderID	Dimension Key Column	int
DWNorthwindLite.dbo.FactOrders.CustomerID	Dimension Key Column	nchar(5)
DWNorthwindLite.dbo.FactOrders.OrderDate	Dimension Key Column	date
DWNorthwindLite.dbo.FactOrderDetails	Fact Table	Table
DWNorthwindLite.dbo.FactOrderDetails.OrderID	Dimension Key Column	int
DWNorthwindLite.dbo.FactOrderDetails.ProductID	Dimension Key Column	int
DWNorthwindLite.dbo.FactOrderDetails.UnitPrice	Measure Column	money
DWNorthwindLite.dbo.FactOrderDetails.Quantity	Measure Column	smallint
DWNorthwindLite.dbo.DimCategories	Dimension Table	Table
DWNorthwindLite.dbo.DimCategories.CategoryKey	Dimension Key Column	int
DWNorthwindLite.dbo.DimCategories.CategoryID	Dimension  Column	int
DWNorthwindLite.dbo.DimCategories.CategoryName	Dimension Column	nvarchar(40)
DWNorthwindLite.dbo.DimProducts	Dimension Table	Table
DWNorthwindLite.dbo.DimProducts.ProductKey	Dimension Key Column	int
DWNorthwindLite.dbo.DimProducts.ProductID	Dimension  Column	int
DWNorthwindLite.dbo.DimProducts.ProductName	Dimension Column	nvarchar(40)
DWNorthwindLite.dbo.DimProducts.CategoryID	Dimension Column	int
DWNorthwindLite.dbo.DimCustomers	Dimension Table	Table
DWNorthwindLite.dbo.DimCustomers.CustomerKey	Dimension Key Column	int
DWNorthwindLite.dbo.DimCustomers.CustomerID	Dimension Column	nchar(5)
DWNorthwindLite.dbo.DimCustomers.CompanyName	Dimension Column	nvarchar(40)
Not Used	Dimension Column	na
Not Used	Dimension Column	na
DWNorthwindLite.dbo.DimCustomers.City	Dimension Column	nvarchar(40)
DWNorthwindLite.dbo.DimCustomers.Country	Dimension Column	nvarchar(40)