SSMS 3 - Creating ETL Views

Views are created to provide abstraction to the data. This abstraction gives a level of security to the underlying data.

I do not think I need to explain this much. I just wanted to show you how to create Views and use that in ETL using Transact-SQL

/*************************************************************
*                                                            *
*   Copyright (C) Microsoft Corporation. All rights reserved.*
*                                                            *
*************************************************************/


 --****************** [ DWAdventureWorksLT2012Lab1 ETL Code ] *********************--
-- This file will flush and fill the sales data mart in the DWAdventureWorksLT2012Lab1 database
--***********************************************************************************************--
Use DWAdventureWorksLT2012Lab01;
go

--********************************************************************--
-- Create ETL Views
--********************************************************************--
If (object_id('vETLDimCustomersData') is not null) Drop View vETLDimCustomersData;
go
CREATE VIEW vETLDimCustomersData
--< Add you ETL Select Satement here!  >-- 
AS 
 SELECT [CustomerID]
   ,[CompanyName]
   ,[ContactFullName] = Customer.FirstName + ' ' + Customer.LastName
   from AdventureWorksLT2012.SalesLT.Customer
go

If (object_id('vETLDimProductsData') is not null) Drop View vETLDimProductsData;
go
CREATE VIEW vETLDimProductsData
AS
--< Add you ETL Select Satement here!  >-- 
select [ProductID]
      ,[ProductName]  = Product.Name
      ,[ProductColor] = IIF(Product.Color is not null, Product.Color, '')
      ,[ProductListPrice] = Product.ListPrice
      ,[ProductSize] = IIF(Product.Size is not null, Product.Size, '0')
      ,[ProductWeight] = Product.Weight
      ,[ProductCategoryID] = Product.ProductCategoryID
      ,[ProductCategoryName] = ProductCategory.Name
   from AdventureWorksLT2012.SalesLT.Product
   JOIN AdventureWorksLT2012.SalesLT.ProductCategory
 ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
go

If (object_id('vETLFactSalesData') is not null) Drop View vETLFactSalesData;
go
CREATE VIEW vETLFactSalesData
AS
--< Add you ETL Select Satement here!  >-- 
select 
   [SalesOrderID] = T1.SalesOrderID
      ,[SalesOrderDetailID] = T2.SalesOrderDetailID
      ,[CustomerKey]
      ,[ProductKey]
      ,[OrderDateKey] = T5.CalendarDateKey
      ,[ShipDateKey] = T6.CalendarDateKey
      ,[OrderQty]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
 from [AdventureWorksLT2012].[SalesLT].[SalesOrderHeader] T1
 JOIN [AdventureWorksLT2012].[SalesLT].[SalesOrderDetail] T2
 on T1.SalesOrderID = T2.SalesOrderID
 JOIN DWAdventureWorksLT2012Lab01.dbo.DimCustomers T3
 on T3.CustomerID = T1.CustomerID
 JOIN DWAdventureWorksLT2012Lab01.dbo.DimProducts T4
 on T4.ProductID = T2.ProductID
 JOIN DWAdventureWorksLT2012Lab01.dbo.DimDates T5
 on T5.CalendarDate = cast(T1.OrderDate as date)
 JOIN DWAdventureWorksLT2012Lab01.dbo.DimDates T6
 on T6.CalendarDate = cast(T1.ShipDate as date)
go


 
--********************************************************************--
-- Drop Foreign Key Constraints
--********************************************************************--

ALTER TABLE dbo.FactSales DROP CONSTRAINT
 fkFactSalesToDimProducts;

ALTER TABLE dbo.FactSales DROP CONSTRAINT 
 fkFactSalesToDimCustomers;

ALTER TABLE dbo.FactSales DROP CONSTRAINT
 fkFactSalesOrderDateToDimDates;

ALTER TABLE dbo.FactSales DROP CONSTRAINT
 fkFactSalesShipDateDimDates;   

--********************************************************************--
-- Clear Table Data
--********************************************************************--

TRUNCATE TABLE dbo.FactSales;
TRUNCATE TABLE dbo.DimCustomers;
TRUNCATE TABLE dbo.DimProducts; 
  

--********************************************************************--
-- Fill Dimension Tables
--********************************************************************--

-- DimCustomers
INSERT INTO [DWAdventureWorksLT2012Lab01].[dbo].[DimCustomers]
( [CustomerID]
, [CompanyName]
, [ContactFullName]
)
SELECT 
   [CustomerID]
 , [CompanyName]
 , [ContactFullName]
FROM  [DWAdventureWorksLT2012Lab01].[dbo].[vETLDimCustomersData]
go

-- DimProducts
INSERT INTO [DWAdventureWorksLT2012Lab01].[dbo].[DimProducts]
( [ProductID]
, [ProductName]
, [ProductColor]
, [ProductListPrice]
, [ProductSize]
, [ProductWeight]
, [ProductCategoryID]
, [ProductCategoryName]
)
SELECT 
   [ProductID]
 , [ProductName]
 , [ProductColor]
 , [ProductListPrice]
 , [ProductSize]
 , [ProductWeight]
 , [ProductCategoryID]
 , [ProductCategoryName]
FROM  [DWAdventureWorksLT2012Lab01].[dbo].[vETLDimProductsData]
go

--********************************************************************--
-- Fill Fact Tables
--********************************************************************--

-- Fill Fact Sales 
INSERT INTO [DWAdventureWorksLT2012Lab01].[dbo].[FactSales]
( [SalesOrderID]
, [SalesOrderDetailID]
, [CustomerKey]
, [ProductKey]
, [OrderDateKey]
, [ShipDateKey]
, [OrderQty]
, [UnitPrice]
, [UnitPriceDiscount]
)
SELECT 
   [SalesOrderID]  
 , [SalesOrderDetailID] 
 , [CustomerKey]
 , [ProductKey]
 , [OrderDateKey]
 , [ShipDateKey]
 , [OrderQty]
 , [UnitPrice]
 , [UnitPriceDiscount]
FROM [DWAdventureWorksLT2012Lab01].[dbo].[vETLFactSalesData] 
go

--********************************************************************--
-- Replace Foreign Key Constraints
--********************************************************************--
ALTER TABLE dbo.FactSales ADD CONSTRAINT
 fkFactSalesToDimProducts FOREIGN KEY (ProductKey) 
 REFERENCES dbo.DimProducts (ProductKey);

ALTER TABLE dbo.FactSales ADD CONSTRAINT 
 fkFactSalesToDimCustomers FOREIGN KEY (CustomerKey) 
 REFERENCES dbo.DimCustomers (CustomerKey);
 
ALTER TABLE dbo.FactSales ADD CONSTRAINT
 fkFactSalesOrderDateToDimDates FOREIGN KEY (OrderDateKey) 
 REFERENCES dbo.DimDates(CalendarDateKey);

ALTER TABLE dbo.FactSales ADD CONSTRAINT
 fkFactSalesShipDateDimDates FOREIGN KEY (ShipDateKey)
 REFERENCES dbo.DimDates (CalendarDateKey);
 
 
--********************************************************************--
-- Verify that the tables are filled
--********************************************************************--
-- Dimension Tables
SELECT * FROM [DWAdventureWorksLT2012Lab01].[dbo].[DimCustomers]; 
SELECT * FROM [DWAdventureWorksLT2012Lab01].[dbo].[DimProducts]; 
SELECT * FROM [DWAdventureWorksLT2012Lab01].[dbo].[DimDates]; 

-- Fact Tables 
SELECT * FROM [DWAdventureWorksLT2012Lab01].[dbo].[FactSales];