2016-03-04

Configuration Setting from SSIS_Configuration. Contains the day for which the package will be executed. Will be used to calculate the Relative Day.

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.

/* SQL INSERT NEW Rows into DimRelativeDay from DWOperations Temp New Table */ INSERT INTO [EnterpriseDW].[dbo].[DimRelativeDay] ( RelativeDaySK , Term , TermYear , TermTypeCode , TermTitle , RelativeDayNumber , RelativeDay , RelativeDayDate , RelativeDayDateSK , RowIsCurrent , RowStartDate , RowEndDate ,[RelativeDayFullDescription_Sort] ,[RelativeDayFullDescription] ) SELECT RelativeDaySK , Term , TermYear , TermTypeCode , TermTitle , RelativeDayNumber , RelativeDay , RelativeDayDate , RelativeDayDateSK , RowIsCurrent , RowStartDate , RowEndDate ,0 ,'N/A' FROM dbo.Staged_dbo_DimRelativeDay__New

" /* SQL INSERT NEW Rows into DimRelativeDay from DWOperations Temp New Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimRelativeDay] ( RelativeDaySK , Term , TermYear , TermTypeCode , TermTitle , RelativeDayNumber , RelativeDay , RelativeDayDate , RelativeDayDateSK , RowIsCurrent , RowStartDate , RowEndDate ,[RelativeDayFullDescription_Sort] ,[RelativeDayFullDescription] ) SELECT RelativeDaySK , Term , TermYear , TermTypeCode , TermTitle , RelativeDayNumber , RelativeDay , RelativeDayDate , RelativeDayDateSK , RowIsCurrent , RowStartDate , RowEndDate ,0 ,'N/A' FROM dbo.Staged_dbo_DimRelativeDay__New "

This variable contains the SQL statement that will be used to insert new rows into the DimTerm table.

DWOperations

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

/* UPDATE SCD1 in DimRelativeDay from DWOperations SCD1 Temp Table */ UPDATE Dim SET TermYear = SCD1.TermYear , TermTypeCode = SCD1.TermTypeCode , TermTitle = SCD1.TermTitle , RelativeDay = SCD1.RelativeDay , RelativeDayDate = SCD1.RelativeDayDate , RelativeDayDateSK = SCD1.RelativeDayDateSK FROM [EnterpriseDW].[dbo].[DimRelativeDay] as Dim INNER JOIN dbo.Staged_dbo_DimRelativeDay__Type1Update SCD1 ON Dim.Term = SCD1.Term and Dim.RelativeDayNumber = SCD1.RelativeDayNumber

" /* UPDATE SCD1 in DimRelativeDay from DWOperations SCD1 Temp Table */ UPDATE Dim SET TermYear = SCD1.TermYear , TermTypeCode = SCD1.TermTypeCode , TermTitle = SCD1.TermTitle , RelativeDay = SCD1.RelativeDay , RelativeDayDate = SCD1.RelativeDayDate , RelativeDayDateSK = SCD1.RelativeDayDateSK FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimRelativeDay] as Dim INNER JOIN dbo.Staged_dbo_DimRelativeDay__Type1Update SCD1 ON Dim.Term = SCD1.Term and Dim.RelativeDayNumber = SCD1.RelativeDayNumber "

This variable contains the SQL statement that is used to update the changed rows in the DimTerm table

/* Primary SELECT for the Relative Day Dimension */ SELECT CAST( CASE WHEN RelDay.SeqNumber < 0 THEN ((LEFT(Term.TRM_YR,4) * 100 ) + SUBSTRING(Term.TRM_YR,5,1) + (RelDay.SeqNumber * -1 * 1000000)) * -1 ELSE (LEFT(Term.TRM_YR,4) * 100 ) + SUBSTRING(Term.TRM_YR,5,1) + (RelDay.SeqNumber * 1000000) END AS INT ) AS SRCRelativeDaySK ,LTRIM(RTRIM(CAST(Term.TRM_YR AS varchar(6)))) AS SRCTerm ,CAST(LEFT(Term.TRM_YR,4) AS varchar(4)) AS SRCTermYear ,CAST(SUBSTRING(Term.TRM_YR,5,1) AS varchar(3)) AS SRCTermTypeCode ,LTRIM(RTRIM(CAST(Term.TRM_TTL AS varchar(30)))) AS SRCTermTitle ,CAST(RelDay.SeqNumber AS INT) AS SRCRelativeDayNumber ,'Day ' + CAST(RelDay.SeqNumber AS varchar(11)) AS SRCRelativeDay ,CAST(dbo.udf_StringToDate(Sess_BEG_DT) + RelDay.SeqNumber AS DATE) AS SRCRelativeDayDate ,CONVERT (varchar(8),dbo.udf_StringToDate(Sess_BEG_DT) + RelDay.SeqNumber,112) AS SRCRelativeDayDateSK /* Term and Session information exists in the Staged TermSession Table */ FROM dbo.Staged_TermSessionTempTable Term /* Cross Join with the Sequential Numbers Table */ CROSS JOIN Lookup_SequentialNbrs RelDay /* Select the two years of Relative Days */ WHERE (RelDay.SeqNumber >= -365 and RelDay.SeqNumber <= 365) /* Order by Term and Relative Day number for processing */ ORDER BY Term.TRM_YR ,RelDay.SeqNumber

"/* Primary SELECT for the Relative Day Dimension */ SELECT CAST( CASE WHEN RelDay.SeqNumber < 0 THEN ((LEFT(Term.TRM_YR,4) * 100 ) + SUBSTRING(Term.TRM_YR,5,1) + (RelDay.SeqNumber * -1 * 1000000)) * -1 ELSE (LEFT(Term.TRM_YR,4) * 100 ) + SUBSTRING(Term.TRM_YR,5,1) + (RelDay.SeqNumber * 1000000) END AS INT ) AS SRCRelativeDaySK ,LTRIM(RTRIM(CAST(Term.TRM_YR AS varchar(6)))) AS SRCTerm ,CAST(LEFT(Term.TRM_YR,4) AS varchar(4)) AS SRCTermYear ,CAST(SUBSTRING(Term.TRM_YR,5,1) AS varchar(3)) AS SRCTermTypeCode ,LTRIM(RTRIM(CAST(Term.TRM_TTL AS varchar(30)))) AS SRCTermTitle ,CAST(RelDay.SeqNumber AS INT) AS SRCRelativeDayNumber ,'Day ' + CAST(RelDay.SeqNumber AS varchar(11)) AS SRCRelativeDay ,CAST(dbo.udf_StringToDate(Sess_BEG_DT) + RelDay.SeqNumber AS DATE) AS SRCRelativeDayDate ,CONVERT (varchar(8),dbo.udf_StringToDate(Sess_BEG_DT) + RelDay.SeqNumber,112) AS SRCRelativeDayDateSK /* T

This variable contain the SQL statement to extract the data for the Term dimension from the source system.