IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformAlertDimProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformAlertDimProc] @WaterMark xml AS RETURN 1')
END
GO
DECLARE @Statement NVARCHAR(MAX)
SET @Statement =
N'ALTER PROCEDURE [dbo].[TransformAlertDimProc](@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
BEGIN TRANSACTION
-- SELECT @task = ''Updating Dimension''
-- UPDATE dest SET
--[AlertDescription] = source.[Description],
--[AlertName] = source.[Name],
--[AlertProblemGuid] = source.[ProblemId],
--[IsMonitorAlert] = source.[IsMonitorAlert],
--[TargetEntityDimKey] = source.[Microsoft.SystemCenter.Datawarehouse.OM.Alert!TargetEntityDimKey],
-- [IsDeleted] = ISNULL(baseEntityDim.[IsDeleted], 0),
-- [SourceId] = source.[DatasourceId],
-- [UpdatedBatchId] = @BatchId
-- FROM DWTemp.TransformAlertDimProc_Source1 source
-- INNER JOIN dbo.AlertDimvw dest ON (source.BaseManagedEntityId = dest.BaseManagedEntityId AND source.DatasourceId = dest.SourceId)
-- INNER JOIN dbo.EntityDim baseEntityDim ON (source.DatasourceId = baseEntityDim.SourceId AND source.MonitoringObjectId = baseEntityDim.BaseManagedEntityId)
-- WHERE source.ProblemId = dest.AlertProblemGuid
-- SELECT @Updated = @Updated + @@ROWCOUNT
SELECT @task = ''Inserting into Dimension''
INSERT INTO dbo.AlertDimvw (
[SourceId],
[BaseManagedEntityId],
[TargetEntityDimKey],
[AlertProblemGuid],
[AlertName],
[AlertDescription],
[IsMonitorAlert],
[InsertedBatchId],
[UpdatedBatchId]
)
SELECT DISTINCT
source.[DatasourceId],
NULL AS BaseManagedEntityId,
NULL AS TargetEntityDimKey,
source.[ProblemId],
MIN(source.[Name]),
MIN(CAST(source.[Description] AS NVARCHAR(256))),
MIN(CAST(source.[IsMonitorAlert] AS VARCHAR(32))),
@BatchId,
0
FROM DWTemp.TransformAlertDimProc_Source1 AS source
LEFT JOIN dbo.AlertDimvw dest ON (source.DatasourceId = dest.SourceId AND source.ProblemId = dest.AlertProblemGuid)
WHERE dest.AlertProblemGuid IS NULL
GROUP BY source.DatasourceId, source.ProblemId
SELECT @Inserted = @Inserted + @@ROWCOUNT
SELECT @task = ''Rejecting early arriving data''
UPDATE source SET
DWTimeStamp = @source1MaxWM,
BatchId = @BatchId,
RejectedCount = ISNULL(source.RejectedCount, 0) + 1
FROM inbound.AlertStaging source
LEFT JOIN dbo.AlertDimvw dest ON (source.DatasourceId = dest.SourceId AND source.ProblemId = dest.AlertProblemGuid)
WHERE dest.AlertDimKey IS NULL
AND source.DWTimeStamp >= @source1WM
AND source.DWTimeStamp < @source1MaxWM