--© 2018 | ByrdNest Consulting


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

--  now create ETL admin tables, sprocs, etc.

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

CREATE TABLE etl.[ETL](									--major ETL admin table
	ETLID					INT IDENTITY(1,1) NOT NULL,
	StartDate				DATETIME NOT NULL,
	EndDate					DATETIME NULL,
	CTCompleteFlag			CHAR(1) NOT NULL,
	ExtractCompleteFlag		CHAR(1) NOT NULL,
	LoadCompleteFlag		CHAR(1) NOT NULL,
	PreviousChangeVersion	BIGINT NULL,
	CurrentChangeVersion	BIGINT NULL,
 CONSTRAINT PK_ETL PRIMARY KEY CLUSTERED (ETLID ASC))
 GO

 CREATE TABLE etl.SourceTable(
	SourceTableID int NOT NULL,
	TableName			varchar(50) NOT NULL,
	PKCol1				VARCHAR(50) NOT NULL,
	PKCol1DataType		VARCHAR(50) NOT NULL,
	PKCOl2				VARCHAR(50) NULL,
	PKCol2DataType		VARCHAR(50) NULL,
	PKCol3				VARCHAR(50) NULL,
	PKCol3DataType		VARCHAR(50) NULL,
 CONSTRAINT PK_SourceTable PRIMARY KEY CLUSTERED (SourceTableID ASC))
GO

INSERT etl.SourceTable
	(SourceTableID,TableName,PKCol1,PKCol1DataType,PKCol2,PKCol2DataType,PKCol3,PKCol3DataType)
	SELECT 1,'Production.Product','ProductID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 2,'Production.ProductModel','ProductModelID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 3,'Production.ProductModelProductDescriptionCulture','ProductModelID','INT','ProductDescriptionID','INT','CultureID','NCHAR(6)' UNION
	SELECT 4,'Production.ProductDescription','ProductDescriptionID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 5,'Production.ProductProductPhoto','ProductID','INT','ProductPhotoID','INT',NULL,NULL UNION
	SELECT 6,'Production.ProductPhoto','ProductPhotoID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 7,'Production.ProductSubCategory','ProductSubCategoryID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 8,'Sales.Customer','CustomerID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 9,'Person.Person','BusinessEntityID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 10,'Person.BusinessEntityAddress','BusinessEntityID','INT','AddressID','INT','AddressTypeID','INT' UNION
	SELECT 11,'Person.Address','AddressID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 12,'Person.PersonPhone','BusinessEntityID','INT','PhoneNumber','nvarchar(25)','PhoneNumberTypeID','INT' UNION
	SELECT 13,'Sales.SalesTerritory','TerritoryID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 14,'Person.StateProvince','StateProvinceID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 15,'Person.EmailAddress','BusinessEntityID','INT','EmailAddressID','INT',NULL,NULL UNION
	SELECT 16,'Sales.SalesOrderHeader','SalesOrderID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 17,'Sales.SalesOrderDetail','SalesOrderID','INT','SalesOrderDetailID','INT',NULL,NULL UNION
	SELECT 18,'Sales.CurrencyRate','CurrencyRateID','INT',NULL,NULL,NULL,NULL UNION
	SELECT 19,'Sales.SpecialOffer','SpecialOfferID','INT',NULL,NULL,NULL,NULL




CREATE TABLE etl.TargetTable(
	TargetTableID 			INT NOT NULL,
	TableName 				VARCHAR(50) NOT NULL,
 CONSTRAINT PK_TargetTable PRIMARY KEY CLUSTERED (TargetTableID ASC))
GO

INSERT etl.TargetTable
	SELECT 1,'DateDim' UNION
	SELECT 2,'ProductCategoryDim' UNION
	SELECT 3,'ProductSubCategoryDim' UNION
	SELECT 4,'ProductDim' UNION
	SELECT 5,'SalesTerritoryDim' UNION
	SELECT 6,'GeographyDim' UNION
	SELECT 7,'CustomerDim' UNION
	SELECT 8,'PromotionDim' UNION
	SELECT 9,'CurrencyDim' UNION
	SELECT 10,'InternetSalesFact';


