Create Scripts for tables And Insert data into Sale DWH

Createdatabase Sales_DW
Go

Use Sales_DW
Go



-------DIMCustomer----------------------
Create table DimCustomer
(
CustomerID int primary key identity,
CustomerAltID varchar(10) not null,
CustomerName varchar(50),
Gender varchar(20)
)
go


--------------DIMProduct-------------------

Create table DimProduct
(
ProductKey int primary key identity,
ProductAltKey varchar(10)not null,
ProductName varchar(100),
ProductActualCost money,
ProductSalesCost money

)
Go


--------------------------DImStore----------------------

Create table DimStores
(
StoreID int primary key identity,
StoreAltID varchar(10)not null,
StoreName varchar(100),
StoreLocation varchar(100),
City varchar(100),
State varchar(100),
Country varchar(100)
)
Go
-----------------------DIMSalesperson-------------------

Create table DimSalesPerson
(
SalesPersonID int primary key identity,
SalesPersonAltID varchar(10)not null,
SalesPersonName varchar(100),
StoreID int,
City varchar(100),
State varchar(100),
Country varchar(100)
)
Go

-------------------------------DIMDate--------------------------


CREATE TABLE [dbo].[DimDate]
( [DateKey] INT primary key,
[Date] DATETIME,
[FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format
[FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format
[DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
[DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
[DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
[DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7
[DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7
[DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
[DayOfWeekInYear] VARCHAR(2),
[DayOfQuarter] VARCHAR(3),
[DayOfYear] VARCHAR(3),
[WeekOfMonth] VARCHAR(1),-- Week Number of Month
[WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
[WeekOfYear] VARCHAR(2),--Week Number of the Year
[Month] VARCHAR(2), --Number of the Month 1 to 12
[MonthName] VARCHAR(9),--January, February etc
[MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
[Quarter] CHAR(1),
[QuarterName] VARCHAR(9),--First,Second..
[Year] CHAR(4),-- Year value of Date stored in Row
[YearName] CHAR(7), --CY 2012,CY 2013
[MonthYear] CHAR(10), --Jan-2013,Feb-2013
[MMYYYY] CHAR(6),
[FirstDayOfMonth] DATE,
[LastDayOfMonth] DATE,
[FirstDayOfQuarter] DATE,
[LastDayOfQuarter] DATE,
[FirstDayOfYear] DATE,
[LastDayOfYear] DATE,
[IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday
[IsWeekday] BIT,-- 0=Week End ,1=Week Day
[HolidayUSA] VARCHAR(50),--Name of Holiday in US
[IsHolidayUK] BIT Null,-- Flag 1=National Holiday, 0-No National Holiday
[HolidayUK] VARCHAR(50) Null --Name of Holiday in UK
)
GO


------------------------------FactProductsales--------------------
Create Table FactProductSales
(
TransactionId bigint primary key identity,
SalesInvoiceNumber int not null,
SalesDateKey int,
SalesTimeKey int,
SalesTimeAltKey int,
StoreID int not null,
CustomerID int not null,
ProductID int not null,
SalesPersonID int not null,
Quantity float,
SalesTotalCost money,
ProductActualCost money,
Deviation float
)
Go



----------------Add Relationships between Fact and dimension table


AlTER TABLE FactProductSales ADD CONSTRAINT
FK_StoreID FOREIGN KEY (StoreID)REFERENCES DimStores(StoreID);
AlTER TABLE FactProductSales ADD CONSTRAINT
FK_CustomerID FOREIGN KEY (CustomerID)REFERENCES Dimcustomer(CustomerID);
AlTER TABLE FactProductSales ADD CONSTRAINT
FK_ProductKey FOREIGN KEY (ProductID)REFERENCES Dimproduct(ProductKey);
AlTER TABLE FactProductSales ADD CONSTRAINT
FK_SalesPersonID FOREIGN KEY (SalesPersonID)REFERENCES Dimsalesperson(SalesPersonID);
Go
AlTER TABLE FactProductSales ADD CONSTRAINT
FK_SalesDateKey FOREIGN KEY (SalesDateKey)REFERENCES DimDate(DateKey);
Go
AlTER TABLE FactProductSales ADD CONSTRAINT
FK_SalesTimeKey FOREIGN KEY (SalesTimeKey)REFERENCES DimDate(TimeKey);
Go