InstallTransformManagedEntityResultFactResource (Resource)

Element properties:

Type	Resource
File Name	InstallTransformManagedEntityResultFact.sql
Accessibility	Public

Source Code:

<Resource ID="InstallTransformManagedEntityResultFactResource" Accessibility="Public" FileName="InstallTransformManagedEntityResultFact.sql"/>

File Content: InstallTransformManagedEntityResultFact.sql

/****** Object:  StoredProcedure [dbo].[TransformManagedEntityResultFactProc]    Script Date: 06/18/2010 03:49:52 ******/


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransformManagedEntityResultFactProc]') AND type in (N'P', N'PC'))


DROP PROCEDURE [dbo].[TransformManagedEntityResultFactProc]


GO





/****** Object:  StoredProcedure [dbo].[TransformManagedEntityResultFactProc]    Script Date: 06/18/2010 03:49:52 ******/


SET ANSI_NULLS ON


GO





SET QUOTED_IDENTIFIER ON


GO











CREATE PROCEDURE [dbo].[TransformManagedEntityResultFactProc] (@WaterMark xml)


AS


BEGIN	


SET NOCOUNT ON


SET XACT_ABORT ON





    DECLARE @errorNumber        INT,


            @errorSeverity      INT,


            @errorState         INT,


            @errorLine          INT,


            @errorProcedure     NVARCHAR(256),


            @errorMessage       NVARCHAR(MAX),


            @startTranCount     INT         = @@TRANCOUNT,


            @task               NVARCHAR(512)





  -- Get Metadata


  DECLARE  @BatchId int, @Inserted int, @Updated int, @MaxActivityTimestamp datetime, @ActivityTimestamp datetime, @MinDateKey int





  SET @Inserted = 0


  SET @Updated = 0


	


 BEGIN TRY


  


	SELECT * INTO #wm FROM etl.ShredWaterMark(@WaterMark)	


	


	SELECT TOP 1 @ActivityTimestamp = WaterMark, @BatchId = BatchId


		FROM #wm 


		WHERE WarehouseEntityName = N'MTV_System$Compliance$ManagedEntityResult'


  


	--SELECT @MaxActivityTimestamp = COALESCE(MAX(DWTimestamp), GETUTCDATE()) 


	--	FROM inbound.MTV_System$Compliance$ManagedEntityResult


	


	SELECT @MaxActivityTimestamp = GETUTCDATE()


	


	--Start: Region Temp tables


	IF OBJECT_ID('TEMPDB..#MERTemp') IS NOT NULL


		DROP TABLE #MERTemp;


			


  Select T1.[System.Compliance.ManagedEntityResult!Details],


  T1.LastModified,


  T1.[System.Compliance.ManagedEntityResult!LastScannedTime],


  T1.DatasourceId,


  T1.BaseManagedEntityId,


  T1.TimeAdded,


  T1.[System.Compliance.ManagedEntityResult!Result],


  T1.[System.Compliance.ManagedEntityResult!ResultType],


  T1.[System.Compliance.ManagedEntityResult!SourceTestID],                        


  T1.DWTimestamp,


  T1.[System.Compliance.ManagedEntityResult]


  INTO #MERTemp


  from inbound.MTV_System$Compliance$ManagedEntityResult T1


  WHERE T1.DWTimestamp >= @ActivityTimestamp AND T1.DWTimestamp < @MaxActivityTimestamp


  


  


	create NONCLUSTERED INDEX [MERTemp_ind]


		on #MERTemp ([System.Compliance.ManagedEntityResult!SourceTestID])


  			


	IF OBJECT_ID('TEMPDB..#CATemp') IS NOT NULL


		DROP TABLE #CATemp;


			


	Select CADim.ControlActivityDimKey,


		CADim.TestId,


		CADim.IsDeleted


		INTO #CATemp


		from dbo.ControlActivityDimvw CADim


		WHERE CADim.TestId <> ''


		AND IsDeleted=0





	create NONCLUSTERED INDEX [CATemp_ind]


		on #CATemp (TestId)	


	


	IF OBJECT_ID('TEMPDB..#MERRelatesCI') IS NOT NULL


		DROP TABLE #MERRelatesCI;


	


	Select SourceEntityId,TargetEntityId 


	INTO #MERRelatesCI


	from inbound.MTV_System$Compliance$ManagedEntityResultRelatesToConfigItem





	


	CREATE NONCLUSTERED INDEX [MERRelatesCI_Ind]


		ON #MERRelatesCI ([SourceEntityId])


	


	IF OBJECT_ID('TEMPDB..#CITemp') IS NOT NULL


		DROP TABLE #CITemp;


	


	Select CI.BaseManagedEntityId AS BaseManagedEntityId,


	CI.ConfigItemDimKey AS ConfigItemDimKey, 


	CI.IsDeleted AS IsDeleted


	INTO #CITemp


	from dbo.ConfigItemDimvw CI 	


	where CI.IsDeleted =0


  


	CREATE NONCLUSTERED INDEX [CITemp_Ind]


		ON #CITemp ([BaseManagedEntityId])





	IF OBJECT_ID('TEMPDB..#MERRelatesSS') IS NOT NULL


		DROP TABLE #MERRelatesSS; 


  


	Select SourceEntityId,TargetEntityId 


	INTO #MERRelatesSS


	from inbound.MTV_System$Compliance$ManagedEntityResultRelatesToSourceSystem


  


	CREATE NONCLUSTERED INDEX [MERRelatesSS_Ind]


		ON #MERRelatesSS ([SourceEntityId])


		


	--End: Region Temp tables





	IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL


        BEGIN


            DROP TABLE #Temp1


        END


   


	CREATE TABLE #Temp1 (


	[ControlActivityDimKey] [int] NULL,


	[ConfigItemDimKey] [int] NULL,


	[MERResultId] [int] NULL,


	[MERResultTypeId] [int] NULL,


	[MERSourceDimKey] [int] NULL,


	[Details] [nvarchar] (4000) NULL,


	[DateKey] [int] NULL,


	[LastScannedTime] [datetime] NULL,


	--[ManagedEntityResultDimKey] [int] NULL,


	[DatasourceId] [uniqueidentifier] NULL,


	[BaseManagedEntityId] [uniqueidentifier] NULL,


	[TimeAdded] [INT] NULL


	)     


	


	--Get the both Manual and Automated into Temp1


	INSERT INTO #Temp1 (


	[ControlActivityDimKey],


	[ConfigItemDimKey],


	[MERResultId],


	[MERResultTypeId],


	[MERSourceDimKey],


	[Details],


	[DateKey],


	[LastScannedTime],


	--[ManagedEntityResultDimKey],


	[DatasourceId],


	[BaseManagedEntityId],


	[TimeAdded]


	)	


	--SELECT DISTINCT Act_DimCA.ControlActivityDimKey,  


 --            Act_DimCI.ConfigItemDimKey, 


 --            COALESCE(MERResultId, 0) AS MERResultId,


 --            COALESCE(MERResultTypeId, 0) AS MERResultTypeId,


 --            ACT_Source.MERSourceDimKey AS MERSourceDimKey,


 --            [System.Compliance.ManagedEntityResult!Details] AS Details,


 --            CONVERT(nvarchar(8), INB.LastModified , 112) AS DateKey,


 --            [System.Compliance.ManagedEntityResult!LastScannedTime] AS LastScannedTime,


 --            --ACT_DimMER.ManagedEntityResultDimKey AS ManagedEntityResultDimKey,


 --            INB.DatasourceId,


 --            INB.BaseManagedEntityId


	--FROM inbound.MTV_System$Compliance$ManagedEntityResult INB


 --           JOIN  dbo.MERResultvw Act_Result 


 --           ON Act_Result.EnumTypeId = INB.[System.Compliance.ManagedEntityResult!Result]                   


 --           JOIN  dbo.MERResultTypevw Act_ResultType 


 --           ON Act_ResultType.EnumTypeId = INB.[System.Compliance.ManagedEntityResult!ResultType]      


            


 --           LEFT JOIN inbound.MTV_System$Compliance$ControlActivityContainsManagedEntityResult Act_DimCAMER


 --           ON Act_DimCAMER.TargetEntityId = INB.BaseManagedEntityId


                        


 --           LEFT JOIN dbo.ControlActivityDimvw Act_DimCA 


 --           ON Act_DimCA.BaseManagedEntityId = Act_DimCAMER.SourceEntityId


 --           AND Act_DimCA.IsDeleted = 0          


            


 --           LEFT JOIN inbound.MTV_System$Compliance$ManagedEntityResultRelatesToConfigItem Act_DimMERCI


 --           ON Act_DimMERCI.SourceEntityId = INB.BaseManagedEntityId


            


 --           LEFT JOIN dbo.ConfigItemDimvw Act_DimCI


 --           ON Act_DimCI.BaseManagedEntityId = Act_DimMERCI.TargetEntityId


 --           AND Act_DimCI.IsDeleted = 0    


           


 --           LEFT JOIN inbound.MTV_System$Compliance$ManagedEntityResultRelatesToSourceSystem Act_DimMERSourceSystem


 --           ON Act_DimMERSourceSystem.SourceEntityId = INB.BaseManagedEntityId


            


 --           LEFT JOIN dbo.MERSourceDimvw ACT_Source


 --           ON ACT_Source.BaseManagedEntityId = Act_DimMERSourceSystem.TargetEntityId





 --           --LEFT JOIN dbo.ManagedEntityResultDimvw ACT_DimMER


 --           --ON ACT_DimMER.BaseManagedEntityId = INB.BaseManagedEntityId


 --           --AND ACT_DimMER.SourceId = INB.DatasourceId


                                         


 --      WHERE INB.DWTimestamp >= @ActivityTimestamp AND INB.DWTimestamp < @MaxActivityTimestamp       


	


	--UNION


		


	SELECT DISTINCT Act_DimCA.ControlActivityDimKey,  


             Act_DimCI.ConfigItemDimKey, 


             COALESCE(MERResultId, 0) AS MERResultId,


             COALESCE(MERResultTypeId, 0) AS MERResultTypeId,


             ACT_Source.MERSourceDimKey AS MERSourceDimKey,


             [System.Compliance.ManagedEntityResult!Details] AS Details,


             CONVERT(nvarchar(8), INB.LastModified , 112) AS DateKey,


             [System.Compliance.ManagedEntityResult!LastScannedTime] AS LastScannedTime,


             --ACT_DimMER.ManagedEntityResultDimKey AS ManagedEntityResultDimKey,


             INB.DatasourceId,


             INB.BaseManagedEntityId,


             CONVERT(VARCHAR(8), INB.TimeAdded, 112) AS TimeAdded


      FROM #MERTemp INB with (nolock)


            JOIN  dbo.MERResultvw Act_Result with(nolock) 


            ON Act_Result.EnumTypeId = INB.[System.Compliance.ManagedEntityResult!Result]                   


            JOIN  dbo.MERResultTypevw Act_ResultType with(nolock)


            ON Act_ResultType.EnumTypeId = INB.[System.Compliance.ManagedEntityResult!ResultType]      


                              


            LEFT JOIN #CATemp Act_DimCA with(nolock)


            ON (Act_DimCA.TestId LIKE INB.[System.Compliance.ManagedEntityResult!SourceTestID] +'%'


            OR INB.[System.Compliance.ManagedEntityResult!SourceTestID] LIKE Act_DimCA.TestId +'%')


            --AND Act_DimCA.IsDeleted = 0  


            


            LEFT JOIN #MERRelatesCI Act_DimMERCI with(nolock)


            ON Act_DimMERCI.SourceEntityId = INB.BaseManagedEntityId


            


            LEFT JOIN #CITemp Act_DimCI with(nolock)


            ON Act_DimCI.BaseManagedEntityId = Act_DimMERCI.TargetEntityId


            --AND Act_DimCI.IsDeleted = 0    


            


            LEFT JOIN #MERRelatesSS Act_DimMERSourceSystem with(nolock)


            ON Act_DimMERSourceSystem.SourceEntityId = INB.BaseManagedEntityId


            


            LEFT JOIN dbo.MERSourceDimvw ACT_Source with(nolock)


			ON ACT_Source.BaseManagedEntityId = Act_DimMERSourceSystem.TargetEntityId


                                    


       WHERE INB.DWTimestamp >= @ActivityTimestamp AND INB.DWTimestamp < @MaxActivityTimestamp


       AND Act_DimCA.TestId <> ''





	--gkapila: There won't be any duplicates rows as we are not taking a union anymore in above query


	--Whenever new MER comes, if it is manual then NULL CA will come in the automated and vice versa


	--So we have to delete the ones that is duplicate


	--DELETE  a


	--FROM #Temp1 a


	--JOIN #Temp1 bb ON 


	--	 a.BaseManagedEntityId = bb.BaseManagedEntityId


	--	and a.DatasourceId = bb.DatasourceId


	--	and a.ControlActivityDimKey IS NULL


	--	and bb.ControlActivityDimKey IS NOT NULL


       	


    --gkapila: There won't be any updates as we are not having manual MER's in V1


	----Get both Manual / Automated data in to #temp2. Join with MER FACT to check for update


	--SELECT DISTINCT ISNULL(INB.ControlActivityDimKey, MER.ControlActivityDimKey) AS ControlActivityDimKey,


             --ISNULL(INB.ConfigItemDimKey, MER.ConfigItemDimKey) AS ConfigItemDimKey, 


             --INB.MERResultId AS MERResultId,


             --INB.MERResultTypeId AS MERResultTypeId,


             --ISNULL(INB.MERSourceDimKey, MER.MERSourceDimKey) AS MERSourceDimKey,


             --INB.Details AS Details,


             --INB.DateKey AS DateKey,


             --INB.LastScannedTime AS LastScannedTime,


             --INB.ManagedEntityResultDimKey AS ManagedEntityResultDimKey,


             --INB.DatasourceId,


             --INB.BaseManagedEntityId


      --INTO #Temp2


      --FROM #Temp1 INB


            --LEFT JOIN ManagedEntityResultFactvw MER


            --ON INB.ManagedEntityResultDimKey = MER.ManagedEntityResultDimKey  


         





 	---- If there are any early arrivals: When a Control Activity Dimkey / Config Item Dimkey / MER Source DimKey is null


	---- We should pick the MER in the next run. 	


 	UPDATE INB SET 


 			DWTimestamp = @MaxActivityTimestamp,	


 			RejectedCount = ISNULL(INB.RejectedCount, 0) + 1,


            BatchId = @BatchId


	FROM inbound.MTV_System$Compliance$ManagedEntityResult INB  


	LEFT JOIN #Temp1 ACT_DimMER ON 


			ACT_DimMER.BaseManagedEntityId = INB.BaseManagedEntityId 


		AND ACT_DimMER.DatasourceId = INB.DatasourceId


	WHERE   (ACT_DimMER.ControlActivityDimKey IS NULL OR


			ACT_DimMER.ConfigItemDimKey IS NULL OR


			ACT_DimMER.MERSourceDimKey IS NULL) AND 


			INB.DWTimestamp >= @ActivityTimestamp AND 


			INB.DWTimestamp < @MaxActivityTimestamp





 	---- If there are any early arrivals: When a Control Activity Dimkey / Config Item Dimkey / MER Source DimKey is null


	---- We should pick the MER to MER Source in the Next run


 	UPDATE INB SET 


 			DWTimestamp = @MaxActivityTimestamp,	


 			RejectedCount = ISNULL(INB.RejectedCount, 0) + 1,


            BatchId = @BatchId


	FROM inbound.MTV_System$Compliance$ManagedEntityResultRelatesToSourceSystem INB  


	LEFT JOIN inbound.Relationship REL ON


	REL.RelationshipId = INB.RelationshipId	


	LEFT JOIN #Temp1 ACT_DimMER ON 


			ACT_DimMER.BaseManagedEntityId = REL.TargetEntityId


		AND ACT_DimMER.DatasourceId = INB.DatasourceId


	WHERE   (ACT_DimMER.ControlActivityDimKey IS NULL OR


			ACT_DimMER.ConfigItemDimKey IS NULL OR


			ACT_DimMER.MERSourceDimKey IS NULL) AND 


			INB.DWTimestamp >= @ActivityTimestamp AND 


			INB.DWTimestamp < @MaxActivityTimestamp





 	---- If there are any early arrivals: When a Control Activity Dimkey / Config Item Dimkey / MER Source DimKey is null


	---- We should pick the MER to Config Item in the Next run


 	UPDATE INB SET 


 			DWTimestamp = @MaxActivityTimestamp,	


 			RejectedCount = ISNULL(INB.RejectedCount, 0) + 1,


            BatchId = @BatchId


	FROM inbound.MTV_System$Compliance$ManagedEntityResultRelatesToConfigItem INB  


	LEFT JOIN inbound.Relationship REL ON


	REL.RelationshipId = INB.RelationshipId	


	LEFT JOIN #Temp1 ACT_DimMER ON 


			ACT_DimMER.BaseManagedEntityId = REL.TargetEntityId


		AND ACT_DimMER.DatasourceId = INB.DatasourceId


	WHERE   (ACT_DimMER.ControlActivityDimKey IS NULL OR


			ACT_DimMER.ConfigItemDimKey IS NULL OR


			ACT_DimMER.MERSourceDimKey IS NULL) AND 


			INB.DWTimestamp >= @ActivityTimestamp AND 


			INB.DWTimestamp < @MaxActivityTimestamp


			


 	DELETE FROM #Temp1 


 	WHERE 


 	( 	


		ControlActivityDimKey IS NULL OR


		ConfigItemDimKey IS NULL OR


		MERSourceDimKey IS NULL


	)


 	


 	SELECT @MinDateKey = MIN(TimeAdded) FROM #Temp1


 	


 	--Prevent Dup Key problems


 	DELETE a From #Temp1 a


	JOIN ManagedEntityResultFactvw b


	ON a.BaseManagedEntityId = b.BaseManagedEntityId


	AND a.DatasourceId = b.SourceId


	AND a.ConfigItemDimKey = b.ConfigItemDimKey


	AND a.ControlActivityDimKey = b.ControlActivityDimKey


	AND a.LastScannedTime = b.LastScannedTime


			WHERE b.DateKey >= @MinDateKey


			


	--Insert into Main table


	INSERT INTO [dbo].[ManagedEntityResultFactvw]


	(


		 [ControlActivityDimKey]


		,[ConfigItemDimKey]


		,[MERResultId]


		,[MERResultTypeId]


		,[MERSourceDimKey]


		,[Details]


		,[DateKey]


		,[InsertedBatchId]


		,[UpdatedBatchId]	


		,[LastScannedTime]


		,[BaseManagedEntityId]


		,[SourceId]


		--,[ManagedEntityResultDimKey]


	)


    SELECT DISTINCT ControlActivityDimKey,  


           ConfigItemDimKey, 


           MERResultId,


           MERResultTypeId,


           MERSourceDimKey,


           Details,


           DateKey,


           @BatchId,


           @BatchId,


           LastScannedTime,


           BaseManagedEntityId,


           DatasourceId


           --ManagedEntityResultDimKey


      --FROM #Temp2


      FROM #Temp1


      


      


	SELECT @Inserted = @@RowCount








    UPDATE #wm SET WaterMark = CONVERT(nvarchar(64), ISNULL(@MaxActivityTimestamp,'1/1/1900'),109) 


	WHERE WarehouseEntityName = N'MTV_System$Compliance$ManagedEntityResult' and WaterMarkType= N'DateTime'


  	


	SELECT @WaterMark = (SELECT 	ModuleName AS "@ModuleName",


				ProcessName AS "@ProcessName",


				@BatchId AS "@BatchId",


			        (SELECT  DISTINCT WarehouseEntityName AS "@WarehouseEntityName",


							WarehouseEntityTypeName AS "@WarehouseEntityTypeName",


							EntityGuid AS "@EntityGuid",


							CASE WarehouseEntityTypeName 


								WHEN 'Inbound' THEN 'DateTime'


								ELSE 'BatchId' END AS "@WaterMarkType",


							WaterMark as "@WaterMark"


						FROM #wm			  


						FOR XML path('Entity'), type)


	FROM  (SELECT  DISTINCT ModuleName, ProcessName from #wm) a	


	FOR XML path('Module'), type)








  SELECT @WaterMark as WaterMark, @BatchId as BatchId, @Updated as UpdatedRowCount, @Inserted as InsertedRowCount





 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


        


        IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL


        BEGIN


            DROP TABLE #Temp1


        END





        IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL


        BEGIN


            DROP TABLE #Temp2


        END


        


        IF OBJECT_ID('tempdb..#wm') IS NOT NULL


        BEGIN


            DROP TABLE #wm


        END





        RAISERROR (


            @errorFmt,


            @errorSeverity,


            @errorState,


            @errorNumber,


            @errorMessage,


            @errorSeverity,


            @errorState,


            @errorProcedure,


            @errorLine,


            @task


        )





        RETURN -1


    END CATCH


    


SET XACT_ABORT OFF


SET NOCOUNT OFF


END





GO