/****** Object: StoredProcedure [dbo].[TransformProgramContainsControlActivitiesFactProc] Script Date: 03/24/2010 04:21:43 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransformProgramContainsControlActivitiesFactProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TransformProgramContainsControlActivitiesFactProc]
GO
/****** Object: StoredProcedure [dbo].[TransformProgramContainsControlActivitiesFactProc] Script Date: 03/24/2010 04:21:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TransformProgramContainsControlActivitiesFactProc](@WaterMark XML)
AS BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
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()
BEGIN TRY
SELECT @task = 'Shredding WaterMark'
SELECT * INTO #tempTable
FROM ETL.ShredWaterMark(@WaterMark)
SELECT @task = 'Reading WaterMark for @source1WM and @BatchId'
SELECT @source1WM = WaterMark, @BatchId = BatchId
FROM #tempTable
WHERE WarehouseEntityName = 'MTV_System$Compliance$ProgramControlActivitySetting' AND WaterMarkType = 'DateTime'
SELECT @task = 'Step 2: Preparing #TransformTemp1 from Inbound'
SELECT source.TimeAdded AS CreatedDate, CAST(NULL AS DATETIME) AS DeletedDate,
sourceDim.ProgramDimKey AS SourceEntityKey, targetDim.ControlActivityDimKey AS TargetEntityKey,
etl.GetDateKey(source.TimeAdded) AS DateKey, DATEPART(HOUR, source.TimeAdded) AS HourId,
source.[System.Compliance.ProgramControlActivitySetting!Threshold] AS Threshold
INTO #transformTemp1
FROM inbound.MTV_System$Compliance$ProgramControlActivitySetting source
JOIN dbo.ProgramDim sourceDim ON (sourceDim.BaseManagedEntityId = source.SourceEntityId AND sourceDim.SourceId = source.DatasourceId)
JOIN dbo.ControlActivityDim targetDim ON (targetDim.BaseManagedEntityId = source.TargetEntityId AND targetDim.SourceId = source.DatasourceId)
WHERE source.DWTimestamp >= @source1WM
AND source.DWTimestamp < @source1MaxWM
SELECT @task = 'Step 3: Preparing #TransformTemp1 from E2EFact'
INSERT INTO #transformTemp1 (
CreatedDate, DeletedDate,
SourceEntityKey, TargetEntityKey,
DateKey, HourId, Threshold
)
SELECT fact.CreatedDate, fact.DeletedDate,
sourceDim.ProgramDimKey, targetDim.ControlActivityDimKey,
fact.DateKey, fact.HourId, 0
FROM (
SELECT fact.CreatedDate, fact.DeletedDate,
fact.EntityDimKey, fact.TargetEntityDimKey,
fact.Datekey, DATEPART(HOUR, fact.CreatedDate) AS HourId,
fact.RelationshipTypeDimKey
FROM dbo.EntityRelatesToEntityFactvw fact
WHERE (fact.InsertedBatchId = @BatchId)
UNION ALL -- given that InsertedBatchId and UpdatedBatchId will never be set for the same record
-- union all or union result in the exact same dataset.
-- Having said that, if there were to be duplicates, the code that generates #transformTemp2 below
-- takes care of the reconciliation of dupes.
-- union all, however, has the advantage of a simplified query plan (sort/distinct operation is eleminated).
SELECT fact.CreatedDate, fact.DeletedDate,
fact.EntityDimKey, fact.TargetEntityDimKey,
fact.Datekey, DATEPART(HOUR, fact.CreatedDate) AS HourId,
fact.RelationshipTypeDimKey
FROM dbo.EntityRelatesToEntityFactvw fact
WHERE (fact.UpdatedBatchId = @BatchId)
) AS fact
JOIN dbo.EntityDimvw dimSrcEnt ON (dimSrcEnt.EntityDimKey = fact.EntityDimKey)
JOIN dbo.EntityDimvw dimTrgEnt ON (dimTrgEnt.EntityDimKey = fact.TargetEntityDimKey)
JOIN dbo.RelationshipTypeDimvw dimRelType ON (dimRelType.RelationshipTypeDimKey = fact.RelationshipTypeDimKey)
JOIN dbo.ProgramDim sourceDim ON (sourceDim.BaseManagedEntityId = dimSrcEnt.BaseManagedEntityId AND sourceDim.SourceId = dimSrcEnt.SourceId)
JOIN dbo.ControlActivityDim targetDim ON (targetDim.BaseManagedEntityId = dimTrgEnt.BaseManagedEntityId AND targetDim.SourceId = dimTrgEnt.SourceId)
WHERE dimRelType.RelationshipTypeId = '8dd87ddc-73c9-1333-aef9-792a7261fc59'
SELECT @task = 'Step 4: Preparing #TransformTemp1 from destination fact'
INSERT INTO #transformTemp1 (
DateKey,
HourId,
SourceEntityKey,
TargetEntityKey,
CreatedDate,
DeletedDate,
Threshold
)
SELECT fact.DateKey,
DATEPART(HOUR, fact.CreatedDate) AS HourId,
fact.ProgramDimKey,
fact.ProgramControlActivitySetting_ControlActivityDimKey,
MAX(fact.CreatedDate) AS CreatedDate,
NULLIF(MAX(ISNULL(fact.DeletedDate, '9999/01/01')), '9999/01/01') AS DeletedDate,
fact.Threshold
FROM dbo.ProgramContainsControlActivitiesFactvw fact
JOIN #transformTemp1 txTemp ON
fact.ProgramDimKey = txTemp.SourceEntityKey
AND fact.ProgramControlActivitySetting_ControlActivityDimKey = txTemp.TargetEntityKey
WHERE fact.CreatedDate <= txTemp.CreatedDate
AND (fact.DeletedDate IS NULL OR fact.DeletedDate >= txTemp.CreatedDate)
GROUP BY fact.DateKey,
DATEPART(HOUR, fact.CreatedDate),
fact.ProgramDimKey,
fact.ProgramControlActivitySetting_ControlActivityDimKey,
fact.Threshold
UNION
SELECT fact.DateKey,
DATEPART(HOUR, fact.CreatedDate) AS HourId,
fact.ProgramDimKey,
fact.ProgramControlActivitySetting_ControlActivityDimKey,
MAX(fact.CreatedDate) AS CreatedDate,
NULLIF(MAX(ISNULL(fact.DeletedDate, '9999/01/01')), '9999/01/01') AS DeletedDate,
fact.Threshold
FROM dbo.ProgramContainsControlActivitiesFactvw fact
JOIN #transformTemp1 txTemp ON
fact.ProgramDimKey = txTemp.SourceEntityKey
AND fact.ProgramControlActivitySetting_ControlActivityDimKey = txTemp.TargetEntityKey
WHERE fact.CreatedDate >= txTemp.CreatedDate
AND (txTemp.DeletedDate IS NULL OR txTemp.DeletedDate >= fact.DeletedDate)
GROUP BY fact.DateKey,
DATEPART(HOUR, fact.CreatedDate),
fact.ProgramDimKey,
fact.ProgramControlActivitySetting_ControlActivityDimKey,
fact.Threshold
SELECT DateKey , SourceEntityKey, TargetEntityKey, MIN(CreatedDate) AS CreatedDate, MAX(DeletedDate) AS DeletedDate, MAX(Threshold) AS Threshold,
ROW_NUMBER() OVER(PARTITION BY DateKey , SourceEntityKey , TargetEntityKey ORDER BY MIN(CreatedDate)) AS CreatedDateSequence,
DENSE_RANK() OVER(ORDER BY DateKey ) PeriodSequence,
ROW_NUMBER() OVER(PARTITION BY SourceEntityKey , TargetEntityKey ORDER BY DateKey ) EntityDimKeySequenceAcrossPeriods
INTO #transformTemp2
FROM #transformTemp1
GROUP BY DateKey , SourceEntityKey, TargetEntityKey
SELECT @task = 'Step 6: Updating #transformTemp2 to match up relationship pairs'
UPDATE tCurrent SET DeletedDate = tNext.CreatedDate
FROM #transformTemp2 tCurrent
INNER JOIN #transformTemp2 tNext ON
-- we are only interested in making sure that there are no conflicts/mismatches for a given Entity.
(tCurrent.SourceEntityKey = tNext.SourceEntityKey)
AND (tCurrent.TargetEntityKey = tNext.TargetEntityKey)
AND (
(
-- For Relationships 'inside' a Period we need to reconcile within the grain
tCurrent.PeriodSequence = tNext.PeriodSequence
-- by matching the next highest CreatedDate (ie, current.CreatedDateSequence = current.CreatedDateSequence + 1)
AND tCurrent.CreatedDateSequence = tNext.CreatedDateSequence - 1
-- and, only if, DeletedDate is null or greater than the next lowest CreatedDate
AND ISNULL(tCurrent.DeletedDate, '9999/01/01') > tNext.CreatedDate
)
-- OR, for Relationships 'outside' or spanning Periods, we need to reconcile across Period grains
OR (
-- by making sure the Periods are different (ie, not the same period as the current Period: current.Period <> next.Period)
tCurrent.PeriodSequence <> tNext.PeriodSequence
-- and getting the 'lowest' possible CreatedDate (ie, tCurrent.EntityDimKeySequenceAcrossPeriods = tNext.EntityDimKeySequenceAcrossPeriods - 1)
AND tCurrent.EntityDimKeySequenceAcrossPeriods = tNext.EntityDimKeySequenceAcrossPeriods - 1
-- and, only if, DeletedDate is null or greater than the next lowest CreatedDate
AND ISNULL(tCurrent.DeletedDate, '9999/01/01') > tNext.CreatedDate
)
)
--
-- Move early arriving data to the next batch by updating DWTimeStamp
--
SELECT @task = 'Update source to move early arriving records to next batch'
UPDATE source SET
DWTimeStamp = GETUTCDATE(),
RejectedCount = ISNULL(RejectedCount, 0) + 1,
BatchId = @BatchId
FROM inbound.MTV_System$Compliance$ProgramControlActivitySetting source
LEFT JOIN dbo.ProgramDim sourceDim ON (sourceDim.BaseManagedEntityId = source.SourceEntityId AND sourceDim.SourceId = source.DatasourceId)
LEFT JOIN dbo.ControlActivityDim targetDim ON (targetDim.BaseManagedEntityId = source.TargetEntityId AND targetDim.SourceId = source.DatasourceId)
WHERE source.DWTimestamp >= @source1WM
AND source.DWTimestamp < @source1MaxWM
AND (sourceDim.BaseManagedEntityId IS NULL OR targetDim.BaseManagedEntityId IS NULL)
BEGIN TRANSACTION
SELECT @task = 'Step 7: Updating destination fact'
UPDATE fact SET
CreatedDate = txTemp.CreatedDate,
DeletedDate = txTemp.DeletedDate,
UpdatedBatchId = @BatchId,
Threshold = txTemp.Threshold
FROM dbo.ProgramContainsControlActivitiesFactvw fact
INNER JOIN #transformTemp2 txTemp ON
fact.DateKey = txTemp.DateKey
AND fact.ProgramDimKey = txTemp.SourceEntityKey
AND fact.ProgramControlActivitySetting_ControlActivityDimKey = txTemp.TargetEntityKey
WHERE fact.CreatedDate <> txTemp.CreatedDate
OR ISNULL(fact.DeletedDate, '9999/01/01') <> ISNULL(txTemp.DeletedDate, '9999/01/01')
SELECT @Updated = @@RowCount,
@err = @@error
SELECT @task = 'Step 8: Getting rid of unnecessary records'
DELETE txTemp
FROM #transformTemp2 txTemp
INNER JOIN dbo.ProgramContainsControlActivitiesFactvw fact ON
fact.DateKey = txTemp.DateKey
AND fact.ProgramDimKey = txTemp.SourceEntityKey
AND fact.ProgramControlActivitySetting_ControlActivityDimKey = txTemp.TargetEntityKey
SELECT @task = 'Step 9: Inserting into destination Fact'
INSERT INTO dbo.ProgramContainsControlActivitiesFactvw (
DateKey,
ProgramDimKey,
ProgramControlActivitySetting_ControlActivityDimKey,
CreatedDate,
DeletedDate,
InsertedBatchId,
UpdatedBatchId,
Threshold
)
SELECT DateKey,
SourceEntityKey,
TargetEntityKey,
CreatedDate,
DeletedDate,
@BatchId AS InsertedBatchId,
0 AS UpdatedBatchId,
Threshold
FROM #transformTemp2 txTemp
COMMIT TRANSACTION
SELECT @task = 'Step 6: Updating WaterMark'
UPDATE #tempTable SET
WaterMark = CONVERT(NVARCHAR(64), @source1MaxWM, 109)
WHERE WarehouseEntityName = N'MTV_System$Compliance$ProgramControlActivitySetting'
AND WaterMarkType= N'DateTime'
SELECT @task = 'Step 7: Preparing WaterMark for returning'
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' WHEN 'Enumeration' THEN 'DateTime' ELSE 'BatchId' END AS "@WaterMarkType",
CASE WarehouseEntityTypeName WHEN 'Inbound' THEN CONVERT(NVARCHAR(64), WaterMark, 109) WHEN 'Enumeration' THEN CONVERT(NVARCHAR(64), WaterMark, 109) ELSE CAST(@BatchId AS NVARCHAR(64)) END AS "@WaterMark"
FROM #tempTable
FOR XML path('Entity'), type)
FROM (SELECT DISTINCT ModuleName, ProcessName from #tempTable) a
FOR XML path('Module'), type)
SELECT @WaterMark AS WaterMark, @BatchId AS BatchId, @Updated AS UpdatedRowCount, @Inserted AS InsertedRowCount
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable
IF OBJECT_ID('tempdb..#transformTemp1') IS NOT NULL DROP TABLE #transformTemp1
IF OBJECT_ID('tempdb..#transformTemp2') IS NOT NULL DROP TABLE #transformTemp2
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)
IF(@@TRANCOUNT > @startTranCount) ROLLBACK TRANSACTION
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable
IF OBJECT_ID('tempdb..#transformTemp1') IS NOT NULL DROP TABLE #transformTemp1
IF OBJECT_ID('tempdb..#transformTemp2') IS NOT NULL DROP TABLE #transformTemp2
Home