TransformManagedTypeDimResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformManagedTypeDimResource" Accessibility="Public" FileName="TransformManagedTypeDim.sql"/>

File Content: TransformManagedTypeDim.sql

if not exists (select * from sysobjects where type = 'P' AND name = N'TransformManagedTypeDimProc')


	begin


		execute ('create procedure dbo.[TransformManagedTypeDimProc] @WaterMark xml as return 1')


	end


go





DECLARE @Statement NVARCHAR(MAX)





SET @Statement=


'alter procedure dbo.TransformManagedTypeDimProc (@WaterMark xml)


as


begin	


set nocount on





    DECLARE @utc DATETIME = GETUTCDATE()





  -- Get Metadata


  declare @BatchId int = 0, @Updated int = 0, @Inserted int, @ModuleName nvarchar(128), 


		  @source1WM datetime, @source1MaxWM datetime, @retval int, @err int, @nullguid uniqueidentifier





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





    EXEC dbo.InitializeTransform


        @transformName          = ''TransformManagedTypeDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Datawarehouse$Base$ManagedType'',


        @waterMarkType          = ''DateTime'',


        @sourceTableName        = ''inbound.ManagedType'',


        @utc                    = @utc,


        @source1WM              = @source1WM OUTPUT,


        @source1MaxWM           = @source1MaxWM OUTPUT,


        @batchId                = @batchId OUTPUT





  EXEC @err = dbo.InferDatasourceDimProc


                @sourceTableName    = ''inbound.ManagedType'',


                @columnName         = ''DataSourceId'',


                @filterColumnName   = ''DWTimeStamp'',


                @minTimeStamp       = @source1WM,


                @maxTimeStamp       = @source1MaxWM,


                @batchId            = @BatchId





  merge dbo.ManagedTypeDim AS target


  using (		


			select ManagedTypeId, TypeName, BaseManagedTypeId, ManagementPackId, IsHosted, IsAbstract,


				  IsSingleton, IsSealed, ManagedTypeTableName, ManagedTypeViewName,


				  DatasourceId, TimeAdded, LastModified, ModifiedBy 				


			FROM (select ManagedTypeId, TypeName, BaseManagedTypeId, ManagementPackId, IsHosted, IsAbstract,


						  IsSingleton, IsSealed, ManagedTypeTableName, ManagedTypeViewName,


						  DatasourceId, TimeAdded, LastModified, ModifiedBy,  ROW_NUMBER() OVER ( PARTITION BY ManagedTypeId,  DataSourceId ORDER BY 


					LastModified DESC) AS SeqNum								


					from inbound.ManagedType


					where DWTimestamp >= @source1WM 


					and DWTimestamp < @source1MaxWM) AS SRC					


			where SeqNum = 1


		) as source 


		on (target.ManagedTypeId = source.ManagedTypeId) 


				and (target.SourceId = source.DatasourceId)


  when matched then 		


			update set 


				   TypeName= isnull(source.TypeName,''NA''),


				   BaseManagedTypeId = isnull(source.BaseManagedTypeId, @nullguid),


				   ManagementPackId = source.ManagementPackId,


				   IsHosted = source.IsHosted,


				   IsAbstract = source.IsAbstract,


				   IsSingleton = source.IsSingleton,


				   IsSealed = source.IsSealed,


				   ManagedTypeTableName = isnull(source.ManagedTypeTableName, ''null''),


				   ManagedTypeViewName = isnull(source.ManagedTypeViewName, ''null''),


				   TimeAdded = source.TimeAdded,


				   LastModified = source.LastModified,


				   ModifiedBy = source.ModifiedBy,


				   UpdatedBatchId = @BatchId,


				   @Updated = @Updated + 1


		


  when not matched 


  by target then 


		insert (BaseManagedEntityId, ManagedTypeId, TypeName, BaseManagedTypeId, ManagementPackId, IsHosted, IsAbstract,


				  IsDeleted, DisplayName, IsSingleton, IsSealed,  ManagedTypeTableName, ManagedTypeViewName,


				  SourceId, TimeAdded, LastModified, ModifiedBy, UpdatedBatchId, InsertedBatchId)


		values (source.ManagedTypeId, source.ManagedTypeId, source.TypeName, isnull(source.BaseManagedTypeId, @nullguid), source.ManagementPackId, source.IsHosted, source.IsAbstract,


				  0, '' '', source.IsSingleton, source.IsSealed,  isnull(source.ManagedTypeTableName, ''null''), isnull(source.ManagedTypeViewName, ''null''),


				  source.DatasourceId, source.TimeAdded, source.LastModified, source.ModifiedBy,0, @BatchId);


			





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





  if(@err<>0)


      begin


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


            return


      end





    EXEC dbo.UninitializeTransform


        @transformName          = ''TransformManagedTypeDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Datawarehouse$Base$ManagedType'',


        @waterMarkType          = ''DateTime'',


        @source1MaxWM           = @source1MaxWM,


        @batchId                = @BatchId,


        @inserted               = @Inserted,


        @updated                = @Updated





set nocount off


end'





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


BEGIN


	EXEC sp_executesql @Statement


END


GO








IF object_id (N'dbo.GetFirstLevelConcreteDerivedManagedTypes') IS NOT NULL


    DROP function dbo.GetFirstLevelConcreteDerivedManagedTypes


GO 





DECLARE @Statement1 NVARCHAR(MAX)





SET @Statement1 =


N'


CREATE FUNCTION dbo.GetFirstLevelConcreteDerivedManagedTypes


(


    @ManagedTypeId uniqueidentifier


)


returns table


as


return (


with DerivedTypes(ManagedTypeId, DerivedManagedTypeId, Depth, IsAbstract) as 


(


     select ManagedTypeId as ManagedTypeId


           ,ManagedTypeId as DerivedManagedTypeId 


           ,0 as Depth


           ,IsAbstract


     from dbo.ManagedTypeDim


     where ManagedTypeId = @ManagedTypeId


     UNION ALL


     select a.BaseManagedTypeId as ManagedTypeId


           ,a.ManagedTypeId as DerivedManagedTypeId


           ,b.Depth + 1 as Depth


           ,a.IsAbstract


     from dbo.ManagedTypeDim a


     join DerivedTypes b on a.BaseManagedTypeId=b.DerivedManagedTypeId


     where b.IsAbstract = 1


)


select DerivedManagedTypeId from DerivedTypes a where a.IsAbstract = 0)'





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


BEGIN


	EXEC sp_executesql @Statement1


END


GO