Update COGS Metrics Data

Last updated by Biao Guo on 4/2/2022 6:39:17 AM This content is over 769 days old.

Background

MSAIDataPlatformMetrics SQL Server database is used for caching the Metrics data for MSAI Data Platform Fundamental. We leverage SOTEL insights data as the source data to compute the COGS metrics values for MSAI Data Platform. There are two approaches for compute the COGS metrics values into our SQL database.

  1. (Live) Execute Azure Data Factory pipeline and hook the SOTEL insights data from kusto.
  2. (Manual) Execute SQL script against the Cosmos Resource Management database.

Update COGS Metrics Data Automatic

Access Permission

  1. Request eligibility CosmanDBReader on OSP Portal . We use AAD authentication for external users, when the eligibility is granted, your debug account will be added into corresponding AAD group.

  2. Request join SOTELS Data Insight Users group on IDWeb.

  3. Request Contributor on DMS-DatacenterManagement.

    Torus Command

    Request-AzureResourceRoleElevation -Role Contributor -SubscriptionId 8b8b2cf1-f7e3-4d86-8581-a15cd35463a4 -Reason "update ADF" -Duration 4

Configure Details Of Pipeline

Create Linked service named SOTELInsightsDataKusto.

Create a new pipeline named MSAIDataPlatformCOGSMetricsComputation.The pipeline named Daily COGS Metrics is executed by default, Monthly COGS Metrics will be used in the LandingPageMetricsComputation.

Source

SOTELS Insights Data on Kusto interface: cluster('resourcemanagement.westus2.kusto.windows.net ').database('prod')

Sink

msaidataplatformmetrics

Compute Monthly COGS Metrics

Parameters

IsMonthly = True

Query
let CosmosStorageRateFY22 = 5.75;
let CosmosTokenRateFY22 = 189;
let Days = 365;
//MSAI Unified Data Platform Service Tree Id
let TargetServiceId = 'c377de9c-e737-4f3f-bd9b-f2024bb57e74';
let StartDate = datetime(2021-06-01);
let DefaultDate = '1900-01-01';
alias database["ResourceManagement"] = cluster('resourcemanagement.westus2.kusto.windows.net ').database('prod');
let Storage = materialize(database('ResourceManagement').CogsInsightESPStorageFullCosman
| where ServiceId == TargetServiceId
| where Date >= StartDate
| summarize round(PhysicalSize = sum(Size)/1024.0/1024/1024/1024,2) by Date
| project StorageDate = Date,PhysicalSizeTiB = PhysicalSize);
let Processing = materialize(database('ResourceManagement').CogsInsightESPProcessingFullCosman
| where ServiceId == TargetServiceId
| where Date >= StartDate
| summarize CostToken = round(sum(SuccessfulToken)+sum(UserErrorFailedToken)+sum(SystemErrorFailedUsedToken)+sum(CancelledToken)+sum(SystemErrorFailedToken)-sum(SystemErrorFailedUsedToken),0) by Date
| project ProcessingDate = Date,DailyCostToken = CostToken
);
let StorageCost = Storage
| join kind = fullouter Processing on $left.StorageDate == $right.ProcessingDate
| summarize StorageCost = round(avg(PhysicalSizeTiB)*CosmosStorageRateFY22/Days,2) by StorageDate,ProcessingDate,PhysicalSizeTiB,DailyCostToken
| extend  COGSStorageDate = case(isnotnull(StorageDate),StorageDate,isnull(StorageDate),ProcessingDate,StorageDate)
| project COGSStorageDate,StorageCost,PhysicalSizeTiB;
let ProcessingCost = Storage
| join kind = fullouter Processing on $left.StorageDate == $right.ProcessingDate
| summarize ProcessingCost = round(avg(DailyCostToken)*CosmosTokenRateFY22/Days,0) by StorageDate,ProcessingDate,PhysicalSizeTiB,DailyCostToken
| extend COGSProcessingDate = case(isnotnull(ProcessingDate),ProcessingDate,isnull(ProcessingDate),StorageDate,ProcessingDate)
| project COGSProcessingDate,ProcessingCost,DailyCostToken;
StorageCost
| join kind = inner ProcessingCost on $left.COGSStorageDate == $right.COGSProcessingDate
| extend PhysicalSizeTiB = case(isempty(PhysicalSizeTiB),0.00,PhysicalSizeTiB)
| extend DailyCostToken = case(isempty(DailyCostToken),0.00,DailyCostToken)
| extend StorageCost = case(isnan(StorageCost),0.00,StorageCost)
| extend ProcessingCost = case(isnan(ProcessingCost),0.00,ProcessingCost)
| extend sumCost = StorageCost + ProcessingCost
//Get Monthly COGS Value
| extend Date =  startofmonth(COGSStorageDate)
| summarize TotalCost = sum(sumCost) by Date
| order by Date asc 
| project MetricName = "($) MSAI Data Platform Cosmos COGS (Storage+Processing)",Date,Value = TotalCost
Pre-copy script
DELETE FROM LandingPageMetrics WHERE MetricName = '($) MSAI Data Platform Cosmos COGS (Storage+Processing)'
Setup Trigger

