-- Creating Transform in dbo schema for now. Should they be in a different schema?
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformPowerActivityDayFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformPowerActivityDayFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[TransformPowerActivityDayFactProc] (@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 @isReconciled BIT = 1
IF OBJECT_ID('DWTemp.TransformPowerActivityDayFact_Temp') IS NOT NULL DROP TABLE DWTemp.TransformPowerActivityDayFact_Temp
SELECT @task = 'Creating temporary table with all the values that have to be inserted or updated'
SELECT DISTINCT
rel.DatasourceId as dsourceId,
rel.RelationshipId as relId,
rel.TargetEntityId as targetEntityId,
sourceDim.ComputerDimKey as ComputerDimKey,
eventType.PowerActivityRecordEventTypeId as PowerActivityRecordEventTypeId,
pr.[System.Power.ActivityRecord!Hour0] as Hour0,
pr.[System.Power.ActivityRecord!Hour1] as Hour1,
pr.[System.Power.ActivityRecord!Hour2] as Hour2,
pr.[System.Power.ActivityRecord!Hour3] as Hour3,
pr.[System.Power.ActivityRecord!Hour4] as Hour4,
pr.[System.Power.ActivityRecord!Hour5] as Hour5,
pr.[System.Power.ActivityRecord!Hour6] as Hour6,
pr.[System.Power.ActivityRecord!Hour7] as Hour7,
pr.[System.Power.ActivityRecord!Hour8] as Hour8,
pr.[System.Power.ActivityRecord!Hour9] as Hour9,
pr.[System.Power.ActivityRecord!Hour10] as Hour10,
pr.[System.Power.ActivityRecord!Hour11] as Hour11,
pr.[System.Power.ActivityRecord!Hour12] as Hour12,
pr.[System.Power.ActivityRecord!Hour13] as Hour13,
pr.[System.Power.ActivityRecord!Hour14] as Hour14,
pr.[System.Power.ActivityRecord!Hour15] as Hour15,
pr.[System.Power.ActivityRecord!Hour16] as Hour16,
pr.[System.Power.ActivityRecord!Hour17] as Hour17,
pr.[System.Power.ActivityRecord!Hour18] as Hour18,
pr.[System.Power.ActivityRecord!Hour19] as Hour19,
pr.[System.Power.ActivityRecord!Hour20] as Hour20,
pr.[System.Power.ActivityRecord!Hour21] as Hour21,
pr.[System.Power.ActivityRecord!Hour22] as Hour22,
pr.[System.Power.ActivityRecord!Hour23] as Hour23,
etl.GetDateKey(pr.[System.Power.ActivityRecord!Date]) as DateKey,
rel.TimeAdded as CreatedDate
INTO DWTemp.TransformPowerActivityDayFact_Temp
from DWTemp.TransformPowerActivityDayFactProc_Source1 rel
inner join inbound.MTV_System$Power$ActivityRecord pr on rel.TargetEntityId = pr.BaseManagedEntityId
inner join PowerActivityRecordEventType eventType on eventType.EnumTypeId = pr.[System.Power.ActivityRecord!EventType]
inner join ComputerDim sourceDim on rel.SourceEntityId = sourceDim.BaseManagedEntityId
WHERE pr.DWTimestamp >= @source1WM
AND pr.DWTimestamp < @source1MaxWM
SELECT @task = 'Rejecting Early Arriving Data'
UPDATE rel SET
DWTimeStamp = @source1MaxWM,
rel.RejectedCount = ISNULL(rel.RejectedCount, 0) + 1,
BatchId = @BatchId
FROM inbound.MTV_System$DeviceHasPowerActivity rel
LEFT JOIN DWTemp.TransformPowerActivityDayFact_Temp tmp1 ON
(tmp1.relId = rel.RelationshipId AND tmp1.dsourceId = rel.DatasourceId )
WHERE rel.DWTimestamp >= @source1WM
AND rel.DWTimestamp < @source1MaxWM
AND tmp1.relId IS NULL
SELECT @rejectedRowCount = @@ROWCOUNT
--Reject and update timestamp for all the rows in PowerActivityRecord which have *not* been transformed in this batch
-- and are within the WaterMark boundaries OR which have datekey in the future.
UPDATE source SET
DWTimeStamp = @source1MaxWM,
source.RejectedCount = ISNULL(source.RejectedCount, 0) + 1,
BatchId = @BatchId
FROM inbound.MTV_System$Power$ActivityRecord source
LEFT JOIN DWTemp.TransformPowerActivityDayFact_Temp transformedrecords
ON transformedrecords.dsourceId = source.DataSourceId
AND transformedrecords.targetEntityId = source.BaseManagedEntityId
AND source.DWTimestamp >= @source1WM
AND source.DWTimestamp < @source1MaxWM
AND ( transformedrecords.relId IS NULL OR transformedrecords.DateKey > etl.GetDateKey(DATEADD(month, 1, GETUTCDATE())) )
-- It appears we don't have any data to process for now
-- this is unlikely to happen. but if it does happen, then it is better to bail out now
IF(@rejectedRowCount = @rowsToProcess) BREAK;
--Delete those records from the records to be inserted which have been rejected because of datekey issues.
DELETE transformedrecords
FROM DWTemp.TransformPowerActivityDayFact_Temp transformedrecords
INNER JOIN inbound.MTV_System$Power$ActivityRecord source
ON transformedrecords.dsourceId = source.DataSourceId
AND transformedrecords.targetEntityId = source.BaseManagedEntityId
AND transformedrecords.DateKey > etl.GetDateKey(DATEADD(month, 1, GETUTCDATE()))
AND source.DWTimestamp >= @source1WM
AND source.DWTimestamp < @source1MaxWM
Home