EnterpriseDW

Configuration setting in SSIS_Configurations that specifies the name of the Enterprise Data Warehouse.

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.

exec usp_DimStudentSuccessCohort_Select

"exec usp_DimStudentSuccessCohort_Select"

This variable contains the execution string for the Stored Procedure that will retrieve the data from the source system.

/* SQL INSERT into DimStudentSuccessCohort from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimStudentSuccessCohort] ( StudentSuccessCohortAK ,StudentId ,CohortTerm ,CohortTermTitle ,FirstYearTerm ,LastTermCaptured ,LastTermCapturedTitle ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,InitialPrepStatus ,FirstTerm_FT_PT ,RowIsCurrent ,RowStartDate ,RowEndDate ) SELECT StudentSuccessCohortAK ,StudentId ,CohortTerm ,CohortTermTitle ,FirstYearTerm ,LastTermCaptured ,LastTermCapturedTitle ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,InitialPrepStatus ,FirstTerm_FT_PT ,RowIsCurrent ,RowStartDate ,RowEndDate FROM Staged_dbo_DimStudentSuccessCohort__New

" /* SQL INSERT into DimStudentSuccessCohort from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudentSuccessCohort] ( StudentSuccessCohortAK ,StudentId ,CohortTerm ,CohortTermTitle ,FirstYearTerm ,LastTermCaptured ,LastTermCapturedTitle ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,InitialPrepStatus ,FirstTerm_FT_PT ,RowIsCurrent ,RowStartDate ,RowEndDate ) SELECT StudentSuccessCohortAK ,StudentId ,CohortTerm ,CohortTermTitle ,FirstYearTerm ,LastTermCaptured ,LastTermCapturedTitle ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,InitialPrepStatus ,FirstTerm_FT_PT ,RowIsCurrent ,RowStartDate ,RowEndDate FROM Staged_dbo_DimStudentSuccessCohort__New "

This variable contains the string that will be used to insert any new rows into the DimStudentTerm table.

DWOperations_Maintenance

Configuration setting in SSIS_Configurations that specifies the name of the Operations Database.

/* Sample Update Type1 to Dim */ UPDATE [EnterpriseDW].[dbo].[DimStudentSuccessCohort] SET StudentSuccessCohortAK = SCD1.StudentSuccessCohortAK, StudentId = SCD1.StudentId, CohortTerm = SCD1.CohortTerm, CohortTermTitle = SCD1.CohortTermTitle, FirstYearTerm = SCD1.FirstYearTerm, LastTermCaptured = SCD1.LastTermCaptured, LastTermCapturedTitle = SCD1.LastTermCapturedTitle, ProgramCode = SCD1.ProgramCode, Program = SCD1.Program, ProgramAdmitStatus = SCD1.ProgramAdmitStatus, AwardTypeCode = SCD1.AwardTypeCode, AwardType = SCD1.AwardType, InitialPrepStatus = SCD1.InitialPrepStatus, FirstTerm_FT_PT = SCD1.FirstTerm_FT_PT FROM [EnterpriseDW].[dbo].[DimStudentSuccessCohort] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimStudentSuccessCohort__Type1Update] AS SCD1 ON Dim.StudentSuccessCohortAK = SCD1.StudentSuccessCohortAK

" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudentSuccessCohort] SET StudentSuccessCohortAK = SCD1.StudentSuccessCohortAK, StudentId = SCD1.StudentId, CohortTerm = SCD1.CohortTerm, CohortTermTitle = SCD1.CohortTermTitle, FirstYearTerm = SCD1.FirstYearTerm, LastTermCaptured = SCD1.LastTermCaptured, LastTermCapturedTitle = SCD1.LastTermCapturedTitle, ProgramCode = SCD1.ProgramCode, Program = SCD1.Program, ProgramAdmitStatus = SCD1.ProgramAdmitStatus, AwardTypeCode = SCD1.AwardTypeCode, AwardType = SCD1.AwardType, InitialPrepStatus = SCD1.InitialPrepStatus, FirstTerm_FT_PT = SCD1.FirstTerm_FT_PT FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimStudentSuccessCohort] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimStudentSuccessCohort__Type1Update] AS SCD1 ON Dim.Stu

The SQL string that will update any changed rows in the DimStudentTerm dimension.