--© 2018 | ByrdNest Consulting --now load ProductDim (Initial data load) USE AdventureWorksDW2012lite GO INSERT INTO dbo.ProductDim (ProductAlternateKey,ProductSubcategoryKey,WeightUnitMeasureCode,SizeUnitMeasureCode,EnglishProductName ,SpainishProductName,FrenchProductName,StandardCost,FinishedGoodsFlag,Color,SafetyStockLevel,ReorderPoint ,ListPrice,Size,SizeRange,[Weight],DaysToManufacture,ProductLine,DealerPrice,Class,Style,ModelName ,LargePhoto,EnglishDescription,FrenchDescription,ChineseDescription,ArabicDescription,HebrewDescription ,ThaiDescription,SpainishDescription,JapaneseDescription,TurkishDescription,StartDate,EndDate,[Status] ,SrcProductID, SrcProductSubcategoryID) SELECT ProductNumber ProductAlternateKey, ISNULL(scd.ProductSubcategoryKey,-1) ProductSubcategoryKey, p.WeightUnitMeasureCode, p.SizeUnitMeasureCode, p.Name EnglishProductName, '' SpanishProductName, '' FrenchProductName, p.StandardCost, p.FinishedGoodsFlag, ISNULL(p.Color,''), p.SafetyStockLevel, p.ReorderPoint, p.ListPrice, p.Size, NULL SizeRange, p.[Weight], p.DaysToManufacture, p.ProductLine, NULL DealerPrice, p.Class, p.Style, pm.Name ModelName, pp.LargePhoto, sub.EnglishDescription, sub.FrenchDescription, sub.ChineseDescription, sub.ArabicDescription, sub.HebrewDescription, sub.ThaiDescription, sub.SpainishDescription, NULL JapaneseDescription, NULl TurkishDescription, p.SellStartDate StartDate, p.SellEndDate EndDate, NULL [Status], p.ProductID SrcProductID, p.ProductSubcategoryID FROM AdventureWorks2012.Production.Product p JOIN AdventureWorks2012.Production.ProductModel pm ON pm.ProductModelID = p.ProductModelID LEFT JOIN (SELECT t.ProductModelID, MAX(CASE WHEN t.CultureID = 'en' THEN t.ProductDescription ELSE NULL END) EnglishDescription, MAX(CASE WHEN t.CultureID = 'fr' THEN t.ProductDescription ELSE NULL END) FrenchDescription, MAX(CASE WHEN t.CultureID = 'zh-cht' THEN t.ProductDescription ELSE NULL END) ChineseDescription, MAX(CASE WHEN t.CultureID = 'ar' THEN t.ProductDescription ELSE NULL END) ArabicDescription, MAX(CASE WHEN t.CultureID = 'he' THEN t.ProductDescription ELSE NULL END) HebrewDescription, MAX(CASE WHEN t.CultureID = 'th' THEN t.ProductDescription ELSE NULL END) ThaiDescription, MAX(CASE WHEN t.CultureID = 'es' THEN t.ProductDescription ELSE NULL END) SpainishDescription FROM (select pmpdc.ProductModelID,pmpdc.CultureID,pd.[Description] ProductDescription FROM AdventureWorks2012.Production.ProductModelProductDescriptionCulture pmpdc JOIN AdventureWorks2012.Production.ProductDescription pd ON pd.ProductDescriptionID = pmpdc.ProductDescriptionID) t GROUP BY t.ProductModelID) sub ON sub.ProductModelID = pm.ProductModelID LEFT JOIN AdventureWorks2012.Production.ProductProductPhoto ppp ON ppp.ProductID = p.ProductID LEFT JOIN AdventureWorks2012.Production.ProductPhoto pp ON pp.ProductPhotoID = ppp.ProductPhotoID LEFT JOIN AdventureWorks2012.[Production].[ProductSubcategory] psc ON psc.ProductSubcategoryID = p.ProductSubcategoryID LEFT JOIN [dbo].[ProductSubCategoryDim] scd ON scd.EnglishProductSubcategoryName = psc.Name GO --now load CustomerDim except for CustomerID = 11566 (25 internet sales orders) IF OBJECT_ID(N'tempdb..#Demographics') IS NOT NULL DROP TABLE #Demographics GO ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns) SELECT BusinessEntityID PersonID, C.value('ns:BirthDate[1]','Date') AS BirthDate, C.value('ns:MaritalStatus[1]','nchar(1)') AS MaritalStatus, C.value('ns:Gender[1]','nchar(1)') AS Gender, C.value('ns:YearlyIncome[1]','VARCHAR(50)') AS YearlyIncome, C.value('ns:TotalChildren[1]','tinyint') AS TotalChildren, C.value('ns:NumberChildrenAtHome[1]','tinyint') AS NumberChildrenAtHome, C.value('ns:Education[1]','nvarchar(40)') AS Education, C.value('ns:Occupation[1]','nvarchar(100)') AS EnglishOccupation, C.value('ns:HomeOwnerFlag[1]','nchar(1)') AS HomeOwnerFlag, C.value('ns:NumberCarsOwned[1]','tinyint') AS NumberCarsOwned, C.value('ns:DateFirstPurchase[1]','date') AS DateFirstPurchase, C.value('ns:CommuteDistance[1]','varchar(50)') AS CommuteDistance INTO #Demographics FROM AdventureWorks2012.Person.Person CROSS APPLY Demographics.nodes('/ns:IndividualSurvey') AS T(C) INSERT INTO dbo.CustomerDim (GeographyKey,CustomerAlternateKey,Title,FirstName,MiddleName ,LastName,NameStyle,BirthDate,MaritalStatus,Suffix,Gender,EmailAddress ,YearlyIncome,TotalChildren,NumberChildrenAtHome,EnglishEducation ,SpanishEducation,FrenchEducation,EnglishOccupation,SpanishOccupation ,FrenchOccupation,HouseOwnerFlag,NumberCarsOwned,AddressLine1,AddressLine2 ,Phone,DateFirstPurchase,CommuteDistance,SrcCustomerID,SrcPersonID) SELECT DISTINCT gd.GeographyKey, c.AccountNumber CustomerAlternateKey, p.Title, p.FirstName, p.MiddleName, p.LastName, p.NameStyle, x.BirthDate, x.MaritalStatus, p.Suffix, x.Gender, ea.EmailAddress, x.YearlyIncome, x.TotalChildren, x.NumberChildrenAtHome, x.Education EnglishEducation, NULL SpainishEducation, NULL FrenchEducation, x.EnglishOccupation, NULL SpainishOccupation, NULL FrenchOccupation, x.HomeOwnerFlag, x.NumberCarsOwned, a.AddressLine1, a.AddressLine2, pp.PhoneNumber Phone, x.DateFirstPurchase, x.CommuteDistance, c.CustomerID SrcCustomerID, c.PersonID SrcPersonID FROM AdventureWorks2012.Sales.Customer c JOIN AdventureWorks2012.Person.Person p ON p.BusinessEntityID = c.PersonID JOIN AdventureWorks2012.[Person].[BusinessEntityAddress] bea ON bea.BusinessEntityID = p.BusinessEntityID JOIN AdventureWorks2012.Person.[Address] a ON a.AddressID = bea.AddressID JOIN AdventureWorks2012.Person.PersonPhone pp on pp.BusinessEntityID = p.BusinessEntityID JOIN AdventureWorks2012.Sales.SalesTerritory t ON t.TerritoryID = c.territoryID JOIN #Demographics x on x.PersonID = c.PersonID JOIN AdventureWorks2012.Person.StateProvince sp ON sp.StateProvinceID = a.StateProvinceID JOIN AdventureWorks2012.Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID JOIN dbo.GeographyDim gd ON gd.City = a.city AND gd.StateProvinceCode = sp.StateProvinceCode AND gd.CountryRegionCode = sp.CountryRegionCode AND gd.PostalCode = a.PostalCode WHERE customerID <> 11566 AND bea.AddressTypeID = 2 --home --(18483 row(s) affected) GO --now load InternetSalesFact table (except for customerID = 11566) INSERT INTO dbo.InternetSalesFact (ProductKey,OrderDateKey,DueDateKey,ShipDateKey,CustomerKey,PromotionKey,CurrencyKey ,SalesTerritoryKey,SalesOrderNumber,SalesOrderLineNumber,RevisionNumber,OrderQuantity ,UnitPrice,ExtendedAmount,UnitPriceDiscountPct,DiscountAmount,ProductStandardCost ,TotalProductCost,SalesAmount,TaxAmt,Freight,CarrierTrackingNumber,CustomerPONumber ,OrderDate,DueDate,ShipDate,SrcProductID,SrcCustomerId) SELECT DISTINCT ISNULL(prod.ProductKey,-1) ProductKey, CONVERT(INT,CONVERT(VARCHAR(25),soh.OrderDate,112)) OrderDateKey, CONVERT(INT,CONVERT(VARCHAR(25),soh.DueDate,112)) DueDateKey, CONVERT(INT,CONVERT(VARCHAR(25),soh.ShipDate,112)) ShipDateKey, ISNULL(cd.CustomerKey,-1) CustomerKey, ISNULL(pd.PromotionKey,-1) PromotionKey, ISNULL(curr.CurrencyKey,-1) CurrencyKey, ISNULL(std.SalesTerritoryKey,-1) SalesTerritoryKey, soh.SalesOrderNumber, Row_Number() OVER (Partition By sod.SalesOrderID ORDER BY sod.SalesOrderDetailID) SalesOrderLineNumber, soh.RevisionNumber, sod.OrderQty OrderQuantity, sod.UnitPrice, 0 ExtendedAmount, sod.UnitPriceDiscount*100/sod.UnitPrice UnitPriceDiscountPct, sod.UnitPriceDiscount DiscountAmount, 0 ProductStandardCost, sod.LineTotal TotalProductCost, soh.SubTotal SalesAmount, soh.TaxAmt, soh.Freight, sod.CarrierTrackingNumber CarrierTrackingNumber, soh.PurchaseOrderNumber CustomerPONumber, soh.OrderDate, soh.DueDate, soh.ShipDate, sod.productID SrcProductID, soh.CustomerID SrcCustomerId FROM AdventureWorks2012.Sales.SalesOrderHeader soh JOIN AdventureWorks2012.Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID JOIN AdventureWorks2012.Sales.CurrencyRate cr ON cr.CurrencyRateID = soh.currencyRateID JOIN AdventureWorks2012.Sales.SpecialOffer so ON so.SpecialOfferID = sod.SpecialOfferID LEFT JOIN dbo.PromotionDim pd ON pd.EnglishPromotionName = so.[Description] AND pd.DiscountPct = so.DiscountPct AND pd.EnglishPromotionType = so.[Type] LEFT JOIN dbo.CustomerDim cd ON cd.SrcCustomerID = soh.CustomerID LEFT JOIN dbo.ProductDim prod ON prod.SrcProductID = sod.ProductID LEFT JOIN dbo.CurrencyDim curr ON curr.CurrencyAlternateKey = cr.ToCurrencyCode LEFT JOIN dbo.SalesTerritoryDim std on std.SrcSalesTerritoryID = soh.TerritoryID WHERE OnlineOrderFlag = 1 AND soh.CustomerID <> 11566; GO --completed initial data load, do admin DECLARE @Date DATETIME = GETUTCDATE(); INSERT etl.[ETL] (StartDate,EndDate, CTCompleteFlag, ExtractCompleteFlag, LoadCompleteFlag, PreviousChangeVersion, CurrentChangeVersion) -- Set Change Versions VALUES (@DATE, @DATE,'Y', 'Y', 'Y', 0, 0) GO