Data Warehouse First Draft

Use Master
go
if exists
  (Select name from sys.databases
     where name = 'Community_AssistDW')
Begin
Drop database Community_AssistDW
End
Go
Create database Community_AssistDW
Go
Use Community_AssistDW
Go
--create the dimension tables
Create table DimEmployee
(
 DimEmployeeKey int identity (1,1),
 EmployeeKey int,
 EmployeeHireDate date,
 EmployeeAnnualSalary money,
 PositionName nvarchar(255)
)
Go
Create table DimClient
(
    DimClientKey int identity(1,1),
 PersonKey int,
 PersonAddressZip nvarchar(255),
 PersonDateEntered DateTime
)
Go
Create table DimGrantType
(
   DimGrantTypeKey int identity(1,1),
   GrantTypeKey int,
   GrantTypeName nvarchar(255)

)
go
Create table DimGrant
(
   DimGrantKey int identity(1,1),
   GrantKey int
   
)
go
Create table DimDate
(
   DimDateKey int identity (1,1),
   GrantReviewDate datetime,
   GrantYear int,
   GrantMonth int
)

Go
--fact table
Create table FactGrant
(
    DimEmployeeKey int not null,
 DimClientKey int not null,
 DimGrantTypeKey int not null,
 DimGrantKey int not null,
 DimDateKey int not null,
 GrantRequestAmount money,
 GrantAllocationAmount money
)

--add primary key
Alter table DimEmployee
Add Constraint pk_DimEmployee primary key (DimEmployeeKey)

Alter table DimClient
Add Constraint pk_Dimclient primary key (DimClientKey)

Alter table DimGrantType
Add Constraint pk_GrantType primary key (DimGrantTypeKey)

Alter table DimGrant
Add Constraint pk_DimGrant primary key (DimGrantKey)

Alter table DimDate
Add Constraint pk_DimDate primary key (DimDateKey)

--add foreign keys
Alter table FactGrant
Add Constraint pk_FactGrant primary key (DimEmployeeKey, DimClientKey, DimGrantTypekey,
  DimGrantKey, DimDateKey)

Alter table FactGrant
Add Constraint fk_Employee foreign key (DimEmployeeKey) 
references DimEmployee(DimEmployeeKey)

Alter table FactGrant
Add Constraint fk_Client foreign key (DimClientKey) 
references DimClient(DimClientKey)

Alter table FactGrant
Add Constraint fk_GrantType foreign key (DimGrantTypeKey) 
references DimGrantType(DimGrantTypeKey)

Alter table FactGrant
Add Constraint fk_Grant foreign key (DimGrantKey) 
references DimGrant(DimGrantKey)

Alter table FactGrant
Add Constraint fk_Date foreign key (DimDateKey) 
references DimDate(DimDateKey)


Insert into DimEmployee(EmployeeKey, EmployeeHireDate, 
EmployeeAnnualSalary, PositionName)
Select e.EmployeeKey, EmployeeHireDate, EmployeeAnnualSalary, PositionName
From Community_Assist.dbo.Employee e
inner join Community_Assist.dbo.EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner join Community_Assist.dbo.Position p
on p.PositionKey=ep.PositionKey

Insert into DimClient(PersonKey, PersonAddressZip, PersonDateEntered)
Select Distinct p.PersonKey, PersonAddressZip, PersonEntryDate
From Community_Assist.dbo.Person p
inner join Community_Assist.dbo.PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join Community_Assist.dbo.GrantRequest gr
on p.PersonKey=gr.PersonKey

--Truncate table DimClient

Insert into DimGrantType (GrantTypeKey, GrantTypeName)
Select GrantTypeKey, GrantTypeName from Community_Assist.dbo.GrantType

Insert into DimDate(GrantReviewDate, GrantYear, GrantMonth)
Select GrantReviewDate, Year(GrantReviewDate), Month(GrantReviewDate)
From Community_Assist.dbo.GrantReview

Insert into DimGrant ( GrantKey)
Select GrantRequestKey from Community_Assist.dbo.GrantRequest

