TransformRelationshipTypeDimResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformRelationshipTypeDimResource" Accessibility="Public" FileName="TransformRelationshipTypeDim.sql"/>

File Content: TransformRelationshipTypeDim.sql

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


	begin


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


	end


go





DECLARE @Statement NVARCHAR(MAX)





SET @Statement=


N'alter procedure dbo.TransformRelationshipTypeDimProc (@WaterMark xml)


as


begin	


set nocount on





    DECLARE @utc DATETIME = GETUTCDATE()





  -- Get Metadata


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


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





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





    EXEC dbo.InitializeTransform


        @transformName          = ''TransformRelationshipTypeDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


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


        @waterMarkType          = ''DateTime'',


        @sourceTableName        = ''inbound.RelationshipType'',


        @utc                    = @utc,


        @source1WM              = @source1WM OUTPUT,


        @source1MaxWM           = @source1MaxWM OUTPUT,


        @batchId                = @batchId OUTPUT





  EXEC @err = dbo.InferDatasourceDimProc


                @sourceTableName    = ''inbound.RelationshipType'',


                @columnName         = ''DataSourceId'',


                @filterColumnName   = ''DWTimeStamp'',


                @minTimeStamp       = @source1WM,


                @maxTimeStamp       = @source1MaxWM,


                @batchId            = @BatchId





  merge dbo.RelationshipTypeDim AS target


  using (		


			select RelationshipTypeId, RelationshipTypeName, BaseRelationshipTypeId, ManagementPackId, IsAbstract,


				  IsCycleOk, IsSealed, IsMembership, IsContainment, IsHosting, RelationshipTypeTableName, RelationshipTypeViewName,


				  SourceManagedTypeId, TargetManagedTypeId, SourceName, TargetName, SourceId, TargetId,


				  SourceMaxCardinality, SourceMinCardinality, TargetMaxCardinality, TargetMinCardinality,


				  DatasourceId, TimeAdded, LastModified, ModifiedBy 				


			from 	(select RelationshipTypeId, RelationshipTypeName, BaseRelationshipTypeId, ManagementPackId, IsAbstract,


						  IsCycleOk, IsSealed, IsMembership, IsContainment, IsHosting, RelationshipTypeTableName, RelationshipTypeViewName,


						  SourceManagedTypeId, TargetManagedTypeId, SourceName, TargetName, SourceId, TargetId,


						  SourceMaxCardinality, SourceMinCardinality, TargetMaxCardinality, TargetMinCardinality,


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


					LastModified DESC) AS SeqNum				


					from inbound.RelationshipType


					where DWTimestamp >= @source1WM 


					and DWTimestamp < @source1MaxWM) AS SRC


			where SeqNum = 1


	


		) as source 


		on (target.RelationshipTypeId = source.RelationshipTypeId) 


				and (target.SourceId = source.DatasourceId)


  when matched then 		


			update set 


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


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


				   ManagementPackId = source.ManagementPackId,


				   IsAbstract = source.IsAbstract,


				   IsCycleOk = source.IsCycleOk,


				   IsSealed = source.IsSealed,


				   IsMembership = source.IsMembership, 


				   IsContainment = source.IsContainment, 


				   IsHosting = source.IsHosting,


				   SourceManagedTypeId = source.SourceManagedTypeId,


				   TargetManagedTypeId = source.TargetManagedTypeId,


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


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


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


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


				   _SourceId_ = source.SourceId,


				   TargetId = source.TargetId,


				   SourceMaxCardinality = isnull(source.SourceMaxCardinality, 2147483647),


				   SourceMinCardinality = isnull(source.SourceMinCardinality, 0),


				   TargetMaxCardinality = isnull(source.TargetMaxCardinality, 2147483647),


				   TargetMinCardinality = isnull(source.TargetMinCardinality, 0),


				   TimeAdded = source.TimeAdded,


				   LastModified = source.LastModified,


				   ModifiedBy = source.ModifiedBy,


				   UpdatedBatchId = @BatchId,


				   @Updated = @Updated + 1


		


  when not matched 


  by target then 


		insert (BaseManagedEntityId, RelationshipTypeId, RelationshipTypeName, BaseRelationshipTypeId, ManagementPackId, IsAbstract,


				  IsDeleted, DisplayName, IsCycleOk, IsSealed,  IsMembership, IsContainment, IsHosting, RelationshipTypeTableName, RelationshipTypeViewName,


				  SourceManagedTypeId, TargetManagedTypeId, SourceName, TargetName, _SourceId_, TargetId,


				  SourceMaxCardinality, SourceMinCardinality, TargetMaxCardinality, TargetMinCardinality,


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


		values (source.RelationshipTypeId, source.RelationshipTypeId, source.RelationshipTypeName, isnull(source.BaseRelationshipTypeId, @nullguid), source.ManagementPackId, source.IsAbstract,


				  0, '' '', source.IsCycleOk, source.IsSealed,  IsMembership, IsContainment, IsHosting, isnull(source.RelationshipTypeTableName, ''null''), isnull(source.RelationshipTypeViewName, ''null''),


				  source.SourceManagedTypeId, source.TargetManagedTypeId, isnull(source.SourceName, ''null''), isnull(source.TargetName, ''null''), source.SourceId, source.TargetId,


				  isnull(source.SourceMaxCardinality, 2147483647), isnull(source.SourceMinCardinality, 0), isnull(source.TargetMaxCardinality, 2147483647), isnull(source.TargetMinCardinality, 0),


				  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 RelationshipType Dimension'', 16,1)


        return


  end





    EXEC dbo.UninitializeTransform


        @transformName          = ''TransformRelationshipTypeDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


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


        @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