/****** Object: StoredProcedure [dbo].[TransformCloudServicesVirtualMachineSummaryFactProc] Script Date: 11/22/2011 16:52:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformCloudServicesVirtualMachineSummaryFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformCloudServicesVirtualMachineSummaryFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[TransformCloudServicesVirtualMachineSummaryFactProc](@WaterMark XML)
AS BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
--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'CloudServicesVirtualMachineDailyFact' AND WaterMarkType = 'BatchId'
SELECT @task = 'Dropping DWTemp tables'
IF OBJECT_ID('DWTemp.MaxDayKeyTable') IS NOT NULL
DROP TABLE DWTemp.MaxDayKeyTable
IF OBJECT_ID('DWTemp.TransformCloudServicesVirtualMachineSummaryFactProc_Source1') IS NOT NULL
DROP TABLE DWTemp.TransformCloudServicesVirtualMachineSummaryFactProc_Source1
-- read the max DateKey from SummaryFact. If it is null, then read the min dateKey from the VirtualMachineDailyFact
SELECT @task = 'Reading MAX DateKey into DWTemp.MaxDayKeyTable'
SELECT ISNULL(MAX(vmSummaryView.DateKey), MIN(vmDailyView.DateKey)) as MaxDayKey,
vmDailyView.CloudServicesVirtualMachineDimKey
INTO DWTemp.MaxDayKeyTable
FROM dbo.CloudServicesVirtualMachineDailyFactvw vmDailyView
LEFT JOIN dbo.CloudServicesVirtualMachineSummaryFactvw vmSummaryView
ON vmSummaryView.CloudServicesVirtualMachineDimKey = vmDailyView.CloudServicesVirtualMachineDimKey
GROUP BY vmDailyView.CloudServicesVirtualMachineDimKey
SELECT @task = 'Read the latest costs from DailyFacts, and store the final summary in Source1'
SELECT
vmDaily.DateKey,
vmDaily.CloudServicesVirtualMachineDimKey,
CAST(vmDaily.CPUCountMeasure * costSettingsDaily.PerUnitCPUCostMeasure AS FLOAT) AS CPUCostMeasure,
CAST(vmDaily.MemoryGBMeasure * costSettingsDaily.PerUnitMemoryCostMeasure AS FLOAT) AS MemoryCostMeasure,
CAST(vmDaily.StorageGBMeasure * costSettingsDaily.PerUnitStorageCostMeasure AS FLOAT) AS StorageCostMeasure,
CAST(costSettingsDaily.PerDayMiscellaneousCostMeasure AS FLOAT) AS MiscellaneousCostMeasure
INTO DWTemp.TransformCloudServicesVirtualMachineSummaryFactProc_Source1
FROM dbo.CloudServicesVirtualMachineDailyFactvw vmDaily
LEFT JOIN dbo.CloudServicesVMCostSettingsDailyFactvw costSettingsDaily
ON vmDaily.DateKey = costSettingsDaily.DateKey
INNER JOIN DWTemp.MaxDayKeyTable dateTable
ON vmDaily.CloudServicesVirtualMachineDimKey = dateTable.CloudServicesVirtualMachineDimKey
AND vmDaily.DateKey >= dateTable.MaxDayKey
WHERE vmDaily.[IsActive] = 1
BEGIN TRANSACTION -- Start
-- update the information back to the SummaryFact for the existing Data
SELECT @task = 'Updating CloudServicesVirtualMachineSummaryFactvw'
UPDATE vmSummary SET
vmSummary.CPUCostMeasure = T.CPUCostMeasure,
vmSummary.MemoryCostMeasure = T.MemoryCostMeasure,
vmSummary.StorageCostMeasure = T.StorageCostMeasure,
vmSummary.MiscellaneousCostMeasure = T.MiscellaneousCostMeasure,
vmSummary.TotalCostMeasure = T.CPUCostMeasure + T.StorageCostMeasure + T.MemoryCostMeasure + T.MiscellaneousCostMeasure,
UpdatedBatchId = @BatchId
FROM dbo.CloudServicesVirtualMachineSummaryFactvw AS vmSummary
INNER JOIN DWTemp.TransformCloudServicesVirtualMachineSummaryFactProc_Source1 AS T
ON vmSummary.DateKey = T.DateKey AND
vmSummary.CloudServicesVirtualMachineDimKey = T.CloudServicesVirtualMachineDimKey AND
(vmSummary.CPUCostMeasure <> T.CPUCostMeasure OR
vmSummary.MemoryCostMeasure <> T.MemoryCostMeasure OR
vmSummary.StorageCostMeasure <> T.StorageCostMeasure OR
vmSummary.MiscellaneousCostMeasure <> T.MiscellaneousCostMeasure)
WHERE T.CPUCostMeasure IS NOT NULL AND
T.MemoryCostMeasure IS NOT NULL AND
T.StorageCostMeasure IS NOT NULL AND
T.MiscellaneousCostMeasure IS NOT NULL
-- DELETE THE ROWS FROM DWTemp.TransformCloudServicesVirtualMachineSummaryFactProc_Source1, which are already inserted
SELECT @task = 'Deleting Already updated rows from temp Table'
DELETE source1
FROM DWTemp.TransformCloudServicesVirtualMachineSummaryFactProc_Source1 source1
INNER JOIN dbo.CloudServicesVirtualMachineSummaryFactvw AS vwSummary
ON vwSummary.DateKey = source1.DateKey AND
vwSummary.CloudServicesVirtualMachineDimKey = source1.CloudServicesVirtualMachineDimKey
-- Finally, insert into the Summary fact
SELECT @task = 'Inserting into CloudServicesVirtualMachineSummaryFactvw'
INSERT INTO dbo.CloudServicesVirtualMachineSummaryFactvw (
CloudServicesVirtualMachineDimKey,
CloudServicesTenantDimKey,
CloudServicesSubscriptionDimKey,
DateKey,
CPUCostMeasure,
MemoryCostMeasure,
StorageCostMeasure,
MiscellaneousCostMeasure,
TotalCostMeasure,
InsertedBatchId,
UpdatedBatchId
)
SELECT DISTINCT
source1.CloudServicesVirtualMachineDimKey,
projectToCapacityFact.CloudServicesTenantDimKey,
capacityToVMFact.CloudServicesSubscriptionDimKey,
source1.DateKey,
source1.CPUCostMeasure,
source1.MemoryCostMeasure,
source1.StorageCostMeasure,
source1.MiscellaneousCostMeasure,
source1.CPUCostMeasure + source1.MemoryCostMeasure + source1.StorageCostMeasure + source1.MiscellaneousCostMeasure,
@batchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM DWTemp.TransformCloudServicesVirtualMachineSummaryFactProc_Source1 source1
INNER JOIN dbo.CloudServicesVirtualMachineDimvw vmDim
ON vmDim.CloudServicesVirtualMachineDimKey = source1.CloudServicesVirtualMachineDimKey
LEFT JOIN dbo.CloudServicesSubscriptionRelatesToVirtualMachineFactvw capacityToVMFact
ON capacityToVMFact.SubscriptionRelatesToVirtualMachine_CloudServicesVirtualMachineDimKey = source1.CloudServicesVirtualMachineDimKey
LEFT JOIN dbo.CloudServicesTenantContainsSubscriptionFactvw projectToCapacityFact
ON projectToCapacityFact.TenantContainsSubscription_CloudServicesSubscriptionDimKey = capacityToVMFact.CloudServicesSubscriptionDimKey
WHERE source1.CPUCostMeasure IS NOT NULL AND
source1.MemoryCostMeasure IS NOT NULL AND
source1.StorageCostMeasure IS NOT NULL AND
source1.MiscellaneousCostMeasure IS NOT NULL
-- update watermark
BEGIN
SELECT @task = 'Updating WaterMark'
UPDATE #tempTable SET
WaterMark = @BatchId
WHERE WarehouseEntityName = N'CloudServicesVirtualMachineDailyFact'
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('DWTemp.MaxDayKeyTable') IS NOT NULL
DROP TABLE DWTemp.MaxDayKeyTable
IF OBJECT_ID('DWTemp.TransformCloudServicesVirtualMachineSummaryFactProc_Source1') IS NOT NULL
DROP TABLE DWTemp.TransformCloudServicesVirtualMachineSummaryFactProc_Source1
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)
UPDATE gi SET gi.RetentionPeriodInMinutes = @dataRetentionTimeInMinutes
FROM etl.WarehouseEntity we
INNER JOIN etl.WarehouseEntityGroomingInfo gi
ON we.WarehouseEntityId = gi.WarehouseEntityId
WHERE we.WarehouseEntityName = 'CloudServicesVirtualMachineSummaryFact'
AND gi.RetentionPeriodInMinutes != @dataRetentionTimeInMinutes
Home