|
|
||||||
|
|
||||||
Workflow Screenshot
Properties
| Property | Value |
| Name | FactStudentGradesLoad |
| Description | This package will extract the Enrollment Information for all the Active Terms. One row per student per term per class will be created if the Student was enrolled on the day for which the package is executed. All the data in this Fact table will be removed during each execution and will be re-created for the current day. |
| Checkpoint File Name | |
| Checkpoint Usage | Never |
| Check Signature On Load | True |
| Creation Date | Tuesday, May 08, 2012 4:11 PM |
| Creator Computer Name | SCOWAN |
| Creator Name | SCOWAN\hetgroup |
| Debug Mode | False |
| Delay Validation | False |
| Disable | False |
| Disable Event Handlers | False |
| Enable Configurations | True |
| Encrypt Checkpoints | False |
| Fail Package On Failure | False |
| Fail Parent On Failure | False |
| Forced Execution Value | 0 |
| Force Execution Value | None |
| Description | False |
| ID | 916b800f-9cdf-4778-bcfe-595b4190f705 |
| Interactive Mode | False |
| Is Default Locale ID | False |
| Isolation Level | Serializable |
| Locale ID | 1033 |
| Logging Mode | UseParentSetting |
| Max Concurrent Executables | -1 |
| Maximum Error Count | 1 |
| Offline Mode | False |
| Package Type | DTSDesigner100 |
| Protection Level | DontSaveSensitive |
| Save Checkpoints | False |
| Suppress Configuration Warnings | False |
| Suspend Required | False |
| Transaction Option | Supported |
| Update Objects | False |
| Version Build | 307 |
| Version Comments | |
| Version Guid | 3992ee86-a8d5-44dc-8c61-530aa2591bc8 |
| Version Major | 1 |
| Version Minor | 0 |
Executables
| Name | Type | Description |
Data Flow Task |
Data Flow Task |
|
Data Flow Task |
Data Flow Task |
|
Task |
Execute SQL Task |
|
Task |
Execute SQL Task |
Connection Managers
| Name | Description |
Log Providers
| Name | Description |
User Defined Variables
| Name | Value | Expression | Description |
EnterpriseDW |
|||
-- Get grade records between the begin and end dates supplied to this routine.
SELECT AH.[STUDENT_ID] AS StudentId
,CAST(RTRIM(SUBSTRING([CLASS_KEY],12,6)) as Varchar(6)) AS Term
,[REF_NUM] AS RefNum
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'SUCCESS' THEN 1
ELSE 0
END) as GradeSuccess
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'MARGINAL' THEN 1
ELSE 0
END) as GradeMarginal
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'FAIL' THEN 1
ELSE 0
END) as GradeFail
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'WITHDRAW' THEN 1
ELSE 0
END) as GradeWithdrawal
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'OTHER' THEN 1
ELSE 0
END) as GradeOther
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'A' THEN 1
ELSE 0
END) as GradeA
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'B' THEN 1
ELSE 0
END) as GradeB
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'C' THEN 1
ELSE 0
END) as GradeC
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'D' THEN 1
ELSE 0
END) as GradeD
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'F' THEN 1
ELSE 0
END) as GradeF
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'W' THEN 1
ELSE 0
END) as GradeW
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'S' THEN 1
ELSE 0
END) as GradeS
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'P' THEN 1
ELSE 0
END) as GradeP
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'U' THEN 1
ELSE 0
END) as GradeU
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'N' THEN 1
ELSE 0
END) as GradeN
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'X' THEN 1
ELSE 0
END) as GradeX
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'I' THEN 1
ELSE 0
END) as GradeI
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'A' OR SUBSTRING(GRADE,1,1) = 'B' OR SUBSTRING(GRADE,1,1) = 'C' OR
SUBSTRING(GRADE,1,1) = 'D' OR SUBSTRING(GRADE,1,1) = 'F' OR SUBSTRING(GRADE,1,1) = 'W' OR
SUBSTRING(GRADE,1,1) = 'S' OR SUBSTRING(GRADE,1,1) = 'P' OR SUBSTRING(GRADE,1,1) = 'U' OR
SUBSTRING(GRADE,1,1) = 'N' OR SUBSTRING(GRADE,1,1) = 'X' OR SUBSTRING(GRADE,1,1) = 'I' THEN 0
ELSE 1
END) as GradeByLetterOther
,CASE
WHEN outAH.CourseAttempt = 1 THEN 1
ELSE 0
END AS Attempt1
,CASE
WHEN outAH.CourseAttempt = 2 THEN 1
ELSE 0
END AS Attempt2
,CASE
WHEN outAH.CourseAttempt >= 2 THEN 1
ELSE 0
END AS Attempt2OrMore
,CASE
WHEN outAH.CourseAttempt >= 3 THEN 1
ELSE 0
END AS Attempt3OrMore
FROM [dbo].[ST_ACDMC_HIST_ARCH_A] AH
/* Get Grade Rank Table record */
LEFT OUTER JOIN UTL_CODE_TABLE graderank
on (
graderank.TABLE_NAME = 'GRADE-RANK' AND
graderank.CODE = AH.GRADE AND
graderank.[STATUS] = 'A'
)
/* Get grade rank table attribute */
LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC gradeCategory
ON (
graderank.ISN_UTL_CODE_TABLE = gradeCategory.ISN_UTL_CODE_TABLE
AND gradeCategory.cnxarraycolumn = 4 /* Fifth sub-attribute of table */
)
/* Determine attempt number */
LEFT OUTER JOIN
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY STUDENT_ID, SUBSTRING(CLASS_KEY,2,10)
Order by STUDENT_ID, SUBSTRING(CLASS_KEY,2,10), SUBSTRING(CLASS_KEY,12,6), SUBSTRING(SESS_END_BEG_DT,9,8), REF_NUM
) AS CourseAttempt
,STUDENT_ID
,REF_NUM AS RefNum
FROM ST_ACDMC_HIST_ARCH_A
) outAH
ON
(
AH.STUDENT_ID = outAH.STUDENT_ID
AND AH.REF_NUM = outAH.RefNum
)
WHERE AH.[REF_NUM] IS NOT NULL
AND AH.[REF_NUM] != 0
AND AH.[RowUpdatedOn] > 'Jan 1 1900 12:00AM'
AND AH.[RowUpdatedOn] <= 'Mar 4 2016 10:33AM'
|
"
-- Get grade records between the begin and end dates supplied to this routine.
SELECT AH.[STUDENT_ID] AS StudentId
,CAST(RTRIM(SUBSTRING([CLASS_KEY],12,6)) as Varchar(6)) AS Term
,[REF_NUM] AS RefNum
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'SUCCESS' THEN 1
ELSE 0
END) as GradeSuccess
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'MARGINAL' THEN 1
ELSE 0
END) as GradeMarginal
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'FAIL' THEN 1
ELSE 0
END) as GradeFail
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'WITHDRAW' THEN 1
ELSE 0
END) as GradeWithdrawal
,(CASE
WHEN gradeCategory.FIELD_VALUE = 'OTHER' THEN 1
ELSE 0
END) as GradeOther
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'A' THEN 1
ELSE 0
END) as GradeA
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'B' THEN 1
ELSE 0
END) as GradeB
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'C' THEN 1
ELSE 0
END) as GradeC
,(CASE
WHEN SUBSTRING(GRADE,1,1) = 'D' THEN 1
ELSE |
||
exec usp_FactActiveStudentGrades 'Jan 1 1900 12:00AM' , 'Mar 4 2016 10:33AM' , 'Y' |
"exec usp_FactActiveStudentGrades '" + @[User::IncrementalBeginDtTm] + "' , '" + @[User::IncrementalEndDtTm] + "' , '" + @[User::IsFullLoad] + "'" |
||
Jan 1 1900 12:00AM |
|||
Mar 4 2016 10:33AM |
|||
Y |
|||
UPDATE EnterpriseDW.dbo.FactStudentGrades
SET StudentSK = obs_std.NewStudentSK
FROM EnterpriseDW.dbo.FactStudentGrades fct
INNER JOIN EnterpriseDW.dbo.DimStudent ds
ON (fct.StudentSK = ds.StudentSK)
INNER JOIN dbo.Obsolete_DimStudent obs_std
ON (obs_std.OldStudentSK = ds.StudentSK);
|
"
UPDATE " + @[User::EDWDBName] + ".dbo.FactStudentGrades
SET StudentSK = obs_std.NewStudentSK
FROM " + @[User::EDWDBName] + ".dbo.FactStudentGrades fct
INNER JOIN " + @[User::EDWDBName] + ".dbo.DimStudent ds
ON (fct.StudentSK = ds.StudentSK)
INNER JOIN dbo.Obsolete_DimStudent obs_std
ON (obs_std.OldStudentSK = ds.StudentSK);
"
|
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
