IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformRuleProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformRuleProc] @WaterMark xml AS RETURN 1')
END
GO
DECLARE @Statement NVARCHAR(MAX)
SET @Statement =
N'ALTER PROCEDURE [dbo].[TransformRuleProc](@WaterMark XML)
AS BEGIN
SET NOCOUNT 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
DECLARE @rowsToProcess INT = 0, @rejectedRowCount INT = 0, @canContinue BIT = 1, @loopCount INT
DECLARE @isReconciled BIT = 0
SELECT @task = ''Deleting extraneous changes - only retaining the most recent change to an entity''
DELETE source
FROM DWTemp.TransformRuleProc_Source1 AS source
INNER JOIN DWTemp.TransformRuleProc_Source1 latest ON
source.Id = latest.Id
AND (@isReconciled = 1 OR source.DatasourceId = latest.DatasourceId)
AND (source.LastModified < latest.LastModified
OR (source.LastModified = latest.LastModified AND source.Id < latest.Id)
)
BEGIN TRANSACTION
SELECT @task = ''Insertint into RuleCategory''
INSERT INTO dbo.RuleCategoryvw (
EnumTypeId,
RuleCategoryValue,
Ordinal,
ParentId,
ID,
InsertedBatchId,
UpdatedBatchId
)
SELECT DISTINCT
NULL AS EnumTypeId,
source.Category,
NULL AS Ordinal,
NULL AS ParentId,
source.Category AS ID,
@BatchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM DWTemp.TransformRuleProc_Source1 source
LEFT JOIN dbo.RuleCategoryvw dest ON
source.Category = dest.RuleCategoryValue
WHERE dest.RuleCategoryValue IS NULL
AND source.Category IS NOT NULL
SELECT @task = ''Updating Rule Dimension''
UPDATE dest SET
[DiscardLevel] = source.[DiscardLevel],
[RuleEnabled] = source.[Enabled],
[RuleGuaranteedDelivery] = source.[ConfirmDelivery],
[RuleName] = source.[Name],
[RulePriority] = source.[Priority],
[RuleRemotable] = source.[Remotable],
[TargetManagedTypeDimKey] = dimMgdType.[ManagedTypeDimKey],
[RuleCategory_RuleCategoryId] = outriggerSrc1.[RuleCategoryId],
[RuleCategory] = outriggerSrc1.RuleCategoryValue,
[IsDeleted] = ISNULL(source.[IsDeleted], 0),
[UpdatedBatchId] = @BatchId
FROM DWTemp.TransformRuleProc_Source1 source
INNER JOIN dbo.RuleDimvw dest ON (source.DatasourceId = dest.SourceId AND source.Id = dest.RuleId)
INNER JOIN dbo.ManagedTypeDimvw dimMgdType ON (source.DatasourceId = dimMgdType.SourceId AND source.TargetMonitoringClassId = dimMgdType.ManagedTypeId)
INNER JOIN dbo.RuleCategoryvw outriggerSrc1 ON (source.[Category] = outriggerSrc1.RuleCategoryValue)
WHERE dest.[DiscardLevel] <> source.[DiscardLevel] OR
dest.[RuleEnabled] <> source.[Enabled] OR
dest.[RuleGuaranteedDelivery] <> source.[ConfirmDelivery] OR
dest.[RuleName] <> source.[Name] OR
dest.[RulePriority] <> source.[Priority] OR
dest.[RuleRemotable] <> source.[Remotable] OR
dest.[IsDeleted] <> source.[IsDeleted] OR
(dest.[TargetManagedTypeDimKey] IS NULL OR dest.[TargetManagedTypeDimKey] <> dimMgdType.[ManagedTypeDimKey]) OR
dest.[RuleCategory_RuleCategoryId] <> outriggerSrc1.[RuleCategoryId] OR
dest.[RuleCategory] <> outriggerSrc1.RuleCategoryValue
SELECT @Updated = @Updated + @@ROWCOUNT
SELECT @task = ''Inserting into Dimension''
INSERT INTO dbo.RuleDimvw (
[RuleId],
[SourceId],
[BaseManagedEntityId],
[DiscardLevel],
[RuleEnabled],
[RuleGuaranteedDelivery],
[RuleName],
[RulePriority],
[RuleRemotable],
[ManagementPackId],
[TargetManagedTypeDimKey],
[RuleCategory_RuleCategoryId],
[RuleCategory],
[IsDeleted],
[InsertedBatchId],
[UpdatedBatchId]
)
SELECT DISTINCT
source.[Id],
source.[DatasourceId],
source.[Id],
source.[DiscardLevel],
source.[Enabled],
source.[ConfirmDelivery],
source.[Name],
source.[Priority],
source.[Remotable],
source.[ManagementPackId],
dimMgdType.[ManagedTypeDimKey],
outriggerSrc1.[RuleCategoryId],
outriggerSrc1.[RuleCategoryValue],
source.[IsDeleted] AS [IsDeleted],
@BatchId,
0
FROM DWTemp.TransformRuleProc_Source1 AS source
INNER JOIN dbo.RuleCategory outriggerSrc1 ON (outriggerSrc1.RuleCategoryValue = source.[Category])
INNER JOIN dbo.ManagedTypeDimvw dimMgdType ON (source.DatasourceId = dimMgdType.SourceId AND source.TargetMonitoringClassId = dimMgdType.ManagedTypeId)
LEFT JOIN dbo.RuleDimvw dest ON (source.DatasourceId = dest.SourceId AND source.Id = dest.RuleId)
WHERE dest.RuleDimKey IS NULL
SELECT @Inserted = @Inserted + @@ROWCOUNT
SELECT @task = ''Update source to move early arriving records to next batch''
UPDATE source SET
DWTimestamp = @source1MaxWM,
RejectedCount = ISNULL(source.RejectedCount, 0) + 1,
BatchId = @BatchId
FROM inbound.RuleStaging source
LEFT JOIN dbo.RuleDimvw dest ON (source.DatasourceId = dest.SourceId AND source.Id = dest.RuleId)
LEFT JOIN dbo.RuleCategoryvw outriggerSrc1 ON (source.[Category] = outriggerSrc1.RuleCategoryValue)
WHERE source.DWTimestamp >= @source1WM AND source.DWTimestamp < @source1MaxWM
AND dest.RuleDimKey IS NULL OR outriggerSrc1.RuleCategoryId IS NULL
Home