--If [dbo].[TransformFIMApprovalResponseFactProc] exists then drop it
IF EXISTS
(
SELECT [name]
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TransformFIMApprovalResponseFactProc]')
AND type in (N'P', N'PC')
)
BEGIN
DROP PROCEDURE [dbo].[TransformFIMApprovalResponseFactProc];
END
GO
CREATE PROCEDURE [dbo].[TransformFIMApprovalResponseFactProc](@WaterMark XML)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET @startTranCount = @@TRANCOUNT;
SET @Inserted = 0; --Not in Generated transform
SET @Updated = 0; --Not in Generated transform
BEGIN TRY
SELECT * INTO #wm FROM [etl].[ShredWaterMark](@WaterMark);
SELECT TOP 1
@ApprovalResponseTimestamp = [WaterMark],
@BatchId = [BatchId]
FROM #wm
WHERE #wm.[WarehouseEntityName] = N'MTV_FIMDW$FIMApprovalResponse';
SET @MaxApprovalResponseTimestamp = GETUTCDATE();
-- Get Approval that have been inserted since the transform ran last.
-- Join to ApprovalDim to get the ApprovalDimKey and join to
-- We are figuring the ApprovalCommittedDate for partitioning optimizations.
SELECT
[INB].[BaseManagedEntityId],
[INB].[DatasourceId],
[FIMApprovalResponseDimKey],
[app_dim].[FIMApprovalDimKey],
[req_dim].[FIMRequestDimKey],
[INB].[FIMDW.FIMApprovalResponse!FIMCreatedDate],
[INB].[FIMDW.FIMApprovalResponse!FIMCreator],
[INB].[FIMDW.FIMApprovalResponse!FIMDisplayName],
[INB].[FIMDW.FIMApprovalResponse!FIMObjectType],
[INB].[FIMDW.FIMApprovalResponse!FIMObjectTypeID],
[INB].[FIMDW.FIMApprovalResponse!FIMUpdatedDate],
[INB].[FIMDW.FIMApprovalResponse!FIMApprovalResponseDecision],
[INB].[FIMDW.FIMApprovalResponse!FIMApprovalResponseDescription],
[INB].[FIMDW.FIMApprovalResponse!FIMApprovalResponseReason],
[INB].[FIMDW.FIMApprovalResponse!FIMApprovalResponseRequestor],
ROW_NUMBER() OVER (PARTITION BY [FIMApprovalResponseDimKey] ORDER BY [INB].LastModified) AS [SeqNum],
[INB].[LastModified] AS [StartDateTime],
CONVERT(NVARCHAR(8), [TimeAdded], 112) AS [ApprovalResponseDateKey]
INTO #Temp1
FROM [inbound].[MTV_FIMDW$FIMApprovalResponse] [INB]
LEFT JOIN [dbo].[FIMApprovalResponseDimvw] [AR_Dim]
ON [AR_Dim].[BaseManagedEntityId] = [INB].[BaseManagedEntityId]
AND [AR_Dim].[SourceId] = [INB].[DatasourceId]
LEFT JOIN [dbo].[FIMApprovalHasApprovalResponsesFactvw] [AhAR_F]
ON [AhAR_F].[FIMApprovalHasApprovalResponses_FIMApprovalResponseDimKey] = [AR_Dim].[FIMApprovalResponseDimKey]
LEFT JOIN [dbo].[FIMApprovalDimvw] [App_Dim]
ON [App_Dim].[FIMApprovalDimKey] = [AhAR_F].[FIMApprovalDimKey]
LEFT JOIN [dbo].[FIMApprovalFactvw] [App_F]
ON [App_F].[FIMApprovalDimKey] = [App_Dim].[FIMApprovalDimKey]
LEFT JOIN [dbo].[FIMRequestDimvw] [Req_Dim]
ON [Req_Dim].[FIMObjectID] = [App_F].[FIMApprovalRequest]
WHERE [INB].[DWTimestamp] >= @ApprovalResponseTimestamp
AND [INB].[DWTimestamp] < @MaxApprovalResponseTimestamp;
-- If there are any early arrivals - e.g. no row in ApprovalResponseDimvw, ApprovalDimvw yet,
-- or No row in the RequestDimVw yet update their DWTimestamp so we
-- get them on the next run.
UPDATE [INB]
SET [DWTimestamp] = @MaxApprovalResponseTimestamp
FROM [inbound].[MTV_FIMDW$FIMApprovalResponse] [INB]
JOIN #Temp1 [App_Dim]
ON [App_Dim].[BaseManagedEntityId] = [INB].[BaseManagedEntityId]
AND [App_Dim].[DatasourceId] = [INB].[DatasourceId]
WHERE [FIMApprovalDimKey] IS NULL
OR [FIMRequestDimKey] IS NULL
OR [FIMApprovalResponseDimKey] IS NULL;
DELETE FROM #Temp1
WHERE [FIMApprovalDimKey] IS NULL
OR [FIMRequestDimKey] IS NULL
OR [FIMApprovalResponseDimKey] IS NULL;
SELECT @MinDateKey = MIN([ApprovalResponseDateKey]) FROM #Temp1;
-- Remove rows that have already been pushed to the ApprovalFact table
-- because Approvals are immutable.
DELETE [aa]
FROM #Temp1 [aa]
JOIN [dbo].[FIMApprovalResponseFactvw] [bb]
ON [bb].[FIMApprovalResponseDimKey] = [aa].[FIMApprovalResponseDimKey];
--Remove duplicate ApprovalResponses from the Temp Table
DELETE FROM #Temp1
WHERE [SeqNum] > 1
-- Move to second temp table so there are no gaps in SeqNum now that all
-- of the early arrivals and duplicates have been removed
SELECT
[FIMApprovalResponseDimKey],
[FIMApprovalDimKey],
[FIMRequestDimKey],
[FIMDW.FIMApprovalResponse!FIMCreatedDate],
[FIMDW.FIMApprovalResponse!FIMCreator],
[FIMDW.FIMApprovalResponse!FIMDisplayName],
[FIMDW.FIMApprovalResponse!FIMObjectType],
[FIMDW.FIMApprovalResponse!FIMObjectTypeID],
[FIMDW.FIMApprovalResponse!FIMUpdatedDate],
[FIMDW.FIMApprovalResponse!FIMApprovalResponseDecision],
[FIMDW.FIMApprovalResponse!FIMApprovalResponseDescription],
[FIMDW.FIMApprovalResponse!FIMApprovalResponseReason],
[FIMDW.FIMApprovalResponse!FIMApprovalResponseRequestor],
ROW_NUMBER() OVER (PARTITION BY [FIMApprovalResponseDimKey] ORDER BY [SeqNum]) AS [SeqNum],
[StartDateTime],
[ApprovalResponseDateKey]
INTO #Temp2
FROM #Temp1;
SELECT @MinDateKey = MIN([ApprovalResponseDateKey]) FROM #Temp2;
-- Insert new facts
INSERT INTO [dbo].[FIMApprovalResponseFactvw]
([DateKey],
[FIMCreatedDate],
[FIMCreator],
[FIMDisplayName],
[FIMObjectType],
[FIMObjectTypeID],
[FIMRequestDimKey],
[FIMUpdatedDate],
[FIMApprovalResponseDecision],
[FIMApprovalResponseDescription],
[FIMApprovalResponseDimKey],
[FIMApprovalResponseReason],
[FIMApprovalResponseRequestor],
[InsertedBatchId],
[UpdatedBatchId])
SELECT
CONVERT(NVARCHAR(8), [aa].[StartDateTime], 112) AS [DateKey],
[aa].[FIMDW.FIMApprovalResponse!FIMCreatedDate],
[aa].[FIMDW.FIMApprovalResponse!FIMCreator],
[aa].[FIMDW.FIMApprovalResponse!FIMDisplayName],
[aa].[FIMDW.FIMApprovalResponse!FIMObjectType],
[aa].[FIMDW.FIMApprovalResponse!FIMObjectTypeID],
[aa].[FIMRequestDimKey],
[aa].[FIMDW.FIMApprovalResponse!FIMUpdatedDate],
[aa].[FIMDW.FIMApprovalResponse!FIMApprovalResponseDecision],
[aa].[FIMDW.FIMApprovalResponse!FIMApprovalResponseDescription],
[aa].[FIMApprovalResponseDimKey],
[aa].[FIMDW.FIMApprovalResponse!FIMApprovalResponseReason],
[aa].[FIMDW.FIMApprovalResponse!FIMApprovalResponseRequestor],
@BatchId,
0
FROM #Temp2 [aa]
LEFT JOIN #Temp2 [bb]
ON [bb].[FIMApprovalResponseDimKey] = [aa].[FIMApprovalResponseDimKey]
AND [bb].[SeqNum] = [aa].[SeqNum] + 1;
SELECT @Inserted = @@ROWCOUNT;
UPDATE #wm
SET [WaterMark] = ISNULL(@MaxApprovalResponseTimestamp, '1/1/1900')
WHERE [WarehouseEntityName] = N'MTV_FIMDW$FIMApprovalResponse'
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