Wednesday, July 18, 2018

Code for MetroAltDW SSIS

--Drop Keys (foreign then primary)
--Remove Data (truncate)
--Insert data
--reapply keys (primary then foreign)

--drop constraints

   Alter table Factschedule
   drop constraint fk_Dimdate

      Alter table Factschedule
   drop constraint fk_schedule

      Alter table Factschedule
   drop constraint fk_DimRoute

      Alter table Factschedule
   drop constraint fk_Dimbus

      Alter table Factschedule
   drop constraint fk_DimEmployee

      Alter table DimEmployee
   drop constraint Pk_DimEmployee

      Alter table DimBus
   drop constraint Pk_DimBus

      Alter table Dimschedule
   drop constraint Pk_Dimschedule

      Alter table DimRoute
   drop constraint PK_DimRoute

      Alter table DimDate
   drop constraint Pk_Dimdate

   Alter table Factschedule
   Drop constraint Pk_FactSchedule
 
 --truncate tables
 truncate table Dimbus
 truncate table dimdate
 truncate table dimEmployee
 Truncate table dimroute
 truncate table dimschedule
 truncate table factschedule



 --reinsert the live data
  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 

 --reapply the constraints
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)


Select * from FactSchedule

36 comments:

  1. Existing without the answers to the difficulties you’ve sorted out through this guide is a critical case, as well as the kind which could have badly affected my entire career if I had not discovered your website.Block Chain Training in chennai

    Block Chain Training in annanagar

    Block Chain Training in pune

    Block Chain Training in velachery

    ReplyDelete
  2. Very good brief and this post helped me alot. Say thank you I searching for your facts. Thanks for sharing with us!
    selenium Training in chennai


    amazon web services Training in chennai


    Block Chain Training in velachery

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.


    rpa training in chennai | best rpa training in chennai | rpa training in chennai | rpa training in bangalore
    rpa training in pune | rpa online training

    ReplyDelete
  5. It was worth visiting your blog and I have bookmarked your blog. Hope to visit again
    python training in velachery
    python training institute in chennai

    ReplyDelete
  6. Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
    Best Devops Training in pune

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
    Selenium training in Chennai
    Selenium Courses in Chennai
    best ios training in chennai
    Digital Marketing Training in Chennai
    java coaching in chennai
    JAVA Training in Chennai
    JAVA Course in Chennai

    ReplyDelete
  9. It is a great post. Keep sharing such kind of useful information.

    Education
    Technology

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. I am really enjoying reading your well written articles.
    It looks like you spend a lot of effort and time on your blog.
    I have bookmarked it and I am looking forward to reading new articles. Keep up the good work..
    big data courses in bangalore
    hadoop training institutes in bangalore
    Java Course in Bangalore
    Java Training Institutes in Bangalore
    Java Institutes in Bangalore

    ReplyDelete
  12. You are an amazing writer. The content is extra-ordinary. Looking for such a masterpiece. Thanks for sharing.
    IoT Training in Chennai
    IoT courses in Chennai
    IoT Courses
    IoT Training in Porur
    IoT Training in Adyar

    ReplyDelete
  13. It’s great to come across a blog every once in a while that isn’t the same out of date rehashed material. Fantastic read.
    cloud computing training in chennai | Cloud computing training class in chennai

    ReplyDelete
  14. Clinical sas training in chennai | SAS Training course chennai
    I have to voice my passion for your kindness giving support to those people that should have guidance on this important matter.

    ReplyDelete
  15. Hi, Honestly, it's a Nice Article
    If you are looking for the best Cloud Solutions that will be helpful for your business. We provide efficient methodologies for the successful implementation of Cloud computing business solutions | cloud computing companies in Madhapur | Cloud Computing Services in Madhapur | cloud computing companies in Hyderabad | cloud computing services companies in India | cloud computing solutions companies.
    Thank you!

    ReplyDelete