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_DimProgramRetentionCohort_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 DimProgramRetentionCohort from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimProgramRetentionCohort] ( ProgramRetentionCohortAK ,StudentId ,CohortTerm ,CohortTermTitle ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,CIPCode ,CIP ,AgeAtProgramRetentionCohort ,AgeRangeAtProgramRetentionCohort ,SwitchedProgramCode ,SwitchedProgram ,SwitchedTerm ,SwitchedTermTitle ,SwitchedAwardTypeCode ,SwitchedAwardType ,SwitchedCIPCode ,SwitchedCIP ,RowIsCurrent ,RowStartDate ,RowEndDate ) SELECT ProgramRetentionCohortAK ,StudentId ,CohortTerm ,CohortTermTitle ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,CIPCode ,CIP ,AgeAtProgramRetentionCohort ,AgeRangeAtProgramRetentionCohort ,SwitchedProgramCode ,SwitchedProgram ,SwitchedTerm ,SwitchedTermTitle ,SwitchedAwardTypeCode ,SwitchedAwardType ,SwitchedCIPCode ,SwitchedCIP ,RowIsCurrent ,RowStartDate ,RowEndDate FROM Staged_dbo_DimProgramRetentionCohort__New

" /* SQL INSERT into DimProgramRetentionCohort from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimProgramRetentionCohort] ( ProgramRetentionCohortAK ,StudentId ,CohortTerm ,CohortTermTitle ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,CIPCode ,CIP ,AgeAtProgramRetentionCohort ,AgeRangeAtProgramRetentionCohort ,SwitchedProgramCode ,SwitchedProgram ,SwitchedTerm ,SwitchedTermTitle ,SwitchedAwardTypeCode ,SwitchedAwardType ,SwitchedCIPCode ,SwitchedCIP ,RowIsCurrent ,RowStartDate ,RowEndDate ) SELECT ProgramRetentionCohortAK ,StudentId ,CohortTerm ,CohortTermTitle ,ProgramCode ,Program ,ProgramAdmitStatus ,AwardTypeCode ,AwardType ,CIPCode ,CIP ,AgeAtProgramRetentionCohort ,AgeRangeAtProgramRetentionCohort ,SwitchedProgramCode ,SwitchedProgram ,SwitchedTerm ,SwitchedTermTitle ,SwitchedAwardTypeCode ,SwitchedAwardType ,Switched

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].[DimProgramRetentionCohort] SET ProgramRetentionCohortAK = SCD1.ProgramRetentionCohortAK, StudentId = SCD1.StudentId, CohortTerm = SCD1.CohortTerm, CohortTermTitle = SCD1.CohortTermTitle, ProgramCode = SCD1.ProgramCode, Program = SCD1.Program, ProgramAdmitStatus = SCD1.ProgramAdmitStatus, AwardTypeCode = SCD1.AwardTypeCode, AwardType = SCD1.AwardType, CIPCode = SCD1.CIPCode, CIP = SCD1.CIP, AgeAtProgramRetentionCohort = SCD1.AgeAtProgramRetentionCohort, AgeRangeAtProgramRetentionCohort = SCD1.AgeRangeAtProgramRetentionCohort, SwitchedProgramCode = SCD1.SwitchedProgramCode, SwitchedProgram = SCD1.SwitchedProgram, SwitchedTerm = SCD1.SwitchedTerm, SwitchedTermTitle = SCD1.SwitchedTermTitle, SwitchedAwardTypeCode = SCD1.SwitchedAwardTypeCode, SwitchedAwardType = SCD1.SwitchedAwardType, SwitchedCIPCode = SCD1.SwitchedCIPCode, SwitchedCIP = SCD1.SwitchedCIP FROM [EnterpriseDW].[dbo].[DimProgramRetentionCohort] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimProgramRetentionCohort__Type1Update] AS SCD1 ON Dim.ProgramRetentionCohortAK = SCD1.ProgramRetentionCohortAK

" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimProgramRetentionCohort] SET ProgramRetentionCohortAK = SCD1.ProgramRetentionCohortAK, StudentId = SCD1.StudentId, CohortTerm = SCD1.CohortTerm, CohortTermTitle = SCD1.CohortTermTitle, ProgramCode = SCD1.ProgramCode, Program = SCD1.Program, ProgramAdmitStatus = SCD1.ProgramAdmitStatus, AwardTypeCode = SCD1.AwardTypeCode, AwardType = SCD1.AwardType, CIPCode = SCD1.CIPCode, CIP = SCD1.CIP, AgeAtProgramRetentionCohort = SCD1.AgeAtProgramRetentionCohort, AgeRangeAtProgramRetentionCohort = SCD1.AgeRangeAtProgramRetentionCohort, SwitchedProgramCode = SCD1.SwitchedProgramCode, SwitchedProgram = SCD1.SwitchedProgram, SwitchedTerm = SCD1.SwitchedTerm, SwitchedTermTitle = SCD1.SwitchedTermTitle, SwitchedAwardTypeCode = SCD1.SwitchedAwardTypeCode, SwitchedAwardType = SCD1.SwitchedAwardType, SwitchedCIPCode = SCD1.SwitchedCIPCod

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