Set the refresh frequency according to the time stamp displayed on the dashboard, and consider the load of ADF, so this trigger is consistent with LandingPageMetricsComputation.

Compute Daily COGS Metrics

Parameters

IsMonthly = False

Query
let CosmosStorageRateFY22 = 5.75;
let CosmosTokenRateFY22 = 189;
let Days = 365;
//MSAI Unified Data Platform Service Tree Id
let TargetServiceId = 'c377de9c-e737-4f3f-bd9b-f2024bb57e74';
let StartDate = datetime(2021-06-01);
let DefaultDate = '1900-01-01';
alias database["ResourceManagement"] = cluster('resourcemanagement.westus2.kusto.windows.net ').database('prod');
let Storage = materialize(database('ResourceManagement').CogsInsightESPStorageFullCosman
| where ServiceId == TargetServiceId
| where Date >= StartDate
| summarize round(PhysicalSize = sum(Size)/1024.0/1024/1024/1024,2) by Date
| project StorageDate = Date,PhysicalSizeTiB = PhysicalSize);
let Processing = materialize(database('ResourceManagement').CogsInsightESPProcessingFullCosman
| where ServiceId == TargetServiceId
| where Date >= StartDate
| summarize CostToken = round(sum(SuccessfulToken)+sum(UserErrorFailedToken)+sum(SystemErrorFailedUsedToken)+sum(CancelledToken)+sum(SystemErrorFailedToken)-sum(SystemErrorFailedUsedToken),0) by Date
| project ProcessingDate = Date,DailyCostToken = CostToken
);
let StorageCost = Storage
| join kind = fullouter Processing on $left.StorageDate == $right.ProcessingDate
| summarize StorageCost = round(avg(PhysicalSizeTiB)*CosmosStorageRateFY22/Days,2) by StorageDate,ProcessingDate,PhysicalSizeTiB,DailyCostToken
| extend  COGSStorageDate = case(isnotnull(StorageDate),StorageDate,isnull(StorageDate),ProcessingDate,StorageDate)
| project COGSStorageDate,StorageCost,PhysicalSizeTiB;
let ProcessingCost = Storage
| join kind = fullouter Processing on $left.StorageDate == $right.ProcessingDate
| summarize ProcessingCost = round(avg(DailyCostToken)*CosmosTokenRateFY22/Days,0) by StorageDate,ProcessingDate,PhysicalSizeTiB,DailyCostToken
| extend COGSProcessingDate = case(isnotnull(ProcessingDate),ProcessingDate,isnull(ProcessingDate),StorageDate,ProcessingDate)
| project COGSProcessingDate,ProcessingCost,DailyCostToken;
StorageCost
| join kind = inner ProcessingCost on $left.COGSStorageDate == $right.COGSProcessingDate
| extend PhysicalSizeTiB = case(isempty(PhysicalSizeTiB),0.00,PhysicalSizeTiB)
| extend DailyCostToken = case(isempty(DailyCostToken),0.00,DailyCostToken)
| extend StorageCost = case(isnan(StorageCost),0.00,StorageCost)
| extend ProcessingCost = case(isnan(ProcessingCost),0.00,ProcessingCost)
| extend sumCost = StorageCost + ProcessingCost
| project Date = COGSStorageDate,PhysicalSizeTiB,StorageCost,DailyCostToken,ProcessingCost,TotalCost = sumCost
Pre-copy script
DELETE FROM COGS
Setup Trigger

Set the refresh frequency according to the time stamp displayed on the dashboard, and it is necessary to ensure the freshness of the data as much as possible. So, this part of data needs to be refreshed daily.

