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