IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformInfraChargebackMonthlyFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformInfraChargebackMonthlyFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.TransformInfraChargebackMonthlyFactProc (@WaterMark xml)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @todayKey INT = CONVERT(NVARCHAR(8), @utc, 112),
@lastMonthlyFactBatchId INT = (SELECT CASE WHEN MaxInsertedBatchId > MaxUpdatedBatchId THEN MaxInsertedBatchId ELSE MaxUpdatedBatchId END AS MaxBatchId
FROM (
SELECT ISNULL(MAX(InsertedBatchId), 0) AS MaxInsertedBatchId, ISNULL(MAX(UpdatedBatchId), 0) AS MaxUpdatedBatchId
FROM dbo.InfraChargebackMonthlyFactvw) T)
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'
IF OBJECT_ID('DWTemp.TransformInfraChargebackMonthlyFactProc_Source') IS NOT NULL
DROP TABLE DWTemp.TransformInfraChargebackMonthlyFactProc_Source
BEGIN TRANSACTION
--------------------------------------------------------------------------------------------
-- Step 1: get the list of VMs updated in daily fact since last process
--------------------------------------------------------------------------------------------
SELECT @task = 'Step 1: get the list of VMs updated in daily fact since last process'
DECLARE @VMsInList TABLE
(
VirtualMachineDimKey INT NOT NULL,
FirstDateKey INT NOT NULL,
LastDateKey INT NOT NULL,
PRIMARY KEY (VirtualMachineDimKey, LastDateKey)
)
INSERT INTO @VMsInList(VirtualMachineDimKey, FirstDateKey, LastDateKey)
SELECT DISTINCT
VirtualMachineDimKey,
DateKey / 100 * 100 + 1 AS FirstDateKey,
DateKey / 100 * 100 + 31 AS LastDateKey
FROM dbo.InfraChargebackDailyFactvw
WHERE InsertedBatchId > @lastMonthlyFactBatchId -- new inserted after last archive
OR UpdatedBatchId > @lastMonthlyFactBatchId -- new updated after last archive
-- Reset last day of month based on daily fact
-- Note that the dimension keys of the summary fact will be based on the last day of the month from daily fact
-- DateKey in monthly fact is set actually as a datekey on the last day of the month from daily fact
UPDATE l
SET LastDateKey = ( SELECT MAX(DateKey)
FROM dbo.InfraChargebackDailyFactvw f
WHERE f.VirtualMachineDimKey = l.VirtualMachineDimKey
AND f.DateKey >= l.FirstDateKey
AND f.DateKey <= l.LastDateKey)
FROM @VMsInList l
--------------------------------------------------------------------------------------------
-- Step 2: aggregate daily facts into work table
--------------------------------------------------------------------------------------------
SELECT @task = 'Step 2: aggregate daily facts into work table'
SELECT DISTINCT
l.VirtualMachineDimKey,
l.LastDateKey,
SUM(f.CoresCostMeasure) AS CoresCostMeasure,
SUM(f.CoresCountMeasure) AS CoresCountMeasure,
SUM(f.MemoryTotalCostMeasure) AS MemoryTotalCostMeasure,
SUM(f.MemoryCountMeasure) AS MemoryCountMeasure,
SUM(f.StorageTotalCostMeasure) AS StorageTotalCostMeasure,
SUM(f.StorageCountMeasure) AS StorageCountMeasure,
SUM(f.HighAvailabilityCostMeasure) AS HighAvailabilityCostMeasure,
SUM(f.HighAvailabilityCountMeasure) AS HighAvailabilityCountMeasure,
SUM(f.DynamicalVHDTypeCostMeasure) AS DynamicalVHDTypeCostMeasure,
SUM(f.DynamicalVHDTypeCountMeasure) AS DynamicalVHDTypeCountMeasure,
SUM(f.StaticIPCostMeasure) AS StaticIPCostMeasure,
SUM(f.StaticIPCountMeasure) AS StaticIPCountMeasure,
SUM(f.VMBaseCostMeasure) AS VMBaseCostMeasure,
SUM(f.PrivateCloudMembershipCostMeasure) AS PrivateCloudMembershipCostMeasure
INTO DWTemp.TransformInfraChargebackMonthlyFactProc_Source
FROM @VMsInList l
JOIN dbo.InfraChargebackDailyFactvw f
ON f.VirtualMachineDimKey = l.VirtualMachineDimKey
AND f.DateKey >= l.FirstDateKey
AND f.DateKey <= l.LastDateKey
GROUP BY l.VirtualMachineDimKey, l.LastDateKey
--------------------------------------------------------------------------------------------
-- Step 3: merge aggregated facts into monthly fact table
--------------------------------------------------------------------------------------------
-- Note that MERGE statement can not be directly used here because the target cannot be a partitioned view.
SELECT @task = 'Step 3: merge aggregated facts into monthly fact table'
UPDATE mf
SET
DateKey = source.LastDateKey,
CoresCostMeasure = source.CoresCostMeasure,
CoresCountMeasure = source.CoresCountMeasure,
MemoryTotalCostMeasure = source.MemoryTotalCostMeasure,
MemoryCountMeasure = source.MemoryCountMeasure,
StorageTotalCostMeasure = source.StorageTotalCostMeasure,
StorageCountMeasure = source.StorageCountMeasure,
HighAvailabilityCostMeasure = source.HighAvailabilityCostMeasure,
HighAvailabilityCountMeasure = source.HighAvailabilityCountMeasure,
DynamicalVHDTypeCostMeasure = source.DynamicalVHDTypeCostMeasure,
DynamicalVHDTypeCountMeasure = source.DynamicalVHDTypeCountMeasure,
StaticIPCostMeasure = source.StaticIPCostMeasure,
StaticIPCountMeasure = source.StaticIPCountMeasure,
VMBaseCostMeasure = source.VMBaseCostMeasure,
PrivateCloudMembershipCostMeasure = source.PrivateCloudMembershipCostMeasure,
UpdatedBatchId = @BatchId
FROM dbo.InfraChargebackMonthlyFactvw mf
JOIN DWTemp.TransformInfraChargebackMonthlyFactProc_Source source
ON mf.VirtualMachineDimKey = source.VirtualMachineDimKey
AND mf.DateKey / 100 = source.LastDateKey / 100
-- Remove the existing ones
DELETE source
FROM dbo.InfraChargebackMonthlyFactvw mf
JOIN DWTemp.TransformInfraChargebackMonthlyFactProc_Source source
ON mf.VirtualMachineDimKey = source.VirtualMachineDimKey
AND mf.DateKey / 100 = source.LastDateKey / 100
--------------------------------------------------------------------------------------------
-- Step 4: update dimension keys and other fields based on last day of month in daily fact
--------------------------------------------------------------------------------------------
SELECT @task = 'Step 4: update dimension keys and other fields based on last day of month in daily fact'
UPDATE mf
SET
mf.UserRoleDimKey = df.UserRoleDimKey,
mf.PricesheetDimKey = df.PricesheetDimKey,
mf.PrivateCloudDimKey = df.PrivateCloudDimKey,
mf.CostCenterDimKey = df.CostCenterDimKey,
mf.LastGuestOS = df.LastGuestOS
FROM dbo.InfraChargebackMonthlyFactvw mf
JOIN dbo.InfraChargebackDailyFactvw df
ON df.VirtualMachineDimKey = mf.VirtualMachineDimKey
AND df.DateKey = mf.DateKey -- DateKey is actually a DateKey from the last day of the month
WHERE mf.InsertedBatchId = @BatchId
OR mf.UpdatedBatchId = @BatchId
SELECT @Inserted = (SELECT COUNT(1) FROM dbo.InfraChargebackMonthlyFactvw WHERE InsertedBatchId = @BatchId)
SELECT @Updated = (SELECT COUNT(1) FROM dbo.InfraChargebackMonthlyFactvw WHERE UpdatedBatchId = @BatchId)
COMMIT TRANSACTION
-- 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.TransformInfraChargebackMonthlyFactProc_Source') IS NOT NULL
DROP TABLE DWTemp.TransformInfraChargebackMonthlyFactProc_Source
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)
Home