IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformAlertResolutionStateFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformAlertResolutionStateFactProc] @WaterMark xml AS RETURN 1')
END
GO
DECLARE @Statement NVARCHAR(MAX)
SET @Statement =
N'ALTER PROCEDURE [dbo].[TransformAlertResolutionStateFactProc](@WaterMark XML)
AS BEGIN
SET NOCOUNT ON
SET XACT_ABORT 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 = GETUTCDATE()
DECLARE @rowsToProcess INT = 0, @rejectedRowCount INT = 0, @canContinue BIT = 1, @loopCount INT
CREATE TABLE #ResolutionState (
AlertStageRowId int NOT NULL,
AlertInstanceDimKey int NOT NULL,
AlertDimKey int NOT NULL,
NewResolutionStateDimKey int NOT NULL,
AlertGuid uniqueidentifier NOT NULL,
LastResolutionState int NULL
)
SELECT @task = ''Deleting extraneous changes - only retaining the most recent change to an entity''
DELETE source
FROM DWTemp.TransformAlertResolutionStateFactProc_Source1 AS source
INNER JOIN DWTemp.TransformAlertResolutionStateFactProc_Source1 latest ON
source.Id = latest.Id
AND source.TimeResolutionStateLastModified = latest.TimeResolutionStateLastModified
AND source.DWId < latest.DWId
SELECT @task = ''Adding index: NCI_TxAlertResStateFact1''
IF NOT EXISTS(select ''x'' from sys.indexes where name = ''NCI_TxAlertResStateFact1'')
BEGIN
CREATE NONCLUSTERED INDEX NCI_TxAlertResStateFact1
ON DWTemp.TransformAlertResolutionStateFactProc_Source1 (ResolutionState,DatasourceId)
INCLUDE (DWId,Id,MonitoringObjectId,ProblemId,TimeResolutionStateLastModified)
END
TRUNCATE TABLE #ResolutionState
INSERT #ResolutionState (
AlertStageRowId,
AlertInstanceDimKey,
AlertDimKey,
NewResolutionStateDimKey,
AlertGuid,
LastResolutionState
)
SELECT
s.DWId,
dimAlertInst.AlertInstanceDimKey,
dimAlert.AlertDimKey,
dimResSt.ResolutionStateDimKey,
s.Id AS AlertGuid,
r.ResolutionStateDimKey
FROM DWTemp.TransformAlertResolutionStateFactProc_Source1 AS s
INNER JOIN dbo.AlertInstanceDimvw dimAlertInst ON (s.DatasourceId = dimAlertInst.SourceId AND s.Id = dimAlertInst.AlertGuid)
INNER JOIN dbo.AlertDimvw dimAlert ON (s.DatasourceId = dimAlert.SourceId AND s.ProblemId = dimAlert.AlertProblemGuid)
INNER JOIN dbo.ResolutionStateDimvw dimResSt ON (s.DatasourceId = dimResSt.SourceId and s.ResolutionState = dimResSt.StateValue)
LEFT JOIN dbo.AlertResolutionStateFactvw AS r ON
dimAlertInst.AlertInstanceDimKey = r.AlertInstanceDimKey
AND dimAlert.AlertDimKey = r.AlertDimKey
AND r.StateSetDateTime = (
SELECT TOP 1 fact.StateSetDateTime
FROM AlertResolutionStateFactvw fact
WHERE fact.AlertInstanceDimKey = dimAlertInst.AlertInstanceDimKey
AND fact.AlertDimKey = dimALert.AlertDimKey
AND StateSetDateTime <= s.TimeResolutionStateLastModified
ORDER BY StateSetDateTime DESC
)
WHERE r.AlertInstanceDimKey IS NULL OR r.StateSetDateTime <> s.TimeResolutionStateLastModified
BEGIN TRANSACTION
SELECT @task = ''Inserting into destination Fact''
INSERT dbo.AlertResolutionStateFactvw (
DateKey,
AlertDimKey,
ResolutionStateDimKey,
AlertInstanceDimKey,
StateSetByUserId,
StateSetDateTime,
TimeInStateSeconds,
TimeFromRaisedSeconds,
UpdatedBatchId,
InsertedBatchId
)
SELECT DISTINCT
etl.GetDateKey(COALESCE(s.TimeRaised, s.TimeAdded)),
r.AlertDimKey,
r.NewResolutionStateDimKey,
r.AlertInstanceDimKey,
s.LastModifiedBy,
s.TimeResolutionStateLastModified,
NULL AS TimeInStateSeconds,
ISNULL(ABS(DATEDIFF(second, s.TimeResolutionStateLastModified, COALESCE(s.TimeRaised, s.TimeAdded))), 0),
0 AS UpdatedBatchId,
@BatchId AS InsertedBatchId
FROM #ResolutionState AS r
JOIN DWTemp.TransformAlertResolutionStateFactProc_Source1 s ON (r.AlertStageRowId = s.DWId)
WHERE ((r.NewResolutionStateDimKey <> r.LastResolutionState) OR (r.LastResolutionState IS NULL))
AND COALESCE(s.TimeRaised, s.TimeAdded) IS NOT NULL
SELECT @Inserted = @Inserted + @@RowCount
SELECT @task = ''Preparing #temp1 to upserting into destination Fact''
IF OBJECT_ID(''tempdb..#temp1'') IS NOT NULL DROP TABLE #temp1
SELECT AlertInstanceDimKey, StateSetDateTime, ROW_NUMBER() OVER (PARTITION BY AlertInstanceDimKey ORDER BY StateSetDateTime) AS StateSetOrder
INTO #temp1
FROM dbo.AlertResolutionStateFactvw
WHERE TimeInStateSeconds IS NULL
SELECT @task = ''Updating destination Fact''
UPDATE ars SET
TimeInStateSeconds = ABS(DATEDIFF(second, ars.StateSetDateTime, ars2.StateSetDateTime)),
UpdatedBatchId = @BatchId
FROM dbo.AlertResolutionStateFactvw AS ars
INNER JOIN #temp1 ars1 ON
ars.AlertInstanceDimKey = ars1.AlertInstanceDimKey
AND ars.StateSetDateTime = ars1.StateSetDateTime
LEFT JOIN #temp1 ars2 ON
ars.AlertInstanceDimKey = ars2.AlertInstanceDimKey
AND ars1.StateSetOrder = ars2.StateSetOrder - 1
WHERE ars.TimeInStateSeconds IS NULL
SELECT @Updated = @Updated + @@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.AlertInstanceDimvw dimAlertInst ON (source.DatasourceId = dimAlertInst.SourceId AND source.Id = dimAlertInst.AlertGuid)
LEFT JOIN dbo.AlertDimvw dest ON (source.DatasourceId = dest.SourceId AND source.ProblemId = dest.AlertProblemGuid)
LEFT JOIN dbo.ResolutionStateDimvw dimResSt ON (source.DatasourceId = dimResSt.SourceId and source.ResolutionState = dimResSt.StateValue)
WHERE (dest.AlertDimKey IS NULL
OR dimAlertInst.AlertGuid IS NULL
OR dimResSt.StateValue IS NULL
)
AND source.DWTimeStamp >= @source1WM
AND source.DWTimeStamp < @source1MaxWM
Home