IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformProvanceBFASoftwareInstallCoverageDailyFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformProvanceBFASoftwareInstallCoverageDailyFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[TransformProvanceBFASoftwareInstallCoverageDailyFactProc] (@WaterMark xml)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
In ServiceManager the logical key for BFASoftwareInstallCoverage is DeviceGuid and VersionGuid, our prefered logical key in the DW would be DeviceGuid, VersionGuid and StartDay
The "system" will add ProvanceBFASoftwareInstallCoverageDimKey and DateKey to the Fact and to the Primary Key, these should never be updated.
The BFA process can be run many times during a day, we want the last run of each day to be saved.
The BFA process may not run every day, or a title may not change every day, the DailyFact will have a StartDay and EndDay field, these represent the days this record was in effect.
The extract process may run many times before the transform process, this could give us many records for a given logical key, we keep the last record for each day, the EndDay of each will be the StartDay -1
of the next record.
The Timestamp field on the inbound.MTV_Provance$Class$BFASoftwareInstallCoverage must always move forward. If it is set to a date previous to its current value this procedure could fail
or produce inconsitant results.
insert into t_log (t_xml,t_date) values (@WaterMark,getdate())
begin transaction
insert into t_log (t_xml,t_date) values (null,getdate())
rollback
create table T_log (t_id int not null identity,t_xml xml,t_date datetime)
SELECT @task = 'Shreding WaterMark.'
SELECT * INTO #wm FROM etl.ShredWaterMark(@WaterMark)
SELECT @task = 'Getting Water Mark Date and BatchId.'
SELECT TOP 1 @WaterMarkDate = WaterMark, @BatchId = BatchId
FROM #wm
WHERE WarehouseEntityName = N'MTV_Provance$Class$BFA$SoftwareInstallCoverage'
SET @CurrentUtcDate = GETUTCDATE()
-- Get the inbound data
-- Join to ProvanceBFASoftwareInstallCoverageDimvw to get the ProvanceBFASoftwareInstallCoverageDimKey
SELECT @task = 'Moving inbound data to #Temp1'
select CAST(CONVERT(nvarchar(8), a.TimeStamp, 112) AS INT) AS StartDay,
CAST(NULL AS int) AS EndDay,
ROW_NUMBER() OVER (PARTITION BY a.SoftwareTitleGuid
ORDER BY a.TimeStamp ) AS KeySeqNum,
CAST(CONVERT(nvarchar(8), a.TimeAdded, 112) AS INT) AS DateKey,
A.*
into #Temp1
from ( SELECT INB.[Provance.Class.BFA.SoftwareInstallCoverage!DeviceGuid] AS DeviceGuid,
INB.BaseManagedEntityId AS BaseManagedEntityId,
INB.[Provance.Class.BFA.SoftwareInstallCoverage!BatchId] AS BatchId,
INB.[System.Entity!DisplayName] AS DisplayName,
INB.[Provance.Class.BFA.SoftwareInstallCoverage!VersionGuid] AS VersionGuid,
INB.[Provance.Class.BFA.SoftwareInstallCoverage!AssignmentType] AS AssignmentType,
INB.[Provance.Class.BFA.SoftwareInstallCoverage!Covered] AS Covered,
INB.[Provance.Class.BFA.SoftwareInstallCoverage!ResolutionPathId] AS ResolutionPathId,
INB.[Provance.Class.BFA.SoftwareInstallCoverage!SoftwareInstallId] AS SoftwareInstallId,
Dim.ProvanceBFASoftwareInstallCoverageDimKey AS ProvanceBFASoftwareInstallCoverageDimKey,
INB.[Provance.Class.BFA.SoftwareInstallCoverage!SoftwareTitleGuid] AS SoftwareTitleGuid,
INB.[Provance.Class.BFA.SoftwareInstallCoverage!TimeStamp] AS TimeStamp,
ROW_NUMBER() OVER (PARTITION BY [Provance.Class.BFA.SoftwareInstallCoverage!DeviceGuid],
[Provance.Class.BFA.SoftwareInstallCoverage!DeviceGuid], CAST(CONVERT(nvarchar(8), INB.[Provance.Class.BFA.SoftwareInstallCoverage!TimeStamp], 112) AS INT)
ORDER BY INB.[Provance.Class.BFA.SoftwareInstallCoverage!TimeStamp] DESC,
INB.[Provance.Class.BFA.SoftwareInstallCoverage!BatchId] DESC)
AS DaySeqNum,
INB.TimeAdded AS TimeAdded
FROM inbound.MTV_Provance$Class$BFA$SoftwareInstallCoverage INB
LEFT JOIN dbo.ProvanceBFASoftwareInstallCoverageDimvw Dim ON Dim.BaseManagedEntityId = INB.BaseManagedEntityId
WHERE INB.DWTimestamp >= @WaterMarkDate
AND INB.DWTimestamp < @CurrentUtcDate) A
where a.DaySeqNum = 1 -- Keep the most recent record by day
-- If there are any early arrivals - e.g. no row in ProvanceBFASoftwareInstallCoverageDimvw yet, update their DWTimestamp so we get them
-- on the next run
SELECT @task = 'Rejecting early arriving records'
UPDATE INB
SET DWTimestamp = @CurrentUtcDate
FROM inbound.MTV_Provance$Class$BFA$SoftwareInstallCoverage INB
JOIN #Temp1 T ON INB.BaseManagedEntityId = T.BaseManagedEntityId
WHERE T.ProvanceBFASoftwareInstallCoverageDimKey IS NULL
AND INB.DWTimestamp >= @WaterMarkDate
AND INB.DWTimestamp < @CurrentUtcDate
DELETE FROM #Temp1 WHERE ProvanceBFASoftwareInstallCoverageDimKey IS NULL
-- KeySeqNo will put records in order of SoftwareTitleGuid,Timestamp each record will be assigned an EndDay that is one day before the StartDay
-- of the next record in order.
SELECT @task = 'Setting EndDay for new facts in t#Temp1'
UPDATE T1
SET T1.EndDay = CAST(CONVERT(nvarchar(8), DATEADD(D,-1,T2.TimeStamp), 112) AS INT)
FROM #Temp1 T1
JOIN #Temp1 T2 ON T1.SoftwareTitleGuid = T2.SoftwareTitleGuid
AND T1.KeySeqNum + 1 = T2.KeySeqNum
-- Update existing facts
SELECT @task = 'Update existing facts'
UPDATE F
SET F.DeviceGuid = T.DeviceGuid,
F.BatchId = T.BatchId,
F.VersionGuid = T.VersionGuid,
F.EndDay = T.EndDay,
F.AssignmentType = T.AssignmentType,
F.Covered = T.Covered,
F.ResolutionPathId = T.ResolutionPathId,
F.SoftwareInstallId = T.SoftwareInstallId,
F.SoftwareTitleGuid = T.SoftwareTitleGuid,
F.TimeStamp = T.TimeStamp,
UpdatedBatchId = @BatchId
FROM dbo.ProvanceBFASoftwareInstallCoverageDailyFactvw F
JOIN #Temp1 T ON F.DeviceGuid = T.DeviceGuid
AND F.VersionGuid = T.VersionGuid
AND F.StartDay = T.StartDay
SELECT @Updated = @@rowcount
-- The should only be one row per DeviceGuid,VersionGuid that has a null EndDay in BFASoftwareInstallCoverageDailyFactvw
-- The oldest per DeviceGuid,VersionGuid in #Temp table (T.KeySeqNo = 1) will be used to set the EndDay of that record
-- if it is older than the oldest in the temp table.
SELECT @task = 'Setting EndDay for exising facts'
UPDATE F
SET F.EndDay = CAST(CONVERT(nvarchar(8), DATEADD(D,-1,T.TimeStamp), 112) AS INT),
UpdatedBatchId = @BatchId
FROM dbo.ProvanceBFASoftwareInstallCoverageDailyFactvw F
JOIN #Temp1 T ON F.DeviceGuid = T.DeviceGuid
AND F.VersionGuid = T.VersionGuid
AND F.StartDay < T.StartDay
AND T.KeySeqNum = 1
WHERE F.EndDay is Null
SELECT @Updated = @Updated + @@rowcount
-- Remove the rows that already exist
DELETE T
FROM #Temp1 T
JOIN dbo.ProvanceBFASoftwareInstallCoverageDailyFactvw F ON T.SoftwareTitleGuid = F.SoftwareTitleGuid
/*
[ProvanceBFASoftwareInstallCoverageDimKey] [int] NOT NULL,
[DeviceGuid] [uniqueidentifier] NOT NULL,
[VersionGuid] [uniqueidentifier] NOT NULL,
[StartDay] [int] NOT NULL,
[EndDay] [int] NULL,
[AssignmentType] [int] NULL,
[BatchId] [int] NULL,
[Covered] [bit] NOT NULL,
[ResolutionPathId] [int] NULL,
[SoftwareInstallId] [int] NULL,
[SoftwareTitleGuid] [uniqueidentifier] NOT NULL,
[TimeStamp] [datetime] NULL,
[DateKey] [int] NOT NULL,
[UpdatedBatchId] [int] NOT NULL,
[InsertedBatchId] [int] NOT NULL,
*/AND T.StartDay = F.StartDay
-- Insert new facts
SELECT @task = 'Insert new facts.'
INSERT INTO dbo.ProvanceBFASoftwareInstallCoverageDailyFactvw (
DeviceGuid,
BatchId,
DateKey,
VersionGuid,
EndDay,
AssignmentType,
Covered,
InsertedBatchId,
ResolutionPathId,
SoftwareInstallId,
ProvanceBFASoftwareInstallCoverageDimKey,
SoftwareTitleGuid,
StartDay,
TimeStamp,
UpdatedBatchId)
SELECT
T.DeviceGuid,
T.BatchId,
T.DateKey,
t.VersionGuid,
T.EndDay,
T.AssignmentType,
T.Covered,
@BatchId,
ResolutionPathId,
SoftwareInstallId,
T.ProvanceBFASoftwareInstallCoverageDimKey,
T.SoftwareTitleGuid,
T.StartDay,
T.TimeStamp,
@BatchId
FROM #Temp1 T
SELECT @Inserted = @@rowcount
-- Test code...
--select 1 stepc, getdate() datec,* into temp_wm from #wm a
--insert into temp_wm select 1 stepc, getdate() datec,* from #wm a
-- select * from temp_wm order by datec,stepc
-- sp_help temp_wm
SELECT @task = 'Updating Watermark.'
UPDATE #wm SET WaterMark = CONVERT(nvarchar(64), isnull(@CurrentUtcDate, '1/1/1900'), 109)
WHERE WarehouseEntityName = N'MTV_Provance$Class$BFA$SoftwareInstallCoverage' 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