-- 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'TransformHardwareAssetLifecycleStatusDurationFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformHardwareAssetLifecycleStatusDurationFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.TransformHardwareAssetLifecycleStatusDurationFactProc (@WaterMark xml)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
-- Get Metadata
DECLARE @BatchId int, @Inserted int, @Updated int, @MaxIncidentTimestamp datetime, @IncidentTimestamp datetime, @MinDateKey int
SET @Inserted = 0
SET @Updated = 0
BEGIN TRY
SELECT * INTO #wm FROM etl.ShredWaterMark(@WaterMark)
SELECT TOP 1 @IncidentTimestamp = WaterMark, @BatchId = BatchId
FROM #wm
WHERE WarehouseEntityName = N'MTV_Provance$ITAM$HardwareAsset'
SET @MaxIncidentTimestamp = GETUTCDATE()
-- Get assets that might have been inserted or had status changed since the transform ran last.
-- Join to HardwareAssetDim to get the HardwareAssetDimKey and join to
-- HardwareAssetLifecycleStatusvw to get the StatusId. We are figuring the HardwareAssetDateKey for
-- partitioning optimizations.
SELECT INB.BaseManagedEntityId, INB.DatasourceId, HardwareAssetDimKey, COALESCE(HardwareAssetLifecycleStatusId, 0) AS HardwareAssetLifecycleStatusId,
ROW_NUMBER() OVER (PARTITION BY HardwareAssetDimKey ORDER BY INB.LastModified) AS SeqNum,
INB.LastModified AS StartDateTime,
CONVERT(nvarchar(8), TimeAdded, 112) AS HardwareAssetDateKey
INTO #Temp1
FROM inbound.MTV_Provance$ITAM$HardwareAsset INB
LEFT JOIN dbo.HardwareAssetDimvw HW_Dim ON HW_Dim.BaseManagedEntityId = INB.BaseManagedEntityId AND
HW_Dim.SourceId = INB.DatasourceId
LEFT JOIN dbo.HardwareAssetLifecycleStatusvw HW_Stat ON HW_Stat.EnumTypeId = INB.[Provance.ITAM.HardwareAsset!LifecycleStatus]
WHERE INB.DWTimestamp >= @IncidentTimestamp AND INB.DWTimestamp < @MaxIncidentTimestamp
-- If there are any early arrivals - e.g. no row in HardwareAssetDimvw yet, update their DWTimestamp so we get them
-- on the next run
UPDATE INB SET DWTimestamp = @MaxIncidentTimestamp
FROM inbound.MTV_Provance$ITAM$HardwareAsset INB
JOIN #Temp1 HW_Dim ON HW_Dim.BaseManagedEntityId = INB.BaseManagedEntityId AND
HW_Dim.DatasourceId = INB.DatasourceId
WHERE HW_Dim.HardwareAssetDimKey IS NULL
DELETE FROM #Temp1 WHERE HardwareAssetDimKey IS NULL
SELECT @MinDateKey = MIN(HardwareAssetDateKey) FROM #Temp1
-- Remove rows that correspond to no change in status
DELETE a
FROM #Temp1 a
JOIN #Temp1 bb ON
a.HardwareAssetDimKey = bb.HardwareAssetDimKey
and a.HardwareAssetLifecycleStatusId = bb.HardwareAssetLifecycleStatusId
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.HardwareAssetLifecycleStatusDurationFactvw bb
ON bb.HardwareAssetDimKey = aa.HardwareAssetDimKey and
bb.HardwareAssetLifecycleStatusId = aa.HardwareAssetLifecycleStatusId 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 HardwareAssetDimKey, HardwareAssetLifecycleStatusId, StartDateTime,
ROW_NUMBER() OVER (PARTITION BY HardwareAssetDimKey ORDER BY SeqNum) AS SeqNum,
HardwareAssetDateKey
INTO #Temp2
FROM #Temp1
SELECT @MinDateKey = MIN(HardwareAssetDateKey) FROM #Temp2
-- Update existing facts where we have a new status
UPDATE HW_status
SET FinishDateTime = tmp.StartDateTime, LifecycleStatusDurationMeasure = DATEDIFF(Minute, HW_status.StartDateTime, tmp.StartDateTime), UpdatedBatchId = @BatchId
FROM dbo.HardwareAssetLifecycleStatusDurationFactvw HW_status
JOIN #Temp2 tmp ON tmp.HardwareAssetDimKey = HW_status.HardwareAssetDimKey AND
tmp.SeqNum = 1 AND HW_status.FinishDateTime IS NULL AND tmp.StartDateTime > HW_status.StartDateTime AND
HW_status.DateKey >= @MinDateKey
SELECT @Updated = @@rowcount
-- Prevent Dup Key problems
DELETE aa
FROM #Temp2 aa
JOIN dbo.HardwareAssetLifecycleStatusDurationFactvw bb
ON bb.HardwareAssetDimKey = aa.HardwareAssetDimKey and
bb.HardwareAssetLifecycleStatusId = aa.HardwareAssetLifecycleStatusId and
bb.StartDateTime = aa.StartDateTime and
bb.DateKey >= @MinDateKey
-- Insert new facts
INSERT INTO dbo.HardwareAssetLifecycleStatusDurationFactvw
(DateKey,
HardwareAssetDimKey,
HardwareAssetLifecycleStatusId,
StartDateTime,
FinishDateTime,
LifecycleStatusDurationMeasure,
InsertedBatchId,
UpdatedBatchId)
SELECT CONVERT(nvarchar(8), aa.StartDateTime, 112) AS DateKey,
aa.HardwareAssetDimKey,
aa.HardwareAssetLifecycleStatusId,
aa.StartDateTime AS StartDateTime,
bb.StartDateTime AS FinishDateTime,
COALESCE(DATEDIFF(Minute, aa.StartDateTime, bb.StartDateTime), 0) AS LifecycleStatusDurationMeasure,
@BatchId,
@BatchId
FROM #Temp2 aa
LEFT JOIN #Temp2 bb ON bb.HardwareAssetDimKey = aa.HardwareAssetDimKey AND bb.SeqNum = aa.SeqNum + 1
SELECT @Inserted = @@rowcount
UPDATE #wm SET WaterMark = isnull(@MaxIncidentTimestamp, '1/1/1900')
WHERE WarehouseEntityName = N'MTV_Provance$ITAM$HardwareAsset' 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