FactGlSummaryBalancesLoad
 SQL Delete Duplicate Fact Records that violate the grain
Home  Diagrams  Documents  Images  FactGlSummaryBalancesLoad  Executables  Connection Managers  Log Providers  Variables 
Current Path:  FactGlSummaryBalancesLoad   SQL Delete Duplicate Fact Records that violate the grain
  Properties
Property Value
Name SQL Delete Duplicate Fact Records that violate the grain
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 False
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 {E74EA1B3-BD7A-4225-B7B5-F3DCB1A8562D}
IsDefaultLocaleID True
IsolationLevel 1048576
IsStoredProcedure False
LocaleID English (United States)
LogEntryInfos System.__ComObject
LoggingMode 0
LoggingOptions System.__ComObject
MaximumErrorCount 1
Name SQL Delete Duplicate Fact Records that violate the grain
ParameterBindings Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterBindings
Parent System.__ComObject
ResultSetBindings Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultBindings
ResultSetType 1
SqlStatementSource DELETE FROM dbo.FactGlSummaryBalances
WHERE GlSummarySK IN
    (
    --Returns list of fact table PKs for duplicate records
SELECT FactRecordsToDelete.GlSummarySK
          FROM 
        (
        --Joins subquery with grain violations to Fact table and
        --includes a row numbering based on grain.
        --Includes Fact table PK field.
        --Order by descending GlSummarySK so that RowNumber=1 will be the latest
        --version of the grain record.
        SELECT 
            ROW_NUMBER() OVER 
                (PARTITION BY 
                GrainViolations.GlAccountSK, GrainViolations.DateSK
                ORDER BY fct.GlSummarySK DESC
                ) AS RowNumber
            ,fct.GlSummarySK
            ,GrainViolations.GlAccountSK
        FROM 
            (
            SELECT 
                fct_GV.GlAccountSK
                ,fct_GV.DateSK
            FROM dbo.FactGlSummaryBalances fct_GV
            GROUP BY 
                fct_GV.GlAccountSK, fct_GV.DateSK
            HAVING COUNT(*) > 1
            ) AS GrainViolations
        INNER JOIN dbo.FactGlSummaryBalances fct
            ON 
                (
                fct.GlAccountSK = GrainViolations.GlAccountSK
                and fct.DateSK = GrainViolations.DateSK
                )
        ) AS FactRecordsToDelete
    WHERE FactRecordsToDelete.RowNumber > 1 and GlAccountSK > -1
    )
SqlStatementSourceType 1
StartTime 3/9/2017 5:49:01 PM
StopTime 3/9/2017 5:49:01 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