SQL
I need to create an ETL Script. This script will then be used to fill a data warehouse.
Create an ETL Script that extracts data from NorthwindLite, performs the transformations you documented, and loads the data into the DWNorthwindLite database.
4 Source Type Database Destination Object Name DWNorthwindLite Transformations for ETL Processing Example Data Database Data Warehouse Dimension Key Column 8 nchars) 9 NA 10 datetine NA DWNorthwindLite.dbo.FactOrders.CustomerK int Dimension Key Column NA Dlmension Key Column Fact Table Dirnerision Key Columr NA NA OWNorthwindlite.dbo.FactOrders.OrderDatckey e.dbo FactOrders Table DWNorthwirdLite.dbo.FactOrders.OrdD Merged) 14 int 15 NA 15 DWNorthwindLite.dbo FactUrders.Productke DWNothwindLite.dbu.actOr der s.ActualOr del Unit Price DWNorthwindl itr.dho.Factorders int Dimension Key Column Measure Colurnn Measure folumn 9.99 18 ProductCete oryID-Products CeteporyD 19 int DWNorthwindLite.dbo.DitmProducts.ProductCate Products.C nvarcha(15) DWNorthwindlite.dho.DimProducts.PraductCategoryNarne nVarchar100) Dimension Cclurnn uctCategpryNarne] CASTCatrgories.CategpryNane as nVarchar(1 Auto Generated with Identit ProductIDI Products.Product D [ProductNamc] CAST(Products.ProductNamc as nVarchar 100) DWNorthwindLite.dbo.DimProducts.Product DWNorthwindLite.dbo.DimProducts PraductID DWNorthwindLitc.dbo.DimProducts.ProductName DWNorthwindLite.dbo LimProducts Productcat DWNorLhwindLite.dbo.DiniProducts.StartDale DWNorthwindl itr.dho.imProducts.Fndatr DWNorthwindLite.dbo LimProducts.IsCurrent DWNorthwindLite.dbo.DitnCusteners DWNorthwindl ite.d DWNorthwindLite.dbo.DimCustomers CustomerlD DWNorthwindLite.dbo.DimCustomers.CustomerName Dimension Key Column Dimension Colurmn Dimension Column Dimension Column SCD Colurn SCD Column SCU Column 23 int 24 nvarchar Prod1 ID (merge int 25 NA 27 NA 28 NA SlarDate 19990101 Fnd Datel Null [isturrent-'Yes 19961231 1998123 Table s.CustomerKey Dimension Key Column DImension Column Dimension Column Dimension Calurnn Dimension Column Dimension Column Dinerision Calurnn Auta Generated with Identity 31 nchars 32 nvarchar 4 nVarchar CustomerName CASTIusmers.CustomerName as nVarcharii00)) NA Ifreds Futterkiste varchar) NA 4 nvarchar(60)NA 35 nvarchar 15) DWNort hwindLite.dbo.DimCus rivarch15) DWNorthwindLite.dbo.DinCustoners.Customer Country tomers customer nVarchar 100) nVarchar(100) ustomerCityl CAST Customers.Customercity as nVarcharic0 CustornerCountry)-CASTICustorner.CustomerCountry as nVarchar(100 Table Lite.dbo.DimCustomers Table Dimension Table Dimension Key Column Auto Generated with Identity Dimension Colurni Dimenslon Column Dimension Column Dimension Column Dimension Column Dimension Colurnin SCD Column SCD Column SCD Column DWNorthwindLite.dbo.DimCustomers.CustomerKey DWNorthwindLite.dbo.DinCustomers.CustomerID int 1 nchar(5) 2 nvarch40) DWNorthwindLite.dbo.DimCustomers.CustomerName 13 nvarcharl30) NA CustomerlDCustomers.CustomerlD CustomerName CAST Customers.CustomerName as nVarchar(100)) ALFKI freds Futterkiste nVarchar100) Dimension Columr NA NA NA Berlin Germarny 20141231 20141231 4 nvarchar(0) NA NA CustomerCity]-CAST(Customers.CustomerCity as nVarchar(100)) CustomerCountry] CAST(Custoner CustomerCountry as nVarchar(10Ol StartDate] 20150421 [EndDatel Null IsCurrent] Yes s narcharl5) DWNorthwindLite.dbo.DimCustomers.CustomerCity nVarchar 100) dh 7 NA 8 NA 9 NA 0 1 NA 2 NA 3 NA 4 NA S NA 6 NA 7 NA 8 NA 9 Source Type Object Name DWNorthwindLite.dbo.DimCustomers.StartDate DWNorthwindLite.dho.DimCustomers.EndDate DWNorthwindLite.dbo DimCustomers.IsCurrent int int char(3) Dimension Key Colurmin Dimension Column Dimension Column Dimension Column Dimenslon DWNorthwindlite.dho.DirriDates.DateKey DWNorthwindLite.dbo.DimDates.USADateName WNorthwindLite.dho.DimDates.MonthKey DWNorthwindLite.dbo.DimDates.MonthName DWNorthwindLite.dbo.DimDates.QuarterKey DWNorthwindlite.dbo.DirmDates.Quarter Name DWNorthwindLite.dbo.DimDates.YearKey DWNorthwindLite.dbo.DimDates.YearName nVarcharl100) int nVarcharl1 Int nVarchar int nVarchar100) Destination Type Description 20141231 Monday, 12/31/2014 201412 December, 2014 201401 Quarter 1, 2014 2014 NA Column Colurnin Dimension Column Limension Column Transformations and ETL functions Example Data Create the Data Narehouse [DUNorthwindLite] -This file contains ETL code for use with the [DwNorthwindLite] database Your Version Go 1) Drop the FOREIGN KEY CONSTRAINTS and Clear the tables Alter Table [DWNorthwindLite].dbo. Factorders The Child Table Alter Table [DWNorthwindLite].dbo. Factorders The Child Table Drop Constraint [FK_FactOrders DinProducts]; Drop Constraint [FK_FactOrders DimDates] Truncate Table [DlorthwindLite].dbo.DimProducts;The Parent Table Truncate Table [DwlorthwindLite].dbo.DimDates;The Parent Table 2) FILL the Tables INSERT NTO [DWNorthwind Lite].dbo.DimProducts SELECT [ProductID] Products. ProductID [ProductName] CAST (Products.ProductName as nVarchar (100)) [ProductCategoryID] Products.CategoryID [ProductCategoryName] CAST (Categories.Categoryame as nVarchar (180)) [StartDate] 20000181 [EndDate] Null [IsCurrent] Yes FROM [NorthwindLite].dbo.Categories INNER JON [NorthwindLite].dbo.Products ON Categories CategoryID - Products.CategoryID Go -Create variables to hold the start and end date Declare @StartDate datetie1/81/199e Declare EndDate datetime 12/31/1999 Use a while loop to add dates to the table Declare @DateInProcess datetime Set @DateInProcess StartDate while OateInProcess-EndDate Begin Go -- Select From DimDates Go Go [dbo]. [FactOrders] ** Go 3) Re-create the FOREIGN KEY CONSTRAINTS ALTER TABLE DANorthwindLite.dbo. Factorders .. The Many Child Table ADD CONSTRAINT FK FactOrders DimProduct:s FOREIGN KEY (Productkey) REFERENCES DimProducts (Productkey); - - The Single Parent Table ALTER TABLE DWNorthwindLite. dbo. Factorders ADD CONSTRAINT FK FactOrders DimDates FOREIGN KEY (OrderDatekey) REFERENCES DnDates (Dat eKey) -Review the results of this script Select from [dbo]. [DimProducts] Selectfrom [dbo].[DimCustomers] Selectfrom [dbo]. [DimDates]; Select from [dbo] [Factrders] 4 Source Type Database Destination Object Name DWNorthwindLite Transformations for ETL Processing Example Data Database Data Warehouse Dimension Key Column 8 nchars) 9 NA 10 datetine NA DWNorthwindLite.dbo.FactOrders.CustomerK int Dimension Key Column NA Dlmension Key Column Fact Table Dirnerision Key Columr NA NA OWNorthwindlite.dbo.FactOrders.OrderDatckey e.dbo FactOrders Table DWNorthwirdLite.dbo.FactOrders.OrdD Merged) 14 int 15 NA 15 DWNorthwindLite.dbo FactUrders.Productke DWNothwindLite.dbu.actOr der s.ActualOr del Unit Price DWNorthwindl itr.dho.Factorders int Dimension Key Column Measure Colurnn Measure folumn 9.99 18 ProductCete oryID-Products CeteporyD 19 int DWNorthwindLite.dbo.DitmProducts.ProductCate Products.C nvarcha(15) DWNorthwindlite.dho.DimProducts.PraductCategoryNarne nVarchar100) Dimension Cclurnn uctCategpryNarne] CASTCatrgories.CategpryNane as nVarchar(1 Auto Generated with Identit ProductIDI Products.Product D [ProductNamc] CAST(Products.ProductNamc as nVarchar 100) DWNorthwindLite.dbo.DimProducts.Product DWNorthwindLite.dbo.DimProducts PraductID DWNorthwindLitc.dbo.DimProducts.ProductName DWNorthwindLite.dbo LimProducts Productcat DWNorLhwindLite.dbo.DiniProducts.StartDale DWNorthwindl itr.dho.imProducts.Fndatr DWNorthwindLite.dbo LimProducts.IsCurrent DWNorthwindLite.dbo.DitnCusteners DWNorthwindl ite.d DWNorthwindLite.dbo.DimCustomers CustomerlD DWNorthwindLite.dbo.DimCustomers.CustomerName Dimension Key Column Dimension Colurmn Dimension Column Dimension Column SCD Colurn SCD Column SCU Column 23 int 24 nvarchar Prod1 ID (merge int 25 NA 27 NA 28 NA SlarDate 19990101 Fnd Datel Null [isturrent-'Yes 19961231 1998123 Table s.CustomerKey Dimension Key Column DImension Column Dimension Column Dimension Calurnn Dimension Column Dimension Column Dinerision Calurnn Auta Generated with Identity 31 nchars 32 nvarchar 4 nVarchar CustomerName CASTIusmers.CustomerName as nVarcharii00)) NA Ifreds Futterkiste varchar) NA 4 nvarchar(60)NA 35 nvarchar 15) DWNort hwindLite.dbo.DimCus rivarch15) DWNorthwindLite.dbo.DinCustoners.Customer Country tomers customer nVarchar 100) nVarchar(100) ustomerCityl CAST Customers.Customercity as nVarcharic0 CustornerCountry)-CASTICustorner.CustomerCountry as nVarchar(100 Table Lite.dbo.DimCustomers Table Dimension Table Dimension Key Column Auto Generated with Identity Dimension Colurni Dimenslon Column Dimension Column Dimension Column Dimension Column Dimension Colurnin SCD Column SCD Column SCD Column DWNorthwindLite.dbo.DimCustomers.CustomerKey DWNorthwindLite.dbo.DinCustomers.CustomerID int 1 nchar(5) 2 nvarch40) DWNorthwindLite.dbo.DimCustomers.CustomerName 13 nvarcharl30) NA CustomerlDCustomers.CustomerlD CustomerName CAST Customers.CustomerName as nVarchar(100)) ALFKI freds Futterkiste nVarchar100) Dimension Columr NA NA NA Berlin Germarny 20141231 20141231 4 nvarchar(0) NA NA CustomerCity]-CAST(Customers.CustomerCity as nVarchar(100)) CustomerCountry] CAST(Custoner CustomerCountry as nVarchar(10Ol StartDate] 20150421 [EndDatel Null IsCurrent] Yes s narcharl5) DWNorthwindLite.dbo.DimCustomers.CustomerCity nVarchar 100) dh 7 NA 8 NA 9 NA 0 1 NA 2 NA 3 NA 4 NA S NA 6 NA 7 NA 8 NA 9 Source Type Object Name DWNorthwindLite.dbo.DimCustomers.StartDate DWNorthwindLite.dho.DimCustomers.EndDate DWNorthwindLite.dbo DimCustomers.IsCurrent int int char(3) Dimension Key Colurmin Dimension Column Dimension Column Dimension Column Dimenslon DWNorthwindlite.dho.DirriDates.DateKey DWNorthwindLite.dbo.DimDates.USADateName WNorthwindLite.dho.DimDates.MonthKey DWNorthwindLite.dbo.DimDates.MonthName DWNorthwindLite.dbo.DimDates.QuarterKey DWNorthwindlite.dbo.DirmDates.Quarter Name DWNorthwindLite.dbo.DimDates.YearKey DWNorthwindLite.dbo.DimDates.YearName nVarcharl100) int nVarcharl1 Int nVarchar int nVarchar100) Destination Type Description 20141231 Monday, 12/31/2014 201412 December, 2014 201401 Quarter 1, 2014 2014 NA Column Colurnin Dimension Column Limension Column Transformations and ETL functions Example Data Create the Data Narehouse [DUNorthwindLite] -This file contains ETL code for use with the [DwNorthwindLite] database Your Version Go 1) Drop the FOREIGN KEY CONSTRAINTS and Clear the tables Alter Table [DWNorthwindLite].dbo. Factorders The Child Table Alter Table [DWNorthwindLite].dbo. Factorders The Child Table Drop Constraint [FK_FactOrders DinProducts]; Drop Constraint [FK_FactOrders DimDates] Truncate Table [DlorthwindLite].dbo.DimProducts;The Parent Table Truncate Table [DwlorthwindLite].dbo.DimDates;The Parent Table 2) FILL the Tables INSERT NTO [DWNorthwind Lite].dbo.DimProducts SELECT [ProductID] Products. ProductID [ProductName] CAST (Products.ProductName as nVarchar (100)) [ProductCategoryID] Products.CategoryID [ProductCategoryName] CAST (Categories.Categoryame as nVarchar (180)) [StartDate] 20000181 [EndDate] Null [IsCurrent] Yes FROM [NorthwindLite].dbo.Categories INNER JON [NorthwindLite].dbo.Products ON Categories CategoryID - Products.CategoryID Go -Create variables to hold the start and end date Declare @StartDate datetie1/81/199e Declare EndDate datetime 12/31/1999 Use a while loop to add dates to the table Declare @DateInProcess datetime Set @DateInProcess StartDate while OateInProcess-EndDate Begin Go -- Select From DimDates Go Go [dbo]. [FactOrders] ** Go 3) Re-create the FOREIGN KEY CONSTRAINTS ALTER TABLE DANorthwindLite.dbo. Factorders .. The Many Child Table ADD CONSTRAINT FK FactOrders DimProduct:s FOREIGN KEY (Productkey) REFERENCES DimProducts (Productkey); - - The Single Parent Table ALTER TABLE DWNorthwindLite. dbo. Factorders ADD CONSTRAINT FK FactOrders DimDates FOREIGN KEY (OrderDatekey) REFERENCES DnDates (Dat eKey) -Review the results of this script Select from [dbo]. [DimProducts] Selectfrom [dbo].[DimCustomers] Selectfrom [dbo]. [DimDates]; Select from [dbo] [Factrders]
