Northwind Lite Data Warehouse ETL Script

This is the ETL Script supporting the Northwind Lite Database created with the previous setup script, through the below steps:

1. Drop Table Constraints

2. Reset the Table Data

3. Insert the data from the Northwind Lite database in accordance with the defined BI Solutions Data Warehouse Objects.

4. Re-attach the Table Contraints

5. Verify resulting data tables

The below table identifies the Data Warehouse Objects mapped to the source and ETL functions for the translation.

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

 Create the Data Warehouse using previous setup script

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

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

-- This file contains ETL code for use with the

-- [DWNorthwindLite] database.

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

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

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

USE [DWNorthwindLite]

ALTER TABLE dbo.FactOrders

       DROP CONSTRAINT FK_FactOrders_DimProducts

GO

ALTER TABLE dbo.FactOrders

       DROP CONSTRAINT FK_FactOrders_DimDates

GO

ALTER TABLE dbo.FactOrders

       DROP CONSTRAINT FK_FactOrders_DimCustomers

GO

ALTER TABLE dbo.FactOrders SET (LOCK_ESCALATION = TABLE)

GO

Truncate Table [DWNorthwindLite].dbo.FactOrders;

Truncate Table [DWNorthwindLite].dbo.DimProducts;

Truncate Table [DWNorthwindLite].dbo.DimCustomers;

Truncate Table [DWNorthwindLite].dbo.DimDates;

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

-- 2) FILL the Tables

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

/****** [dbo].[DimProducts] ******/

-- INSERT INTO [DWNorthwindLite].dbo.DimProducts

/* Columns

ProductKey = Auto Generated

 [ProductID] = [NorthwindLite].dbo.Products.ProductID

,[ProductName] = CAST([NorthwindLite].dbo.Products.ProductName as nVarchar(100))

,[ProductCategoryID] = [NorthwindLite].dbo.Products.CategoryID

,[ProductCategoryName] = CAST([NorthwindLite].dbo.Categories.CategoryName as nVarchar(100))

, StartDate = 20000101

, EndDate = Null

, IsCurrent = 'Yes'

*/

INSERT INTO DimProducts

SELECT

 [ProductID] = [NorthwindLite].dbo.Products.ProductID

,[ProductName] = CAST([NorthwindLite].dbo.Products.ProductName as nVarchar(100))

,[ProductCategoryID] = [NorthwindLite].dbo.Products.CategoryID

,[ProductCategoryName] = CAST([NorthwindLite].dbo.Categories.CategoryName as nVarchar(100))

,[StartDate] = 20000101

,[EndDate] = Null

,[IsCurrent] = 'Yes'

FROM [NorthwindLite].dbo.Categories

INNER JOIN [NorthwindLite].dbo.Products

ON [NorthwindLite].dbo.Categories.CategoryID = [NorthwindLite].dbo.Products.CategoryID;

Go

/****** [dbo].[DimCustomers] ******/

/*

Columns

CustomerKey

, CustomerID

, CustomerName = CAST([NorthwindLite].[dbo].[Customers].[CompanyName] as nVarchar(100))

, CustomerCity = CAST([NorthwindLite].[dbo].[Customers].[City] as nVarchar(100))

, CustomerCountry = CAST([NorthwindLite].[dbo].[Customers].[Country] as nVarchar(100))

, StartDate = 20000101

, EndDate = Null

, IsCurrent = 'Yes'

*/

INSERT INTO DimCustomers

SELECT [CustomerID] = [NorthwindLite].[dbo].[Customers].[CustomerID]

       ,[CustomerName] = CAST([NorthwindLite].[dbo].[Customers].[CompanyName] as nVarchar(100))

       ,[CustomerCity] = CAST([NorthwindLite].[dbo].[Customers].[City] as nVarchar(100))

       ,[CustomerCountry] = CAST([NorthwindLite].[dbo].[Customers].[Country] as nVarchar(100))

       ,[StartDate] = 20000101

       ,[EndDate] = Null

       ,[IsCurrent] = 'Yes'

  FROM [NorthwindLite].[dbo].[Customers];

