--If [dbo].[TransformFIMRequestTargetDetailFactProc] exists then drop it
IF EXISTS
(
SELECT [name]
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TransformFIMRequestTargetDetailFactProc]')
AND type in (N'P', N'PC')
)
BEGIN
DROP PROCEDURE [dbo].[TransformFIMRequestTargetDetailFactProc]
END
GO
CREATE PROCEDURE [dbo].[TransformFIMRequestTargetDetailFactProc](@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
@RequestTargetDetailTimestamp = [WaterMark],
@BatchId = [BatchId]
FROM #wm
WHERE #wm.[WarehouseEntityName] = N'MTV_FIMDW$FIMRequestTargetDetail';
SET @MaxRequestTargetDetailTimestamp = 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],
[Req_dim].[FIMRequestDimKey],
[Seq_dim].[FIMSequenceDimKey],
[OT_Dim].[FIMObjectTypeDimKey],
[AT_Dim].[FIMAttributeTypeDimKey],
[INB].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailAttributeValue],
[INB].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailIsReference],
[INB].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailTarget],
[INB].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailMode],
ROW_NUMBER() OVER (PARTITION BY [FIMRequestDimKey],
[FIMSequenceDimKey],
[FIMObjectTypeDimKey],
[FIMAttributeTypeDimKey]
ORDER BY [INB].LastModified) AS [SeqNum],
[INB].[LastModified] AS [StartDateTime],
CONVERT(NVARCHAR(8), [TimeAdded], 112) AS [RequestTargetDetailDateKey]
INTO #Temp1
FROM [inbound].[MTV_FIMDW$FIMRequestTargetDetail] [INB]
LEFT JOIN [dbo].[FIMRequestDimvw] AS [Req_Dim]
ON [INB].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailRequest] = [Req_Dim].[FIMObjectID]
AND [INB].[DatasourceId] = [Req_Dim].[SourceId]
LEFT JOIN [dbo].[FIMSequenceDimvw] AS [Seq_Dim]
ON [INB].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailSequence] = [Seq_Dim].[FIMSequenceID]
LEFT JOIN [dbo].[FIMObjectTypeDimvw] AS [OT_Dim]
ON [INB].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailTargetObjectTypeID] = [OT_Dim].[FIMObjectID]
AND [INB].[DatasourceId] = [OT_Dim].[SourceId]
LEFT JOIN [dbo].[FIMAttributeTypeDimvw] AS [AT_Dim]
ON [INB].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailAttributeTypeID] = [AT_Dim].[FIMObjectID]
AND [INB].[DatasourceId] = [AT_Dim].[SourceId]
WHERE [INB].[DWTimestamp] >= @RequestTargetDetailTimestamp
AND [INB].[DWTimestamp] < @MaxRequestTargetDetailTimestamp;
-- If there are any early arrivals - e.g. no row in RequestDimvw, EntityDimvw, Attribute and Object or Entity yet,
-- or update their DWTimestamp so we
-- get them on the next run.
UPDATE [INB]
SET [DWTimestamp] = @MaxRequestTargetDetailTimestamp
FROM [inbound].[MTV_FIMDW$FIMRequestTargetDetail] [INB]
JOIN #Temp1 [RTD_Fact]
ON [RTD_Fact].[BaseManagedEntityId] = [INB].[BaseManagedEntityId]
AND [RTD_Fact].[DatasourceId] = [INB].[DatasourceId]
WHERE [FIMRequestDimKey] IS NULL
OR [FIMSequenceDimKey] IS NULL
OR [FIMObjectTypeDimKey] IS NULL
OR [FIMAttributeTypeDimKey] IS NULL
--Remove the Early Arrivers from Temp Table
DELETE FROM #Temp1
WHERE [FIMRequestDimKey] IS NULL
OR [FIMSequenceDimKey] IS NULL
OR [FIMObjectTypeDimKey] IS NULL
OR [FIMAttributeTypeDimKey] IS NULL
SELECT @MinDateKey = MIN([RequestTargetDetailDateKey]) FROM #Temp1;
-- Remove rows that have already been pushed to the RTDFact table
-- because RTDFacts are immutable.
DELETE [aa]
FROM #Temp1 [aa]
JOIN [dbo].[FIMRequestTargetDetailFactvw] [bb]
ON [bb].[FIMRequestDimKey] = [aa].[FIMRequestDimKey]
AND [bb].[FIMSequenceDimKey] = [aa].[FIMSequenceDimKey]
AND [bb].[FIMObjectTypeDimKey] = [aa].[FIMObjectTypeDimKey]
AND [bb].[FIMAttributeTypeDimKey] = [aa].[FIMAttributeTypeDimKey]
--Remove duplicate RequestTargetDetails 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
[FIMRequestDimKey],
[FIMSequenceDimKey],
[FIMObjectTypeDimKey],
[FIMAttributeTypeDimKey],
[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailAttributeValue],
[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailIsReference],
[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailTarget],
[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailMode],
ROW_NUMBER() OVER (PARTITION BY [FIMRequestDimKey],
[FIMSequenceDimKey],
[FIMObjectTypeDimKey]
ORDER BY [SeqNum]) AS [SeqNum],
[StartDateTime],
[RequestTargetDetailDateKey]
INTO #Temp2
FROM #Temp1;
SELECT @MinDateKey = MIN([RequestTargetDetailDateKey]) FROM #Temp2;
-- Insert new facts
INSERT INTO [dbo].[FIMRequestTargetDetailFactvw]
([DateKey],
[FIMRequestDimKey],
[FIMSequenceDimKey],
[FIMObjectTypeDimKey],
[FIMAttributeTypeDimKey],
[FIMRequestTargetDetailAttributeValue],
[FIMRequestTargetDetailIsReference],
[FIMRequestTargetDetailTarget],
[FIMRequestRequestStatusMode],
[InsertedBatchId],
[UpdatedBatchId])
SELECT
CONVERT(NVARCHAR(8), [aa].[StartDateTime], 112) AS [DateKey],
[aa].[FIMRequestDimKey],
[aa].[FIMSequenceDimKey],
[aa].[FIMObjectTypeDimKey],
[aa].[FIMAttributeTypeDimKey],
[aa].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailAttributeValue],
[aa].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailIsReference],
[aa].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailTarget],
[aa].[FIMDW.FIMRequestTargetDetail!FIMRequestTargetDetailMode],
@BatchId,
0
FROM #Temp2 [aa]
LEFT JOIN #Temp2 [bb]
ON [bb].[FIMRequestDimKey] = [aa].[FIMRequestDimKey]
AND [bb].[FIMSequenceDimKey] = [aa].[FIMSequenceDimKey]
AND [bb].[FIMObjectTypeDimKey] = [aa].[FIMObjectTypeDimKey]
AND [bb].[FIMAttributeTypeDimKey] = [aa].[FIMAttributeTypeDimKey]
AND [bb].[SeqNum] = [aa].[SeqNum] + 1;
SELECT @Inserted = @@ROWCOUNT;
UPDATE #wm
SET [WaterMark] = ISNULL(@MaxRequestTargetDetailTimestamp, '1/1/1900')
WHERE [WarehouseEntityName] = N'MTV_FIMDW$FIMRequestTargetDetail'
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