/****** Object: StoredProcedure [dbo].[TransformCloudServicesVMCostSettingsDailyFactProc] 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'TransformCloudServicesVMCostSettingsDailyFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformCloudServicesVMCostSettingsDailyFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[TransformCloudServicesVMCostSettingsDailyFactProc](@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'CloudServicesVMCostSettingsSnapshotFact' AND WaterMarkType = 'BatchId'
SELECT @task = 'Dropping DWTemp table'
IF OBJECT_ID('DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source0') IS NOT NULL
DROP TABLE DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source0
IF OBJECT_ID('DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source1') IS NOT NULL
DROP TABLE DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source1
IF OBJECT_ID('DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source2') IS NOT NULL
DROP TABLE DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source2
-- The grain of the fact table is 1 row per day.
-- The logic of the transform is that it takes the row from the max datekey (from DailyFact)
-- and replicate that value to all the day's for which the records are missing.
-- IE, say, today is 10th of Jan and max record in teh Fact is 1st Jan
-- we copy the row from the 1st Jan and replicate it for all the days till the 10th.
-- In addition to replicating the cost from the max row, this tx merges the cost
-- from snapshot fact if there are any changes to the cost recorded in the fact.
-- read the max DateKey from DailyFact. If it is null, then read the min dateKey from the Snapshot Fact. If that is also null, that is fine.
SELECT @task = 'Reading MAX DateKey'
SELECT @maxDayKey = ISNULL((SELECT MAX(DateKey) FROM dbo.CloudServicesVMCostSettingsDailyFactvw),
(SELECT MIN(DateKey) FROM dbo.CloudServicesVMCostSettingsSnapshotFactvw))
--Get the maxDayKey for SnapShot View too. If it is null, it is fine.
SELECT @maxDayKey2 = MAX(DateKey) FROM dbo.CloudServicesVMCostSettingsSnapshotFactvw
--We need to start replicating Source2 from the eariler of these two dates.
if ( @maxDayKey < @maxDayKey2)
Set @maxDayKey2 = @maxDayKey;
-- Once we got the @MaxDayKey, we need to check if there is atleast one row in the DailyFAct
-- Because if not, the replication code below would not work.
--therefore we should insert a dummy row
if exists (select * from dbo.CloudServicesVMCostSettingsDailyFactvw)
BEGIN
SELECT
vwfDaily.DateKey,
CAST(vwfDaily.PerUnitCPUCostMeasure AS FLOAT) AS PerUnitCPUCostMeasure,
CAST(vwfDaily.PerUnitMemoryCostMeasure AS FLOAT) AS PerUnitMemoryCostMeasure,
CAST(vwfDaily.PerUnitStorageCostMeasure AS FLOAT) AS PerUnitStorageCostMeasure,
CAST(vwfDaily.PerDayMiscellaneousCostMeasure AS FLOAT) AS PerDayMiscellaneousCostMeasure
INTO DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source0
FROM dbo.CloudServicesVMCostSettingsDailyFactvw vwfDaily
WHERE vwfDaily.DateKey = @maxDayKey
END
ELSE
BEGIN
SELECT
@maxDayKey as DateKey,
CAST(0 AS FLOAT) AS PerUnitCPUCostMeasure,
CAST(0 AS FLOAT) AS PerUnitMemoryCostMeasure,
CAST(0 AS FLOAT) AS PerUnitStorageCostMeasure,
CAST(0 AS FLOAT) AS PerDayMiscellaneousCostMeasure
INTO DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source0
END
-- Read all the cost changes from the snapshot fact
-- only retain the latest change for any given day
-- IE, suppose that there a 3 changes to cost on 3rd of Jan
-- What we want is only the last change for any given day.
-- In addition to only reading the latest change, we also want to create 1 row per day
-- in the temp table source2
-- NOTE, for all the days for which there are no changes, we will end up inserted NULL values for the cost
SELECT @task = 'Read the latest cost changes from the snapshot fact'
;WITH T AS
(
SELECT DateKey,
PerUnitCPUCostMeasure,
PerUnitMemoryCostMeasure,
PerUnitStorageCostMeasure,
PerDayMiscellaneousCostMeasure,
ROW_NUMBER() OVER(PARTITION BY DateKey ORDER BY UpdatedDate DESC) AS DayIdx
FROM dbo.CloudServicesVMCostSettingsSnapshotFactvw
)
SELECT dimDT.DateKey,
CAST(T.PerUnitCPUCostMeasure AS FLOAT) AS PerUnitCPUCostMeasure,
CAST(T.PerUnitMemoryCostMeasure AS FLOAT) AS PerUnitMemoryCostMeasure,
CAST(T.PerUnitStorageCostMeasure AS FLOAT) AS PerUnitStorageCostMeasure,
CAST(T.PerDayMiscellaneousCostMeasure AS FLOAT) AS PerDayMiscellaneousCostMeasure
INTO DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source2
FROM dbo.DateDim dimDT
LEFT JOIN T ON dimDT.DateKey = T.DateKey AND T.DayIdx = 1
WHERE dimDT.DateKey >= @maxDayKey2
AND dimDT.DateKey <= @todayKey
-- Now we need to update TransformCloudServicesVMCostSettingsDailyFactProc_Source2
-- from TransformCloudServicesVMCostSettingsDailyFactProc_Source0, because some rows here may have null,
-- though the data exists in source0 table already
UPDATE source2 SET
source2.PerUnitCPUCostMeasure = ISNULL(source2.PerUnitCPUCostMeasure, source0.PerUnitCPUCostMeasure),
source2.PerUnitMemoryCostMeasure = ISNULL(source2.PerUnitMemoryCostMeasure, source0.PerUnitMemoryCostMeasure),
source2.PerUnitStorageCostMeasure = ISNULL(source2.PerUnitStorageCostMeasure, source0.PerUnitStorageCostMeasure),
source2.PerDayMiscellaneousCostMeasure = ISNULL(source2.PerDayMiscellaneousCostMeasure, source0.PerDayMiscellaneousCostMeasure)
FROM DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source2 source2
INNER JOIN DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source0 source0
ON source2.DateKey = source0.DateKey AND
source0.PerUnitCPUCostMeasure IS NOT NULL AND
source0.PerUnitMemoryCostMeasure IS NOT NULL AND
source0.PerUnitStorageCostMeasure IS NOT NULL AND
source0.PerDayMiscellaneousCostMeasure IS NOT NULL
-- Clustered index is needed for the next update statement to work
CREATE CLUSTERED INDEX Idx1_VMSource2 ON DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source2(DateKey)
-- This update statement works a little mysteriously
-- This is how it works: SQL starts updating the rows one after another
-- in the proces of iterating row after row, I end up saving the cost values in the local variables
-- This will help in copying forward values for any missing (NULL) cost values.
-- For ex: suppose we have cost for 1st jan, 3rd jan, 7th jan
-- this query, will end up updating setting the cost as it was on the 1st jan to both 1st and 2nd (because 2nd was null)
-- then the cost from the 3rd day gets copied to 3rd, 4th, 5th and 6th days
-- and then the cost from the 7th day gets copied to 7th, 8th, 9th and 10th days.
UPDATE T SET
PerUnitCPUCostMeasure = ISNULL(PerUnitCPUCostMeasure, @PerUnitCPUCostMeasure),
PerUnitMemoryCostMeasure = ISNULL(PerUnitMemoryCostMeasure, @PerUnitMemoryCostMeasure),
PerUnitStorageCostMeasure = ISNULL(PerUnitStorageCostMeasure, @PerUnitStorageCostMeasure),
PerDayMiscellaneousCostMeasure = ISNULL(PerDayMiscellaneousCostMeasure, @PerDayMiscellaneousCostMeasure),
@PerUnitCPUCostMeasure = ISNULL(PerUnitCPUCostMeasure, @PerUnitCPUCostMeasure),
@PerUnitMemoryCostMeasure = ISNULL(PerUnitMemoryCostMeasure, @PerUnitMemoryCostMeasure),
@PerUnitStorageCostMeasure = ISNULL(PerUnitStorageCostMeasure, @PerUnitStorageCostMeasure),
@PerDayMiscellaneousCostMeasure = ISNULL(PerDayMiscellaneousCostMeasure, @PerDayMiscellaneousCostMeasure)
FROM DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source2 AS T
OPTION (MAXDOP 1)
-- We have left Sourceo unattended so far, lets replicate it to Source1
-- Read the latest cost values from the daily fact
-- and replicate it to all the missing days
-- store the replicated rows in temp table source1
SELECT @task = 'Replicate cost from the MAX DateKey to all the missing days'
SELECT
dimDT.DateKey,
source0.PerUnitCPUCostMeasure,
source0.PerUnitMemoryCostMeasure,
source0.PerUnitStorageCostMeasure,
source0.PerDayMiscellaneousCostMeasure
INTO DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source1
FROM dbo.DateDim dimDT,
(SELECT
PerUnitCPUCostMeasure,
PerUnitMemoryCostMeasure,
PerUnitStorageCostMeasure,
PerDayMiscellaneousCostMeasure
FROM DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source0
WHERE DateKey = @maxDayKey) AS source0
WHERE dimDT.DateKey >= @maxDayKey
AND dimDT.DateKey <= @todayKey
-- finally it is time to merge cost from the snapshot fact and the daily fact
-- if there are any updated cost values for any given day, it will be used to update the cost from the daily fact
UPDATE source1 SET
PerUnitCPUCostMeasure = ISNULL(source2.PerUnitCPUCostMeasure, source1.PerUnitCPUCostMeasure),
PerUnitMemoryCostMeasure = ISNULL(source2.PerUnitMemoryCostMeasure, source1.PerUnitMemoryCostMeasure),
PerUnitStorageCostMeasure = ISNULL(source2.PerUnitStorageCostMeasure, source1.PerUnitStorageCostMeasure),
PerDayMiscellaneousCostMeasure = ISNULL(source2.PerDayMiscellaneousCostMeasure, source1.PerDayMiscellaneousCostMeasure)
FROM DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source1 source1
INNER JOIN DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source2 source2 ON source1.DateKey = source2.DateKey
BEGIN TRANSACTION
-- update the information back to the DailyFactVw for the existing Data
UPDATE vwDaily SET
PerUnitCPUCostMeasure = T.PerUnitCPUCostMeasure,
PerUnitMemoryCostMeasure = T.PerUnitMemoryCostMeasure,
PerUnitStorageCostMeasure = T.PerUnitStorageCostMeasure,
PerDayMiscellaneousCostMeasure = T.PerDayMiscellaneousCostMeasure,
UpdatedBatchId = @BatchId
FROM dbo.CloudServicesVMCostSettingsDailyFactvw AS vwDaily
INNER JOIN
DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source1 AS T
ON vwDaily.DateKey = T.DateKey AND
(vwDaily.PerUnitCPUCostMeasure <> T.PerUnitCPUCostMeasure OR
vwDaily.PerUnitMemoryCostMeasure <> T.PerUnitMemoryCostMeasure OR
vwDaily.PerUnitStorageCostMeasure <> T.PerUnitStorageCostMeasure OR
vwDaily.PerDayMiscellaneousCostMeasure <> T.PerDayMiscellaneousCostMeasure)
-- DELETE THE ROWS FROM DWTemp.TransformCloudServicesVirtualMachineDailyFactProc_Source1, which are already inserted
DELETE source1
FROM DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source1 source1
INNER JOIN dbo.CloudServicesVMCostSettingsDailyFactvw AS vwDaily
ON vwDaily.DateKey = source1.DateKey
-- Finally, insert into the daily fact
SELECT @task = 'Inserting into CloudServicesVMCostSettingsDailyFact'
INSERT INTO dbo.CloudServicesVMCostSettingsDailyFactvw (
CloudServicesVirtualMachineDimKey,
DateKey,
PerUnitCPUCostMeasure,
PerUnitMemoryCostMeasure,
PerUnitStorageCostMeasure,
PerDayMiscellaneousCostMeasure,
InsertedBatchId,
UpdatedBatchId
)
SELECT DISTINCT
NULL,
source1.DateKey,
source1.PerUnitCPUCostMeasure,
source1.PerUnitMemoryCostMeasure,
source1.PerUnitStorageCostMeasure,
source1.PerDayMiscellaneousCostMeasure,
@batchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source1 source1
-- update watermark
BEGIN
SELECT @task = 'Updating WaterMark'
UPDATE #tempTable SET
WaterMark = @BatchId
WHERE WarehouseEntityName = N'CloudServicesVMCostSettingsSnapshotFact'
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.TransformCloudServicesVMCostSettingsDailyFactProc_Source0') IS NOT NULL
DROP TABLE DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source0
IF OBJECT_ID('DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source1') IS NOT NULL
DROP TABLE DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source1
IF OBJECT_ID('DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source2') IS NOT NULL
DROP TABLE DWTemp.TransformCloudServicesVMCostSettingsDailyFactProc_Source2
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)
Home