--this isn't working
Insert into FactGrant(DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, GrantRequestAmount, GrantAllocationAmount)
Select Distinct DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, GrantRequestAmount, GrantAllocationAmount 
From DimClient dc
inner join Community_Assist.Dbo.GrantRequest gr
on dc.PersonKey=gr.PersonKey
inner join dimGrant dg
on dg.GrantKey=gr.GrantRequestKey
inner join DimGrantType dgt
on dgt.GrantTypeKey=gr.GrantTypeKey
inner join Community_Assist.dbo.GrantReview rev
on rev.GrantRequestKey =gr.GrantRequestKey
inner join dimEmployee de
on de.EmployeeKey=rev.EmployeeKey
inner join DimDate dd
on dd.GrantReviewDate=rev.GrantReviewDate


delete from FactGrant

Select * from FactGrant

Select * from Community_Assist.dbo.GrantReview

---

Here is Turner's script which works better.

USE MASTER
GO
IF EXISTS (SELECT NAME FROM sys.databases WHERE name = 'Community_AssistDW')
BEGIN
	DROP DATABASE Community_AssistDW
END
GO

CREATE DATABASE Community_AssistDW
GO
USE Community_AssistDW
GO
-----------
CREATE TABLE DimEmployee
(
	DimEmployeeKey int identity(1,1),
	EmployeeKey int,
	EmployeeHireDate date,
	EmployeeAnnualSalary money,
	PositionName nvarchar(255)
)
GO

CREATE TABLE DimClient
(
	DimClientKey int identity(1,1),
	PersonKey int,
	PersonAddressZip nvarchar(255),
	PersonDateEntered DateTime
)
GO

CREATE TABLE DimGrantType
(
	DimGrantTypeKey int identity(1,1),
	GrantTypeKey int,
	GrantTypeName nvarchar(255)
)
GO

CREATE TABLE DimGrant
(
	DimGrantKey int identity(1,1),
	GrantKey int
)
GO

CREATE TABLE DimDate
(
	DimDateKey int identity(1,1),
	GrantReviewDate DateTime,
	GrantYear int,
	GrantMonth int
)
GO

CREATE TABLE FactGrant
(
	DimEmployeeKey int NOT NULL,
	DimClientKey int NOT NULL,
	DimGrantTypeKey int NOT NULL,
	DimGrantKey int NOT NULL,
	DimDateKey int NOT NULL,
	GrantRequestAmount money,
	GrantAllocationAmount money
)
GO

ALTER TABLE DimEmployee ADD CONSTRAINT pk_DimEmployee PRIMARY KEY (DimEmployeeKey)
ALTER TABLE DimClient ADD CONSTRAINT pk_Dimclient PRIMARY KEY (DimClientKey)
ALTER TABLE DimGrantType ADD CONSTRAINT pk_GrantType PRIMARY KEY (DimGrantTypeKey)
ALTER TABLE DimGrant ADD CONSTRAINT pk_DimGrant PRIMARY KEY (DimGrantKey)
ALTER TABLE DimDate ADD CONSTRAINT pk_DimDate PRIMARY KEY (DimDateKey)
ALTER TABLE FactGrant ADD CONSTRAINT pk_FactGrant PRIMARY KEY (DimEmployeeKey,DimClientKey,DimGrantTypeKey,DimGrantKey,DimDateKey)

ALTER TABLE FactGrant ADD CONSTRAINT fk_Employee FOREIGN KEY (DimEmployeeKey) REFERENCES DimEmployee(DimEmployeeKey)
ALTER TABLE FactGrant ADD CONSTRAINT fk_Client FOREIGN KEY (DimClientKey) REFERENCES DimClient(DimClientKey)
ALTER TABLE FactGrant ADD CONSTRAINT fk_GrantType FOREIGN KEY (DimGrantTypeKey) REFERENCES DimGrantType(DimGrantTypeKey)
ALTER TABLE FactGrant ADD CONSTRAINT fk_Grant FOREIGN KEY (DimGrantKey) REFERENCES DimGrant(DimGrantKey)
ALTER TABLE FactGrant ADD CONSTRAINT fk_Date FOREIGN KEY (DimDateKey) REFERENCES DimDate(DimDateKey)
GO

INSERT INTO DimEmployee(EmployeeKey, EmployeeHireDate, EmployeeAnnualSalary, PositionName)
	SELECT e.EmployeeKey, EmployeeHireDate, EmployeeAnnualSalary, PositionName FROM Community_Assist.dbo.Employee e
	INNER JOIN Community_Assist.dbo.EmployeePosition ep ON e.EmployeeKey = ep.EmployeeKey
	INNER JOIN Community_Assist.dbo.Position p ON p.PositionKey = ep.PositionKey
