TransformMonitorDimProcResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformMonitorDimProcResource" Accessibility="Public" FileName="TransformMonitorDimProc.sql"/>

File Content: TransformMonitorDimProc.sql

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformMonitorDimProc')


BEGIN


    EXECUTE ('CREATE PROCEDURE dbo.[TransformMonitorDimProc] @WaterMark xml AS RETURN 1')


END


GO





DECLARE @Statement NVARCHAR(MAX)





SET @Statement =


N'ALTER PROCEDURE [dbo].[TransformMonitorDimProc](@WaterMark XML)


AS BEGIN 


SET NOCOUNT ON 





    DECLARE @utc DATETIME = GETUTCDATE()


    DECLARE @errorNumber INT, @errorSeverity INT, @errorState INT, @errorLine INT, @errorProcedure NVARCHAR(256), @errorMessage NVARCHAR(MAX), @task NVARCHAR(512)


    DECLARE @BatchId INT, @Updated INT = 0, @Inserted INT = 0, @retval INT = -1, @err INT = 0, @startTranCount INT = @@TRANCOUNT, @source1WM DATETIME, @source1MaxWM DATETIME


    DECLARE @rowsToProcess INT = 0, @rejectedRowCount INT = 0, @canContinue BIT = 1, @loopCount INT


    DECLARE @isReconciled BIT = 0





    BEGIN TRY


    EXEC dbo.InitializeTransform


        @transformName          = ''TransformMonitorDimProc'',


        @transformTemplateType  = ''CustomDimension'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Datawarehouse$OM$Monitor'',


        @waterMarkType          = ''DateTime'',


        @sourceTableName        = ''inbound.MonitorStaging'',


        @utc                    = @utc,


        @source1WM              = @source1WM OUTPUT,


        @source1MaxWM           = @source1MaxWM OUTPUT,


        @batchId                = @batchId OUTPUT





    WHILE(@canContinue = 1)


    BEGIN


        SELECT @task = ''Initializing Transform loop''


        EXEC dbo.InitializeTransformLoop


            @transformName          = ''TransformMonitorDimProc'',


            @transformTemplateType  = ''CustomDimension'',


            @sourceTableName        = ''inbound.MonitorStaging'',


            @utc                    = @utc,


            @targetClassCheck       = '''',


            @batchId                = @batchId,


            @source1WM              = @source1WM OUTPUT,


            @source1MaxWM           = @source1MaxWM OUTPUT,


            @rowsToProcess          = @rowsToProcess OUTPUT,


            @loopCount              = @loopCount OUTPUT





        IF(@rowsToProcess = 0) BREAK;





        SELECT @task = ''Deleting extraneous changes - only retaining the most recent change to an entity''


        DELETE source


        FROM DWTemp.TransformMonitorDimProc_Source1 AS source


        INNER JOIN DWTemp.TransformMonitorDimProc_Source1 latest ON


                    source.Id = latest.Id


                AND (@isReconciled = 1 OR source.DatasourceId = latest.DatasourceId)


                AND (source.LastModified < latest.LastModified


                    OR (source.LastModified = latest.LastModified AND source.Id < latest.Id)


                )





        BEGIN TRANSACTION





        SELECT @task = ''Updating Dimension''


        UPDATE dest SET


            [ManagementPackId] = source.ManagementPackId,


            [MonitorDefaultDescription] = CAST(source.[Description] AS NVARCHAR(256)),


            [MonitorDefaultName] = source.[Name],


            [MonitorSystemName] = source.[Name],


            [IsDeleted] = source.[IsDeleted],


            [UpdatedBatchId] = @BatchId


        FROM DWTemp.TransformMonitorDimProc_Source1 source


        INNER JOIN dbo.MonitorDimvw dest ON (source.DatasourceId = dest.SourceId AND source.Id = dest.MonitorGuid)





        SELECT @Updated = @Updated + @@ROWCOUNT





        SELECT @task = ''Inserting into Dimension''


        INSERT INTO dbo.MonitorDimvw (


            [SourceId],


            [MonitorGuid],


            [ManagementPackId],


            [MonitorDefaultDescription],


            [MonitorDefaultName],


            [MonitorSystemName],


            [IsDeleted],


            [InsertedBatchId],


            [UpdatedBatchId],


            [BaseManagedEntityId]


        )


        SELECT DISTINCT


            source.[DatasourceId],


            source.[Id],


            MIN(CAST(source.ManagementPackId AS NVARCHAR(256))),


            MIN(CAST(source.[Description] AS NVARCHAR(256))),


            MIN(source.[Name]),


            MIN(source.[Name]),


            MAX(CAST(ISNULL(source.[IsDeleted], 0) AS TINYINT)),


            @BatchId,


            0,


            source.[Id]


        FROM DWTemp.TransformMonitorDimProc_Source1 AS source


        LEFT JOIN dbo.MonitorDimvw dest ON (source.DatasourceId = dest.SourceId AND source.Id = dest.MonitorGuid)


        WHERE dest.BaseManagedEntityId IS NULL


        GROUP BY source.[DatasourceId], source.[Id]





        SELECT @Inserted = @Inserted + @@ROWCOUNT





        COMMIT TRANSACTION





        EXEC dbo.UninitializeTransformLoop


            @transformName          = ''TransformMonitorDimProc'',


            @transformTemplateType  = ''CustomDimension'',


            @utc                    = @utc,


            @inserted               = @Inserted,


            @updated                = @Updated,


            @loopCount              = @loopCount,


            @canContinue            = @canContinue OUTPUT





    END -- WHILE(@canContinue = 1)





    EXEC dbo.UninitializeTransform


        @transformName          = ''TransformMonitorDimProc'',


        @transformTemplateType  = ''CustomDimension'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Datawarehouse$OM$Monitor'',


        @waterMarkType          = ''DateTime'',


        @source1MaxWM           = @source1MaxWM,


        @batchId                = @BatchId,


        @inserted               = @Inserted,


        @updated                = @Updated





    END TRY


    BEGIN CATCH


    DECLARE @errorFmt       VARCHAR(256)





    SELECT  @errorFmt = N''ErrorNumber="%d" Message="%s" Severity="%d" State="%d" ProcedureName="%s" LineNumber="%d" Task="%s"'',


            @errorNumber = ERROR_NUMBER(), @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY(), @errorState = ERROR_STATE(), @errorLine = ERROR_LINE(), @errorProcedure = ERROR_PROCEDURE() 





    IF(@@TRANCOUNT > @startTranCount) ROLLBACK TRANSACTION


    


    RAISERROR (@errorFmt, 18, @errorState, @errorNumber, @errorMessage, @errorSeverity, @errorState, @errorProcedure, @errorLine, @task)


    RETURN -1


    END CATCH





SET NOCOUNT OFF


END'








IF EXISTS(SELECT * FROM sys.objects WHERE type = 'U' AND name = 'TablePartition' )


BEGIN


	EXEC sp_executesql @Statement


END


GO