Explain: This is Legacy's calculation and data input method.

Update COGS Metrics data via SQL manually

Access Permission

  1. Request eligibility CosmanDBReader on OSP Portal . We use AAD authentication for external users, when the eligibility is granted, your debug account will be added into corresponding AAD group.
  2. Once the request is approved, use torus debug account to connect Cosman DB:

Example to connect with Microsoft SQL Server Management Studio (SSMS):

In Options >>:

Query the latest data of COGS

Execute this SQL Query against the ResourceManagementDatabase. The latest query is here.

DECLARE @CosmosStorageRateFY22 FLOAT;
SET @CosmosStorageRateFY22 = 5.75;

DECLARE @CosmosTokenRateFY22 FLOAT;
SET @CosmosTokenRateFY22 = 189;

DECLARE @TargetServiceId CHAR(36);
--MSAI Unified Data Platform Service Tree Id
SET @TargetServiceId = 'c377de9c-e737-4f3f-bd9b-f2024bb57e74';

DECLARE @StartDate DateTime2;
SET @StartDate = '2021-06-01';

DECLARE @DefaultDate DateTime2;
SET @DefaultDate = '1900-01-01';

WITH GetDailyCOGSData AS (
SELECT
	ISNULL(Storage.Date,' ') AS 'Storage_Date',
	ISNULL(Processing.Date,' ') AS 'Processing_Date',
	PhysicalSizeTiB,
	ISNULL(Convert(decimal(18,2), Convert(decimal(18,2), AVG(PhysicalSizeTiB))*@CosmosStorageRateFY22),0) AS StorageCost,
	DailyCostToken,
	ISNULL(Convert(decimal(18, 0), Convert(decimal(18, 2), AVG(DailyCostToken)) * @CosmosTokenRateFY22),0)  AS ProcessingCost,
	ISNULL(Convert(decimal(18,2), Convert(decimal(18,2), AVG(PhysicalSizeTiB))*@CosmosStorageRateFY22),0)+
	ISNULL(Convert(decimal(18, 0), Convert(decimal(18, 2), AVG(DailyCostToken)) * @CosmosTokenRateFY22),0)  AS TotalCost
FROM(
	select 
		Date,
		Convert(decimal(18,2),SUM(PhysicalSize)/1024.0/1024/1024/1024) as PhysicalSizeTiB
	FROM dbo.CosmosStorageInfo 
	WHERE ServiceId = @TargetServiceId and Date >= @StartDate
	GROUP BY Date
	) AS Storage
FULL OUTER JOIN(
	select
		Date,
		Convert(decimal(18,0), SUM(CostToken)) AS DailyCostToken
	FROM dbo.CosmosProcessingInfo
	WHERE ServiceId = @TargetServiceId and Date >= @StartDate
	GROUP BY Date
	) AS Processing
ON Storage.Date = Processing.Date
GROUP BY 
	Storage.Date,
	Processing.Date,
	PhysicalSizeTiB,
	DailyCostToken)

SELECT
	CASE
		WHEN Storage_Date = @DefaultDate THEN Processing_Date
		ELSE Storage_Date
	END AS 'Date',
	StorageCost,
	ProcessingCost,
	TotalCost
FROM GetDailyCOGSData
ORDER BY Date

Manually insert new data for COGS

Example to connect with Microsoft SQL Server Management Studio (SSMS):

Attribute Information
Server Type Database Engine
Server Name msai-datavolumepipeline.database.windows.net
Authentication SQL server authentication
User Name datavolumepipeline
Database MSAIDataPlatformMetrics

Compare the data of table cogs in MSAIDataPlatformMetrics SQL Server database with the data queried above, and insert the missing data.

Note: There may be numerical differences between the previously inserted data and the latest data. Replace the data with large difference to ensure the accuracy of the data.

INSERT INTO COGS(Date,StorageCost,ProcessingCost,COGS_StorageProcessing) VALUES
			('2022-02-01',83571.31,1058778,1142349.31),
			('2022-02-02',82651.42,1191078,1273729.42),
			('2022-02-03',83255.29,1163862,1247117.29),
			('2022-02-04',0.00,1187109,1187109.00),
			('2022-02-05',0.00,1075788,1075788.00),
			('2022-02-06',0.00,747306,747306.00)