Go

/****** [dbo].[DimDates] ******/

/* Columns

DateKey

, USADateName

, MonthKey

, MonthName

, QuarterKey

, QuarterName

, YearKey

, YearName

*/

DECLARE @DateKey Date

DECLARE @EndDate Date

SET @DateKey = '1/1/1950'

SET @EndDate = '12/31/2050'

WHILE (@DateKey <= @EndDate)

BEGIN

INSERT INTO DimDates

    SELECT [DateKey] = Cast( Convert(nVarchar(100), @DateKey, 112) as int)

       , [USADateName] = Convert(nVarchar(100), @DateKey, 110)

       , [MonthKey] = Cast( Convert(nVarchar(100), DatePart(MONTH,@DateKey)) as int)

       , [MonthName] = Convert(nVarchar(100), DateName(MONTH,@DateKey), 110)

       , [QuarterKey] = Cast( Convert(nVarchar(100), DatePart(QUARTER,@DateKey), 110) as int)

       , [QuarterName] = Convert(nVarchar(100), DateName(QUARTER,@DateKey), 110)

       , [YearKey] = Cast( Convert(nVarchar(100), DatePart(Year,@DateKey), 110) as int)

       , [YearName] = Convert(nVarchar(100), DateName(Year,@DateKey), 110)

       SET @DateKey = DATEADD(DAY,1, @DateKey)

END

/****** [dbo].[FactOrders] ******/

/* Columns

OrderID

, CustomerKey

, OrderDateKey

, ProductKey

, ActualOrderUnitPrice

, ActualOrderQuantity

*/

INSERT INTO DWNorthwindLite.dbo.FactOrders

SELECT Orders.OrderID

       , DimCustomers.CustomerKey

       , OrderDateKey = Cast( Convert(nVarchar(50), [NorthwindLite].[dbo].[Orders].[OrderDate], 112) as int)

       , DimProducts.ProductKey

       , [AcutalOrderUnitPrice] = OrderDetails.UnitPrice

       , [ActualOrderQuanity] = Cast(OrderDetails.Quantity as smallint)

FROM            [NorthwindLite].[dbo].[Orders]

INNER JOIN

                [NorthwindLite].[dbo].[OrderDetails]

                           ON Orders.OrderID = OrderDetails.OrderID

INNER JOIN

                           [DimCustomers]

                           ON Orders.CustomerID = DimCustomers.CustomerID

INNER JOIN

                           [DimProducts]

                           ON OrderDetails.ProductID = DWNorthwindLite.dbo.DimProducts.ProductID

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

-- 3) Re-Create the FOREIGN KEY CONSTRAINTS

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

--BEGIN

ALTER TABLE dbo.FactOrders ADD CONSTRAINT

       FK_FactOrders_DimCustomers FOREIGN KEY

       (

       CustomerKey

       ) REFERENCES dbo.DimCustomers

       (

       CustomerKey

       ) ON UPDATE  NO ACTION

        ON DELETE  NO ACTION

      

GO

ALTER TABLE dbo.FactOrders ADD CONSTRAINT

       FK_FactOrders_DimDates FOREIGN KEY

       (

       OrderDateKey

       ) REFERENCES dbo.DimDates

       (

       DateKey

       ) ON UPDATE  NO ACTION

        ON DELETE  NO ACTION

      

GO

ALTER TABLE dbo.FactOrders ADD CONSTRAINT

       FK_FactOrders_DimProducts FOREIGN KEY

       (

       ProductKey

       ) REFERENCES dbo.DimProducts

       (

       ProductKey

       ) ON UPDATE  NO ACTION

        ON DELETE  NO ACTION

      

GO

ALTER TABLE dbo.FactOrders SET (LOCK_ESCALATION = TABLE)

GO

--COMMIT

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

-- Review the results of this script

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

Select * from [dbo].[DimProducts]

Select * from [dbo].[DimCustomers]

Select * from [dbo].[DimDates]

Select * from [dbo].[FactOrders]