November 1, 2017 at 3:55 am
Hi all
I'm in the middle of creating a FACT table for our data warehouse.
I've got the data I want (and the code is built) but it's ugly and relatively slow (it takes 10 minutes to do a full run).
The code is here:-SELECT --TOP 1000
sess.pkSpellLocalID
,sess.pkCaseID
--,sess.TheatreID
,TheatreKey = ISNULL(loc.pkTheatreLocationsKey,-1)
--,sess.SessionClinician
,SessionClinicanKey = ISNULL(staff.pkStaffKey,-1)
--,sess.StartDate
,SessionStartDateKey = ISNULL(startdate.pkDateKey,-1)
--,sess.StartTime
,SessionStartTimeKey = ISNULL(starttime.pkTimeKey,-1)
--,sess.EndDate
,SessionEndDateKey = ISNULL(enddate.pkDateKey,-1)
--,sess.EndTime
,SessionEndTimeKey = ISNULL(endtime.pkTimeKey,-1)
--,sess.SessionStatus
,SessionStatusKey = ISNULL(stat.pkTheatreSessionStatusKey,-1)
--,sess.CancelReasonID
--,sess.CancelReasonName
,CancellationReasonKey = ISNULL(cancel.pkTheatreCancelReasonKey,-1)
,SessionPriority = isnull(sess.Priority,-1)
--,sess.CaseType
,CaseTypeKey = ISNULL(ct.pkTheatreCaseTypeKey,-1)
--,pt.ScheduledDateTime
,ScheduledDateTimeDateKey = ISNULL(ptschdate.pkDateKey,-1)
,ScheduledDateTimeTimeKey = ISNULL(ptschtime.pkTimeKey,-1)
--,pt.ActualDateTime
,ActualDateTimeDateKey = ISNULL(ptactdate.pkDateKey,-1)
,ActualDateTimeTimeKey = ISNULL(ptacttime.pkTimeKey,-1)
--,pt.PatientReadyForCalling
,PatientReadyForCallingDateKey = ISNULL(ptcalleddate.pkDateKey,-1)
,PatientReadyForCallingTimeKey = ISNULL(ptcalledtime.pkTimeKey,-1)
--,pt.TheatreCallPatient
,TheatreCalledPatientDateKey = ISNULL(pttheatrecalleddate.pkDateKey,-1)
,TheatreCalledPatientTimeKey = ISNULL(pttheatrecalledtime.pkTimeKey,-1)
--,pt.EscortGoneForPatient
,EscortGoneForPatientDateKey = ISNULL(ptescortgonedate.pkDateKey,-1)
,EscortGoneForPatientTimeKey = ISNULL(ptescortgonetime.pkTimeKey,-1)
--,pt.PatientIntoTheatreReception
,PatientIntoTheatreReceptionDateKey = ISNULL(pttotheatrereceptiondate.pkDateKey,-1)
,PatientIntoTheatreReceptionTimeKey = ISNULL(pttotheatrereceptiontime.pkTimeKey,-1)
--,pt.PatientIntoAnaestheticRoom
,PatientIntoAnaestheticRoomDateKey = ISNULL(ptanaestheticroomdate.pkDateKey,-1)
,PatientIntoAnaestheticRoomTimeKey = ISNULL(ptanaestheticroomtime.pkTimeKey,-1)
--,pt.AnaesthetistTreatingPatient
,AnaesthetistTreatingPatientDateKey = ISNULL(anaesthetisttreatingdate.pkDateKey,-1)
,AnaesthetistTreatingPatientTimeKey = ISNULL(anaesthetisttreatingtime.pkTimeKey,-1)
--,pt.PatientReadyForSurgeon
,PatientReadyForSurgeonDateKey = ISNULL(ptreadysurgeondate.pkDateKey,-1)
,PatientReadyForSurgeonTimeKey = ISNULL(ptreadysurgeontime.pkTimeKey,-1)
--,pt.PatientReadyForTheatre
,PatientReadyForTheatreDateKey = ISNULL(ptreadytheatredate.pkDateKey,-1)
,PatientReadyForTheatreTimeKey = ISNULL(ptreadytheatretime.pkTimeKey,-1)
--,pt.StartOfCase
,StartOfCaseDateKey = ISNULL(startofcasedate.pkDateKey,-1)
,StartOfCaseTimeKey = ISNULL(startofcasetime.pkTimeKey,-1)
--,pt.PatientIntoTheatre
,PatientIntoTheatreDateKey = ISNULL(ptintotheatredate.pkDateKey,-1)
,PatientIntoTheatreTimeKey = ISNULL(ptintotheatretime.pkTimeKey,-1)
--,pt.AnaestheticInduction
,AnaestheticInductionDateKey = ISNULL(anaestheticinductiondate.pkDateKey,-1)
,AnaestheticInductionTimeKey = ISNULL(anaestheticinductiontime.pkTimeKey,-1)
--,pt.PatientReadyForProcedure
,PatientReadyForProcedureDateKey = ISNULL(ptreadyprocdate.pkDateKey,-1)
,PatientReadyForProcedureTimeKey = ISNULL(ptreadyproctime.pkTimeKey,-1)
--,pt.StartOfProcedure
,StartOfProcedureDateKey = ISNULL(startofprocdate.pkDateKey,-1)
,StartOfProcedureTimeKey = ISNULL(startofproctime.pkTimeKey,-1)
--,pt.KnifeToUterus
,KnifeToUterusDateKey = ISNULL(knifetouterusdate.pkDateKey,-1)
,KnifeToUterusTimeKey = ISNULL(knifetouterustime.pkTimeKey,-1)
--,pt.SuturingBegun
,SuturingBegunDateKey = ISNULL(suturingdate.pkDateKey,-1)
,SuturingBegunTimeKey = ISNULL(suturingtime.pkTimeKey,-1)
--,pt.EndOfProcedure
,EndOfProcedureDateKey = ISNULL(endofprocdate.pkDateKey,-1)
,EndOfProcedureTimeKey = ISNULL(endofproctime.pkTimeKey,-1)
--,pt.RecoveryRequested
,RecoveryRequestedDateKey = ISNULL(recreqdate.pkDateKey,-1)
,RecoveryRequestedTimeKey = ISNULL(recreqtime.pkTimeKey,-1)
--,pt.PatientLeftTheatre
,PatientLeftTheatreDateKey = ISNULL(ptlefttheatredate.pkDateKey,-1)
,PatientLeftTheatreTimeKey = ISNULL(ptlefttheatretime.pkTimeKey,-1)
--,pt.PatientRecoveryStarted
,PatientRecoveryStartedDateKey = ISNULL(recstartdate.pkDateKey,-1)
,PatientRecoveryStartedTimeKey = ISNULL(recstarttime.pkTimeKey,-1)
--,pt.PatientFitToLeaveRecovery
,PatientFitToLeaveRecoveryDateKey = ISNULL(ptfitleaverecdate.pkDateKey,-1)
,PatientFitToLeaveRecoveryTimeKey = ISNULL(ptfitleaverectime.pkTimeKey,-1)
--,pt.WardNotified
,WardNotifiedDateKey = ISNULL(wardnotifieddate.pkDateKey,-1)
,WardNotifiedTimeKey = ISNULL(wardnotifiedtime.pkTimeKey,-1)
--,pt.PatientLeftRecovery
,PatientLeftRecoveryDateKey = ISNULL(ptleftrecoverydate.pkDateKey,-1)
,PatientLeftRecoveryTimeKey = ISNULL(ptleftrecoverytime.pkTimeKey,-1)
--,pt.PatientIntoDSCWard
,PatientIntoDSCWardDateKey = ISNULL(ptintodscdate.pkDateKey,-1)
,PatientIntoDSCWardTimeKey = ISNULL(ptintodsctime.pkTimeKey,-1)
--,pt.PatientLeftDSCWard
,PatientLeftDSCWardDateKey = ISNULL(ptleftdscdate.pkDateKey,-1)
,PatientLeftDSCWardTimeKey = ISNULL(ptleftdsctime.pkTimeKey,-1)
--,pt.OutcomeMnemonic
--,pt.Outcome
,otc.pkTheatreOutcomesKey
--,del.PatientReadyForCallingDelayReason
,PatientReadyForCallingDelayReasonKey = isnull(patientreadycallingdelay.pkTheatreDelayReasonKey,-1)
,PatientReadyForCallingDelayTime = isnull(del.PatientReadyForCallingDelayTime,0)
--,del.TheatreCallPatientDelayReason
,TheatreCallPatientDelayReasonKey = isnull(theatrecallpatientdelay.pkTheatreDelayReasonKey,-1)
,TheatreCallPatientDelayTime = isnull(del.TheatreCallPatientDelayTime,0)
--,del.EscortGoneForPatientDelayReason
,EscortGoneForPatientDelayReasonKey = isnull(escortpatientdelay.pkTheatreDelayReasonKey,-1)
,EscortGoneForPatientDelayTime = isnull(del.EscortGoneForPatientDelayTime,0)
--,del.PatientIntoTheatreReceptionDelayReason
,PatientIntoTheatreReceptionDelayReasonKey = isnull(patienttheatrereceptiondelay.pkTheatreDelayReasonKey,-1)
,PatientIntoTheatreReceptionDelayTime = isnull(del.PatientIntoTheatreReceptionDelayTime,0)
--,del.PatientIntoAnaestheticRoomDelayReason
,PatientIntoAnaestheticRoomDelayReasonKey = isnull(patientanaestheticreceptiondelay.pkTheatreDelayReasonKey,-1)
,PatientIntoAnaestheticRoomDelayTime = isnull(del.PatientIntoAnaestheticRoomDelayTime,0)
--,del.AnaesthetistTreatingPatientDelayReason
,AnaesthetistTreatingPatientDelayReasonKey = isnull(anaesthetictreatpatientdelay.pkTheatreDelayReasonKey,-1)
,AnaesthetistTreatingPatientDelayTime = isnull(del.AnaesthetistTreatingPatientDelayTime,0)
--,del.PatientReadyForSurgeonDelayReason
,PatientReadyForSurgeonDelayReasonKey = isnull(patientreadysurgeondelay.pkTheatreDelayReasonKey,-1)
,PatientReadyForSurgeonDelayTime = isnull(del.PatientReadyForSurgeonDelayTime,0)
--,del.PatientReadyForTheatreDelayReason
,PatientReadyForTheatreDelayReasonKey = isnull(patientreadytheatredelay.pkTheatreDelayReasonKey,-1)
,PatientReadyForTheatreDelayTime = isnull(del.PatientReadyForTheatreDelayTime,0)
--,del.StartOfCaseDelayReason
,StartOfCaseDelayReasonKey = isnull(startofcasedelay.pkTheatreDelayReasonKey,-1)
,StartOfCaseDelayTime = isnull(del.StartOfCaseDelayTime,0)
--,del.PatientIntoTheatreDelayReason
,PatientIntoTheatreDelayReasonKey = isnull(patientintotheatredelay.pkTheatreDelayReasonKey,-1)
,PatientIntoTheatreDelayTime = isnull(del.PatientIntoTheatreDelayTime,0)
--,del.AnaestheticInductionDelayReason
,AnaestheticInductionDelayReasonKey = isnull(anaestheticinductiondelay.pkTheatreDelayReasonKey,-1)
,AnaestheticInductionDelayTime = isnull(del.AnaestheticInductionDelayTime,0)
--,del.PatientReadyForProcedureDelayReason
,PatientReadyForProcedureDelayReasonKey = isnull(patientreadyprocdelay.pkTheatreDelayReasonKey,-1)
,PatientReadyForProcedureDelayTime = isnull(del.PatientReadyForProcedureDelayTime,0)
--,del.StartOfProcedureDelayReason
,StartOfProcedureDelayReasonKey = isnull(startofprocdelay.pkTheatreDelayReasonKey,-1)
,StartOfProcedureDelayTime = isnull(del.StartOfProcedureDelayTime,0)
--,del.KnifeToUterusDelayReason
,KnifeToUterusDelayReasonKey = isnull(knifetouterusdelay.pkTheatreDelayReasonKey,-1)
,KnifeToUterusDelayTime = isnull(del.KnifeToUterusDelayTime,0)
--,del.SuturingBegunDelayReason
,SuturingBegunDelayReasonKey = isnull(suturingbegundelay.pkTheatreDelayReasonKey,-1)
,SuturingBegunDelayTime = isnull(del.SuturingBegunDelayTime,0)
--,del.EndOfProcedureDelayReason
,EndOfProcedureDelayReasonKey = isnull(endofprocdelay.pkTheatreDelayReasonKey,-1)
,EndOfProcedureDelayTime = isnull(del.EndOfProcedureDelayTime,0)
--,del.RecoveryRequestedDelayReason
,RecoveryRequestedDelayReasonKey = isnull(recoveryrequesteddelay.pkTheatreDelayReasonKey,-1)
,RecoveryRequestedDelayTime = isnull(del.RecoveryRequestedDelayTime,0)
--,del.PatientLeftTheatreDelayReason
,PatientLeftTheatreDelayReasonKey = isnull(patientlefttheatredelay.pkTheatreDelayReasonKey,-1)
,PatientLeftTheatreDelayTime = isnull(del.PatientLeftTheatreDelayTime,0)
--,del.PatientRecoveryStartedDelayReason
,PatientRecoveryStartedDelayReasonKey = isnull(recoverystarteddelay.pkTheatreDelayReasonKey,-1)
,PatientRecoveryStartedDelayTime = isnull(del.PatientRecoveryStartedDelayTime,0)
--,del.PatientFitToLeaveRecoveryDelayReason
,PatientFitToLeaveRecoveryDelayReasonKey = isnull(patientfitleaverecoverydelay.pkTheatreDelayReasonKey,-1)
,PatientFitToLeaveRecoveryDelayTime = isnull(del.PatientFitToLeaveRecoveryDelayTime,0)
--,del.WardNotifiedDelayReason
,WardNotifiedDelayReasonKey = isnull(wardnotifieddelay.pkTheatreDelayReasonKey,-1)
,WardNotifiedDelayTime = isnull(del.WardNotifiedDelayTime,0)
--,del.PatientLeftRecoveryDelayReason
,PatientLeftRecoveryDelayReasonKey = isnull(patientleftrecoverydelay.pkTheatreDelayReasonKey,-1)
,PatientLeftRecoveryDelayTime = isnull(del.PatientLeftRecoveryDelayTime,0)
--,del.PatientIntoDSCWardDelayReason
,PatientIntoDSCWardDelayReasonKey = isnull(patientintodscdelay.pkTheatreDelayReasonKey,-1)
,PatientIntoDSCWardDelayTime = isnull(del.PatientIntoDSCWardDelayTime,0)
--,del.PatientLeftDSCWardDelayReason
,PatientLeftDSCWardDelayReasonKey = isnull(patientleftdscdelay.pkTheatreDelayReasonKey,-1)
,PatientLeftDSCWardDelayTime = isnull(del.PatientLeftDSCWardDelayTime,0)
FROM
DataWarehouseStaging.dbo.tbl_APC_Theatres_Patient_Sessions sess -- Main session information
INNER JOIN DataWarehouseStaging.dbo.tbl_APC_Theatres_Patient_Procedure_Times pt -- Patient procedure times
ON sess.pkSYSSourceSystem = pt.pkSYSSourceSystem
AND sess.pkCaseID = pt.pkCaseID
INNER JOIN DataWarehouseStaging.dbo.tbl_APC_Theatres_Patient_Procedure_Delays del -- Patient delay reasons and times
ON sess.pkSYSSourceSystem = del.pkSYSSourceSystem
AND sess.pkCaseID = del.pkCaseID
-- Now get the rest of the keys
-- Theatres location
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Locations loc
ON sess.TheatreID = loc.TheatreID
-- Clinician
LEFT JOIN DataWarehouse.dbo.Dim_Staff staff
ON sess.SessionClinician = staff.StaffLocalID
-- Session start date
LEFT JOIN DataWarehouse.dbo.Dim_Date startdate
ON sess.StartDate = startdate.ActualDate
-- Session end date
LEFT JOIN DataWarehouse.dbo.Dim_Date enddate
ON sess.EndDate = enddate.ActualDate
-- Session start time
LEFT JOIN DataWarehouse.dbo.Dim_Time starttime
ON sess.StartTime = starttime.ActualTime
-- Session end time
LEFT JOIN DataWarehouse.dbo.Dim_Time endtime
ON sess.EndTime = endtime.ActualTime
-- Session status
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Session_Status stat
ON sess.SessionStatus = stat.SessionStatus
-- Session Cancel Reason
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Cancel_Reason cancel
ON sess.CancelReasonID = cancel.CancelReasonID
-- Session case type
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Case_Type ct
ON sess.CaseType = ct.CaseTypeID
-- Outcome information
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Outcomes otc
ON pt.OutcomeMnemonic = otc.OutcomeMnemonic
AND pt.Outcome = otc.Outcome
-- Scheduled start date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptschdate
ON CAST(pt.ScheduledDateTime AS DATE) = ptschdate.ActualDate
-- Scheduled start time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptschtime
ON CAST(pt.ScheduledDateTime AS TIME) = ptschtime.ActualTime
-- Actual start date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptactdate
ON CAST(pt.ActualDateTime AS DATE) = ptactdate.ActualDate
-- Actual start time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptacttime
ON CAST(pt.ActualDateTime AS TIME) = ptacttime.ActualTime
-- Patient called date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptcalleddate
ON CAST(pt.PatientReadyForCalling AS DATE) = ptcalleddate.ActualDate
-- Patient called time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptcalledtime
ON CAST(pt.PatientReadyForCalling AS TIME) = ptcalledtime.ActualTime
-- Theatre called patient date
LEFT JOIN DataWarehouse.dbo.Dim_Date pttheatrecalleddate
ON CAST(pt.TheatreCallPatient AS DATE) = pttheatrecalleddate.ActualDate
-- Theatre called patient time
LEFT JOIN DataWarehouse.dbo.Dim_Time pttheatrecalledtime
ON CAST(pt.TheatreCallPatient AS TIME) = pttheatrecalledtime.ActualTime
-- Escort gone for patient date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptescortgonedate
ON CAST(pt.EscortGoneForPatient AS DATE) = ptescortgonedate.ActualDate
-- Escort gone for patient time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptescortgonetime
ON CAST(pt.EscortGoneForPatient AS TIME) = ptescortgonetime.ActualTime
-- Patient into theatre reception date
LEFT JOIN DataWarehouse.dbo.Dim_Date pttotheatrereceptiondate
ON CAST(pt.PatientIntoTheatreReception AS DATE) = pttotheatrereceptiondate.ActualDate
-- Patient into theatre reception time
LEFT JOIN DataWarehouse.dbo.Dim_Time pttotheatrereceptiontime
ON CAST(pt.PatientIntoTheatreReception AS TIME) = pttotheatrereceptiontime.ActualTime
-- Patient into anaesthetic room date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptanaestheticroomdate
ON CAST(pt.PatientIntoAnaestheticRoom AS DATE) = ptanaestheticroomdate.ActualDate
-- Patient into anaesthetic room time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptanaestheticroomtime
ON CAST(pt.PatientIntoAnaestheticRoom AS TIME) = ptanaestheticroomtime.ActualTime
-- Anaethetist treating patient date
LEFT JOIN DataWarehouse.dbo.Dim_Date anaesthetisttreatingdate
ON CAST(pt.AnaesthetistTreatingPatient AS DATE) = anaesthetisttreatingdate.ActualDate
-- Anaethetist treating patient time
LEFT JOIN DataWarehouse.dbo.Dim_Time anaesthetisttreatingtime
ON CAST(pt.AnaesthetistTreatingPatient AS TIME) = anaesthetisttreatingtime.ActualTime
-- Patient ready for surgeon date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptreadysurgeondate
ON CAST(pt.PatientReadyForSurgeon AS DATE) = ptreadysurgeondate.ActualDate
-- Patient ready for surgeon time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptreadysurgeontime
ON CAST(pt.PatientReadyForSurgeon AS TIME) = ptreadysurgeontime.ActualTime
-- Patient ready for theatre date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptreadytheatredate
ON CAST(pt.PatientReadyForTheatre AS DATE) = ptreadytheatredate.ActualDate
-- Patient ready for theatre time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptreadytheatretime
ON CAST(pt.PatientReadyForTheatre AS TIME) = ptreadytheatretime.ActualTime
-- Start of case date
LEFT JOIN DataWarehouse.dbo.Dim_Date startofcasedate
ON CAST(pt.StartOfCase AS DATE) = startofcasedate.ActualDate
-- Start of case time
LEFT JOIN DataWarehouse.dbo.Dim_Time startofcasetime
ON CAST(pt.StartOfCase AS TIME) = startofcasetime.ActualTime
-- Patient into theatre date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptintotheatredate
ON CAST(pt.PatientIntoTheatre AS DATE) = ptintotheatredate.ActualDate
-- Patient into theatre time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptintotheatretime
ON CAST(pt.PatientIntoTheatre AS TIME) = ptintotheatretime.ActualTime
-- Anaethetic induction date
LEFT JOIN DataWarehouse.dbo.Dim_Date anaestheticinductiondate
ON CAST(pt.AnaestheticInduction AS DATE) = anaestheticinductiondate.ActualDate
-- Anaethetic induction time
LEFT JOIN DataWarehouse.dbo.Dim_Time anaestheticinductiontime
ON CAST(pt.AnaestheticInduction AS TIME) = anaestheticinductiontime.ActualTime
-- Patient ready for procedure date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptreadyprocdate
ON CAST(pt.PatientReadyForProcedure AS DATE) = ptreadyprocdate.ActualDate
-- Patient ready for procedure time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptreadyproctime
ON CAST(pt.PatientReadyForProcedure AS TIME) = ptreadyproctime.ActualTime
-- Start of procedure date
LEFT JOIN DataWarehouse.dbo.Dim_Date startofprocdate
ON CAST(pt.StartOfProcedure AS DATE) = startofprocdate.ActualDate
-- Start of procedure time
LEFT JOIN DataWarehouse.dbo.Dim_Time startofproctime
ON CAST(pt.StartOfProcedure AS TIME) = startofproctime.ActualTime
-- Knife to uterus date
LEFT JOIN DataWarehouse.dbo.Dim_Date knifetouterusdate
ON CAST(pt.KnifeToUterus AS DATE) = knifetouterusdate.ActualDate
-- Knife to uterus time
LEFT JOIN DataWarehouse.dbo.Dim_Time knifetouterustime
ON CAST(pt.KnifeToUterus AS TIME) = knifetouterustime.ActualTime
-- Suturing begun date
LEFT JOIN DataWarehouse.dbo.Dim_Date suturingdate
ON CAST(pt.SuturingBegun AS DATE) = suturingdate.ActualDate
-- Suturing begun time
LEFT JOIN DataWarehouse.dbo.Dim_Time suturingtime
ON CAST(pt.SuturingBegun AS TIME) = suturingtime.ActualTime
-- End of procedure date
LEFT JOIN DataWarehouse.dbo.Dim_Date endofprocdate
ON CAST(pt.EndOfProcedure AS DATE) = endofprocdate.ActualDate
-- End of procedure time
LEFT JOIN DataWarehouse.dbo.Dim_Time endofproctime
ON CAST(pt.EndOfProcedure AS TIME) = endofproctime.ActualTime
-- Recovery requested date
LEFT JOIN DataWarehouse.dbo.Dim_Date recreqdate
ON CAST(pt.RecoveryRequested AS DATE) = recreqdate.ActualDate
-- Recovery requested time
LEFT JOIN DataWarehouse.dbo.Dim_Time recreqtime
ON CAST(pt.RecoveryRequested AS TIME) = recreqtime.ActualTime
-- Patient left theatre date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptlefttheatredate
ON CAST(pt.PatientLeftTheatre AS DATE) = ptlefttheatredate.ActualDate
-- Patient left theatre time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptlefttheatretime
ON CAST(pt.PatientLeftTheatre AS TIME) = ptlefttheatretime.ActualTime
-- Recovery started date
LEFT JOIN DataWarehouse.dbo.Dim_Date recstartdate
ON CAST(pt.PatientRecoveryStarted AS DATE) = recstartdate.ActualDate
-- Recovery started time
LEFT JOIN DataWarehouse.dbo.Dim_Time recstarttime
ON CAST(pt.PatientRecoveryStarted AS TIME) = recstarttime.ActualTime
-- Patient fit to leave recovery date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptfitleaverecdate
ON CAST(pt.PatientFitToLeaveRecovery AS DATE) = ptfitleaverecdate.ActualDate
-- Patient fit to leave recovery time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptfitleaverectime
ON CAST(pt.PatientFitToLeaveRecovery AS TIME) = ptfitleaverectime.ActualTime
-- Ward notified date
LEFT JOIN DataWarehouse.dbo.Dim_Date wardnotifieddate
ON CAST(pt.WardNotified AS DATE) = wardnotifieddate.ActualDate
-- Ward notified time
LEFT JOIN DataWarehouse.dbo.Dim_Time wardnotifiedtime
ON CAST(pt.WardNotified AS TIME) = wardnotifiedtime.ActualTime
-- Patient left recovery date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptleftrecoverydate
ON CAST(pt.PatientLeftRecovery AS DATE) = ptleftrecoverydate.ActualDate
-- Patient left recovery time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptleftrecoverytime
ON CAST(pt.PatientLeftRecovery AS TIME) = ptleftrecoverytime.ActualTime
-- Patient into DSC date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptintodscdate
ON CAST(pt.PatientIntoDSCWard AS DATE) = ptintodscdate.ActualDate
-- Patient into DSC time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptintodsctime
ON CAST(pt.PatientIntoDSCWard AS TIME) = ptintodsctime.ActualTime
-- Patient left DSC date
LEFT JOIN DataWarehouse.dbo.Dim_Date ptleftdscdate
ON CAST(pt.PatientLeftDSCWard AS DATE) = ptleftdscdate.ActualDate
-- Patient left DSC time
LEFT JOIN DataWarehouse.dbo.Dim_Time ptleftdsctime
ON CAST(pt.PatientLeftDSCWard AS TIME) = ptleftdsctime.ActualTime
-- Patient ready for calling delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientreadycallingdelay
ON del.PatientReadyForCallingDelayReasonID = patientreadycallingdelay.DelayReasonID
-- Theatre call patient delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason theatrecallpatientdelay
ON del.TheatreCallPatientDelayReasonID = theatrecallpatientdelay.DelayReasonID
-- Escort gone for patient delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason escortpatientdelay
ON del.EscortGoneForPatientDelayReasonID = escortpatientdelay.DelayReasonID
-- Patient into theatre reception delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patienttheatrereceptiondelay
ON del.PatientIntoTheatreReceptionDelayReasonID = patienttheatrereceptiondelay.DelayReasonID
-- Patient into theatre anaethetic delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientanaestheticreceptiondelay
ON del.PatientIntoAnaestheticRoomDelayReasonID = patientanaestheticreceptiondelay.DelayReasonID
-- Anaethetist treating patient delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason anaesthetictreatpatientdelay
ON del.AnaesthetistTreatingPatientDelayReason = anaesthetictreatpatientdelay.DelayReasonID
-- Patient ready for surgeon delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientreadysurgeondelay
ON del.PatientReadyForSurgeonDelayReasonID = patientreadysurgeondelay.DelayReasonID
-- Patient ready for theatre delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientreadytheatredelay
ON del.PatientReadyForTheatreDelayReason = patientreadytheatredelay.DelayReasonID
-- Start of case delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason startofcasedelay
ON del.StartOfCaseDelayReasonID = startofcasedelay.DelayReasonID
-- Patient into theatre delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientintotheatredelay
ON del.PatientIntoTheatreDelayReasonID = patientintotheatredelay.DelayReasonID
-- Anaesthetic induction delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason anaestheticinductiondelay
ON del.AnaestheticInductionDelayReasonID = anaestheticinductiondelay.DelayReasonID
-- Patient ready for procedure delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientreadyprocdelay
ON del.PatientReadyForProcedureDelayReasonID = patientreadyprocdelay.DelayReasonID
-- Start of procedure delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason startofprocdelay
ON del.StartOfProcedureDelayReasonID = startofprocdelay.DelayReasonID
-- Knife to uterus delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason knifetouterusdelay
ON del.KnifeToUterusDelayReasonID = knifetouterusdelay.DelayReasonID
-- Suturing begun delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason suturingbegundelay
ON del.SuturingBegunDelayReasonID = suturingbegundelay.DelayReasonID
-- End of procedure delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason endofprocdelay
ON del.EndOfProcedureDelayReasonID = endofprocdelay.DelayReasonID
-- Recovery requested delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason recoveryrequesteddelay
ON del.RecoveryRequestedDelayReasonID = recoveryrequesteddelay.DelayReasonID
-- Patient left theatre delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientlefttheatredelay
ON del.PatientLeftTheatreDelayReasonID = patientlefttheatredelay.DelayReasonID
-- Recovery started delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason recoverystarteddelay
ON del.PatientRecoveryStartedDelayReasonID = recoverystarteddelay.DelayReasonID
-- Patient fit to leave recovery delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientfitleaverecoverydelay
ON del.PatientFitToLeaveRecoveryDelayReasonID = patientfitleaverecoverydelay.DelayReasonID
-- Ward notified delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason wardnotifieddelay
ON del.WardNotifiedDelayReasonID = wardnotifieddelay.DelayReasonID
-- Patient left recovery delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientleftrecoverydelay
ON del.PatientLeftRecoveryDelayReasonID = patientleftrecoverydelay.DelayReasonID
-- Patient into DSC delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientintodscdelay
ON del.PatientIntoDSCWardDelayReasonID = patientintodscdelay.DelayReasonID
-- Patient left DSC delay
LEFT JOIN DataWarehouse.dbo.Dim_APC_Theatres_Delay_Reason patientleftdscdelay
ON del.PatientLeftDSCWardDelayReasonID = patientleftdscdelay.DelayReasonID
As you'll be able to see, I'm referencing the same few tables multiple times (hence the reason for all the comments so I can see which join does what):-
Dim_Date
Dim_Time
Dim_APC_Theatres_Delay_Reason
I'm using those tables to get the keys but from different source fields each time.
I've tried to use an OUTER APPLY to get round the number of joins but I got an error message when I tried.
I'd like to get away from the 88 LEFT JOINs if at all possible.
Anyone any ideas?
November 1, 2017 at 5:45 am
How many unique dimensions are you connecting to?
Also, how large are these dimensions?
Not sure if it would speed things up in your case but, if your dimensions aren't very big, you could create them as CTEs and do all of your joins to those.
November 1, 2017 at 5:54 am
Is it possible to calculate pkDateKey and pkTimeKey from the date / time of your source tables, rather than using joins to Dim_Date / Dim_Time?
If so, then you could encapsulate the calculation within inline table-valued functions referenced in your SELECT.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 1, 2017 at 6:27 am
@Paulo - Dim_Date has 91312 records, Dim_Time has 86401 records and Dim_APC_Theatres_Delay_Reason has 116 records.
I'm not sure about using CTEs but I'll give it a go and see what happens.
@chris-2 - Unfortunately not, unless we do some very odd maths. Our date dimension starts at 01/01/1850 (don't ask!) and runs to 31/12/2099 (again, don't ask).
Our Time dimension has one record very every second of the day.
Both dimensions have an extra record for any unknown values which marks any unknown/blank dates/times as -1.
November 1, 2017 at 6:44 am
richardmgreen1 - Wednesday, November 1, 2017 6:27 AM@Paulo - Dim_Date has 91312 records, Dim_Time has 86401 records and Dim_APC_Theatres_Delay_Reason has 116 records.
I'm not sure about using CTEs but I'll give it a go and see what happens.@chris-2 - Unfortunately not, unless we do some very odd maths. Our date dimension starts at 01/01/1850 (don't ask!) and runs to 31/12/2099 (again, don't ask).
Our Time dimension has one record very every second of the day.
Both dimensions have an extra record for any unknown values which marks any unknown/blank dates/times as -1.
Richard, the start and end dates don't matter so long as the sequence of dates maps exactly to your number sequence with no gaps or extras. The time element is easy.
Can you provide a few dates with their corresponding key? Same with time?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 1, 2017 at 7:03 am
richardmgreen1 - Wednesday, November 1, 2017 6:27 AM@Paulo - Dim_Date has 91312 records, Dim_Time has 86401 records and Dim_APC_Theatres_Delay_Reason has 116 records.
I'm not sure about using CTEs but I'll give it a go and see what happens.@chris-2 - Unfortunately not, unless we do some very odd maths. Our date dimension starts at 01/01/1850 (don't ask!) and runs to 31/12/2099 (again, don't ask).
Our Time dimension has one record very every second of the day.
Both dimensions have an extra record for any unknown values which marks any unknown/blank dates/times as -1.
Just curious how a time dimension table can have more records (although only one more) than there are seconds in a day? A day has exactly 86,400 seconds in it. The valid times stretch from 00:00:00 to 23:59:59 using hh:mm:ss notation. If you have 86,401 records in there, what is that other records value for time of day ? NULL ? If there's a use case for that additional record, I'd be interested in knowing what it is...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 1, 2017 at 7:21 am
A day has exactly 86,400 seconds in it. The valid times stretch from 00:00:00 to 23:59:59 using hh:mm:ss notation. If you have 86,401 records in there, what is that other records value for time of day ?
He said he's got an additional value for the unknown bucket. This is the recommended practice, so is no surprise.
As for the number of joins, 88 is a lot. But if you have 88 different dimensions, that may be the way that it is. I assume that many of these are actually date and time pairs. These should be separate. I have 20 something LEFT JOINS on some of my fact table loads. That's also a lot, although maybe not as many as I thought based on your experience. My advice would be don't avoid the LEFT JOINS just because you have a lot of them. Do review your fact and dimension table design. Per Kimball, a dimension count beyond a certain number (can't remember exactly but around 20 to 30) becomes unwieldy for the users. That's not a hard and fast rule. I do consolidate smaller dimensions into larger "junk dimensions." But in your case that doesn't seem to be an option.
November 1, 2017 at 8:24 am
richardmgreen1 - Wednesday, November 1, 2017 7:50 AM@chris-2 - As requested, I've dropped 100 records from Dim Date and Dim Time into a spreadsheet (hopefully a usable format for you).
Each of the Dims has a record marked "-1" which is a marker for unknown/empty dates/times respectively.
Thanks Richard. Here you are:
DECLARE @dt DATETIME = CONVERT(DATE,'16/08/2017',103)
SELECT @dt, DATEDIFF(DAY,0-18263,@dt)
DECLARE @TM TIME(0) = CAST('2017-11-01 00:01:24' AS TIME(0))
SELECT @TM, DATEDIFF(SECOND,0,@TM)+1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 1, 2017 at 8:44 am
relatively slow (it takes 10 minutes to do a full run).
If that's really the full run time, that's very fast for a data warehouse ETL. I've seen some full runs take hours.
November 1, 2017 at 9:27 am
sgmunson - Wednesday, November 1, 2017 7:03 AMJust curious how a time dimension table can have more records (although only one more) than there are seconds in a day? A day has exactly 86,400 seconds in it. The valid times stretch from 00:00:00 to 23:59:59 using hh:mm:ss notation. If you have 86,401 records in there, what is that other records value for time of day ? NULL ? If there's a use case for that additional record, I'd be interested in knowing what it is...
Leap Seconds. --edit-- thinking about it, particularly this week, daylight savings time / end of British Summer Time... 25 hours in a day. --end-edit--
Not that SQL Server seems to give a stuff about them... but, in theory, it should mean that, say, 23:59:60 on 2016-12-31 was a valid date/time.
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 1, 2017 at 1:05 pm
RonKyle - Wednesday, November 1, 2017 7:21 AMA day has exactly 86,400 seconds in it. The valid times stretch from 00:00:00 to 23:59:59 using hh:mm:ss notation. If you have 86,401 records in there, what is that other records value for time of day ?
He said he's got an additional value for the unknown bucket. This is the recommended practice, so is no surprise.
As for the number of joins, 88 is a lot. But if you have 88 different dimensions, that may be the way that it is. I assume that many of these are actually date and time pairs. These should be separate. I have 20 something LEFT JOINS on some of my fact table loads. That's also a lot, although maybe not as many as I thought based on your experience. My advice would be don't avoid the LEFT JOINS just because you have a lot of them. Do review your fact and dimension table design. Per Kimball, a dimension count beyond a certain number (can't remember exactly but around 20 to 30) becomes unwieldy for the users. That's not a hard and fast rule. I do consolidate smaller dimensions into larger "junk dimensions." But in your case that doesn't seem to be an option.
I still don't see the use case. NULL is NULL. An "unknown" bucket doesn't have a lot of meaning beyond knowing how much of your data has that characteristic, but you don't need a dimension table join for that purpose, and as you can't join on a NULL value anyway, I fail to see the point. Care to provide the logic behind the idea?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 1, 2017 at 1:24 pm
I still don't see the use case. NULL is NULL. An "unknown" bucket doesn't have a lot of meaning beyond knowing how much of your data has that characteristic, but you don't need a dimension table join for that purpose, and as you can't join on a NULL value anyway, I fail to see the point. Care to provide the logic behind the idea?
All dimension should have an unknown bucket, or use the built in unknown member, or have some way of identifying records that are outside of the expected parameters. While some of my unknown buckets never get used, the one associated with my work order completed date gets used a lot because if the completed date is before the created date or missing (but the status is completed) or more than one day in the future, it gets assigned to the unknown bucket. Time works the same way. As a practical matter, the time is never incorrectly formatted due to data types. But if it's missing and it shouldn't be or it's midnight (there's no way this time or any time close to that could be legitimate in my case) then it gets bucketed to unknown.
I have tried to make use of the inherent unknown member, but it seems kludgy to me in its practical application. Not that I'd be closed if someone could show me how to make it effectively work, as it would have the advantage of always being at the bottom of the list. I've read it's not as high performing as an actual unknown bucket, but that could mean nothing in human terms.
I've also seen unknown codes combined with the word Unknown, as in Unkown Code: XYZ. This does have the advantage of identifying what is unknown, though there's more to implement.
November 1, 2017 at 1:25 pm
ThomasRushton - Wednesday, November 1, 2017 9:27 AMsgmunson - Wednesday, November 1, 2017 7:03 AMJust curious how a time dimension table can have more records (although only one more) than there are seconds in a day? A day has exactly 86,400 seconds in it. The valid times stretch from 00:00:00 to 23:59:59 using hh:mm:ss notation. If you have 86,401 records in there, what is that other records value for time of day ? NULL ? If there's a use case for that additional record, I'd be interested in knowing what it is...
Leap Seconds. --edit-- thinking about it, particularly this week, daylight savings time / end of British Summer Time... 25 hours in a day. --end-edit--
Not that SQL Server seems to give a stuff about them... but, in theory, it should mean that, say, 23:59:60 on 2016-12-31 was a valid date/time.
There's a darn good reason to ignore "leap time". If you tried to keep track of it, in the long run, it would be rather problematic to do. Every duration calculation would suddenly have to take that into account. Not particularly practical. Similarly, the "extra hour" introduced by daylight savings time is not really extra, but instead, borrowed from ~6 months earlier. However, short-term duration calculations CAN and DO get messy during those times. Which is actually a pretty good reason to schedule maintenance during those windows for the simpler fixes and occasional regular server re-boots. Frankly, I'd really rather see the DST thing disappear entirely. Nothing but a PITA, and that's being generous.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply