if not exists (select * from sysobjects where type = 'P' AND name = N'TransformServiceContainsConfigItemFactProc')
begin
execute ('create procedure dbo.TransformServiceContainsConfigItemFactProc(@WaterMark xml) as return 1')
end
go
DECLARE @Statement NVARCHAR(MAX)
SET @Statement=
N'ALTER PROCEDURE [dbo].[TransformServiceContainsConfigItemFactProc](@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 @serviceTypes AS TABLE(SourceId uniqueidentifier, ManagedTypeDimKey int)
INSERT INTO @serviceTypes
SELECT SourceId, ManagedTypeDimKey
FROM dbo.ManagedTypeDimvw
WHERE TypeName = ''System.Service''
UNION
SELECT DatasourceId, ManagedTypeDimKey
FROM dbo.fn_GetFlattenedManagedTypeHierarchy(''System.Service'')
CREATE INDEX containmentRel_IX ON #containmentRelationshipTypes(RelationshipTypeId)
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
-- Scenario when we see that the relationship S1->G1 was added initally, etl was run. Then G1->C1 was added. If etl is run again then S1->C1 will not be picked up
-- That''s why we are adding the S1->G1 back again so that S1->C1 is picked up.
DECLARE @RelationshipTypeDimKey AS INT
SELECT @RelationshipTypeDimKey = RelationshipTypeDimKey from RelationshipTypeDimvw where RelationshipTypeName = ''Microsoft.SystemCenter.ServiceDesigner.ServiceHasGroups''
INSERT INTO #transformTemp0(
EntityDimKey,
TargetEntityDimKey,
RelationshipTypeDimKey,
CreatedDate,
DeletedDate,
DataSourceDimKey,
DateKey,
UpdatedBatchId,
InsertedBatchId)
SELECT DISTINCT serviceDim.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.ServiceContainsConfigItemFactvw fact on configItemDim.ConfigItemDimKey = fact.ConfigItemDimKey
INNER JOIN dbo.ServiceDimvw serviceDim ON fact.ServiceDimKey = serviceDim.ServiceDimKey
;with ContainmentList (ContainerKey, ChildKey, Level, CreatedDate, DeletedDate, SourceId) AS
(
SELECT erefact.EntityDimKey AS ContainerKey,
erefact.TargetEntityDimKey AS ChildKey,
1 AS Level,
erefact.CreatedDate AS CreatedDate,
erefact.DeletedDate AS DeletedDate,
erefact.DataSourceDimKey AS SourceId
FROM #transformTemp0 erefact
INNER JOIN RelationshipTypeDimvw relationshipTypeDim on relationshipTypeDim.RelationshipTypeDimKey = erefact.RelationshipTypeDimKey
INNER JOIN #containmentRelationshipTypes containmentTypes on relationshipTypeDim.RelationshipTypeId = containmentTypes.RelationshipTypeId
INNER JOIN @serviceTypes serviceTypes ON containmentTypes.SourceId = serviceTypes.SourceId AND containmentTypes.SourceManagedTypeDimKey = serviceTypes.ManagedTypeDimKey
UNION ALL
SELECT list.ContainerKey AS ContainerKey,
erefact.TargetEntityDimKey AS ChildKey,
list.Level + 1 AS Level,
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 #transformRelationships
FROM ContainmentList
-- Finding all ServiceContainsCI relationships ( not including those that are early arriving )
SELECT DISTINCT
CONVERT(NVARCHAR(8), MIN(CreatedDate), 112) AS DateKey,
dimSvc.ServiceDimKey,
dimConfigItem.ConfigItemDimKey,
MIN(CreatedDate) AS CreatedDate,
MAX(DeletedDate) AS DeletedDate
INTO #transformMatches
FROM #transformRelationships list
INNER JOIN dbo.ServiceDimvw dimSvc ON list.ContainerKey = dimSvc.EntityDimKey
INNER JOIN dbo.EntityDimvw dimEntity on list.ChildKey = dimEntity.EntityDimKey
INNER JOIN dbo.ConfigItemDimvw dimConfigItem ON dimEntity.BaseManagedEntityId = dimConfigItem.BaseManagedEntityId
GROUP BY
dimSvc.ServiceDimKey,
dimConfigItem.ConfigItemDimKey
BEGIN TRANSACTION
SELECT @task = ''Updating destination fact''
UPDATE fact SET
CreatedDate = txTemp.CreatedDate,
DeletedDate = NULLIF(txTemp.DeletedDate, ''9999/01/01''),
UpdatedBatchId = @BatchId
FROM dbo.ServiceContainsConfigItemFactvw fact
INNER JOIN #transformMatches txTemp ON
fact.DateKey = txTemp.DateKey
AND fact.ServiceDimKey = txTemp.ServiceDimKey
AND fact.ConfigItemDimKey = txTemp.ConfigItemDimKey
WHERE fact.CreatedDate < txTemp.CreatedDate
OR ISNULL(fact.DeletedDate, ''9999/01/01'') <> ISNULL(txTemp.DeletedDate, ''9999/01/01'')
--check to see if there is any early arriving data, if there is don''t update the watermark
DECLARE @EarlyArrivingDataCount INT
SELECT @EarlyArrivingDataCount = COUNT(*)
FROM #transformRelationships
LEFT JOIN ServiceDimvw serviceDim ON #transformRelationships.ContainerKey = serviceDim.EntityDimKey
LEFT JOIN ConfigItemDimvw configItemDim on #transformRelationships.ChildKey = configItemDim.EntityDimKey
WHERE serviceDim.ServiceDimKey IS NULL OR configItemDim.ConfigItemDimKey IS NULL
-- update watermark only if there is no early arriving data
IF @EarlyArrivingDataCount = 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..#t'') IS NOT NULL DROP TABLE #t
IF OBJECT_ID(''tempdb..#transformTemp0'') IS NOT NULL DROP TABLE #transformTemp0
IF OBJECT_ID(''tempdb..#transformMatches'') IS NOT NULL DROP TABLE #transformMatches
IF OBJECT_ID(''tempdb..#transformRelationships'') IS NOT NULL DROP TABLE #transformRelationships
IF OBJECT_ID(''tempdb..#containmentRelationshipTypes'') IS NOT NULL DROP TABLE #containmentRelationshipTypes
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)
IF OBJECT_ID(''tempdb..#tempTable'') IS NOT NULL DROP TABLE #tempTable
IF OBJECT_ID(''tempdb..#t'') IS NOT NULL DROP TABLE #t
IF OBJECT_ID(''tempdb..#transformTemp0'') IS NOT NULL DROP TABLE #transformTemp0
IF OBJECT_ID(''tempdb..#transformMatches'') IS NOT NULL DROP TABLE #transformMatches
IF OBJECT_ID(''tempdb..#transformRelationships'') IS NOT NULL DROP TABLE #transformRelationships
IF OBJECT_ID(''tempdb..#containmentRelationshipTypes'') IS NOT NULL DROP TABLE #containmentRelationshipTypes
Home