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.

IF 2=3 BEGIN SELECT CAST(0 AS INT) AS EmployeeAK ,CAST('aaa' AS VARCHAR(8)) AS PositionAK ,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK ,CAST(0 AS INT) AS StartDateSK ,CAST(0 AS INT) AS EndDateSK ,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK -- Calculate the average for the OverTime hours over the number of days in the Reporting Period ,CAST(0 AS NUMERIC(9,4)) AS HoursWorked END DECLARE @OverTimeType VARCHAR(4) -- Find out what TimeType to use for OverTime Hours SET @OverTimeType = ( SELECT CAST(DESCRIPTION AS VARCHAR(4)) AS TimeTypeAK FROM UTL_CODE_TABLE WHERE TABLE_NAME = 'DW-PARMS' AND CODE = 'HR-OVERTM' AND STATUS = 'A' ) -- Get the Normal and OverTime hours if we are reporting per Day from Attendance-Day SELECT AttendDay.PERSON_ID_TD AS EmployeeAK ,POSITION_CODE_TD AS PositionAK ,CAST( REPLICATE('0', 9 - LEN(AttendDay.PERSON_ID_TD)) + CAST(AttendDay.PERSON_ID_TD AS VARCHAR(9)) + CAST(AttendDay.POSITION_CODE_TD AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(AttendDay.POSITION_CODE_TD)) + REPLICATE('0', 3 - LEN(AttendDay.ASSIGNMENT_INTERNAL_SEQ)) + CAST(AttendDay.ASSIGNMENT_INTERNAL_SEQ AS VARCHAR(3)) AS VARCHAR(20)) AS AssignmentAK ,StartDate.SundayDate AS StartDateSK ,EndDate.SaturdayDate AS EndDateSK ,CAST( CASE WHEN TimeType.OVERTIME_IND = 1 THEN @OverTimeType ELSE 'NORM' END AS VARCHAR(4)) AS TimeTypeAK ,CAST(ISNULL(AttendDay.HOURS_USED,0) AS NUMERIC(9,4)) AS HoursWorked FROM ATTENDANCE_AND_DAY_A AttendDay -- Find the Sunday before the Reporting Day OUTER APPLY ( SELECT MAX(DateSK) AS SundayDate FROM[EnterpriseDW ].[dbo].[DimDate] GetSun WHERE GetSun.DayOfWeek = 1 AND GetSun.DateSK <= AttendDay.ATTENDANCE_DAY_DATE ) StartDate -- Find the Saturday after the Reporting Day OUTER APPLY ( SELECT MIN(DateSK) AS SaturdayDate FROM [EnterpriseDW ].[dbo].[DimDate] GetSat WHERE GetSat.DayOfWeek = 7 AND GetSat.DateSK >= AttendDay.ATTENDANCE_DAY_DATE ) EndDate LEFT OUTER JOIN TIME_TYPE_A TimeType ON ( TimeType.TIME_TYPE_CODE = AttendDay.TIME_TYPE_CODE ) WHERE AttendDay.ATTENDANCE_DAY_INTERNAL_ID IS NOT NULL AND (AttendDay.TIME_TYPE_CODE = 'NORM' OR TimeType.OVERTIME_IND = 1) AND AttendDay.UNPROCESSED_IND = 0

" IF 2=3 BEGIN SELECT CAST(0 AS INT) AS EmployeeAK ,CAST('aaa' AS VARCHAR(8)) AS PositionAK ,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK ,CAST(0 AS INT) AS StartDateSK ,CAST(0 AS INT) AS EndDateSK ,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK -- Calculate the average for the OverTime hours over the number of days in the Reporting Period ,CAST(0 AS NUMERIC(9,4)) AS HoursWorked END DECLARE @OverTimeType VARCHAR(4) -- Find out what TimeType to use for OverTime Hours SET @OverTimeType = ( SELECT CAST(DESCRIPTION AS VARCHAR(4)) AS TimeTypeAK FROM UTL_CODE_TABLE WHERE TABLE_NAME = 'DW-PARMS' AND CODE = 'HR-OVERTM' AND STATUS = 'A' ) -- Get the Normal and OverTime hours if we are reporting per Day from Attendance-Day SELECT AttendDay.PERSON_ID_TD AS EmployeeAK ,POSITION_CODE_TD AS PositionAK ,CAST( REPLICATE('0', 9 - LEN(AttendDay.PERSON_ID_TD)) + CAST(AttendDay.PERSON_ID_TD AS VARCHAR(9)) +

