if not exists (select * from sysobjects where type = 'P' AND name = N'TransformGroupContainsConfigItemFactProc')
BEGIN
execute ('create procedure dbo.TransformGroupContainsConfigItemFactProc(@WaterMark xml) as return 1')
END
GO
DECLARE @Statement NVARCHAR(MAX)
SET @Statement=
N'ALTER PROCEDURE [dbo].[TransformGroupContainsConfigItemFactProc](@WaterMark XML)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @BatchId INT,
@WaterMarkBatchId INT,
@Updated INT = 0,
@Inserted INT = 0,
@retval INT = -1,
@err INT = 0,
@startTranCount INT = @@TRANCOUNT,
@startTime DATETIME,
@processedRowCount INT = 0,
@rejectedRowCount INT = 0
BEGIN TRY
--Shred Watermark and get BatchId and present watermarked BatchId
SELECT @task = ''Shredding WaterMark''
SELECT * INTO #tempTable
FROM etl.ShredWaterMark(@WaterMark)
SELECT @task = ''Reading WaterMark for @WaterMarkBatchId and @BatchId''
SELECT @WaterMarkBatchId = WaterMark, @BatchId = BatchId
FROM #tempTable
WHERE WarehouseEntityName = N''EntityRelatesToEntityFact'' AND WaterMarkType = ''BatchId''
-- Get all the rows changed since the last seen BatchId
SELECT @task = ''Preparing #transformTemp0''
SELECT *
INTO #transformTemp0
FROM EntityRelatesToEntityFactvw erefact
WHERE erefact.InsertedBatchId >= @WaterMarkBatchId OR erefact.UpdatedBatchId >= @WaterMarkBatchId
DECLARE @groupTypes AS TABLE(SourceId uniqueidentifier, ManagedTypeDimKey int)
INSERT INTO @groupTypes
SELECT SourceId, ManagedTypeDimKey
FROM dbo.ManagedTypeDimvw
WHERE TypeName = ''Microsoft.SystemCenter.ComputerGroup''
UNION
SELECT DatasourceId, ManagedTypeDimKey
FROM dbo.fn_GetFlattenedManagedTypeHierarchy(''Microsoft.SystemCenter.ComputerGroup'')
INSERT INTO @groupTypes
SELECT SourceId, ManagedTypeDimKey
FROM dbo.ManagedTypeDimvw
WHERE TypeName = ''Microsoft.SystemCenter.InstanceGroup''
UNION
SELECT DatasourceId, ManagedTypeDimKey
FROM dbo.fn_GetFlattenedManagedTypeHierarchy(''Microsoft.SystemCenter.InstanceGroup'')
INSERT INTO #containmentRelationshipTypes
SELECT DatasourceId, RelationshipTypeId, RelationshipTypeName, dimSrcMgdType.ManagedTypeDimKey, dimTrgMgdType.ManagedTypeDimKey
FROM dbo.fn_GetFlattenedRelationshipTypeHierarchy(''System.Containment'') relType
INNER JOIN dbo.ManagedTypeDimvw dimSrcMgdType on relType.DataSourceId = dimSrcMgdType.SourceId AND relType.SourceManagedTypeId = dimSrcMgdType.ManagedTypeId
INNER JOIN dbo.ManagedTypeDimvw dimTrgMgdType on relType.DataSourceId = dimTrgMgdType.SourceId AND relType.TargetManagedTypeId = dimTrgMgdType.ManagedTypeId
CREATE INDEX containmentRel_IX ON #containmentRelationshipTypes(RelationshipTypeId)
-- Scenario when we see that the relationship G1->C1 was added initally, etl was run. Then C1->C2 was added. If etl is run again then G1->C2 will not be picked up
-- That''s why we are adding the G1->C1 back again so that G1->C2 is picked up.
DECLARE @RelationshipTypeDimKey AS INT
SELECT @RelationshipTypeDimKey = RelationshipTypeDimKey from RelationshipTypeDimvw where RelationshipTypeName = ''Microsoft.SystemCenter.ComputerGroupContainsComputer''
INSERT INTO #transformTemp0(
EntityDimKey,
TargetEntityDimKey,
RelationshipTypeDimKey,
CreatedDate,
DeletedDate,
DataSourceDimKey,
DateKey,
UpdatedBatchId,
InsertedBatchId)
SELECT DISTINCT groupDim.EntityDimKey as EntityDimKey,
configItemDim.EntityDimKey as TargetEntityDimKey,
@RelationshipTypeDimKey as RelationshipTypeDimKey,
fact.CreatedDate as CreatedDate,
fact.DeletedDate as DeletedDate,
ereFact.DataSourceDimKey as DataSourceDimKey,
fact.DateKey as DateKey,
0,
0
FROM #transformTemp0 ereFact
INNER JOIN dbo.ConfigItemDimvw configItemDim ON ereFact.EntityDimKey = configItemDim.EntityDimKey
INNER JOIN dbo.GroupContainsConfigItemFactvw fact on configItemDim.ConfigItemDimKey = fact.ConfigItemDimKey
INNER JOIN dbo.GroupDimvw groupDim ON fact.GroupDimKey = groupDim.GroupDimKey
declare @count int
SELECT DISTINCT
IDENTITY(int,1,1) AS Id,
erefact.EntityDimKey AS ContainerKey,
erefact.TargetEntityDimKey AS ChildKey,
erefact.CreatedDate AS CreatedDate,
erefact.DeletedDate AS DeletedDate,
erefact.DataSourceDimKey AS SourceId
into #anchorTable
FROM #transformTemp0 erefact
INNER JOIN RelationshipTypeDimvw relationshipTypeDim on relationshipTypeDim.RelationshipTypeDimKey = erefact.RelationshipTypeDimKey
INNER JOIN #containmentRelationshipTypes containmentTypes on relationshipTypeDim.RelationshipTypeId = containmentTypes.RelationshipTypeId
INNER JOIN @groupTypes groupTypes ON containmentTypes.SourceId = groupTypes.SourceId AND containmentTypes.SourceManagedTypeDimKey = groupTypes.ManagedTypeDimKey
SELECT @count = @@ROWCOUNT
declare @startbatchvalue int = 1
declare @batch int = 500
IF OBJECT_ID(''DWTemp.GroupContainsConfigItem'') IS NOT NULL DROP TABLE DWTemp.GroupContainsConfigItem
--check to see if there is any early arriving data, if there is don''t update the watermark
DECLARE @EarlyArrivingDataCount INT
DECLARE @EarlyArrivingData INT = 0
WHILE ( @count > 0 )
BEGIN
IF OBJECT_ID(''tempdb..#tempTableBase'') IS NOT NULL DROP TABLE #tempTableBase
IF OBJECT_ID(''tempdb..#tempTableResult'') IS NOT NULL DROP TABLE #tempTableResult
IF OBJECT_ID(''tempdb..#GroupContainsConfigItem'') IS NOT NULL DROP TABLE #GroupContainsConfigItem
SELECT *
INTO #tempTableBase
FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY Id) AS rownumb
FROM #anchorTable) anchor
WHERE rownumb >= @startbatchvalue AND rownumb <= (@startbatchvalue + @batch - 1)
;with ContainmentList (ContainerKey, ChildKey, CreatedDate, DeletedDate, SourceId) AS
(
SELECT ContainerKey, ChildKey, CreatedDate, DeletedDate, SourceId FROM #tempTableBase
UNION ALL
SELECT list.ContainerKey AS ContainerKey,
erefact.TargetEntityDimKey AS ChildKey,
case when erefact.CreatedDate > list.CreatedDate then erefact.CreatedDate else list.CreatedDate end AS CreatedDate,
case when isnull(erefact.DeletedDate, ''9999/01/01'') < isnull(list.DeletedDate, ''9999/01/01'') then erefact.DeletedDate else list.DeletedDate end AS DeletedDate,
erefact.DataSourceDimKey As SourceId
FROM ContainmentList list
INNER JOIN EntityRelatesToEntityFactvw erefact ON list.ChildKey = erefact.EntityDimKey
INNER JOIN RelationshipTypeDimvw relationshipTypeDim on relationshipTypeDim.RelationshipTypeDimKey = erefact.RelationshipTypeDimKey
INNER JOIN #containmentRelationshipTypes containmentTypes on relationshipTypeDim.RelationshipTypeId = containmentTypes.RelationshipTypeId
WHERE list.DeletedDate IS NULL
)
SELECT * INTO #tempTableResult
FROM ContainmentList
--Check for early arriving data
IF @EarlyArrivingData = 0
BEGIN
SELECT @EarlyArrivingDataCount = COUNT(*)
FROM #tempTableResult
LEFT JOIN GroupDimvw groupDim ON #tempTableResult.ContainerKey = groupDim.GroupDimKey
LEFT JOIN ConfigItemDimvw configItemDim on #tempTableResult.ChildKey = configItemDim.ConfigItemDimKey
WHERE groupDim.GroupDimKey IS NULL OR configItemDim.ConfigItemDimKey IS NULL
IF ( @EarlyArrivingDataCount > 0 AND @EarlyArrivingData = 0 ) SELECT @EarlyArrivingData = 1
END
SELECT DISTINCT
CONVERT(NVARCHAR(8), MIN(CreatedDate), 112) AS DateKey,
dimGrp.GroupDimKey,
dimConfigItem.ConfigItemDimKey,
MIN(CreatedDate) AS CreatedDate,
MAX(DeletedDate) AS DeletedDate
INTO #GroupContainsConfigItem
FROM #tempTableResult list
INNER JOIN dbo.GroupDimvw dimGrp ON list.ContainerKey = dimGrp.EntityDimKey
INNER JOIN dbo.EntityDimvw dimEntity on list.ChildKey = dimEntity.EntityDimKey
INNER JOIN dbo.ConfigItemDimvw dimConfigItem ON dimEntity.BaseManagedEntityId = dimConfigItem.BaseManagedEntityId
GROUP BY
dimGrp.GroupDimKey,
dimConfigItem.ConfigItemDimKey
-- update the source for the final insert
UPDATE DWTemp.GroupContainsConfigItem SET
CreatedDate = txTemp.CreatedDate,
DeletedDate = NULLIF(txTemp.DeletedDate, ''9999/01/01'')
FROM DWTemp.GroupContainsConfigItem fact
INNER JOIN #GroupContainsConfigItem txTemp ON
fact.DateKey = txTemp.DateKey
AND fact.GroupDimKey = txTemp.GroupDimKey
AND fact.ConfigItemDimKey = txTemp.ConfigItemDimKey
WHERE fact.CreatedDate < txTemp.CreatedDate
OR ISNULL(fact.DeletedDate, ''9999/01/01'') <> ISNULL(txTemp.DeletedDate, ''9999/01/01'')
--Remove any duplicates from the temp table
DELETE txTemp
FROM #GroupContainsConfigItem txTemp
INNER JOIN DWTemp.GroupContainsConfigItem fact ON
fact.GroupDimKey = txTemp.GroupDimKey
AND fact.ConfigItemDimKey = txTemp.ConfigItemDimKey
--Finally insert into the source for the final insert
INSERT INTO DWTemp.GroupContainsConfigItem(DateKey, GroupDimKey, ConfigItemDimKey, CreatedDate, DeletedDate )
SELECT DateKey, GroupDimKey, ConfigItemDimKey, CreatedDate, DeletedDate
FROM #GroupContainsConfigItem
-- update watermark only if there is no early arriving data
IF @EarlyArrivingData = 0
BEGIN
SELECT @task = ''Updating WaterMark''
UPDATE #tempTable SET
WaterMark = @BatchId
WHERE WarehouseEntityName = N''EntityRelatesToEntityFact''
AND WaterMarkType= N''BatchId''
END
SELECT @task = ''Preparing WaterMark for returning''
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
IF OBJECT_ID(''tempdb..#tempTable'') IS NOT NULL DROP TABLE #tempTable
IF OBJECT_ID(''tempdb..#tempTableBase'') IS NOT NULL DROP TABLE #tempTableBase
IF OBJECT_ID(''tempdb..#anchortable'') IS NOT NULL DROP TABLE #anchorTable
IF OBJECT_ID(''tempdb..#transformTemp0'') IS NOT NULL DROP TABLE #transformTemp0
IF OBJECT_ID(''tempdb..#transformRelationships'') IS NOT NULL DROP TABLE #transformRelationships
IF OBJECT_ID(''tempdb..#containmentRelationshipTypes'') IS NOT NULL DROP TABLE #containmentRelationshipTypes
IF OBJECT_ID(''DWTemp.GroupContainsConfigItem'') IS NOT NULL DROP TABLE DWTemp.GroupContainsConfigItem
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)
IF OBJECT_ID(''tempdb..#tempTable'') IS NOT NULL DROP TABLE #tempTable
IF OBJECT_ID(''tempdb..#tempTableBase'') IS NOT NULL DROP TABLE #tempTableBase
IF OBJECT_ID(''tempdb..#anchortable'') IS NOT NULL DROP TABLE #anchorTable
IF OBJECT_ID(''tempdb..#transformTemp0'') IS NOT NULL DROP TABLE #transformTemp0
IF OBJECT_ID(''tempdb..#transformRelationships'') IS NOT NULL DROP TABLE #transformRelationships
IF OBJECT_ID(''tempdb..#containmentRelationshipTypes'') IS NOT NULL DROP TABLE #containmentRelationshipTypes
IF OBJECT_ID(''DWTemp.GroupContainsConfigItem'') IS NOT NULL DROP TABLE DWTemp.GroupContainsConfigItem
Home