Metro Alt Data Warehouse

Use master
/*Dimension Bus, BusRoute, Employee, Schedule,
Date, Fact schedule
*/
go
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,
   BusTypeKey int,
   BustypeDescription nvarchar(255),
   BusTypePurchasePrice decimal(12,2)
)
go
Create table DimRoute
(
  DimrouteKey int identity(1,1),
  RouteKey int,
  BusRouteZone nvarchar(255)
)
Go
Create table DimSchedule
(
    DimScheduleKey int identity(1,1),
 BusDriverShiftKey int,
 BusScheduleAssignmentKey int,
 Employeekey 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_FactSchedule 
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_Schedule 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, BusTypeKey, 
  BustypeDescription, BusTypePurchasePrice)
  Select b.BusKey, bt.BusTypeKey, 
  BustypeDescription, BusTypePurchasePrice
  From MetroAlt.dbo.Bus b
  inner join MetroAlt.dbo.Bustype bt
  on b.BusTypekey=bt.BusTypeKey

 Insert into Dimroute(RouteKey, BusRouteZone)
 Select BusRouteKey, BusRouteZone
 From MetroAlt.dbo.BusRoute

 Insert into dimSchedule(BusDriverShiftKey, 
 BusScheduleAssignmentKey, 
 Employeekey)
 Select BusDriverShiftKey, 
 BusScheduleAssignmentKey, 
 Employeekey
 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, Riders * FareAmount
 From MetroAlt.Dbo.BusScheduleAssignment bsa
 inner join DimEmployee de
 on de.EmployeeKey=bsa.EmployeeKey
 inner join dimBus db
 on db.BusKey=bsa.BusKey
 inner join Dimroute dr
 on dr.RouteKey=bsa.BusRouteKey
 inner join DimSchedule ds
 on ds.BusScheduleAssignmentKey=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 

/*
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.RouteKey=bsa.BusRouteKey
inner join DimSchedule ds
on ds.busScheduleAssignmentKey=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