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
