TransformVirtualNicSnapshotFactResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformVirtualNicSnapshotFactResource" Accessibility="Public" HasNullStream="false" FileName="TransformVirtualNicSnapshotFact.sql"/>

File Content: TransformVirtualNicSnapshotFact.sql

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


    BEGIN


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


    END


GO





ALTER PROCEDURE dbo.TransformVirtualNicSnapshotFactProc (@WaterMark xml)


AS


BEGIN    


SET NOCOUNT ON 


SET XACT_ABORT 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,


            @WaterMarkBatchId   INT,


            @Updated            INT         = 0,


            @Inserted           INT         = 0,


            @retval             INT         = -1,


            @err                INT         = 0,


            @startTranCount     INT         = @@TRANCOUNT,


            @source1WM          DATETIME,


            @source1MaxWM       DATETIME,


            @startTime          DATETIME,


            @batchSize          INT,


            @processedRowCount  INT         = 0,


            @rejectedRowCount   INT         = 0,


            @rowsToProcess      INT         = 0


            


    DECLARE @loopCount      INT = 0,


            @canContinue    BIT = 1,


            @rc             INT = 1,


            @level          INT = 1


            


    DECLARE @transformName          SYSNAME     = 'TransformVirtualNicSnapshotFactProc',


            @warehouseEntityName    SYSNAME     = 'MTV_Microsoft$SystemCenter$VirtualMachineManager$Network$VNic',


            @sourceTableName        SYSNAME     = 'inbound.MTV_Microsoft$SystemCenter$VirtualMachineManager$Network$VNic'





    IF OBJECT_ID('DWTemp.TransformVirtualNicSnapshotFactProc_Source2') IS NOT NULL


        DROP TABLE DWTemp.TransformVirtualNicSnapshotFactProc_Source2





    BEGIN TRY


        EXEC dbo.InitializeTransform


            @transformName          = @transformName,


            @transformTemplateType  = 'Manual',


            @waterMark              = @WaterMark,


            @warehouseEntityName    = @warehouseEntityName,


            @waterMarkType          = 'DateTime',


            @sourceTableName        = @sourceTableName,


            @utc                    = @utc,


            @isOneToOneCardinality  = 0,


            @factGrain              = Daily,


            @propertiesList         = '',


            @collapseProperties     = 1,


            @source1WM              = @source1WM OUTPUT,


            @source1MaxWM           = @source1MaxWM OUTPUT,


            @batchId                = @batchId OUTPUT


    


    WHILE(@canContinue = 1)


    BEGIN


        -- 'Step 1: Initializing Transform loop'


        SELECT @task = 'Initializing Transform loop'


        EXEC dbo.InitializeTransformLoop


            @transformName          = @transformName,


            @transformTemplateType  = 'Manual',


            @sourceTableName        = @sourceTableName,


            @utc                    = @utc,


            @targetClassCheck       = '',


            @batchId                = @batchId,


            @collapseProperties     = 1,


            @source1WM              = @source1WM OUTPUT,


            @source1MaxWM           = @source1MaxWM OUTPUT,


            @rowsToProcess          = @rowsToProcess OUTPUT,


            @loopCount              = @loopCount OUTPUT





        IF(@rowsToProcess = 0) BREAK;


        


        -- 'Step 2: Deleting records from source, which already exist in the SnapShotFact with same ModifiedDate'


        SELECT @task = 'Deleting records from source, which already exist in the SnapShotFact with same ModifiedDate'


        DELETE source


        FROM DWTemp.TransformVirtualNicSnapshotFactProc_Source1 source


        INNER JOIN dbo.VirtualNicDimvw d


            ON source.BaseManagedEntityId = d.BaseManagedEntityId


        INNER JOIN dbo.VirtualNicSnapshotFactvw SnapShotFact


            ON source.LastModified = SnapShotFact.LastModifiedDate


            AND d.VirtualNicDimKey = SnapShotFact.VirtualNicDimKey





        -- 'Step 3: Copying Records into a temp Table'


        SELECT @task = 'Copying Records into a temp Table'


        SELECT


            d.VirtualNicDimKey,


            source1.[LastModified],


            source1.[Microsoft.SystemCenter.VirtualMachineManager.Network.VNic!IPv4AddressType] AS IPv4AddressType,


            source1.[Microsoft.SystemCenter.VirtualMachineManager.Network.VNic!IPv6AddressType] AS IPv6AddressType


        INTO DWTemp.TransformVirtualNicSnapshotFactProc_Source2


        FROM DWTemp.TransformVirtualNicSnapshotFactProc_Source1 source1


        INNER JOIN dbo.VirtualNicDimvw d


            ON source1.BaseManagedEntityId = d.BaseManagedEntityId





        -- 'Step 4: Update source to move early arriving records to next batch'


        SELECT @task = 'Update source to move early arriving records to next batch'


        UPDATE source


        SET


            DWTimeStamp = GETUTCDATE(),


            RejectedCount = ISNULL(RejectedCount, 0) + 1,


            BatchId = @BatchId


        FROM inbound.MTV_Microsoft$SystemCenter$VirtualMachineManager$Network$2012$VNic source


        LEFT JOIN dbo.VirtualNicDimvw sourceDim


            ON sourceDim.BaseManagedEntityId = source.BaseManagedEntityId


            AND sourceDim.SourceId = source.DatasourceId


        WHERE source.DWTimestamp >= @source1WM


            AND source.DWTimestamp < @source1MaxWM


            AND sourceDim.BaseManagedEntityId IS NULL





        SELECT @rejectedRowCount = @@ROWCOUNT





        


        BEGIN TRANSACTION





        SELECT @task = 'Step 5: Inserting into VirtualNicSnapshotFact'


        INSERT INTO dbo.VirtualNicSnapshotFactvw (


            VirtualNicDimKey,


            PaddingMeasure,


            IPv4AddressType,


            IPv6AddressType,


            LastModifiedDate,


            DateKey,


            InsertedBatchId,


            UpdatedBatchId


        )


        SELECT DISTINCT


            source2.VirtualNicDimKey,


            0 AS PaddingMeasure,


            source2.IPv4AddressType,


            source2.IPv6AddressType,


            source2.[LastModified],


            CONVERT(nvarchar(8), source2.[LastModified], 112) AS DateKey,


            @BatchId AS InsertedBatchId,


            0 AS UpdatedBatchId


        FROM DWTemp.TransformVirtualNicSnapshotFactProc_Source2 source2


        WHERE source2.[LastModified] IS NOT NULL


            


        SELECT @Inserted = @@ROWCOUNT, @err = @@error





        COMMIT TRANSACTION


        


        --PRINT N'End of Body'





        EXEC dbo.UninitializeTransformLoop


            @transformName          = @transformName,


            @transformTemplateType  = 'Manual',


            @utc                    = @utc,


            @inserted               = @Inserted,


            @updated                = @Updated,


            @loopCount              = @loopCount,


            @canContinue            = @canContinue OUTPUT





    END -- WHILE(@canContinue = 1)





    EXEC dbo.UninitializeTransform


        @transformName          = @transformName,


        @transformTemplateType  = 'Manual',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = @warehouseEntityName,


        @waterMarkType          = 'DateTime',


        @source1MaxWM           = @source1MaxWM,


        @batchId                = @BatchId,


        @inserted               = @Inserted,


        @updated                = @Updated





    IF OBJECT_ID('DWTemp.TransformVirtualNicSnapshotFactProc_Source2') IS NOT NULL


        DROP TABLE DWTemp.TransformVirtualNicSnapshotFactProc_Source2


            


    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 XACT_ABORT OFF


SET NOCOUNT OFF


END