EnterpriseDW

N/A

If the Enterprise Data Warehouse resides on a different server from the DWStaging and DWoperations databases, a linked server must be defined and the name populated into the Var_EDWLinkedServername. If it resides on the same server, this setting must be set to ?N/A?. This is a configuration setting from SSIS_Configurations.

-- Get add and drop log records between the begin and end dates supplied to this routine. SELECT (CASE WHEN StudentId.STUDENT_ID IS NOT NULL THEN StudentId.STUDENT_ID WHEN PrevStudentLook.STUDENT_ID IS NOT NULL THEN PrevStudentLook.STUDENT_ID ELSE '99999' END) AS STDNT_ID ,[EFF_TRM] AS EFF_TRM ,[REF_NUM] AS REF_NUM ,dbo.udf_StringToDateTime([LOG_DATE],'') AS ActivityDate ,(CASE WHEN LOG_ACTION = 'M' AND TRNSCTN_TY = 'D' THEN 1 -- This is a Drop Action ELSE 0 END) as DropAction ,(CASE WHEN LOG_ACTION = 'A' AND TRNSCTN_TY = 'A' THEN 1 -- This is an Add Action ELSE 0 END) as AddAction FROM [dbo].[ST_STDNT_CLS_LOG] AS ClsLog LEFT JOIN [dbo].[ST_STDNT_A] AS StudentId ON ClsLog.STDNT_ID = StudentId.STUDENT_ID LEFT JOIN ( SELECT PrevStudentId.PREV_STDNT_ID ,Student1.STUDENT_ID FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] AS PrevStudentId JOIN ST_STDNT_A AS Student1 ON Student1.ISN_ST_STDNT_A = PrevStudentId.ISN_ST_STDNT_A ) PrevStudentLook ON ClsLog.STDNT_ID = PrevStudentLook.PREV_STDNT_ID WHERE (([LOG_ACTION] = 'A' AND [TRNSCTN_TY] = 'A') OR ([LOG_ACTION] = 'M' AND [TRNSCTN_TY] = 'D')) AND [REF_NUM] IS NOT NULL AND dbo.udf_StringToDate([LOG_DATE]) >= 'Jan 1 1900 12:00AM' AND dbo.udf_StringToDate([LOG_DATE]) <= 'Mar 4 2016 10:33AM'

" -- Get add and drop log records between the begin and end dates supplied to this routine. SELECT (CASE WHEN StudentId.STUDENT_ID IS NOT NULL THEN StudentId.STUDENT_ID WHEN PrevStudentLook.STUDENT_ID IS NOT NULL THEN PrevStudentLook.STUDENT_ID ELSE '99999' END) AS STDNT_ID ,[EFF_TRM] AS EFF_TRM ,[REF_NUM] AS REF_NUM ,dbo.udf_StringToDateTime([LOG_DATE],'') AS ActivityDate ,(CASE WHEN LOG_ACTION = 'M' AND TRNSCTN_TY = 'D' THEN 1 -- This is a Drop Action ELSE 0 END) as DropAction ,(CASE WHEN LOG_ACTION = 'A' AND TRNSCTN_TY = 'A' THEN 1 -- This is an Add Action ELSE 0 END) as AddAction FROM [dbo].[ST_STDNT_CLS_LOG] AS ClsLog LEFT JOIN [dbo].[ST_STDNT_A] AS StudentId ON ClsLog.STDNT_ID = StudentId.STUDENT_ID LEFT JOIN ( SELECT PrevStudentId.PREV_STDNT_ID ,Student1.STUDENT_ID FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] AS PrevStudentId JOIN ST_STDNT_A AS Student1

SELECT (CASE WHEN StudentId.STUDENT_ID IS NOT NULL THEN StudentId.STUDENT_ID WHEN PrevStudentLook.STUDENT_ID IS NOT NULL THEN PrevStudentLook.STUDENT_ID ELSE '99999' END) AS STDNT_ID ,[EFF_TRM] AS EFF_TRM ,[REF_NUM] AS REF_NUM ,dbo.udf_StringToDateTime([LOG_DATE],'') AS ActivityDate ,(CASE WHEN LOG_ACTION = 'M' AND TRNSCTN_TY = 'D' THEN 1 -- This is a Drop Action ELSE 0 END) as DropAction ,(CASE WHEN LOG_ACTION = 'A' AND TRNSCTN_TY = 'A' THEN 1 -- This is an Add Action ELSE 0 END) as AddAction FROM [dbo].[ST_STDNT_CLS_LOG_ARCH_A] AS ClsLog LEFT JOIN [dbo].[ST_STDNT_A] AS StudentId ON ClsLog.STDNT_ID = StudentId.STUDENT_ID LEFT JOIN ( SELECT PrevStudentId.PREV_STDNT_ID ,Student1.STUDENT_ID FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] AS PrevStudentId JOIN ST_STDNT_A AS Student1 ON Student1.ISN_ST_STDNT_A = PrevStudentId.ISN_ST_STDNT_A ) PrevStudentLook ON ClsLog.STDNT_ID = PrevStudentLook.PREV_STDNT_ID WHERE (([LOG_ACTION] = 'A' AND [TRNSCTN_TY] = 'A') OR ([LOG_ACTION] = 'M' AND [TRNSCTN_TY] = 'D')) AND [REF_NUM] IS NOT NULL AND dbo.udf_StringToDate([LOG_DATE]) >= 'Jan 1 1900 12:00AM' AND dbo.udf_StringToDate([LOG_DATE]) <= 'Mar 4 2016 10:33AM'

