IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformPricesheetDetailDailyFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformPricesheetDetailDailyFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.TransformPricesheetDetailDailyFactProc (@WaterMark xml)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @todayKey INT = CONVERT(NVARCHAR(8), @utc, 112),
@lastSnapshotDateKey INT = (SELECT ISNULL(MAX(DateKey), 19000101) FROM dbo.PricesheetDetailDailyFactvw)
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'
SELECT @task = 'Drop Temp Table'
IF OBJECT_ID('DWTemp.TransformPricesheetDetailDailyFactProc_DeltaRangeTable') IS NOT NULL
DROP TABLE DWTemp.TransformPricesheetDetailDailyFactProc_DeltaRangeTable
IF OBJECT_ID('DWTemp.TransformPricesheetDetailDailyFactProc_Source2') IS NOT NULL
DROP TABLE DWTemp.TransformPricesheetDetailDailyFactProc_Source2
IF OBJECT_ID('DWTemp.TransformPricesheetDetailDailyFactProc_Source3') IS NOT NULL
DROP TABLE DWTemp.TransformPricesheetDetailDailyFactProc_Source3
IF OBJECT_ID('DWTemp.TransformPricesheetDetailDailyFactProc_Source4') IS NOT NULL
DROP TABLE DWTemp.TransformPricesheetDetailDailyFactProc_Source4
-- Prepare EntityDim info for all PricedItem singleton objects
DECLARE @PriceableAspectTypes TABLE
(
PriceableAspectDimKey INT PRIMARY KEY,
BaseManagedEntityId UNIQUEIDENTIFIER,
SourceId UNIQUEIDENTIFIER,
TypeName NVARCHAR(255)
)
INSERT INTO @PriceableAspectTypes(PriceableAspectDimKey, BaseManagedEntityId, SourceId, TypeName)
SELECT e.PriceableAspectDimKey, e.BaseManagedEntityId, e.SourceId, t.TypeName
FROM dbo.PriceableAspectDimvw e
JOIN dbo.ManagedTypeDimvw t
ON t.BaseManagedEntityId = e.BaseManagedEntityId
AND t.SourceId = e.SourceId
AND t.TypeName IN (
'ServiceManager.Chargeback.GenericVirtualMachineAspectSingleton',
'ServiceManager.Chargeback.GenericCloudMembershipAspectSingleton',
'ServiceManager.Chargeback.GenericCpuAspectSingleton',
'ServiceManager.Chargeback.GenericMemoryAspectSingleton',
'ServiceManager.Chargeback.GenericStorageAspectSingleton',
'ServiceManager.Chargeback.HighAvailabilityAspectSingleton',
'ServiceManager.Chargeback.StaticIpAddressAspectSingleton',
'ServiceManager.Chargeback.ExpandingDiskAspectSingleton'
)
BEGIN TRANSACTION
-- Step 1: prepare priceditem delta from last processed snapshot and inbound extraction
-- 1.a: get last processed snapshot as base
SELECT @task = '1.a: get last processed snapshot as base'
SELECT DISTINCT
fact.PricesheetDimKey,
fact.PriceableAspectDimKey,
fact.PriceValueMeasure,
@lastSnapshotDefaultDate AS ModifiedDate,
0 AS IsDeleted,
fact.DateKey
INTO DWTemp.TransformPricesheetDetailDailyFactProc_Source3
FROM dbo.PricesheetDetailDailyFactvw fact
WHERE DateKey = @lastSnapshotDateKey
WHILE(@canContinue = 1)
BEGIN
-- 1.c: iterate to retrie ve new delta from inbound source table
SELECT @task = '1.c: iterate to retrieve new delta from inbound source table'
EXEC dbo.InitializeTransformLoop
@transformName = @transformName,
@transformTemplateType = @transformTemplateType,
@sourceTableName = @sourceTableName,
@utc = @utc,
@targetClassCheck = '',
@batchId = @batchId,
@collapseProperties = 1,
@source1WM = @source1WM OUTPUT,
@source1MaxWM = @source1MaxWM OUTPUT,
@rowsToProcess = @rowsToProcess OUTPUT,
@loopCount = @loopCount OUTPUT
IF(@rowsToProcess = 0) BREAK;
-- 1.d: deleting processed records from source
SELECT @task = '1.d: deleting processed records from source'
DELETE source
FROM DWTemp.TransformPricesheetDetailDailyFactProc_Source1 source
INNER JOIN dbo.PricesheetDimvw dim
ON source.SourceEntityId = dim.BaseManagedEntityId
INNER JOIN dbo.PricesheetDetailDailyFactvw fact
ON dim.PricesheetDimKey = fact.PricesheetDimKey
WHERE source.LastModified < @lastSnapshotDefaultDate
-- 1.e: copying Records into the temp Table
SELECT @task = '1.e: copying Records into the temp Table'
INSERT INTO DWTemp.TransformPricesheetDetailDailyFactProc_Source3 (
PricesheetDimKey,
PriceableAspectDimKey,
PriceValueMeasure,
ModifiedDate,
IsDeleted,
DateKey
)
SELECT
dimSrc.PricesheetDimKey,
dimTrg.PriceableAspectDimKey,
ISNULL(source1.[System.Chargeback.PricesheetContainsPriceableAspect!Value], 0.0) AS PriceValueMeasure,
source1.LastModified AS ModifiedDate,
source1.IsDeleted,
CONVERT(NVARCHAR(8), source1.LastModified, 112) AS DateKey
FROM DWTemp.TransformPricesheetDetailDailyFactProc_Source1 source1
JOIN dbo.PricesheetDimvw dimSrc
ON dimSrc.BaseManagedEntityId = source1.SourceEntityId
JOIN @PriceableAspectTypes dimTrg
ON dimTrg.BaseManagedEntityId = source1.TargetEntityId
-- 1.f: updating source to move early arriving records to next batch
SELECT @task = '1.f: updating source to move early arriving records to next batch'
UPDATE source
SET
DWTimeStamp = GETUTCDATE(),
RejectedCount = ISNULL(RejectedCount, 0) + 1,
BatchId = @BatchId
FROM inbound.MTV_System$Chargeback$PricesheetContainsPriceableAspect source
LEFT JOIN dbo.PricesheetDimvw dimSrc
ON dimSrc.BaseManagedEntityId = source.SourceEntityId
LEFT JOIN @PriceableAspectTypes dimTrg
ON dimTrg.BaseManagedEntityId = source.TargetEntityId
WHERE source.DWTimestamp >= @source1WM
AND source.DWTimestamp < @source1MaxWM
AND (dimSrc.BaseManagedEntityId IS NULL OR dimTrg.BaseManagedEntityId IS NULL)
-- Step 2: create daily facts for priceditem delta data
-- 2.a: setup TransformPricesheetDetailDailyFactProc_DeltaRangeTable for all available priceditem
SELECT @task = '2.a: setup TransformPricesheetDetailDailyFactProc_DeltaRangeTable for all available priceditem'
SELECT PricesheetDimKey,
PriceableAspectDimKey,
MIN(DateKey) AS MinDayKey
INTO DWTemp.TransformPricesheetDetailDailyFactProc_DeltaRangeTable
FROM DWTemp.TransformPricesheetDetailDailyFactProc_Source3
GROUP BY PricesheetDimKey, PriceableAspectDimKey
-- 2.b: create daily facts in work table for priceditem
-- Only one entry will be picked if there are multiple changes for a priceitem on same day: the last active entry
SELECT @task = '2.b: create daily facts in work table for priceditem'
;WITH T AS
(
SELECT PricesheetDimKey,
PriceableAspectDimKey,
PriceValueMeasure,
DateKey,
IsDeleted,
ROW_NUMBER() OVER(PARTITION BY DateKey, PricesheetDimKey, PriceableAspectDimKey ORDER BY ModifiedDate DESC) AS DayIdx
FROM DWTemp.TransformPricesheetDetailDailyFactProc_Source3
)
SELECT
dimDT.DateKey,
MinDayKey.PricesheetDimKey,
MinDayKey.PriceableAspectDimKey,
s.PriceValueMeasure,
s.IsDeleted
INTO DWTemp.TransformPricesheetDetailDailyFactProc_Source4
FROM dbo.DateDim dimDT
CROSS JOIN DWTemp.TransformPricesheetDetailDailyFactProc_DeltaRangeTable MinDayKey
LEFT JOIN T s
ON s.DateKey = dimDT.DateKey
AND s.PricesheetDimKey = MinDayKey.PricesheetDimKey
AND s.PriceableAspectDimKey = MinDayKey.PriceableAspectDimKey
AND s.DayIdx = 1
WHERE dimDT.DateKey >= MinDayKey.MinDayKey
AND dimDT.DateKey <= @todayKey -- replicate entry till today
-- 2.c: create clustered index for the next update statement to work
SELECT @task = '2.c: create clustered index for the next update statement to work'
CREATE CLUSTERED INDEX Idx_PricedItemSource4 ON
DWTemp.TransformPricesheetDetailDailyFactProc_Source4(PricesheetDimKey, PriceableAspectDimKey, DateKey)
-- 2.d: update null entries which we do not have new delta directly matched from inbound source table
-- This is how it works: SQL starts updating the rows one after another
-- in the proces of iterating row after row via saving the attributes in the local variables
-- This will help in copying forward values for any missing (NULL) values.
SELECT @task = '2.d: update null entries which we do not have new delta directly matched from inbound source table'
UPDATE T
SET PriceValueMeasure = ISNULL(PriceValueMeasure, @PriceValueMeasure),
IsDeleted = ISNULL(IsDeleted, @IsDeleted),
@PriceValueMeasure = ISNULL(PriceValueMeasure, @PriceValueMeasure),
@IsDeleted = ISNULL(IsDeleted, @IsDeleted)
FROM DWTemp.TransformPricesheetDetailDailyFactProc_Source4 AS T
OPTION (MAXDOP 1)
-- 2.e: drop Pricesheet entries for days which are not active
SELECT @task = '2.e: drop Pricesheet entries for days which are not active'
DELETE source4
FROM DWTemp.TransformPricesheetDetailDailyFactProc_Source4 source4
LEFT JOIN dbo.PricesheetDim psDim -- get the deleted price sheets
ON psDim.PricesheetDimKey = source4.PricesheetDimKey
AND psDim.IsDeleted = 1
LEFT JOIN dbo.EntityDim eDim -- get the timestamp when it is deleted
ON eDim.EntityDimKey = psDim.EntityDimKey
WHERE source4.IsDeleted = 1
OR source4.DateKey > ISNULL(CONVERT(NVARCHAR(8), eDim.LastModified, 112), 99990101)
-- Step 3: update the information back to the DailyFactVw for the existing Data
-- with the data combined from both relationship and price setup work tables
SELECT @task = 'Step 3: update the information back to the DailyFactVw for the existing Data'
UPDATE vwDaily
SET vwDaily.PriceValueMeasure = ISNULL(inboundSource.PriceValueMeasure, vwDaily.PriceValueMeasure),
UpdatedBatchId = @BatchId
FROM dbo.PricesheetDetailDailyFactvw AS vwDaily
INNER JOIN DWTemp.TransformPricesheetDetailDailyFactProc_Source4 inboundSource
ON vwDaily.DateKey = inboundSource.DateKey
AND vwDaily.PricesheetDimKey = inboundSource.PricesheetDimKey
AND vwDaily.PriceableAspectDimKey = inboundSource.PriceableAspectDimKey
WHERE vwDaily.PriceValueMeasure <> inboundSource.PriceValueMeasure
-- Step 4: delete rows which are already inserted
SELECT @task = 'Step 4: delete rows which are already inserted'
DELETE inboundSource
FROM DWTemp.TransformPricesheetDetailDailyFactProc_Source4 inboundSource
INNER JOIN dbo.PricesheetDetailDailyFactvw vwDaily
ON vwDaily.DateKey = inboundSource.DateKey
AND vwDaily.PricesheetDimKey = inboundSource.PricesheetDimKey
AND vwDaily.PriceableAspectDimKey = inboundSource.PriceableAspectDimKey
-- Step 5: insert into Fact for new delta after last processed date
SELECT @task = 'Step 5: insert into Fact for new delta after last processed date'
INSERT INTO dbo.PricesheetDetailDailyFactvw (
PricesheetDimKey,
PriceableAspectDimKey,
DateKey,
PriceValueMeasure,
ServiceType,
InsertedBatchId,
UpdatedBatchId
)
SELECT DISTINCT
inboundSource.PricesheetDimKey,
inboundSource.PriceableAspectDimKey,
inboundSource.DateKey,
inboundSource.PriceValueMeasure,
t.TypeName AS ServiceType,
@BatchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM DWTemp.TransformPricesheetDetailDailyFactProc_Source4 inboundSource
JOIN @PriceableAspectTypes t
ON t.PriceableAspectDimKey = inboundSource.PriceableAspectDimKey
SELECT @Inserted = (SELECT COUNT(1) FROM dbo.PricesheetDetailDailyFactvw WHERE InsertedBatchId = @BatchId)
SELECT @Updated = (SELECT COUNT(1) FROM dbo.PricesheetDetailDailyFactvw WHERE UpdatedBatchId = @BatchId)
-- update watermark for DW
BEGIN
SELECT @task = 'Updating WaterMark'
UPDATE #tempTable SET
WaterMark = CONVERT(nvarchar(64), @utc, 109)
WHERE WaterMarkType = '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('DWTemp.TransformPricesheetDetailDailyFactProc_DeltaRangeTable') IS NOT NULL
DROP TABLE DWTemp.TransformPricesheetDetailDailyFactProc_DeltaRangeTable
IF OBJECT_ID('DWTemp.TransformPricesheetDetailDailyFactProc_Source2') IS NOT NULL
DROP TABLE DWTemp.TransformPricesheetDetailDailyFactProc_Source2
IF OBJECT_ID('DWTemp.TransformPricesheetDetailDailyFactProc_Source3') IS NOT NULL
DROP TABLE DWTemp.TransformPricesheetDetailDailyFactProc_Source3
IF OBJECT_ID('DWTemp.TransformPricesheetDetailDailyFactProc_Source4') IS NOT NULL
DROP TABLE DWTemp.TransformPricesheetDetailDailyFactProc_Source4
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)
Home