/****** Object: StoredProcedure [dbo].[TransformControlActivityStatusDurationFactProc] Script Date: 02/15/2010 01:55:40 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransformControlActivityStatusDurationFactProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TransformControlActivityStatusDurationFactProc]
GO
/****** Object: StoredProcedure [dbo].[TransformControlActivityStatusDurationFactProc] Script Date: 02/15/2010 01:55:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TransformControlActivityStatusDurationFactProc] (@WaterMark xml)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
-- Get Metadata
DECLARE @BatchId int, @Inserted int, @Updated int, @MaxActivityTimestamp datetime, @ActivityTimestamp datetime, @MinDateKey int
SET @Inserted = 0
SET @Updated = 0
BEGIN TRY
SELECT * INTO #wm FROM etl.ShredWaterMark(@WaterMark)
SELECT TOP 1 @ActivityTimestamp = WaterMark, @BatchId = BatchId
FROM #wm
WHERE WarehouseEntityName = N'MTV_System$Compliance$ControlActivity'
SELECT @MaxActivityTimestamp = COALESCE(MAX(DWTimestamp), GETUTCDATE()) FROM inbound.MTV_System$Compliance$ControlActivity
-- Get activities that might have been inserted or had status changed since the transform ran last.
-- Join to EntityDim and ControlActivityDim to get the ControlActivityDimKey and join to
-- CAStatusvw to get the StatusId
SELECT ControlActivityDimKey, COALESCE(CAStatusId, 0) AS CAStatusId,
ROW_NUMBER() OVER (PARTITION BY ControlActivityDimKey ORDER BY INB.LastModified) AS SeqNum,
INB.LastModified AS StartDateTime,
INB.LastModified AS LastModifiedDateTime,
CONVERT(nvarchar(8), CreatedDate, 112) AS ControlActivityDateKey
INTO #Temp1
FROM inbound.MTV_System$Compliance$ControlActivity INB
JOIN dbo.ControlActivityDimvw Act_Dim ON Act_Dim.BaseManagedEntityId = INB.BaseManagedEntityId AND
Act_Dim.SourceId = INB.DatasourceId
LEFT JOIN dbo.CAStatusvw Act_Stat ON Act_Stat.EnumTypeId = INB.[System.Compliance.ControlActivity!ControlActivityStatus]
WHERE INB.DWTimestamp > @ActivityTimestamp AND INB.DWTimestamp <= @MaxActivityTimestamp
SELECT @MinDateKey = MIN(ControlActivityDateKey) FROM #Temp1
-- Remove rows that correspond to no change in status
DELETE a
FROM #Temp1 a
JOIN #Temp1 bb ON
a.ControlActivityDimKey = bb.ControlActivityDimKey
and a.CAStatusId = bb.CAStatusId
and a.SeqNum = bb.SeqNum+1
-- Join to Fact table to remove more rows that correspond to no change in status
DELETE aa
FROM #Temp1 aa
JOIN dbo.ControlActivityStatusDurationFactvw bb
ON bb.ControlActivityDimKey = aa.ControlActivityDimKey and
bb.CAStatusId = aa.CAStatusId and
bb.FinishDateTime IS NULL and aa.SeqNum = 1 and
bb.DateKey >= @MinDateKey
-- Move to second temp table so there are no gaps in SeqNum now that all
-- of the "no change in status" rows have been removed
SELECT ControlActivityDimKey, CAStatusId, StartDateTime, LastModifiedDateTime,
ROW_NUMBER() OVER (PARTITION BY ControlActivityDimKey ORDER BY SeqNum) AS SeqNum
INTO #Temp2
FROM #Temp1
SELECT @MinDateKey = MIN(ControlActivityDateKey) FROM #Temp1
-- Update existing facts where we have a new status
UPDATE Act_Status
SET FinishDateTime = tmp.StartDateTime, UpdatedBatchId = @BatchId
FROM dbo.ControlActivityStatusDurationFactvw Act_Status
JOIN #Temp2 tmp ON tmp.ControlActivityDimKey = Act_Status.ControlActivityDimKey AND
tmp.SeqNum = 1 AND Act_Status.FinishDateTime IS NULL AND tmp.StartDateTime > Act_Status.StartDateTime AND
Act_Status.DateKey >= @MinDateKey
SELECT @Updated = @@rowcount
-- Prevent Dup Key Problems
DELETE aa
FROM #Temp2 aa
JOIN dbo.ControlActivityStatusDurationFactvw bb
ON bb.ControlActivityDimKey = aa.ControlActivityDimKey and
bb.CAStatusId = aa.CAStatusId and
bb.StartDateTime = aa.StartDateTime and
bb.DateKey >= @MinDateKey
-- Insert new facts
INSERT INTO dbo.ControlActivityStatusDurationFactvw
(DateKey,
ControlActivityDimKey,
CAStatusId,
StartDateTime,
FinishDateTime,
LastModifiedDateTime,
-- ControlActivityTotalTimeMeasure,
InsertedBatchId,
UpdatedBatchId)
SELECT CONVERT(nvarchar(8), aa.StartDateTime, 112) AS DateKey,
aa.ControlActivityDimKey,
aa.CAStatusId,
aa.StartDateTime AS StartDateTime,
bb.StartDateTime AS FinishDateTime,
aa.LastModifiedDateTime AS LastModifiedDateTime,
-- COALESCE(DATEDIFF(Minute, aa.StartDateTime, bb.StartDateTime), 0) AS ControlActivityTotalTimeMeasure,
@BatchId,
@BatchId
FROM #Temp2 aa
LEFT JOIN #Temp2 bb ON bb.ControlActivityDimKey = aa.ControlActivityDimKey AND bb.SeqNum = aa.SeqNum + 1
UPDATE #wm SET WaterMark = isnull(@MaxActivityTimestamp, '1/1/1900')
WHERE WarehouseEntityName = N'MTV_System$Compliance$ControlActivity' and WaterMarkType= N'DateTime'
SELECT @WaterMark = (SELECT ModuleName AS "@ModuleName",
ProcessName AS "@ProcessName",
@BatchId AS "@BatchId",
(SELECT DISTINCT WarehouseEntityName AS "@WarehouseEntityName",
WarehouseEntityTypeName AS "@WarehouseEntityTypeName",
EntityGuid AS "@EntityGuid",
CASE WarehouseEntityTypeName
WHEN 'Inbound' THEN 'DateTime'
ELSE 'BatchId' END AS "@WaterMarkType",
WaterMark as "@WaterMark"
FROM #wm
FOR XML path('Entity'), type)
FROM (SELECT DISTINCT ModuleName, ProcessName from #wm) a
FOR XML path('Module'), type)
SELECT @WaterMark as WaterMark, @BatchId as BatchId, @Updated as UpdatedRowCount, @Inserted as InsertedRowCount
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)
Home