GO

INSERT INTO DimClient(PersonKey,PersonAddressZip,PersonDateEntered)
	SELECT p.PersonKey, PersonAddressZip, PersonEntryDate FROM Community_Assist.dbo.Person p
	INNER JOIN (SELECT MIN(PersonAddressKey) PersonAddressKey, PersonKey FROM Community_Assist.dbo.PersonAddress GROUP BY PersonKey) mpk ON p.PersonKey = mpk.PersonKey
	INNER JOIN Community_Assist.dbo.PersonAddress pa ON mpk.PersonAddressKey = pa.PersonAddressKey
	WHERE p.PersonKey IN (SELECT PersonKey FROM Community_Assist.dbo.GrantRequest)
GO

--TRUNCATE TABLE DimClient
INSERT INTO DimGrantType(GrantTypeKey, GrantTypeName)
	SELECT GrantTypeKey, GrantTypeName FROM Community_Assist.dbo.GrantType
GO

INSERT INTO DimDate(GrantReviewDate, GrantYear, GrantMonth)
	SELECT DISTINCT GrantReviewDate, YEAR(GrantReviewDate), MONTH(GrantReviewDate) FROM Community_Assist.dbo.GrantReview
GO

INSERT INTO DimGrant(GrantKey)
	SELECT GrantRequestKey FROM Community_Assist.dbo.GrantRequest
GO

INSERT INTO FactGrant(DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, GrantRequestAmount, GrantAllocationAmount)
	SELECT DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, GrantRequestAmount, GrantAllocationAmount
	FROM DimClient dc

	INNER JOIN Community_Assist.dbo.GrantRequest greq ON dc.PersonKey = greq.PersonKey
	INNER JOIN Community_Assist.dbo.Person p ON dc.PersonKey = p.PersonKey
	INNER JOIN Community_Assist.dbo.GrantReview  grev ON greq.GrantRequestKey = grev.GrantRequestKey
	INNER JOIN DimGrantType dgt ON greq.GrantTypeKey = dgt.GrantTypeKey
	INNER JOIN DimGrant dg ON dg.GrantKey = greq.GrantRequestKey
	INNER JOIN Community_Assist.dbo.Employee e ON grev.EmployeeKey = e.EmployeeKey	
	INNER JOIN DimEmployee de ON e.EmployeeKey = de.EmployeeKey
	INNER JOIN DimDate dd ON grev.GrantReviewDate = dd.GrantReviewDate
GO

SELECT * FROM FactGrant ORDER BY DimGrantTypeKey ASC
SELECT GrantTypeKey, COUNT(*) FROM Community_Assist.dbo.GrantRequest GROUP BY GrantTypeKey

SELECT * FROM Community_Assist.dbo.GrantReview
SELECT * FROM Community_Assist.dbo.GrantRequest WHERE GrantRequestKey = 18

-- shitty debug query
SELECT DimEmployeeKey, DimClientKey, DimGrantTypeKey, DimGrantKey, DimDateKey, grev.GrantReviewDate FROM DimClient dc
INNER JOIN Community_Assist.dbo.GrantRequest greq ON dc.PersonKey = greq.PersonKey
INNER JOIN Community_Assist.dbo.GrantReview grev ON greq.GrantRequestKey = grev.GrantRequestKey
INNER JOIN DimEmployee de ON grev.EmployeeKey = de.EmployeeKey
INNER JOIN DimGrantType dgt ON greq.GrantTypeKey = dgt.GrantTypeKey
INNER JOIN DimGrant dg ON greq.GrantRequestKey = dg.GrantKey
INNER JOIN DimDate dd ON grev.GrantReviewDate = dd.GrantReviewDate
ORDER BY DimClientKey ASC

SELECT * FROM DimClient ORDER BY PersonKey


SELECT MIN(PersonKey) PersonKey, MIN(PersonAddressKey) PersonAddressKey, PersonAddressZip, PersonAddressStreet FROM Community_Assist.dbo.PersonAddress GROUP BY PersonAddressZip, PersonAddressStreet ORDER BY PersonKey
SELECT * FROM Community_Assist.dbo.PersonAddress WHERE PersonAddressZip = ''

SELECT MIN(PersonAddressKey) PersonAddressKey, PersonKey FROM Community_Assist.dbo.PersonAddress GROUP BY PersonKey