IF 2=3 BEGIN SELECT CAST(0 AS INT) AS EmployeeAK ,CAST('aaa' AS VARCHAR(8)) AS PositionAK ,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK ,CAST(0 AS INT) AS StartDateSK ,CAST(0 AS INT) AS EndDateSK ,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK -- Calculate the average for the OverTime hours over the number of days in the Reporting Period ,CAST(0 AS NUMERIC(9,4)) AS HoursWorked END SELECT AttendWeek.PERSON_ID AS EmployeeAK ,POSITION_CODE AS PositionAK ,CAST( REPLICATE('0', 9 - LEN(AttendWeek.PERSON_ID)) + CAST(AttendWeek.PERSON_ID AS VARCHAR(9)) + CAST(AttendWeek.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(AttendWeek.POSITION_CODE)) + REPLICATE('0', 3 - LEN(AttendWeek.ASSIGNMENT_INTERNAL_SEQ)) + CAST(AttendWeek.ASSIGNMENT_INTERNAL_SEQ AS VARCHAR(3)) AS VARCHAR(20)) AS AssignmentAK ,StartDate.SundayDate AS StartDateSK ,EndDate.SaturdayDate AS EndDateSK ,CAST('NORM' AS VARCHAR(4)) AS TimeTypeAK -- Calculate the average for the Normal hours over the number of days in the Reporting Period ,CAST(ROUND(ISNULL(AttendWeek.TOTAL_NORMAL_HOURS, 0) / (DATEDIFF(DD, CAST(AttendWeek.ATTENDANCE_START_DATE AS DATE), CAST(AttendWeek.ATTENDANCE_END_DATE AS DATE)) + 1),4) AS NUMERIC(9,4)) AS HoursWorked FROM ATTENDANCE_AND_DAY_A AttendWeek -- Create one record for each day in the Reporting Period LEFT OUTER JOIN [EnterpriseDW ].[dbo].[DimDate] Daily ON ( Daily.DateSK >= AttendWeek.ATTENDANCE_START_DATE AND Daily.DateSK <= AttendWeek.ATTENDANCE_END_DATE ) -- Get the Sunday before each day in the Period OUTER APPLY ( SELECT MAX(DateSK) AS SundayDate FROM [EnterpriseDW].[dbo].[DimDate] GetSun WHERE GetSun.DayOfWeek = 1 AND GetSun.DateSK <= Daily.DateSK ) StartDate -- Get the Saturday after each day in the Period OUTER APPLY ( SELECT MIN(DateSK) AS SaturdayDate FROM [EnterpriseDW].[dbo].[DimDate] GetSat WHERE GetSat.DayOfWeek = 7 AND GetSat.DateSK >= Daily.DateSK ) EndDate WHERE AttendWeek.ATTENDANCE_INTERNAL_ID IS NOT NULL AND AttendWeek.TOTAL_NORMAL_HOURS IS NOT NULL AND AttendWeek.UNPROCESSED_IND = 0

"IF 2=3 BEGIN SELECT CAST(0 AS INT) AS EmployeeAK ,CAST('aaa' AS VARCHAR(8)) AS PositionAK ,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK ,CAST(0 AS INT) AS StartDateSK ,CAST(0 AS INT) AS EndDateSK ,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK -- Calculate the average for the OverTime hours over the number of days in the Reporting Period ,CAST(0 AS NUMERIC(9,4)) AS HoursWorked END SELECT AttendWeek.PERSON_ID AS EmployeeAK ,POSITION_CODE AS PositionAK ,CAST( REPLICATE('0', 9 - LEN(AttendWeek.PERSON_ID)) + CAST(AttendWeek.PERSON_ID AS VARCHAR(9)) + CAST(AttendWeek.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(AttendWeek.POSITION_CODE)) + REPLICATE('0', 3 - LEN(AttendWeek.ASSIGNMENT_INTERNAL_SEQ)) + CAST(AttendWeek.ASSIGNMENT_INTERNAL_SEQ AS VARCHAR(3)) AS VARCHAR(20)) AS AssignmentAK ,StartDate.SundayDate AS StartDateSK ,EndDate.SaturdayDate AS EndDa

This is the SQL statement to extract all the Student Fee Data from the Source System.

