FactEnrollmentSnapshotLoad
 SQL Remove Duplicate Fact Records
Home  Diagrams  Documents  Images  FactEnrollmentSnapshotLoad  Executables  Connection Managers  Log Providers  Variables 
Current Path:  FactEnrollmentSnapshotLoad   SQL Remove Duplicate Fact Records
  Properties
Property Value
Name SQL Remove Duplicate Fact Records
Description Execute SQL Task
Precedence Executables none
Contrained Executables none
BypassPrepare True
CodePage 1252
Connection EnterpriseDW
CreationName Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
DelayValidation False
Description Execute SQL Task
Disable Expression: @[User::IsFullLoad] == "Y" ? True : False

True
DisableEventHandlers False
EventHandlers System.__ComObject
EventInfos System.__ComObject
ExecutionDuration 0
ExecutionResult 0
ExecutionStatus 1
ExecutionValue
FailPackageOnFailure False
FailParentOnFailure False
ForcedExecutionValue 0
ForceExecutionResult -1
ForceExecutionValue False
ID {809F3266-3FD9-4391-9FE2-FE35D051188F}
IsDefaultLocaleID True
IsolationLevel 1048576
IsStoredProcedure False
LocaleID English (United States)
LogEntryInfos System.__ComObject
LoggingMode 0
LoggingOptions System.__ComObject
MaximumErrorCount 1
Name SQL Remove Duplicate Fact Records
ParameterBindings Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterBindings
Parent System.__ComObject
ResultSetBindings Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultBindings
ResultSetType 1
SqlStatementSource DELETE FROM dbo.FactEnrollmentSnapshot
WHERE EnrollmentSnapshotSK IN
    (
    --Returns list of fact table PKs for duplicate records
    SELECT FactRecordsToDelete.EnrollmentSnapshotSK 
    FROM 
        (
        --Joins subquery with grain violations to Fact table and
        --includes a row numbering based on grain. Includes Fact table
        --PK field
        SELECT 
            ROW_NUMBER() OVER 
                (PARTITION BY 
                GrainViolations.RelativeDaySK
                ,GrainViolations.StudentSK
                ,GrainViolations.CurriculumSK
                ,GrainViolations.TermSK
                ORDER BY fct.EnrollmentSnapshotSK
                ) AS RowNumber
            ,fct.EnrollmentSnapshotSK
            ,GrainViolations.RelativeDaySK
            ,GrainViolations.StudentSK
            ,GrainViolations.CurriculumSK
            ,GrainViolations.TermSK
        FROM 
            (
            --Finds all records that violate grain
            SELECT 
                fct_GV.RelativeDaySK,
                fct_GV.StudentSK, 
                fct_GV.TermSK, 
                fct_GV.CurriculumSK
            FROM dbo.FactEnrollmentSnapshot fct_GV
            INNER JOIN dbo.DimStudent ds
                ON (fct_GV.StudentSK = ds.StudentSK)
            GROUP BY 
                fct_GV.RelativeDaySK,
                fct_GV.StudentSK, 
                fct_GV.TermSK, 
                fct_GV.CurriculumSK
            HAVING COUNT(*) > 1
            ) AS GrainViolations
        INNER JOIN dbo.FactEnrollmentSnapshot fct
            ON 
                (
                fct.RelativeDaySK = GrainViolations.RelativeDaySK AND
                fct.StudentSK = GrainViolations.StudentSK AND
                fct.TermSK = GrainViolations.TermSK AND
                fct.CurriculumSK = GrainViolations.CurriculumSK 
                )
        ) AS FactRecordsToDelete
    WHERE FactRecordsToDelete.RowNumber > 1
    )
SqlStatementSourceType 1
StartTime 3/9/2017 5:47:46 PM
StopTime 3/9/2017 5:47:46 PM
TimeOut 0
TransactionOption 1
VariableDispenser System.__ComObject
Variables System.__ComObject
Version 0
  Variables
Name Value Description
  Event Handlers
Name Description Enabled Fail Package On Failure Fail Parent On Failure
Powered by BI Documenter