-- Creating Transform in dbo schema for now. Should they be in a different schema?
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformSoftwareUsageMonthlyUsageCountFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformSoftwareUsageMonthlyUsageCountFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.TransformSoftwareUsageMonthlyUsageCountFactProc (@WaterMark xml)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
-- Get Metadata
DECLARE @BatchId int, @Inserted int, @Updated int, @MaxIncidentTimestamp datetime, @IncidentTimestamp datetime, @MinDateKey int
SET @Inserted = 0
SET @Updated = 0
BEGIN TRY
SELECT * INTO #wm FROM etl.ShredWaterMark(@WaterMark)
SELECT TOP 1 @IncidentTimestamp = WaterMark, @BatchId = BatchId
FROM #wm
WHERE WarehouseEntityName = N'MTV_Provance$Itam$SoftwareUsage$MonthlySoftwareUsage'
SET @MaxIncidentTimestamp = GETUTCDATE()
-- Get monthly usage data that might have been inserted or had Usage Count changed since the transform ran last.
-- Join to MonthlySoftwareUsageDim to get the MonthlySoftwareUsageDimKey and the current UsageCount and TimeKey. We are figuring the
-- MonthlySoftwareUsageDateKey for partitioning optimizations.
SELECT INB.BaseManagedEntityId, INB.DatasourceId, MonthlySoftwareUsageDimKey, COALESCE(UsageCount, 0) AS UsageCount, TimeKey,
ROW_NUMBER() OVER (PARTITION BY MonthlySoftwareUsageDimKey, TimeKey, DATEPART(MONTH, INB.LastModified), DATEPART(YEAR,INB.LastModified) ORDER BY INB.LastModified DESC) AS SeqNum,
INB.LastModified AS StartDateTime,
CONVERT(nvarchar(8), TimeAdded, 112) AS MonthlySoftwareUsageDateKey
INTO #Temp1
FROM inbound.MTV_Provance$Itam$SoftwareUsage$MonthlySoftwareUsage INB
LEFT JOIN dbo.MonthlySoftwareUsageDimvw MSU_Dim ON MSU_Dim.BaseManagedEntityId = INB.BaseManagedEntityId AND
MSU_Dim.SourceId = INB.DatasourceId
WHERE INB.DWTimestamp >= @IncidentTimestamp AND INB.DWTimestamp < @MaxIncidentTimestamp
-- If there are any early arrivals - e.g. no row in MonthlySoftwareUsageDimvw yet, update their DWTimestamp so we get them
-- on the next run
UPDATE INB SET DWTimestamp = @MaxIncidentTimestamp
FROM inbound.MTV_Provance$Itam$SoftwareUsage$MonthlySoftwareUsage INB
JOIN #Temp1 MSU_Dim ON MSU_Dim.BaseManagedEntityId = INB.BaseManagedEntityId AND
MSU_Dim.DatasourceId = INB.DatasourceId
WHERE MSU_Dim.MonthlySoftwareUsageDimKey IS NULL
DELETE FROM #Temp1 WHERE MonthlySoftwareUsageDimKey IS NULL
SELECT @MinDateKey = MIN(MonthlySoftwareUsageDateKey) FROM #Temp1
-- remove multiple change records for the same softwareusage for the same month. Keep the most recent value (i.e. SeqNum = 1).
DELETE aa
FROM #Temp1 aa
WHERE aa.SeqNum > 1
-- Remove rows that correspond to no change in status ** not needed because we have only kept the most recent update for the month, SeqNum will never exceed 1
--DELETE a
-- FROM #Temp1 a
-- JOIN #Temp1 bb ON
-- a.MonthlySoftwareUsageDimKey = bb.MonthlySoftwareUsageDimKey
-- and a.UsageCount = bb.UsageCount
-- and a.SeqNum = bb.SeqNum+1
-- Join to Fact table to remove more rows that correspond to no change in status **for the same month!!!****
DELETE aa
FROM #Temp1 aa
JOIN dbo.SoftwareUsageMonthlyUsageCountFactvw bb
ON bb.MonthlySoftwareUsageDimKey = aa.MonthlySoftwareUsageDimKey and
bb.UsageCount = aa.UsageCount and
bb.TimeKey = aa.TimeKey and
bb.DateKey >= @MinDateKey
-- Move to second temp table so there are no gaps in SeqNum now that all
-- of the "no change in status" rows have been removed ** not needed, only kept the most recent update for the month
--SELECT MonthlySoftwareUsageDimKey, UsageCount, StartDateTime,
-- ROW_NUMBER() OVER (PARTITION BY MonthlySoftwareUsageDimKey ORDER BY SeqNum) AS SeqNum,
-- MonthlySoftwareUsageDateKey
--INTO #Temp2
--FROM #Temp1
-- remove rows that indicate a "zero" usage (feature requirement, "do not record 0 usage")
DELETE aa
FROM #Temp1 aa
WHERE aa.UsageCount = 0
SELECT @MinDateKey = MIN(MonthlySoftwareUsageDateKey) FROM #Temp1
-- Update existing facts where we have a new status ** for the same month!!! **
UPDATE aa
SET aa.UsageCount = tmp.UsageCount, UpdatedBatchId = @BatchId
FROM dbo.SoftwareUsageMonthlyUsageCountFactvw aa
JOIN #Temp1 tmp ON tmp.MonthlySoftwareUsageDimKey = aa.MonthlySoftwareUsageDimKey AND
tmp.SeqNum = 1 AND
aa.TimeKey = tmp.TimeKey AND
tmp.StartDateTime > aa.StartDateTime AND
aa.DateKey >= @MinDateKey
SELECT @Updated = @@rowcount
-- Prevent Dup Key problems, remove the rows that updated an existing record in the previous step
DELETE aa
FROM #Temp1 aa
JOIN dbo.SoftwareUsageMonthlyUsageCountFactvw bb
ON bb.MonthlySoftwareUsageDimKey = aa.MonthlySoftwareUsageDimKey and
bb.UsageCount = aa.UsageCount and
bb.TimeKey = aa.TimeKey and
bb.DateKey >= @MinDateKey
-- Insert new facts
INSERT INTO dbo.SoftwareUsageMonthlyUsageCountFactvw
(DateKey,
MonthlySoftwareUsageDimKey,
UsageCount,
TimeKey,
StartDateTime,
InsertedBatchId,
UpdatedBatchId)
SELECT CONVERT(nvarchar(8), aa.StartDateTime, 112) AS DateKey,
aa.MonthlySoftwareUsageDimKey,
aa.UsageCount,
aa.TimeKey,
aa.StartDateTime AS StartDateTime,
@BatchId,
@BatchId
FROM #Temp1 aa
SELECT @Inserted = @@rowcount
UPDATE #wm SET WaterMark = isnull(@MaxIncidentTimestamp, '1/1/1900')
WHERE WarehouseEntityName = N'MTV_Provance$Itam$SoftwareUsage$MonthlySoftwareUsage' 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