CommunityAssist Data Warehouse

Create database CommunityAssistDw

use CommunityAssistDW

Create table DimClient
(
    DimClientKey int identity(1,1) primary key,
 PersonKey int,
 ClientLastName nvarchar(255),
 ClientFirstName nvarchar(255),
 ClientEmail nvarchar(25),
 ClientZipcode nchar(10)
)

Create table DimEmployee
(
 DimEmployeeKey int identity(1,1) primary key,
 PersonKey int,
 EmployeeKey int,
 EmployeeHireDate dateTime,
 EmployeeStatus nchar(2)
)

Create table DimService
(
 DimServiceKey int identity(1,1) primary key,
 ServiceName nvarchar(255),
 ServiceKey int
)

Create table DimGrant
(
 DimGrantKey int identity(1,1) primary key,
 Grantkey int,
 GrantApprovalStatus nvarchar(10)
)

Create Table DimDate
(
 DimDateKey int identity (1,1) primary key,
 GrantDate dateTime,
 GrantReviewDate dateTime,
 GrantYear int,
 GrantMonth int,
 ReviewYear int,
 ReviewMonth int
)


Create table FactGrant
(
 DimClientKey int not null,
 DimServiceKey int not Null,
 DimGrantKey int not null,
 DimDateKey int not null,
 GrantAmount money,
 GrantAllocation money
)

Alter table FactGrant
Add Constraint PK_factGrant primary key 
(DimclientKey, dimServiceKey,  DimDateKey, DimGrantKey)

Alter Table FactGrant
Add Constraint FK_DimClient foreign key(DimClientKey)
references DimClient(DimClientKey)

Alter Table FactGrant
Add Constraint FK_DimSevice foreign key(DimServiceKey)
references DimService(DimServiceKey)

Alter Table FactGrant
Add Constraint FK_DimDate foreign key(DimDateKey)
references DimDate(DimDateKey)

Alter Table FactGrant
Add Constraint FK_DimGrant foreign key(DimGrantKey)
references DimGrant(DimGrantKey)

Insert into DimClient(PersonKey, ClientLastName, ClientFirstName, ClientEmail, ClientZipcode)
Select Distinct sg.PersonKey, PersonLastName, PersonFirstName,
  PersonUserName, Zip
 From CommunityAssist.dbo.Person p
 inner Join CommunityAssist.dbo.PersonAddress pa
 on p.PersonKey=pa.PersonKey
 inner Join CommunityAssist.dbo.ServiceGrant sg
 on p.PersonKey=sg.PersonKey


insert into DimService(ServiceName, ServiceKey)
Select ServiceName, ServiceKey from CommunityAssist.dbo.CommunityService

Insert into DimGrant(Grantkey, GrantApprovalStatus)
Select GrantKey, GrantApprovalStatus from CommunityAssist.dbo.ServiceGrant

Insert into dimDate(GrantDate, GrantReviewDate, GrantYear, GrantMonth, ReviewYear, ReviewMonth)
Select GrantDate, GrantReviewDate, Year(GrantDate), Month(GrantDate), 
      Year(GrantReviewDate), Month(GrantReviewDate)
   From CommunityAssist.dbo.ServiceGrant

Insert into FactGrant(DimClientKey, DimServiceKey, DimGrantKey, DimDateKey, GrantAmount, GrantAllocation)
Select DimClientKey, DimServiceKey, DimGrantKey, DimDateKey, GrantAmount, GrantAllocation
From DimClient dc
inner join CommunityAssist.dbo.serviceGrant sg
on dc.PersonKey=sg.PersonKey
inner join DimService ds
on ds.ServiceKey =sg.ServiceKey
inner join DimGrant dg
on dg.Grantkey=sg.GrantKey
inner join DimDate dd
on dd.GrantDate=sg.GrantDate