TransformCloudServicesVMCostSettingsSnapshotFactResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformCloudServicesVMCostSettingsSnapshotFactResource" Accessibility="Public" FileName="TransformCloudServicesVMCostSettingsSnapshotFact.sql"/>

File Content: TransformCloudServicesVMCostSettingsSnapshotFact.sql

USE [DWRepository]


GO





/****** Object:  StoredProcedure [dbo].[TransformCloudServicesVMCostSettingsSnapshotFactProc]    Script Date: 11/22/2011 16:52:15 ******/


SET ANSI_NULLS ON


GO





SET QUOTED_IDENTIFIER ON


GO





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


BEGIN


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


END


GO





ALTER PROCEDURE [dbo].[TransformCloudServicesVMCostSettingsSnapshotFactProc](@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,


            @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     = 'TransformCloudServicesVMCostSettingsSnapshotFactProc'


    SELECT  @source1MaxWM       = @utc,


            @startTime          = @utc


    BEGIN TRY


    


    EXEC dbo.InitializeTransform


        @transformName          = 'TransformCloudServicesVMCostSettingsSnapshotFactProc',


        @transformTemplateType  = 'Manual',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = 'MTV_ServiceManager$CloudServices$VMCostSettings',


        @waterMarkType          = 'DateTime',


        @sourceTableName        = 'inbound.MTV_ServiceManager$CloudServices$VMCostSettings',


        @utc                    = @utc,


        @isOneToOneCardinality  = 0,


        @factGrain              = Daily,


        @propertiesList         = '',


        @collapseProperties     = 1,


        @source1WM              = @source1WM OUTPUT,


        @source1MaxWM           = @source1MaxWM OUTPUT,


        @batchId                = @batchId OUTPUT


    


    WHILE(@canContinue = 1)


    BEGIN


        SELECT @task = 'Initializing Transform loop'





        EXEC dbo.InitializeTransformLoop


            @transformName          = 'TransformCloudServicesVMCostSettingsSnapshotFactProc',


            @transformTemplateType  = 'Manual',


            @sourceTableName        = 'inbound.MTV_ServiceManager$CloudServices$VMCostSettings',


            @utc                    = @utc,


            @targetClassCheck       = 'AND source.[ServiceManager.CloudServices.VMCostSettings] = 1',


            @batchId                = @batchId,


            @collapseProperties     = 1,


            @source1WM              = @source1WM OUTPUT,


            @source1MaxWM           = @source1MaxWM OUTPUT,


            @rowsToProcess          = @rowsToProcess OUTPUT,


            @loopCount              = @loopCount OUTPUT





        


        -- PRINT N'Start of Body'


        


        IF(@rowsToProcess = 0) BREAK;





        BEGIN TRANSACTION





        SELECT @task = 'Deleting records from source'





        DELETE source


        FROM DWTemp.TransformCloudServicesVMCostSettingsSnapshotFactProc_Source1 source


        INNER JOIN dbo.CloudServicesVMCostSettingsSnapshotFactvw dest ON


            source.LastModified = dest.UpdatedDate








        SELECT @task = 'Inserting into CloudServicesVMCostSettingsSnapshotFact'





        INSERT INTO dbo.CloudServicesVMCostSettingsSnapshotFactvw (


            CloudServicesVirtualMachineDimKey,


            UpdatedDate,


            DateKey,


            PerUnitCPUCostMeasure,


            PerUnitStorageCostMeasure,


            PerUnitMemoryCostMeasure,


            PerDayMiscellaneousCostMeasure,


            InsertedBatchId,


            UpdatedBatchId


        )


        SELECT DISTINCT


            NULL,


            source.[LastModified],


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


            source.[ServiceManager.CloudServices.VMCostSettings!PerUnitCPUCost],


            source.[ServiceManager.CloudServices.VMCostSettings!PerUnitStorageCost],


            source.[ServiceManager.CloudServices.VMCostSettings!PerUnitMemoryCost],


            source.[ServiceManager.CloudServices.VMCostSettings!MiscellaneousCost],


            @BatchId AS InsertedBatchId,


            0 AS UpdatedBatchId


        FROM DWTemp.TransformCloudServicesVMCostSettingsSnapshotFactProc_Source1 source





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





        COMMIT TRANSACTION


         --PRINT N'End of Body'





        EXEC dbo.UninitializeTransformLoop


            @transformName          = 'TransformCloudServicesVMCostSettingsSnapshotFactProc',


            @transformTemplateType  = 'Manual',


            @utc                    = @utc,


            @inserted               = @Inserted,


            @updated                = @Updated,


            @loopCount              = @loopCount,


            @canContinue            = @canContinue OUTPUT





    END -- WHILE(@canContinue = 1)





    EXEC dbo.UninitializeTransform


        @transformName          = 'TransformCloudServicesVMCostSettingsSnapshotFactProc',


        @transformTemplateType  = 'Manual',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = 'MTV_ServiceManager$CloudServices$VMCostSettings',


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


SET NOCOUNT OFF


END