IF 2=3 BEGIN SELECT CAST(0 AS INT) AS EmployeeAK ,CAST('aaa' AS VARCHAR(8)) AS PositionAK ,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK ,CAST(0 AS INT) AS StartDateSK ,CAST(0 AS INT) AS EndDateSK ,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK -- Calculate the average for the OverTime hours over the number of days in the Reporting Period ,CAST(0 AS NUMERIC(9,4)) AS HoursWorked END DECLARE @OverTimeType VARCHAR(4) -- Find out what TimeType to use for OverTime Hours SET @OverTimeType = ( SELECT CAST(ISNULL(DESCRIPTION,'N/A') AS VARCHAR(4)) AS TimeTypeAK FROM UTL_CODE_TABLE WHERE TABLE_NAME = 'DW-PARMS' AND CODE = 'HR-OVERTM' AND STATUS = 'A' ) SELECT AttendWeek.PERSON_ID AS EmployeeAK ,POSITION_CODE AS PositionAK ,CAST( REPLICATE('0', 9 - LEN(AttendWeek.PERSON_ID)) + CAST(AttendWeek.PERSON_ID AS VARCHAR(9)) + CAST(AttendWeek.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(AttendWeek.POSITION_CODE)) + REPLICATE('0', 3 - LEN(AttendWeek.ASSIGNMENT_INTERNAL_SEQ)) + CAST(AttendWeek.ASSIGNMENT_INTERNAL_SEQ AS VARCHAR(3)) AS VARCHAR(20)) AS AssignmentAK ,StartDate.SundayDate AS StartDateSK ,EndDate.SaturdayDate AS EndDateSK ,@OverTimeType AS TimeTypeAK -- Calculate the average for the OverTime hours over the number of days in the Reporting Period ,CAST(ROUND(ISNULL(AttendWeek.TOTAL_OVERTIME_HOURS, 0) / (DATEDIFF(DD, CAST(AttendWeek.ATTENDANCE_START_DATE AS DATE), CAST(AttendWeek.ATTENDANCE_END_DATE AS DATE)) + 1),4) AS NUMERIC(9,4)) AS HoursWorked FROM ATTENDANCE_AND_DAY_A AttendWeek LEFT OUTER JOIN [EnterpriseDW].[dbo].[DimDate] Daily ON ( Daily.DateSK >= AttendWeek.ATTENDANCE_START_DATE AND Daily.DateSK <= AttendWeek.ATTENDANCE_END_DATE ) OUTER APPLY ( SELECT MAX(DateSK) AS SundayDate FROM [EnterpriseDW].[dbo].[DimDate] GetSun WHERE GetSun.DayOfWeek = 1 AND GetSun.DateSK <= Daily.DateSK ) StartDate OUTER APPLY ( SELECT MIN(DateSK) AS SaturdayDate FROM [EnterpriseDW].[dbo].[DimDate] GetSat WHERE GetSat.DayOfWeek = 7 AND GetSat.DateSK >= Daily.DateSK ) EndDate WHERE AttendWeek.ATTENDANCE_INTERNAL_ID IS NOT NULL AND AttendWeek.TOTAL_OVERTIME_HOURS IS NOT NULL AND AttendWeek.UNPROCESSED_IND = 0

" IF 2=3 BEGIN SELECT CAST(0 AS INT) AS EmployeeAK ,CAST('aaa' AS VARCHAR(8)) AS PositionAK ,CAST('aaa' AS VARCHAR(20)) AS AssignmentAK ,CAST(0 AS INT) AS StartDateSK ,CAST(0 AS INT) AS EndDateSK ,CAST('aaa' AS VARCHAR(4)) AS TimeTypeAK -- Calculate the average for the OverTime hours over the number of days in the Reporting Period ,CAST(0 AS NUMERIC(9,4)) AS HoursWorked END DECLARE @OverTimeType VARCHAR(4) -- Find out what TimeType to use for OverTime Hours SET @OverTimeType = ( SELECT CAST(ISNULL(DESCRIPTION,'N/A') AS VARCHAR(4)) AS TimeTypeAK FROM UTL_CODE_TABLE WHERE TABLE_NAME = 'DW-PARMS' AND CODE = 'HR-OVERTM' AND STATUS = 'A' ) SELECT AttendWeek.PERSON_ID AS EmployeeAK ,POSITION_CODE AS PositionAK ,CAST( REPLICATE('0', 9 - LEN(AttendWeek.PERSON_ID)) + CAST(AttendWeek.PERSON_ID AS VARCHAR(9)) + CAST(AttendWeek.POSITION_CODE AS VARCHAR(8)) + REPLICATE(' ', 8 - LEN(AttendW

This is the SQL statement to extract all the Archived Student Fee Data from the Source System.

Jan 1 1900 12:00AM

The IncrementalBeginDtTm setting will contain the start date and time for which updates will be extracted. This is a configuration setting from SSIS_Configurations that are set in the Control Package.

Mar 4 2016 10:33AM

The IncrementalEndDtTm setting will contain the End Date and Time for which updates will be extracted. These values will be compared against the RowUpdatedOn column on the tables in the DWStaging database to determine which rows to extract and load into the Enterprise Data Warehouse.

Y

This SSIS_Configuration Setting will determine if a Full Load will be done or not.