IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformAlertParameterFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformAlertParameterFactProc] @WaterMark xml AS RETURN 1')
END
GO
DECLARE @Statement NVARCHAR(MAX)
SET @Statement =
N'ALTER PROCEDURE [dbo].[TransformAlertParameterFactProc](@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
SELECT @task = ''Preparing #AlertParams for upserting into destination Fact''
IF OBJECT_ID(''tempdb..#AlertParams'') IS NOT NULL DROP TABLE #AlertParams
SELECT DISTINCT
dimAlertInst.AlertInstanceDimKey,
dimAlert.AlertDimKey,
alert.TimeRaised,
ROW_NUMBER() OVER(PARTITION BY alert.AlertGuid ORDER BY GETUTCDATE()) AS ParamIdx,
dimParams.AlertParameterDimKey,
CASE WHEN dest.AlertInstanceDimKey IS NULL THEN CAST(0 AS BIT) ELSE 1 END AS IsPresent
INTO #AlertParams
FROM (SELECT DatasourceId,
ProblemId,
MonitoringObjectId,
Id AS AlertGuid,
COALESCE(TimeRaised, TimeAdded) AS TimeRaised,
CAST(AlertParams AS XML) as AlertParams,
ROW_NUMBER() OVER(PARTITION BY DatasourceId, Id ORDER BY LastModified DESC) AS NKey
FROM DWTemp.TransformAlertParameterFactProc_Source1
WHERE AlertParams IS NOT NULL
) AS alert
CROSS APPLY alert.AlertParams.nodes(''AlertParameters/*'') as paramNodes(parameter)
INNER JOIN dbo.AlertParameterDimvw dimParams ON (paramNodes.parameter.value(''.'', ''nvarchar(255)'') = dimParams.ParameterValue)
INNER JOIN dbo.AlertDimvw dimAlert ON (alert.DatasourceId = dimAlert.SourceId AND alert.ProblemId = dimAlert.AlertProblemGuid)
INNER JOIN dbo.AlertInstanceDimvw dimAlertInst ON (alert.AlertGuid = dimAlertInst.AlertGuid)
LEFT JOIN dbo.AlertParameterFactvw dest ON dimAlertInst.AlertInstanceDimKey = dest.AlertInstanceDimKey
WHERE alert.NKey = 1
BEGIN TRANSACTION
SELECT @task = ''Updating destination Fact''
UPDATE dest SET
AlertParameterDimKey1 = ISNULL(source.AlertParameterDimKey1, 0),
AlertParameterDimKey2 = ISNULL(source.AlertParameterDimKey2, 0),
AlertParameterDimKey3 = ISNULL(source.AlertParameterDimKey3, 0),
AlertParameterDimKey4 = ISNULL(source.AlertParameterDimKey4, 0),
AlertParameterDimKey5 = ISNULL(source.AlertParameterDimKey5, 0),
AlertParameterDimKey6 = ISNULL(source.AlertParameterDimKey6, 0),
AlertParameterDimKey7 = ISNULL(source.AlertParameterDimKey7, 0),
AlertParameterDimKey8 = ISNULL(source.AlertParameterDimKey8, 0),
AlertParameterDimKey9 = ISNULL(source.AlertParameterDimKey9, 0),
AlertParameterDimKey10 = ISNULL(source.AlertParameterDimKey10, 0),
UpdatedBatchId = @batchId
FROM dbo.AlertParameterFactvw dest
INNER JOIN (
SELECT
source.AlertInstanceDimKey,
MAX(CASE WHEN ParamIdx = 1 THEN AlertParameterDimKey END) AS AlertParameterDimKey1,
MAX(CASE WHEN ParamIdx = 2 THEN AlertParameterDimKey END) AS AlertParameterDimKey2,
MAX(CASE WHEN ParamIdx = 3 THEN AlertParameterDimKey END) AS AlertParameterDimKey3,
MAX(CASE WHEN ParamIdx = 4 THEN AlertParameterDimKey END) AS AlertParameterDimKey4,
MAX(CASE WHEN ParamIdx = 5 THEN AlertParameterDimKey END) AS AlertParameterDimKey5,
MAX(CASE WHEN ParamIdx = 6 THEN AlertParameterDimKey END) AS AlertParameterDimKey6,
MAX(CASE WHEN ParamIdx = 7 THEN AlertParameterDimKey END) AS AlertParameterDimKey7,
MAX(CASE WHEN ParamIdx = 8 THEN AlertParameterDimKey END) AS AlertParameterDimKey8,
MAX(CASE WHEN ParamIdx = 9 THEN AlertParameterDimKey END) AS AlertParameterDimKey9,
MAX(CASE WHEN ParamIdx = 10 THEN AlertParameterDimKey END) AS AlertParameterDimKey10
FROM #AlertParams source
WHERE source.IsPresent = 1
GROUP BY source.AlertInstanceDimKey
) AS source ON dest.AlertInstanceDimKey = source.AlertInstanceDimKey
SELECT @Updated = @Updated + @@RowCount
SELECT @task = ''Inserting into destination Fact''
INSERT INTO dbo.AlertParameterFactvw (
DateKey,
AlertInstanceDimKey,
AlertDimKey,
AlertParameterDimKey1,
AlertParameterDimKey2,
AlertParameterDimKey3,
AlertParameterDimKey4,
AlertParameterDimKey5,
AlertParameterDimKey6,
AlertParameterDimKey7,
AlertParameterDimKey8,
AlertParameterDimKey9,
AlertParameterDimKey10,
InsertedBatchId,
UpdatedBatchId
)
SELECT
ETL.GetDateKey(source.TimeRaised) AS DateKey,
source.AlertInstanceDimKey,
source.AlertDimKey,
ISNULL(MAX(CASE WHEN ParamIdx = 1 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey1,
ISNULL(MAX(CASE WHEN ParamIdx = 2 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey2,
ISNULL(MAX(CASE WHEN ParamIdx = 3 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey3,
ISNULL(MAX(CASE WHEN ParamIdx = 4 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey4,
ISNULL(MAX(CASE WHEN ParamIdx = 5 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey5,
ISNULL(MAX(CASE WHEN ParamIdx = 6 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey6,
ISNULL(MAX(CASE WHEN ParamIdx = 7 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey7,
ISNULL(MAX(CASE WHEN ParamIdx = 8 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey8,
ISNULL(MAX(CASE WHEN ParamIdx = 9 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey9,
ISNULL(MAX(CASE WHEN ParamIdx = 10 THEN AlertParameterDimKey END), 0) AS AlertParameterDimKey10,
@batchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM #AlertParams source
WHERE source.IsPresent = 0
AND source.TimeRaised IS NOT NULL
GROUP BY
ETL.GetDateKey(source.TimeRaised),
source.AlertInstanceDimKey,
source.AlertDimKey
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.AlertInstanceDimvw AS dimAltInst ON source.Id = dimAltInst.AlertGuid
LEFT JOIN dbo.AlertDimvw dest ON (source.DatasourceId = dest.SourceId AND source.ProblemId = dest.AlertProblemGuid)
WHERE (dest.AlertDimKey IS NULL
OR dimAltInst.AlertGuid IS NULL
)
AND source.DWTimeStamp >= @source1WM
AND source.DWTimeStamp < @source1MaxWM
Home