TransformEntityDimResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformEntityDimResource" Accessibility="Public" FileName="TransformEntityDim.sql"/>

File Content: TransformEntityDim.sql

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


BEGIN


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


END





DECLARE @Statement NVARCHAR(MAX)








SET @Statement=


N'ALTER Procedure [dbo].[TransformEntityDimProc](@WaterMark xml)


as


begin


set nocount on





    declare @utc DATETIME = GETUTCDATE()


      declare @source1WM datetime


            ,@source1MaxWM datetime


            ,@BatchId int


            ,@Inserted int


            ,@err int


            ,@Updated int





      declare @nullguid uniqueidentifier


      set @nullguid = ''00000000-0000-0000-0000-000000000000''





    EXEC dbo.InitializeTransform


        @transformName          = ''TransformEntityDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_System$Entity'',


        @waterMarkType          = ''DateTime'',


        @sourceTableName        = ''inbound.BaseManagedEntity'',


        @utc                    = @utc,


        @source1WM              = @source1WM OUTPUT,


        @source1MaxWM           = @source1MaxWM OUTPUT,


        @batchId                = @batchId OUTPUT





      EXEC @err = dbo.InferDatasourceDimProc


                @sourceTableName    = ''inbound.BaseManagedEntity'',


                @columnName         = ''DataSourceId'',


                @filterColumnName   = ''DWTimeStamp'',


                @minTimeStamp       = @source1WM,


                @maxTimeStamp       = @source1MaxWM,


                @batchId            = @BatchId





    ;WITH TopRecordFromSource (


            BaseManagedEntityId,


            DisplayName,


            IsDeleted,


            SourceId,


            TimeAdded,


            LastModifiedBy,


            LastModified,


        RowNumber


    ) AS (


    SELECT


            BaseManagedEntityId,


            SUBSTRING(DisplayName, 1, 4000) AS DisplayName,


            IsDeleted,


            source.DataSourceId as SourceId,


            TimeAdded,


            LastModifiedBy,


            LastModified,


            ROW_NUMBER() OVER (PARTITION BY source.DatasourceId, BaseManagedEntityId ORDER BY DWTimeStamp DESC, LastModified DESC) as RowNumber


    FROM    inbound.BaseManagedEntity source


    LEFT JOIN Staging.EntityTypeExceptionList exList ON


            source.DatasourceId = ISNULL(exList.DatasourceId, source.DatasourceId) -- if sourceid is null, then exclude from all sources


        and source.BaseManagedTypeId = exList.BaseManagedTypeId


    WHERE exList.BaseManagedTypeId IS NULL


        AND source.DWTimestamp >= @source1WM


        AND source.DWTimestamp < @source1MaxWM


    )


    SELECT  *


    INTO    #transformTemp1


    FROM    TopRecordFromSource source


    WHERE   source.RowNumber = 1





    CREATE CLUSTERED INDEX CI0_TxEntityDim ON #transformTemp1 (SourceId, BaseManagedEntityId)





      begin tran


      Update dest


            set


                  DisplayName       = source.[DisplayName],


                  IsDeleted         = source.IsDeleted,


                  TimeAdded         = source.TimeAdded,


                  LastModifiedBy  = source.LastModifiedBy,


                  LastModified      = source.LastModified,


                  UpdatedBatchId  = @BatchId


      from #transformTemp1 source


      inner join dbo.EntityDim dest on(source.BaseManagedEntityId=dest.BaseManagedEntityId)


                        and (source.SourceId = dest.SourceId)





      select @Updated = @@RowCount, @err = @@error





      if(@err<>0)


      begin


            raiserror(''Failed to update EntityDim Dimension'', 16,1)


            return


      end





      insert into dbo.EntityDim(BaseManagedEntityId,DisplayName,IsDeleted,SourceId,TimeAdded,


                                                LastModifiedBy,LastModified,InsertedBatchId,UpdatedBatchId)


      select source.BaseManagedEntityId,


                  source.DisplayName,


            source.IsDeleted,


            source.SourceId,


            source.TimeAdded,


            source.LastModifiedBy,


            source.LastModified,


            @BatchId,


            0


      from #transformTemp1 source


            left join dbo.EntityDim dest on (source.BaseManagedEntityId=dest.BaseManagedEntityId)


                                                      and (source.SourceId = dest.SourceId)


      where dest.BaseManagedEntityId is null and dest.SourceId is null





      select @Inserted=@@RowCount,@err=@@error





      if(@err<>0)


      begin


            raiserror(''Failed to insert into EntityDim Dimension'', 16,1)


            return


      end


      commit tran





    EXEC dbo.UninitializeTransform


        @transformName          = ''TransformEntityDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_System$Entity'',


        @waterMarkType          = ''DateTime'',


        @source1MaxWM           = @source1MaxWM,


        @batchId                = @BatchId,


        @inserted               = @Inserted,


        @updated                = @Updated





cleanup:


      if(OBJECT_ID(''tempdb..#transformTemp1'') is not null) drop table #transformTemp1





      if object_id(''tempdb..#tempTable'') is not null


      begin


            drop table #tempTable


      end





set nocount off


end'





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


BEGIN


	EXEC sp_executesql @Statement


END