MetroAltDW script

use Master
if exists
  (Select name from sys.Databases
      where name = 'MetroAltDW')
Begin
   Drop Database MetroAltDW
End
Go
Create Database MetroAltDW
go
Use MetroAltDW
Go
Create table DimEmployee
(
  DimEmployeeKey int identity(1,1),
  EmployeeKey int,
  PositionKey int,
  PositionName nvarchar(255),
  EmployeeHireDate date,
  EmployeeHourlyPayRate decimal(5,2)
)
Go
Create table DimBus
( 
   DimBuskey int identity(1,1),
   BusKey int,
   BustTypeKey int,
   BusTypeDescription nvarchar(255),
   BusTypePurchasePrice decimal(12,2)
)
Go
Create table DimRoute
(
    DimRouteKey int identity(1,1),
 BusRouteKey int,
 BusRouteZone nvarchar(255)
)
Go
Create table DimSchedule
(
    DimscheduleKey int identity(1,1),
 BusDriverShiftKey int,
 busSheduleAssignmentKey int
 
)
Go
Create table DimDate
(
    dimDateKey int identity(1,1),
 BusScheduleAssignmentDate date,
 BusScheduleYear int,
 BusScheduleMonth int
)

go
Create table FactSchedule
(
   DimEmployeeKey int not null,
   DimBusKey int not null,
   DimRouteKey int not null,
   DimScheduleKey int not null,
   DimdateKey int not null,
   riders int,
   FaresPerRoute money
)
go

Alter table DimEmployee
Add Constraint pk_DimEmployee primary key (DimEmployeeKey)

Alter table DimBus
Add Constraint pk_DimBus primary key (DimBusKey)

Alter table DimRoute
Add Constraint pk_DimRoute primary key (DimRouteKey)

Alter table DimSchedule
Add Constraint pk_DimSchedule primary key (DimScheduleKey)

Alter table DimDate
Add Constraint pk_DimDate primary key (DimDateKey)

Alter table FactSchedule
Add Constraint pk_FactScheduleKey 
primary key (DimEmployeeKey, DimBusKey, DimRouteKey, dimScheduleKey, DimDateKey)

Alter table FactSchedule
Add Constraint FK_DimEmployee foreign key(DimEmployeeKey)
References DimEmployee(DimEmployeeKey)

Alter table FactSchedule
Add Constraint FK_DimBus foreign key(DimBusKey)
References DimBus(DimBusKey)

Alter table FactSchedule
Add Constraint FK_DimRoute foreign key(DimRouteKey)
References DimRoute(DimRouteKey)

Alter table FactSchedule
Add Constraint FK_DimSchedule foreign key(DimScheduleKey)
References DimSchedule(DimScheduleKey)

Alter table FactSchedule
Add Constraint FK_DimDate foreign key(DimDateKey)
References DimDate(DimDateKey)

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

Insert into DimBus(BusKey, BustTypeKey, BusTypeDescription, BusTypePurchasePrice)
Select BusKey, bt.BusTypeKey, BusTypeDescription, BusTypePurchasePrice
From MetroAlt.dbo.Bus b
inner Join MetroAlt.dbo.Bustype bt
on b.BusTypekey=bt.BusTypeKey

Insert into DimRoute ( BusRouteKey, BusRouteZone)
Select  BusRouteKey, BusRouteZone from MetroAlt.dbo.BusRoute

insert into DimSchedule(BusDriverShiftKey, busSheduleAssignmentKey)
select BusDriverShiftKey, busScheduleAssignmentKey
From MetroAlt.dbo.BusScheduleAssignment

Insert into DimDate(BusScheduleAssignmentDate, BusScheduleYear, BusScheduleMonth)
Select Distinct BusScheduleAssignmentDate, Year(BusScheduleAssignmentDate), Month(BusScheduleAssignmentDate)
From MetroAlt.dbo.BusScheduleAssignment

Insert into FactSchedule(DimEmployeeKey, DimBusKey, DimRouteKey, DimScheduleKey, DimdateKey, riders, FaresPerRoute)
Select Distinct DimEmployeeKey, DimBusKey, DimRouteKey, DimScheduleKey, DimdateKey, riders, FareAmount * riders 
From MetroAlt.dbo.BusScheduleAssignment bsa
inner join DimEmployee de
on de.EmployeeKey=bsa.EmployeeKey
inner join dimBus db
on bsa.BusKey=db.BusKey
inner join dimRoute dr
on dr.BusRouteKey=bsa.BusRouteKey
inner join DimSchedule ds
on ds.busSheduleAssignmentKey=bsa.BusScheduleAssignmentKey
inner join dimDate dd
on dd.BusScheduleAssignmentDate=bsa.BusScheduleAssignmentDate
inner join MetroAlt.dbo.Ridership r
on r.BusScheduleAssigmentKey=bsa.BusScheduleAssignmentKey
inner join MetroAlt.dbo.fare f
on f.FareKey =r.FareKey

Select * from FactSchedule

Full text Catalog

Alter Database Community_Assist
Add FileGroup FullTextGroup

Use Community_Assist

Create FullText Catalog ClientNeedDescriptions
on Filegroup FullTextGroup

--Drop FullText Catalog ClientNeedDescriptions

Create FullText index on GrantRequest(GrantRequestExplanation)
Key Index [PK__GrantReq__75A91ED011DB90BA]
on ClientNeedDescriptions
With Change_tracking auto
go

Select * from GrantRequest
Update GrantRequest
Set GrantRequestExplanation= 'I just got a new job and needed a bus pass'
where GrantRequestKey = 9

Select GrantRequestExplanation from GrantRequest 
where Freetext(GrantRequestExplanation, 'child')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'formsof(Inflectional, needing)')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'formsof(Inflectional, break)')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'child*')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'near((food, groceries),10)')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'afford AND month')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'Rent OR school')

Select GrantRequestExplanation from GrantRequest 
where Contains(GrantRequestExplanation, 'Food AND NOT stamps')

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