SSMS 2- Loading Data to DW (FactTable)

Now that Dimension tables are loaded, we load the fact table.

Have a look at below SQL. Notice how all the tables are JOINED to get the dimension surrogate key.

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

-- Fill Fact Sales 
--  <Add ETL Code Here>
INSERT into DWAdventureWorksLT2012Lab01.dbo.FactSales
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

Once, Fact table is loaded, we will add the constraints which were dropped before the ETL process

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

Now, verify weather the tables are loaded or not

--********************************************************************--
-- 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];