IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformAlertInstanceFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformAlertInstanceFactProc] @WaterMark xml AS RETURN 1')
END
GO
DECLARE @Statement NVARCHAR(MAX)
SET @Statement =
N'ALTER PROCEDURE [dbo].[TransformAlertInstanceFactProc](@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
DECLARE @incidentsWithAlerts TABLE(IncidentDimKey INT, AlertId UNIQUEIDENTIFIER, AlertInstanceDimKey INT NULL)
SELECT @task = ''Creating indices on _Source1''
IF NOT EXISTS(select ''x'' from sys.indexes where name = ''NCI1_TxAlertInstanceFactProc'')
BEGIN
CREATE INDEX NCI1_TxAlertInstanceFactProc ON DWTemp.TransformAlertInstanceFactProc_Source1 (TimeRaised)
INCLUDE (Id,MonitoringObjectId,ProblemId,MonitoringRuleId,Priority,Severity,Category,LastModified,LastModifiedBy,RepeatCount, AlertStringId,SiteName,DatasourceId,IncidentDimKey)
CREATE INDEX NCI2_TxAlertInstanceFactProc ON DWTemp.TransformAlertInstanceFactProc_Source1 (Category, TimeRaised)
INCLUDE (Id,MonitoringObjectId,ProblemId,MonitoringRuleId,Priority,Severity,LastModified,LastModifiedBy,RepeatCount, AlertStringId,SiteName,DatasourceId,IncidentDimKey)
END
SELECT @task = ''Retaining the latest data for a given Id''
DELETE source
FROM DWTemp.TransformAlertInstanceFactProc_Source1 AS source
INNER JOIN DWTemp.TransformAlertInstanceFactProc_Source1 latest ON
source.Id = latest.Id
AND (source.RepeatCount < latest.RepeatCount
OR (source.RepeatCount = latest.RepeatCount AND source.DWId < latest.DWId)
)
/*
* BUG 212946: Missing MP dependency between Alert MP and Incident Library. Datawarehouse MP.
* Cube infra does not have a facility to corelate facts/dims from different sources.
* However, for Alerts to be meaningful, we need a way to associate Incident information with Alert fact.
* This should have been ideally established as an MP reference dependency.
* However, adding a dependency on Incident.DW MP (in which the IncidentDim has been declared) from the AlertMP was not possible because
* the Incident.DW MP directly/indirectly ends up referencing a significant number of MPs including several MPs related to views.
* As a result, we are doing this work around to ensure dependency on IncidentDim is a "soft dependency" only.
*/
SELECT @task = ''Updating IncidentDimKey from destination''
IF EXISTS(SELECT ''x'' FROM sys.columns WHERE object_id = OBJECT_ID(''IncidentDim'') and name = ''AlertId'')
BEGIN
-- 1 to 1 mapping between Alerts and Incidents (even across multiple sources).
-- confirmed with Darshat, Vijay and Mark
DECLARE @t NVARCHAR(MAX) = ''
UPDATE source SET
IncidentDimKey = dimInc.IncidentDimKey
FROM DWTemp.TransformAlertInstanceFactProc_Source1 source
INNER JOIN dbo.IncidentDimvw dimInc ON source.Id = dimInc.AlertId
''
EXEC (@t)
SELECT @t = ''SELECT IncidentDimKey, AlertId, NULL FROM dbo.IncidentDimvw WHERE InsertedBatchId = @currentBatchId''
INSERT INTO @incidentsWithAlerts(IncidentDimKey, AlertId, AlertInstanceDimKey)
EXEC sp_executesql
@statement = @t,
@parameters = N''@currentBatchId INT'',
@currentBatchId = @batchId
SELECT @t = ''SELECT IncidentDimKey, AlertId, NULL FROM dbo.IncidentDimvw WHERE UpdatedBatchId = @currentBatchId''
INSERT INTO @incidentsWithAlerts(IncidentDimKey, AlertId, AlertInstanceDimKey)
EXEC sp_executesql
@statement = @t,
@parameters = N''@currentBatchId INT'',
@currentBatchId = @batchId
END
UPDATE t SET
AlertInstanceDimKey = dimAltInst.AlertInstanceDimKey
FROM @incidentsWithAlerts t
INNER JOIN dbo.AlertInstanceDimvw dimAltInst ON t.AlertId = dimAltInst.AlertGuid
BEGIN TRANSACTION
SELECT @task = ''Updating destination Fact''
UPDATE factAlt SET
AlertCategoryId = outCtg.AlertCategoryId,
AlertPriorityId = outPri.AlertPriorityId,
AlertSeverityId = outSev.AlertSeverityId,
RuleDimKey = ISNULL(dimRule.RuleDimKey, 0),
MonitorDimKey = ISNULL(dimMon.MonitorDimKey, 0),
ManagementPackDimKey = ISNULL(dimMP.ManagementPackDimKey, 0),
EntityDimKey = baseEntityDim.EntityDimKey,
DisplayStringDimKey = ISNULL(dimDisp.DisplayStringDimKey, 0),
IncidentDimKey = ISNULL(source.IncidentDimKey, 0),
AlertSiteId = ISNULL(outSite.AlertSiteId, 0),
LastModifiedByUser = source.LastModifiedBy,
LastModifiedTime = source.LastModified,
RepeatCount = source.RepeatCount,
UpdatedBatchId = @BatchId
FROM DWTemp.TransformAlertInstanceFactProc_Source1 AS source
INNER JOIN dbo.AlertInstanceDimvw AS dimAltInst ON source.Id = dimAltInst.AlertGuid
INNER JOIN dbo.AlertDimvw dest ON source.DatasourceId = dest.SourceId AND source.ProblemId = dest.AlertProblemGuid
INNER JOIN dbo.AlertInstanceFactvw factAlt ON dimAltInst.AlertInstanceDimKey = factAlt.AlertInstanceDimKey
INNER JOIN dbo.EntityDim baseEntityDim ON source.DatasourceId = baseEntityDim.SourceId AND source.MonitoringObjectId = baseEntityDim.BaseManagedEntityId
INNER JOIN dbo.AlertCategoryvw outCtg ON source.Category = outCtg.AlertCategoryValue
INNER JOIN dbo.AlertPriority outPri ON CAST(source.Priority AS VARCHAR(32)) = outPri.AlertPriorityValue
INNER JOIN dbo.AlertSeverity outSev ON CAST(source.Severity AS VARCHAR(32)) = outSev.AlertSeverityValue
LEFT JOIN dbo.RuleDimvw dimRule ON source.DatasourceId = dimRule.SourceId AND source.MonitoringRuleId = dimRule.RuleId
LEFT JOIN dbo.MonitorDimvw dimMon ON source.DatasourceId = dimMon.SourceId AND source.MonitoringRuleId = dimMon.MonitorGuid
LEFT JOIN dbo.ManagementPackDimvw dimMP ON source.DatasourceId = dimMP.SourceId AND dimMP.ManagementPackId = COALESCE(dimRule.ManagementPackId, dimMon.ManagementPackId)
LEFT JOIN dbo.DisplayStringDimvw dimDisp ON source.AlertStringId = dimDisp.BaseManagedEntityId and dimDisp.LanguageCode = source.LanguageCode
LEFT JOIN dbo.AlertSite outSite ON source.SiteName = outSite.AlertSiteValue
WHERE factAlt.RepeatCount < source.RepeatCount
OR factAlt.IncidentDimKey <> source.IncidentDimKey
SELECT @Updated = @Updated + @@RowCount
SELECT @task = ''Updating destination Fact - IncidentDimKey''
UPDATE factAlt SET
IncidentDimKey = t.IncidentDimKey
FROM dbo.AlertInstanceFactvw factAlt
INNER JOIN @incidentsWithAlerts t ON factAlt.AlertInstanceDimKey = t.AlertInstanceDimKey
SELECT @Updated = @Updated + @@RowCount
SELECT @task = ''Deleting Alerts that are already processed''
DELETE source
FROM DWTemp.TransformAlertInstanceFactProc_Source1 AS source
INNER JOIN dbo.AlertInstanceDimvw AS dimAltInst ON source.Id = dimAltInst.AlertGuid
INNER JOIN dbo.AlertInstanceFactvw dest ON dimAltInst.AlertInstanceDimKey = dest.AlertInstanceDimKey
SELECT @task = ''Inserting into destination Fact''
INSERT INTO dbo.AlertInstanceFactvw (
DateKey,
AlertInstanceDimKey,
AlertDimKey,
AlertCategoryId,
AlertPriorityId,
AlertSeverityId,
RuleDimKey,
MonitorDimKey,
ManagementPackDimKey,
EntityDimKey,
DisplayStringDimKey,
IncidentDimKey,
AlertSiteId,
LastModifiedByUser,
LastModifiedTime,
RepeatCount,
TimeRaised,
InsertedBatchId,
UpdatedBatchId
)
SELECT DISTINCT
etl.GetDateKey(COALESCE(source.TimeRaised, source.TimeAdded)) AS DateKey,
dimAltInst.AlertInstanceDimKey,
dimAlt.AlertDimKey,
outCtg.AlertCategoryId,
outPri.AlertPriorityId,
outSev.AlertSeverityId,
ISNULL(dimRule.RuleDimKey, 0),
ISNULL(dimMon.MonitorDimKey, 0),
ISNULL(dimMP.ManagementPackDimKey, 0),
baseEntityDim.EntityDimKey,
ISNULL(dimDisp.DisplayStringDimKey, 0),
ISNULL(source.IncidentDimKey, 0),
ISNULL(outSite.AlertSiteId, 0),
source.LastModifiedBy,
source.LastModified,
source.RepeatCount,
COALESCE(source.TimeRaised, source.TimeAdded),
@BatchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM DWTemp.TransformAlertInstanceFactProc_Source1 AS source
INNER JOIN dbo.AlertInstanceDimvw AS dimAltInst ON source.Id = dimAltInst.AlertGuid
INNER JOIN dbo.EntityDim baseEntityDim ON source.DatasourceId = baseEntityDim.SourceId AND source.MonitoringObjectId = baseEntityDim.BaseManagedEntityId
INNER JOIN dbo.AlertDimvw dimAlt ON source.DatasourceId = dimAlt.SourceId AND source.ProblemId = dimAlt.AlertProblemGuid
INNER JOIN dbo.AlertCategoryvw outCtg ON source.Category = outCtg.AlertCategoryValue
INNER JOIN dbo.AlertPriority outPri ON CAST(source.Priority AS VARCHAR(32)) = outPri.AlertPriorityValue
INNER JOIN dbo.AlertSeverity outSev ON CAST(source.Severity AS VARCHAR(32)) = outSev.AlertSeverityValue
LEFT JOIN dbo.RuleDimvw dimRule ON source.DatasourceId = dimRule.SourceId AND source.MonitoringRuleId = dimRule.RuleId
LEFT JOIN dbo.MonitorDimvw dimMon ON source.DatasourceId = dimMon.SourceId AND source.MonitoringRuleId = dimMon.MonitorGuid
LEFT JOIN dbo.ManagementPackDimvw dimMP ON source.DatasourceId = dimMP.SourceId AND dimMP.ManagementPackId = COALESCE(dimRule.ManagementPackId, dimMon.ManagementPackId)
LEFT JOIN dbo.DisplayStringDimvw dimDisp ON source.AlertStringId = dimDisp.BaseManagedEntityId and dimDisp.LanguageCode = source.LanguageCode
LEFT JOIN dbo.AlertSite outSite ON source.SiteName = outSite.AlertSiteValue
WHERE COALESCE(source.TimeRaised, source.TimeAdded) IS NOT NULL
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.EntityDim baseEntityDim ON source.DatasourceId = baseEntityDim.SourceId AND source.MonitoringObjectId = baseEntityDim.BaseManagedEntityId
LEFT JOIN dbo.AlertDimvw dimAlt ON source.DatasourceId = dimAlt.SourceId AND source.ProblemId = dimAlt.AlertProblemGuid
LEFT JOIN dbo.AlertCategoryvw outCtg ON source.Category = outCtg.AlertCategoryValue
LEFT JOIN dbo.AlertPriority outPri ON CAST(source.Priority AS VARCHAR(32)) = outPri.AlertPriorityValue
LEFT JOIN dbo.AlertSeverity outSev ON CAST(source.Severity AS VARCHAR(32)) = outSev.AlertSeverityValue
WHERE (dimAltInst.AlertInstanceDimKey IS NULL
OR baseEntityDim.EntityDimKey IS NULL
OR dimAlt.AlertDimKey IS NULL
OR outCtg.AlertCategoryId IS NULL
OR outPri.AlertPriorityId IS NULL
OR outSev.AlertSeverityId IS NULL
)
AND source.DWTimeStamp >= @source1WM
AND source.DWTimeStamp < @source1MaxWM
SELECT @task = ''Dropping indices on _Source1''
IF EXISTS(select ''x'' from sys.indexes where name = ''NCI1_TxAlertInstanceFactProc'')
BEGIN
DROP INDEX NCI1_TxAlertInstanceFactProc ON DWTemp.TransformAlertInstanceFactProc_Source1
DROP INDEX NCI2_TxAlertInstanceFactProc ON DWTemp.TransformAlertInstanceFactProc_Source1
END
Home