Thursday, August 7, 2014

Populating the Dim tables

Insert into DimCustomer(RegisteredCustomerKey, PersonKey, LastName, FirstName, Email)
Select RegisteredCustomerID, p.PersonKey, LastName, FirstName, Email
From Automart.dbo.Person p
inner join Automart.Customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey

Insert into DimEmployee(EmployeeID, PersonKey, HireDate, LocationID, SupervisorID)
Select EmployeeID, PersonKey, HireDate, LocationID, SupervisorID
From Automart.dbo.Employee

Insert into DimLocation(LocationID, LocationName, LocationAddress, LocationCity, LocationState, LocationZip, LocationPhone)
Select LocationID, LocationName, LocationAddress, LocationCity, LocationState, LocationZip, LocationPhone
From Automart.Customer.Location

Insert into DimVehicle (VehicleID, VehicleMake, VehicleYear)
Select VehicleID, VehicleMake, VehicleYear
From Automart.Customer.Vehicle

Insert into DimVehicleService( VehicleServiceID, VehicleID, LocationName, ServiceName)
Select  vs.VehicleServiceID, VehicleID, LocationName, ServiceName
From Automart.Employee.VehicleService vs
inner Join Automart.Employee.VehicleServiceDetail vsd
on vs.VehicleServiceID=vsd.VehicleServiceID
inner Join Automart.Customer.Location loc
on loc.LocationID=vs.LocationID
inner Join Automart.Customer.AutoService a
on a.AutoServiceID=vsd.AutoServiceID

Insert into DimDate (ServiceDate, ServiceTime, sDay, sMonth, sYear)
Select  ServiceDate, ServiceTime, Day(ServiceDate),Month(ServiceDate), 
Year(ServiceDate)
From automart.Employee.VehicleService

Alter table FactSales Drop constraint [PK_FactSales]

No comments:

Post a Comment