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_DimInstitutionalRetentionCohortT2T_Select

exec usp_DimInstitutionalRetentionCohortT2T_Select2

exec usp_DimInstitutionalRetentionCohortY2Y_Select

"exec usp_DimInstitutionalRetentionCohort_Select"

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

/* SQL INSERT into DimInstitutionalRetentionCohort from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimInstitutionalRetentionCohort] ( InstitutionalRetentionCohortAK ,StudentId ,CohortYear ,CohortTerm ,CohortTermTitle ,FirstTerm_FT_PT ,RetentionCohortTypeCode ,RetentionCohortType ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,AgeAtInstitutionalRetentionCohort ,AgeRangeAtInstitutionalRetentionCohort ,RowIsCurrent ,RowStartDate ,RowEndDate ) SELECT InstitutionalRetentionCohortAK ,StudentId ,CohortYear ,CohortTerm ,CohortTermTitle ,FirstTerm_FT_PT ,RetentionCohortTypeCode ,RetentionCohortType ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,AgeAtInstitutionalRetentionCohort ,AgeRangeAtInstitutionalRetentionCohort ,RowIsCurrent ,RowStartDate ,RowEndDate FROM Staged_dbo_DimInstitutionalRetentionCohort__New

" /* SQL INSERT into DimInstitutionalRetentionCohort from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimInstitutionalRetentionCohort] ( InstitutionalRetentionCohortAK ,StudentId ,CohortYear ,CohortTerm ,CohortTermTitle ,FirstTerm_FT_PT ,RetentionCohortTypeCode ,RetentionCohortType ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,AgeAtInstitutionalRetentionCohort ,AgeRangeAtInstitutionalRetentionCohort ,RowIsCurrent ,RowStartDate ,RowEndDate ) SELECT InstitutionalRetentionCohortAK ,StudentId ,CohortYear ,CohortTerm ,CohortTermTitle ,FirstTerm_FT_PT ,RetentionCohortTypeCode ,RetentionCohortType ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,AgeAtInstitutionalRetentionCohort ,AgeRangeAtInstitutionalRetentionCohort ,RowIsCurrent ,RowStartDate ,RowEndDate FROM Staged_dbo_DimInstitutionalRetentionCohort__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].[DimInstitutionalRetentionCohort] SET InstitutionalRetentionCohortAK = SCD1.InstitutionalRetentionCohortAK, StudentId = SCD1.StudentId, CohortYear = SCD1.CohortYear, CohortTerm = SCD1.CohortTerm, CohortTermTitle = SCD1.CohortTermTitle, FirstTerm_FT_PT = SCD1.FirstTerm_FT_PT, RetentionCohortTypeCode = SCD1.RetentionCohortTypeCode, RetentionCohortType = SCD1.RetentionCohortType, ProgramCode = SCD1.ProgramCode, Program = SCD1.Program, ProgramAdmitStatus = SCD1.ProgramAdmitStatus, AwardTypeCode = SCD1.AwardTypeCode, AwardType = SCD1.AwardType, AgeAtInstitutionalRetentionCohort = SCD1.AgeAtInstitutionalRetentionCohort, AgeRangeAtInstitutionalRetentionCohort = SCD1.AgeRangeAtInstitutionalRetentionCohort FROM [EnterpriseDW].[dbo].[DimInstitutionalRetentionCohort] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimInstitutionalRetentionCohort__Type1Update] AS SCD1 ON Dim.InstitutionalRetentionCohortAK = SCD1.InstitutionalRetentionCohortAK

" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimInstitutionalRetentionCohort] SET InstitutionalRetentionCohortAK = SCD1.InstitutionalRetentionCohortAK, StudentId = SCD1.StudentId, CohortYear = SCD1.CohortYear, CohortTerm = SCD1.CohortTerm, CohortTermTitle = SCD1.CohortTermTitle, FirstTerm_FT_PT = SCD1.FirstTerm_FT_PT, RetentionCohortTypeCode = SCD1.RetentionCohortTypeCode, RetentionCohortType = SCD1.RetentionCohortType, ProgramCode = SCD1.ProgramCode, Program = SCD1.Program, ProgramAdmitStatus = SCD1.ProgramAdmitStatus, AwardTypeCode = SCD1.AwardTypeCode, AwardType = SCD1.AwardType, AgeAtInstitutionalRetentionCohort = SCD1.AgeAtInstitutionalRetentionCohort, AgeRangeAtInstitutionalRetentionCohort = SCD1.AgeRangeAtInstitutionalRetentionCohort FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].

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