CREATE TABLE etl.SourceTargetMapping(
	SourceTableID 			INT NOT NULL,
	TargetTableID 			INT NOT NULL,
	SourcePKFlag 			CHAR(1) NOT NULL,
	ChangeFlag 				CHAR(1) NOT NULL,
 CONSTRAINT PK_SourceTargetMapping PRIMARY KEY CLUSTERED (SourceTableID ASC,TargetTableID ASC))
 GO

 INSERT etl.SourceTargetMapping
	(SourceTableID,TargetTableID,SourcePKFlag,ChangeFlag)
	SELECT 1,4,'Y','Y' UNION	--Product,ProductDim
	SELECT 2,4,'N','Y' UNION	--ProductModel,ProductDim
	SELECT 3,4,'N','Y' UNION	--ProductModelProductDescriptionCulture,ProductDim
	SELECT 4,4,'N','Y' UNION	--ProductDescription,ProductDim
	SELECT 5,4,'N','Y' UNION	--ProductProductPhoto,ProductDim
	SELECT 6,4,'N','Y' UNION	--ProductPhoto,ProductDim
	SELECT 7,4,'N','Y' UNION	--ProductSubCategory,ProductDim
	SELECT 8,7,'Y','Y' UNION	--Customer,CustomerDim
	SELECT 9,7,'N','Y' UNION	--Person,CustomerDim
	SELECT 10,7,'N','Y' UNION	--BusinessEntityAddress,CustomerDim
	SELECT 11,7,'N','Y' UNION	--Address,CustomerDim
	SELECT 12,7,'N','Y' UNION	--PersonPhone,CustomerDim
	SELECT 13,7,'N','Y' UNION	--SalesTerritory,CustomerDim
	SELECT 14,7,'N','Y' UNION	--StateProvince,CustomerDim
	SELECT 15,7,'N','Y' UNION	--EmailAddress,CustomerDim
	SELECT 16,10,'Y','Y' UNION	--SalesOrderHeader,InternetSalesFact
	SELECT 17,10,'N','Y' UNION	--SalesOrderDetail,InternetSalesFact
	SELECT 18,10,'N','Y' UNION	--CurrencyRate,InternetSalesFact
	SELECT 19,10,'N','Y';		--SpecialOffer,InternetSalesFact
	 
CREATE TABLE etl.ETLSourceRecord (
	ETLID			INT, 
	SourceTableID	INT, 
	Col1ID			VARCHAR(50), 
	Col2ID			VARCHAR(50), 
	Col3ID			VARCHAR(50),
	Col1DataType	VARCHAR(50),
	Col2DataType	VARCHAR(50),
	Col3DataType	VARCHAR(50),
	ChangeVersion	BIGINT,
	CUD				CHAR(1))
GO


CREATE TABLE etl.[ETLSource](
	[ETLID]			[int] NOT NULL,
	[SourceTableID] [int] NOT NULL,
 CONSTRAINT [PK_ETLInstanceSource] PRIMARY KEY CLUSTERED 
	([ETLID] ASC,[SourceTableID] ASC))
GO




CREATE TABLE etl.ProductDimExtract(
	ProductKey					int NULL,
	ProductAlternateKey			nvarchar(25) NULL,
	ProductSubcategoryKey		int NULL,
	WeightUnitMeasureCode		nchar(3) NULL,
	SizeUnitMeasureCode			nchar(3) NULL,
	EnglishProductName			nvarchar(50) NOT NULL,
	SpanishProductName			nvarchar(50) NULL,
	FrenchProductName			nvarchar(50) NULL,
	StandardCost				money NULL,
	FinishedGoodsFlag			bit NOT NULL,
	Color						nvarchar(15) NOT NULL,
	SafetyStockLevel			smallint NULL,
	ReorderPoint				smallint NULL,
	ListPrice					money NULL,
	Size						nvarchar(50) NULL,
	SizeRange					nvarchar(50) NULL,
	[Weight]					float NULL,
	DaysToManufacture			int NULL,
	ProductLine					nchar(2) NULL,
	DealerPrice					money NULL,
	Class						nchar(2) NULL,
	Style						nchar(2) NULL,
	ModelName					nvarchar(50) NULL,
	LargePhoto					varbinary(max) NULL,
	EnglishDescription			nvarchar(400) NULL,
	FrenchDescription			nvarchar(400) NULL,
	ChineseDescription			nvarchar(400) NULL,
	ArabicDescription			nvarchar(400) NULL,
	HebrewDescription			nvarchar(400) NULL,
	ThaiDescription				nvarchar(400) NULL,
	SpainishDescription			nvarchar(400) NULL,
	JapaneseDescription			nvarchar(400) NULL,
	TurkishDescription			nvarchar(400) NULL,
	StartDate					datetime NULL,
	EndDate						datetime NULL,
	[Status]					nvarchar(7) NULL,
	SrcProductID				INT,
	SrcProductSubcategoryID		INT, 
	[Version]					BIGINT, 
	CUD							CHAR(1) )


