Calculating Azure Partner Margin in FlexReport

Products

• CloudHealth

Issue/Introduction

Please note that this method is suitable for MCA CSP Partner Customers who pass the PayG Cost for their Channel customers.

To calculate the margin using the Amortized Cost metric, follow the below steps:

Resolution

Step 1: Create the Perspective for Azure Partner Customers (Skip this step if you already have the Perspective for Azure Partner Customers.)

1. Create a Perspective named "Azure Partner Customers."
2. Use the following Query (refer to the screenshot):
   • Asset type = Azure >> Partner Customer
   • Field to Categorize By = Company Name
   • Filter = Partner Customer >> Is Active? - IS - True
3. If you have more than 400 customers, increase the Group Limit.
4. Wait for approximately 3-4 hours before creating the FlexReport since Perspective integration is needed.

Step 2: Create the FlexReport

Use the following Report Query to create a FlexReport:

{
"sqlStatement" : 
" SELECT
timeInterval_Month AS Month,
ROUND(SUM(CostInBillingCurrency), 2) AS PartnerCost,
ROUND(
CASE
WHEN PricingModel IN ('SavingsPlan', 'Reservation', 'Spot') THEN SUM(CostInBillingCurrency)
WHEN PricingModel = 'OnDemand' THEN SUM(PaygCostInBillingCurrency)
ELSE SUM(PaygCostInBillingCurrency)
END,
2
) AS CustomerCostInPayG,
ROUND(
(
CASE
WHEN PricingModel IN ('SavingsPlan', 'Reservation', 'Spot') THEN SUM(CostInBillingCurrency)
WHEN PricingModel = 'OnDemand' THEN SUM(PaygCostInBillingCurrency)
ELSE SUM(PaygCostInBillingCurrency)
END
) - SUM(CostInBillingCurrency),
2
) AS PartnerMargin,
SUM(Quantity) AS SUM_Quantity,
ELEMENT_AT (azure_AdditionalInfo_Map, 'servicetype') AS ServiceType,
Lower(ResourceLocation) AS ResourceLocation,
MeterCategory AS MeterCategory,
Term AS RIorASP_Term,
BenefitName AS RIorASP_Name,
SubscriptionName AS SubscriptionName,
SubscriptionId AS SubscriptionId,
ResourceGroup AS ResourceGroupName,
PricingModel AS PricingModel,
ChargeType AS ChargeType,
COALESCE(
CASE
WHEN perspective.\"Azure Partner Customers\" IS NULL THEN 'Asset Not Allocated'
WHEN perspective.\"Azure Partner Customers\" = 'Other' THEN 'Asset Not Allocated'
ELSE perspective.\"Azure Partner Customers\"
END,
'Asset Not Allocated'
) AS AzureCustomerName
FROM
AZURE_COST_USAGE
WHERE
(MetricType LIKE '%Amortized%')
and (AccountType IN ('MCA'))
GROUP BY
timeInterval_Month,
MetricType,
PricingModel,
ELEMENT_AT (azure_AdditionalInfo_Map, 'servicetype'),
lower(ResourceLocation),
MeterCategory,
Term,
BenefitName,
SubscriptionName,
SubscriptionId,
ResourceGroup,
AccountType,
ChargeType,
perspective.\"Azure Partner Customers\" ",

"needBackLinkingForTags" : true ,
"dataGranularity" : "MONTHLY" ,
"timeRange" : {
"last": 12
} ,
"limit" : -1
}

Step 3: Update Chart View in the FlexReport

• Chart type: Bar
• X-Axis: Month
• Y-Axis: PartnerMargin
• Category: AzureCustomerName