" SELECT (CASE WHEN StudentId.STUDENT_ID IS NOT NULL THEN StudentId.STUDENT_ID WHEN PrevStudentLook.STUDENT_ID IS NOT NULL THEN PrevStudentLook.STUDENT_ID ELSE '99999' END) AS STDNT_ID ,[EFF_TRM] AS EFF_TRM ,[REF_NUM] AS REF_NUM ,dbo.udf_StringToDateTime([LOG_DATE],'') AS ActivityDate ,(CASE WHEN LOG_ACTION = 'M' AND TRNSCTN_TY = 'D' THEN 1 -- This is a Drop Action ELSE 0 END) as DropAction ,(CASE WHEN LOG_ACTION = 'A' AND TRNSCTN_TY = 'A' THEN 1 -- This is an Add Action ELSE 0 END) as AddAction FROM [dbo].[ST_STDNT_CLS_LOG_ARCH_A] AS ClsLog LEFT JOIN [dbo].[ST_STDNT_A] AS StudentId ON ClsLog.STDNT_ID = StudentId.STUDENT_ID LEFT JOIN ( SELECT PrevStudentId.PREV_STDNT_ID ,Student1.STUDENT_ID FROM [dbo].[ST_STDNT_A_PREV_STDNT_ID_USED] AS PrevStudentId JOIN ST_STDNT_A AS Student1 ON Student1.ISN_ST_STDNT_A = PrevStudentId.ISN_ST_STDNT_A ) PrevStudentLoo

Jan 1 1900 12:00AM

Mar 4 2016 10:33AM

Y

UPDATE [EnterpriseDW].dbo.FactEnrollmentActivity SET StudentSK = obs_std.NewStudentSK FROM [EnterpriseDW].dbo.FactEnrollmentActivity fct INNER JOIN [EnterpriseDW].dbo.DimStudent ds ON (fct.StudentSK = ds.StudentSK) INNER JOIN dbo.Obsolete_DimStudent obs_std ON (obs_std.OldStudentSK = ds.StudentSK) WHERE ds.RowIsCurrent = 'Y';

" UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) +"[" + @[User::EDWDBName] + "].dbo.FactEnrollmentActivity SET StudentSK = obs_std.NewStudentSK FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentActivity fct INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimStudent ds ON (fct.StudentSK = ds.StudentSK) INNER JOIN dbo.Obsolete_DimStudent obs_std ON (obs_std.OldStudentSK = ds.StudentSK) WHERE ds.RowIsCurrent = 'Y'; "

UPDATE [EnterpriseDW].dbo.FactEnrollmentActivity SET StudentTermSK = NEW_d_StdTrm.StudentTermSK FROM [EnterpriseDW].dbo.FactEnrollmentActivity f_enract INNER JOIN [EnterpriseDW].dbo.DimStudentTerm OLD_d_StdTrm ON (OLD_d_StdTrm.StudentTermSK = f_enract.StudentTermSK) INNER JOIN dbo.Obsolete_DimStudent obs_DimStd ON (obs_DimStd.OldStudentIdAK = OLD_d_StdTrm.StudentId) INNER JOIN [EnterpriseDW].dbo.DimStudentTerm NEW_d_StdTrm ON ( NEW_d_StdTrm.StudentId = obs_DimStd.NewStudentIdAK AND NEW_d_StdTrm.Term = OLD_d_StdTrm.Term ) WHERE OLD_d_StdTrm.RowIsCurrent = 'Y' AND NEW_d_StdTrm.RowIsCurrent = 'Y';

"UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) +"[" + @[User::EDWDBName] + "].dbo.FactEnrollmentActivity SET StudentTermSK = NEW_d_StdTrm.StudentTermSK FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.FactEnrollmentActivity f_enract INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimStudentTerm OLD_d_StdTrm ON (OLD_d_StdTrm.StudentTermSK = f_enract.StudentTermSK) INNER JOIN dbo.Obsolete_DimStudent obs_DimStd ON (obs_DimStd.OldStudentIdAK = OLD_d_StdTrm.StudentId) INNER JOIN " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "[" + @[User::EDWDBName] + "].dbo.DimStudentTerm NEW_d_StdTrm ON ( NEW_d_StdTrm.StudentId = obs_DimStd.NewStudentIdAK AND NEW_d_StdTrm.Term = OLD_d_StdTrm.Term ) WHERE OLD_d_StdTrm.RowIsCurrent = 'Y' AND NEW_d