/****** Object: StoredProcedure [dbo].[TransformApplicabilityGroupDimProc] Script Date: 05/10/2010 01:44:34 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransformApplicabilityGroupDimProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TransformApplicabilityGroupDimProc]
GO
/****** Object: StoredProcedure [dbo].[TransformApplicabilityGroupDimProc] Script Date: 05/10/2010 01:44:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TransformApplicabilityGroupDimProc](@WaterMark XML)
AS BEGIN
SET NOCOUNT 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, @Count INT, @retval INT = -1, @err INT = 0, @startTranCount INT = @@TRANCOUNT, @source1WM DATETIME, @source1MaxWM DATETIME
BEGIN TRY
SELECT @task = 'Shredding WaterMark'
SELECT * INTO #tempTable FROM etl.ShredWaterMark(@WaterMark)
SELECT @task = 'Reading WaterMark for @source1WM, @BatchId and @source1MaxWM'
SELECT @source1WM = WaterMark, @BatchId = BatchId, @source1MaxWM = MaxWaterMark
FROM #tempTable
WHERE WarehouseEntityName='MTV_System$Compliance$ApplicabilityGroup'
AND WaterMarkType='DateTime'
IF(@source1WM = ISNULL(@source1MaxWM, @source1WM)) SET @source1MaxWM = GETUTCDATE()
source.[System.ConfigItem!AssetStatus],
source.[System.ConfigItem!ObjectStatus],
BaseManagedEntityId,
DatasourceId,
ROW_NUMBER() OVER (PARTITION BY BaseManagedEntityId , DatasourceId ORDER BY DWTimestamp DESC, BaseManagedEntityId) as RowNumber
FROM inbound.MTV_System$Compliance$ApplicabilityGroup source
WHERE source.DWTimestamp >= @source1WM
AND source.DWTimestamp < @source1MaxWM
)
SELECT *
INTO #transformTemp1
FROM TopRecordFromSource source
WHERE source.RowNumber = 1
SELECT @task = 'Adding Clustered Index to #transformTemp1'
DECLARE @temp UNIQUEIDENTIFIER = NEWID()
DECLARE @t NVARCHAR(MAX) = 'CREATE CLUSTERED INDEX [CI1_' + CAST(@temp AS VARCHAR(64)) + '] ON #transformTemp1(BaseManagedEntityId, DatasourceId)'
EXEC(@t)
SELECT @task = 'Updating Dimension'
UPDATE dest SET
[DisplayName] = source.[System.Entity!DisplayName],
[Notes] = source.[System.ConfigItem!Notes],
[AssetStatus_ConfigItemAssetStatusId] = outriggerSrc1.[ConfigItemAssetStatusId],
[AssetStatus] = outriggerSrc1.ID,
[ObjectStatus_ConfigItemObjectStatusId] = outriggerSrc2.[ConfigItemObjectStatusId],
[ObjectStatus] = outriggerSrc2.ID,
[IsDeleted] = baseEntityDim.[IsDeleted],
[SourceId] = source.[DatasourceId],
[UpdatedBatchId] = @BatchId
FROM #transformTemp1 source
INNER JOIN dbo.EntityDim baseEntityDim ON (source.BaseManagedEntityId = baseEntityDim.BaseManagedEntityId AND source.DatasourceId = baseEntityDim.SourceId)
INNER JOIN dbo.ApplicabilityGroupDimvw dest ON (source.BaseManagedEntityId = dest.BaseManagedEntityId AND source.DatasourceId = dest.SourceId)
LEFT JOIN dbo.ConfigItemAssetStatus outriggerSrc1 ON (outriggerSrc1.EnumTypeId = source.[System.ConfigItem!AssetStatus])
LEFT JOIN dbo.ConfigItemObjectStatus outriggerSrc2 ON (outriggerSrc2.EnumTypeId = source.[System.ConfigItem!ObjectStatus])
WHERE dest.BaseManagedEntityId is not null
SELECT @Updated=@@RowCount,@err=@@error
IF(@err<>0)
BEGIN
RAISERROR('Failed to update ApplicabilityGroupDim Dimension', 16,1)
ROLLBACK TRAN
RETURN
END
SELECT @task = 'Inserting into Dimension'
INSERT INTO dbo.ApplicabilityGroupDimvw (
[BaseManagedEntityId],
[EntityDimKey],
[DisplayName],
[Notes],
[AssetStatus_ConfigItemAssetStatusId],
[AssetStatus],
[ObjectStatus_ConfigItemObjectStatusId],
[ObjectStatus],
[IsDeleted],
[SourceId],
[InsertedBatchId],
[UpdatedBatchId]
)
SELECT DISTINCT
baseEntityDim.BaseManagedEntityId,
baseEntityDim.EntityDimKey,
source.[System.Entity!DisplayName],
source.[System.ConfigItem!Notes],
outriggerSrc1.[ConfigItemAssetStatusId],
outriggerSrc1.[ID],
outriggerSrc2.[ConfigItemObjectStatusId],
outriggerSrc2.[ID],
baseEntityDim.[IsDeleted],
source.[DatasourceId],
@BatchId,
0
FROM #transformTemp1 source
INNER JOIN dbo.EntityDim baseEntityDim ON (source.BaseManagedEntityId = baseEntityDim.BaseManagedEntityId AND source.DatasourceId = baseEntityDim.SourceId)
LEFT JOIN dbo.ApplicabilityGroupDimvw dest ON (source.BaseManagedEntityId = dest.BaseManagedEntityId AND source.DatasourceId = dest.SourceId)
LEFT JOIN dbo.ConfigItemAssetStatus outriggerSrc1 ON (outriggerSrc1.EnumTypeId = source.[System.ConfigItem!AssetStatus])
LEFT JOIN dbo.ConfigItemObjectStatus outriggerSrc2 ON (outriggerSrc2.EnumTypeId = source.[System.ConfigItem!ObjectStatus])
WHERE dest.BaseManagedEntityId is null
select @Inserted=@@RowCount,@err=@@error
IF(@err<>0)
BEGIN
RAISERROR('Failed to insert into ApplicabilityGroupDim Dimension', 16,1)
ROLLBACK TRAN
RETURN
END
--
-- Deleted instances do not reach the Staging tables.
-- These events go to Inbound.BME and then to Repository dbo.EntityDim
-- This the reason for the following UPDATE.
-- Make sure to filter data to the current batch
-- and to only records where IsDeleted values do not match
--
SELECT @task = 'Updating Dimension for IsDeleted from EntityDim'
UPDATE dest SET
dest.IsDeleted = baseEntityDim.IsDeleted,
dest.[UpdatedBatchId] = @BatchId
FROM dbo.EntityDim baseEntityDim
INNER JOIN dbo.ApplicabilityGroupDimvw dest ON (baseEntityDim.EntityDimKey = dest.EntityDimKey)
WHERE baseEntityDim.UpdatedBatchId = @BatchId
AND dest.IsDeleted <> baseEntityDim.IsDeleted
IF(@err <> 0)
BEGIN
RAISERROR('Failed to update IsDeleted flags in Dimension', 16,1)
ROLLBACK TRAN
RETURN
END
select Source.*
into #temp2
FROM inbound.MTV_System$Compliance$ApplicabilityGroup source
LEFT JOIN dbo.EntityDim baseEntityDim ON (source.BaseManagedEntityId = baseEntityDim.BaseManagedEntityId AND source.DatasourceId = baseEntityDim.SourceId)
WHERE source.DWTimestamp >= @source1WM AND source.DWTimestamp < @source1MaxWM
AND baseEntityDim.BaseManagedEntityId is null
select @Count = COUNT(*) from #temp2
--IF(@err <> 0)
--BEGIN
-- RAISERROR('Failed to update inbound table for Dimension', 16,1)
-- ROLLBACK TRAN
-- RETURN
--END
COMMIT TRANSACTION
SELECT @task = 'Update #tempTable for WaterMark'
IF(@count > 0) -- we have early arriving data
BEGIN
UPDATE #tempTable SET WaterMark = CONVERT(nvarchar(64), WaterMark,109)
WHERE WarehouseEntityName='MTV_System$Compliance$ApplicabilityGroup' AND WaterMarkType='DateTime'
END
ELSE
UPDATE #tempTable SET WaterMark = CONVERT(nvarchar(64), ISNULL(@source1MaxWM,WaterMark),109)
WHERE WarehouseEntityName='MTV_System$Compliance$ApplicabilityGroup' AND WaterMarkType='DateTime'
IF(@err <> 0)
BEGIN
RAISERROR('Failed to update set watermark', 16,1)
RETURN
END
SELECT @task = 'Building output Watermark XML'
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
SELECT @task = 'Cleaning up by deleting temp tables no longer needed'
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..#temp2') IS NOT NULL DROP TABLE #temp2
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)
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..#temp2') IS NOT NULL DROP TABLE #temp2
Home