CREATE TABLE etl.[CustomerDimExtract](
	[CustomerKey]				[int] NULL,
	[GeographyKey]				[int] NULL,
	[CustomerAlternateKey]		[nvarchar](15) NULL,
	[Title]						[nvarchar](8) NULL,
	[FirstName]					[nvarchar](50) NULL,
	[MiddleName]				[nvarchar](50) NULL,
	[LastName]					[nvarchar](50) NULL,
	[NameStyle]					[bit] NULL,
	[BirthDate]					[date] NULL,
	[MaritalStatus]				[nchar](1) NULL,
	[Suffix]					[nvarchar](10) NULL,
	[Gender]					[nvarchar](1) NULL,
	[EmailAddress]				[nvarchar](50) NULL,
	[YearlyIncome]				nvarchar(50) NULL,
	[TotalChildren]				[tinyint] NULL,
	[NumberChildrenAtHome]		[tinyint] NULL,
	[EnglishEducation]			[nvarchar](40) NULL,
	[SpanishEducation]			[nvarchar](40) NULL,
	[FrenchEducation]			[nvarchar](40) NULL,
	[EnglishOccupation]			[nvarchar](100) NULL,
	[SpanishOccupation]			[nvarchar](100) NULL,
	[FrenchOccupation]			[nvarchar](100) NULL,
	[HouseOwnerFlag]			[nchar](1) NULL,
	[NumberCarsOwned]			[tinyint] NULL,
	[AddressLine1]				[nvarchar](120) NULL,
	[AddressLine2]				[nvarchar](120) NULL,
	[Phone]						[nvarchar](20) NULL,
	[DateFirstPurchase]			[date] NULL,
	[CommuteDistance]			[nvarchar](15) NULL,
	SrcCustomerID				INT NULL, 
	SrcPersonID					INT NULL,
	[Version]					BIGINT NULL, 
	CUD							CHAR(1) NULL,
	City						NVARCHAR(30),
	StateProvinceCode			NVARCHAR(3),
	CountryRegionCode			NVARCHAR(3),
	PostalCode					NVARCHAR(15))


SET IDENTITY_INSERT dbo.CurrencyDim ON
GO
INSERT dbo.CurrencyDim
	(CurrencyKey, CurrencyName,CurrencyAlternateKey)
	SELECT CurrencyKey, CurrencyName,CurrencyAlternateKey
		FROM AdventureWorksDW2012.dbo.DimCurrency
GO
SET IDENTITY_INSERT dbo.CurrencyDim OFF
GO

CREATE TABLE etl.InternetSalesFactExtract(				--identical to DW table except for added AW PKs
	ProductKey				int NULL,
	OrderDateKey			int NULL,
	DueDateKey				int NULL,
	ShipDateKey				int NULL,
	CustomerKey				int NULL,
	PromotionKey			int NULL,
	CurrencyKey				int NULL,
	SalesTerritoryKey		int NULL,
	SalesOrderNumber		nvarchar(20) NOT NULL,
	SalesOrderLineNumber	tinyint NOT NULL,
	RevisionNumber			tinyint NOT NULL,
	OrderQuantity			smallint NOT NULL,
	UnitPrice				money NOT NULL,
	ExtendedAmount			money NULL,
	UnitPriceDiscountPct	float NOT NULL,
	DiscountAmount			float NOT NULL,
	ProductStandardCost		money NOT NULL,
	TotalProductCost		money NOT NULL,
	SalesAmount				money NOT NULL,
	TaxAmt					money NOT NULL,
	Freight					money NOT NULL,
	CarrierTrackingNumber	nvarchar(25) NULL,
	CustomerPONumber		nvarchar(25) NULL,
	OrderDate				datetime NULL,
	DueDate					datetime NULL,
	ShipDate				datetime NULL,
	SrcProductID			INT,
	SrcCustomerID			INT,
	SrcPromotionID			INT,
	SrcCurrencyCode			NCHAR(3),
	SrcSalesTerritoryID		INT,
	[Version]				BIGINT,
	CUD						CHAR(1))
GO

SET IDENTITY_INSERT dbo.PromotionDim ON
GO
INSERT INTO dbo.PromotionDim
           (PromotionKey,PromotionAlternateKey,EnglishPromotionName,SpanishPromotionName
           ,FrenchPromotionName,DiscountPct,EnglishPromotionType,SpanishPromotionType
           ,FrenchPromotionType,EnglishPromotionCategory,SpanishPromotionCategory,FrenchPromotionCategory
           ,StartDate,EndDate,MinQty,MaxQty)
	SELECT PromotionKey,PromotionAlternateKey,EnglishPromotionName,SpanishPromotionName
           ,FrenchPromotionName,DiscountPct,EnglishPromotionType,SpanishPromotionType
           ,FrenchPromotionType,EnglishPromotionCategory,SpanishPromotionCategory,FrenchPromotionCategory
           ,DATEADD(yy,5,StartDate) StartDate,DATEADD(yy,5,EndDate) EndDate,MinQty,MaxQty
		FROM AdventureWorksDW2012.dbo.DimPromotion
GO
SET IDENTITY_INSERT dbo.PromotionDim OFF
GO