Yarken Help Centre
Yarken Help Centre
Breadcrumbs

Field Conversion for Cloud Usage

This page shows the field conversion for cloud usage data into a standardized format that can be easily analyzed and integrated into the application. The field conversion ensures that data from different cloud providers is consistent for reporting.

The following table defines a mapping between the source fields (from cloud providers) and the target fields (in schema).

Azure:

FOCUS Column (Target Field)

Azure Source Field

Conversion Description

AvailabilityZone

N/A


BilledCost

costInBillingCurrency

If ChargeType == “Usage” and PricingModel == “Reservation” or “SavingsPlan”, then 0; otherwise, use CostInBillingCurrency

BillingAccountId

billingAccountId


BillingAccountName

billingAccountName


BillingCurrency

billingCurrency


BillingPeriodEnd

billingPeriodEndDate


BillingPeriodStart

billingPeriodStartDate


CapacityReservationId

N/A


CapacityReservationStatus

N/A


ChargeCategory

chargeType

If “Usage”, “Purchase”, “Credit”, or “Tax”, same value; if “UnusedReservation” or “UnusedSavingsPlan”, then Usage; if “Refund”, “Purchase”; otherwise, Adjustment

ChargeClass

N/A

If “Refund”, then use Correction

ChargeDescription

ProductName


ChargeFrequency

N/A

If “OneTime”, One-Time; if “Recurring”, Recurring; if “UsageBased”, Usage-Based; otherwise, Other

ChargePeriodEnd

date

Add 1 day for the exclusive end date

ChargePeriodStart

date


CommitmentDiscountCategory

N/A

If BenefitId contains “/microsoft.capacity/” (case-insensitive), Usage; if contains “/microsoft.billingbenefits/”, use Spend; otherwise, null

CommitmentDiscountId

benefitId


CommitmentDiscountName

benefitName


CommitmentDiscountQuantity

N/A


CommitmentDiscountStatus

N/A

If “UnusedReservation” or “UnusedSavingsPlan”, then Unused; else if PricingModel == “Reservation” or “SavingsPlan”, then Used; otherwise, null

CommitmentDiscountType

N/A

If BenefitId contains “/microsoft.capacity/” (case-insensitive), Reservation; if contains “/microsoft.billingbenefits/”, Savings Plan; otherwise, null

CommitmentDiscountUnit

N/A


ConsumedQuantity

quantity

If ChargeType == “Usage”, then Quantity; otherwise, null

ConsumedUnit

unitOfMeasure

If ChargeType == “Usage”, then map using Pricing units data file; otherwise, null.

Refer: https://learn.microsoft.com/en-in/cloud-computing/finops/toolkit/open-data#pricing-units

ContractedCost

N/A

UnitPrice * Quantity / focus:x_PricingBlockSize

ContractedUnitPrice

unitPrice


EffectiveCost

effectivePrice

If ChargeType == “Purchase” or “Refund” and PricingModel == “Reservation” or “SavingsPlan”, then 0; otherwise, use CostInBillingCurrency

InvoiceIssuerName

N/A

If PartnerName is empty, use Microsoft

ListCost

paygCostInBillingCurrency


ListUnitPrice

PayGPrice


NULL

NULL


PricingCategory

N/A

If “OnDemand”, then Standard; if “Spot”, then Dynamic; if “Reservation” or “Savings Plan”, then Committed; otherwise, null

PricingQuantity

N/A

Quantity / focus:x_PricingBlockSize

PricingUnit

N/A

Map UnitOfMeasure to DistinctUnits using Pricing units data file.

Refer: https://learn.microsoft.com/en-in/cloud-computing/finops/toolkit/open-data#pricing-units

ProductId

ProductId


ProductName

ProductName


ProviderName

provider


PublisherName

publisherName


RegionId

resourceLocation

focus:RegionName, Lowercase and remove spaces

RegionName

N/A

Map ResourceLocation (OriginalValue) to RegionName using Regions data file2.

Refer: https://learn.microsoft.com/en-in/cloud-computing/finops/toolkit/open-data#regions

ResourceGroupName

resourceGroupName


ResourceId

ResourceId


ResourceName

NULL

last(split(ResourceId, “/”))

ResourceType

NULL

it will pick the value after /Providers until it encounters 2nd slash(/) or end of the string

ServiceCategory

serviceFamily

Picks service Category based on service Name

ServiceName

consumedService

Map ConsumedService and ResourceType to SerivceName using Services data file.

Refer: https://learn.microsoft.com/en-in/cloud-computing/finops/toolkit/open-data#services

ServiceSubcategory

N/A


SkuId

N/A


SkuMeter

N/A


SkuPriceDetails

N/A


SkuPriceId

N/A


SubAccountId

SubscriptionId


SubAccountName

subscriptionName


X_BilledCostInUSD

costInUsd


X_BillingExchangeRate

exchangeRatePricingToBilling


X_bill_bill_type

N/A


X_estimate_type

NULL


X_frequency

frequency


X_line_item_line_item_type

N/A


X_line_item_net_unblended_cost

N/A


X_line_item_usage_amount

N/A


X_manufacturing_emissions_tonnes_co2e

NULL


X_partnerName

NULL


X_pricingModel

pricingModel


X_pricing_public_on_demand_cost

N/A


X_pricing_public_on_demand_rate

N/A


X_pricing_rate_code

N/A


X_pricing_rate_id

N/A


X_product

NULL


X_product_purchase_option

N/A


X_reservation_effective_cost

N/A


X_reservation_reservation_a_r_n

N/A


X_reservation_reservation_arn

N/A


X_reservation_total_reserved_units

N/A


X_reservation_unused_amortized_upfront_fee_for_billing_period

N/A


X_reservation_unused_recurring_fee

N/A


X_savings_plan_savings_plan_arn

N/A


X_savings_plan_savings_plan_effective_cost

N/A


X_savings_plan_total_commitment_to_date

N/A


X_savings_plan_used_commitment

N/A


X_total_combined_emissions_tonnes_co2e

NULL


X_usage_electricity_consumption_kwh

NULL


X_usage_emissions_tonnes_co2e

NULL


X_water_consumption_cooling_litres

NULL


X_water_consumption_electricity_production_litres

NULL


customDate1

NULL


customDate2

NULL


customDate3

NULL


customDate4

NULL


customDate5

NULL


customNumber1

NULL


customNumber2

NULL


customNumber3

NULL


customNumber4

NULL


customNumber5

NULL


customString1

NULL


customString2

NULL


customString3

NULL


customString4

NULL


customString5

NULL


tags

tags

Wrap in { and } if needed

AWS V2:

FOCUS Column (Target Field)

AWS V2 Source Field

Conversion Description

AvailabilityZone

line_item_availability_zone


BilledCost

line_item_unblended_cost

conditions:

  • WHEN line_item_net_unblended_cost is not null THEN line_item_net_unblended_cost


default_value: line_item_unblended_cost

BillingAccountId

bill_payer_account_id


BillingAccountName

bill_payer_account_name


BillingCurrency

line_item_currency_code


BillingPeriodEnd

bill_billing_period_end_date


BillingPeriodStart

bill_billing_period_start_date


CapacityReservationId

N/A


CapacityReservationStatus

N/A


ChargeCategory

N/A

conditions:

  • WHEN line_item_line_item_type = 'Tax' THEN 'Tax'

  • WHEN line_item_line_item_type = 'Fee' THEN 'Purchase'

  • WHEN line_item_line_item_type = 'SavingsPlanUpfrontFee' THEN 'Purchase'

  • WHEN line_item_line_item_type = 'RIFee' THEN 'Purchase'

  • WHEN line_item_line_item_type = 'SavingsPlanRecurringFee' THEN 'Purchase'

  • WHEN line_item_line_item_type = 'Usage' THEN 'Usage'

  • WHEN line_item_line_item_type = 'SavingsPlanCoveredUsage' THEN 'Usage'

  • WHEN line_item_line_item_type = 'SavingsPlanNegation' THEN 'Usage'

  • WHEN line_item_line_item_type = 'DiscountedUsage' THEN 'Usage'

  • WHEN line_item_line_item_type = 'BundledDiscount' THEN 'Usage'

  • WHEN line_item_line_item_type = 'Discount' THEN 'Usage'

  • WHEN line_item_line_item_type = 'PrivateRateDiscount' THEN 'Usage'

  • WHEN line_item_line_item_type = 'EdpDiscount' THEN 'Usage'

  • WHEN line_item_line_item_type = 'Credit' THEN 'Adjustment'

  • WHEN line_item_line_item_type = 'Refund' THEN 'Adjustment'


default_value:  Whatever is Mapped it will pick its value

ChargeClass

N/A


ChargeDescription

line_item_line_item_description


ChargeFrequency

N/A

default_value: Whatever is Mapped it will pick its value
value_list:

  • key: Refund


  value: One-Time

  • key: Purchase


  value: One-Time

  • key: Anniversary


  value: Recurring

ChargePeriodEnd

line_item_usage_end_date


ChargePeriodStart

line_item_usage_start_date


CommitmentDiscountCategory

N/A


CommitmentDiscountId

N/A


CommitmentDiscountName

N/A


CommitmentDiscountQuantity

N/A


CommitmentDiscountStatus

N/A


CommitmentDiscountType

N/A


CommitmentDiscountUnit

N/A


ConsumedQuantity

N/A


ConsumedUnit

pricing_unit


ContractedCost

N/A


ContractedUnitPrice

N/A


EffectiveCost

N/A

    WHEN (line_item_line_item_type = 'SavingsPlanCoveredUsage') THEN savings_plan_savings_plan_effective_cost
    WHEN (line_item_line_item_type = 'SavingsPlanRecurringFee') THEN (savings_plan_total_commitment_to_date - savings_plan_used_commitment)
    WHEN (line_item_line_item_type = 'SavingsPlanNegation') THEN 0
    WHEN (line_item_line_item_type = 'SavingsPlanUpfrontFee') THEN 0
    WHEN (line_item_line_item_type = 'DiscountedUsage') THEN reservation_effective_cost
    WHEN (line_item_line_item_type = 'RIFee') THEN (reservation_unused_amortized_upfront_fee_for_billing_period + reservation_unused_recurring_fee)
    WHEN ((line_item_line_item_type = 'Fee') AND (reservation_reservation_a_r_n <> '')) THEN 0
    ELSE line_item_unblended_cost.
default_value: Whatever is Mapped it will pick its value

InvoiceIssuerName

bill_invoicing_entity


ListCost

N/A

conditions:

  • WHEN pricing_public_on_demand_rate is not null THEN pricing_public_on_demand_rate

  • line_item_usage_amount


default_value: pricing_public_on_demand_cost

ListUnitPrice

pricing_public_on_demand_rate


NULL

NULL


PricingCategory

N/A


PricingQuantity

N/A

conditions:

  • WHEN reservation_total_reserved_units is not null THEN reservation_total_reserved_units


default_value: line_item_usage_amount

PricingUnit

N/A


ProductId

line_item_product_code


ProductName

line_item_product_code


ProviderName

bill_billing_entity


PublisherName

N/A


RegionId

product_region_code


RegionName

N/A


ResourceGroupName

N/A


ResourceId

line_item_resource_id


ResourceName

N/A

it will pick the value after last slash('/')

ResourceType

N/A


ServiceCategory

line_item_product_code

Picks service Category based on service Name

ServiceName

line_item_product_code


ServiceSubcategory

N/A


SkuId

product_sku


SkuMeter

N/A


SkuPriceDetails

N/A


SkuPriceId

N/A

conditions:

  • WHEN pricing_rate_code is not null THEN pricing_rate_code


default_value: pricing_rate_id

SubAccountId

line_item_usage_account_id


SubAccountName

line_item_usage_account_name


X_BilledCostInUSD

N/A


X_BillingExchangeRate

N/A


X_bill_bill_type

bill_bill_type


X_estimate_type

NULL


X_frequency

N/A


X_line_item_line_item_type

line_item_line_item_type


X_line_item_net_unblended_cost

line_item_net_unblended_cost


X_line_item_usage_amount

line_item_usage_amount


X_manufacturing_emissions_tonnes_co2e

NULL


X_partnerName

N/A


X_pricingModel

N/A


X_pricing_public_on_demand_cost

pricing_public_on_demand_cost


X_pricing_public_on_demand_rate

pricing_public_on_demand_rate


X_pricing_rate_code

pricing_rate_code


X_pricing_rate_id

pricing_rate_id


X_product

N/A


X_product_purchase_option

NULL


X_reservation_effective_cost

reservation_effective_cost


X_reservation_reservation_a_r_n

reservation_reservation_a_r_n


X_reservation_reservation_arn

NULL


X_reservation_total_reserved_units

reservation_total_reserved_units


X_reservation_unused_amortized_upfront_fee_for_billing_period

reservation_unused_amortized_upfront_fee_for_billing_period


X_reservation_unused_recurring_fee

reservation_unused_recurring_fee


X_savings_plan_savings_plan_arn

NULL


X_savings_plan_savings_plan_effective_cost

savings_plan_savings_plan_effective_cost


X_savings_plan_total_commitment_to_date

savings_plan_total_commitment_to_date


X_savings_plan_used_commitment

savings_plan_used_commitment


X_total_combined_emissions_tonnes_co2e

NULL


X_usage_electricity_consumption_kwh

NULL


X_usage_emissions_tonnes_co2e

NULL


X_water_consumption_cooling_litres

NULL


X_water_consumption_electricity_production_litres

NULL


customDate1

NULL


customDate2

NULL


customDate3

NULL


customDate4

NULL


customDate5

NULL


customNumber1

NULL


customNumber2

NULL


customNumber3

NULL


customNumber4

NULL


customNumber5

NULL


customString1

NULL


customString2

NULL


customString3

NULL


customString4

NULL


customString5

NULL


tags

resource_tags

Wrap in { and } if needed

AWS - Legacy:

FOCUS Column (Target Field)

AWS - Legacy Source Field

Conversion Description

AvailabilityZone

lineItem/AvailabilityZone


BilledCost

lineItem/UnblendedCost

conditions:

  • WHEN "lineItem/NetUnblendedCost"  is not null THEN "lineItem/NetUnblendedCost"


default_value: '"lineItem/UnblendedCost"'

BillingAccountId

bill/PayerAccountId


BillingAccountName

N/A


BillingCurrency

lineItem/CurrencyCode


BillingPeriodEnd

bill/BillingPeriodEndDate


BillingPeriodStart

bill/BillingPeriodStartDate


CapacityReservationId

N/A


CapacityReservationStatus

N/A


ChargeCategory

N/A

conditions:

  • WHEN "lineItem/LineItemType" = 'Tax' THEN 'Tax'

  • WHEN "lineItem/LineItemType" = 'Fee' THEN 'Purchase'

  • WHEN "lineItem/LineItemType" = 'SavingsPlanUpfrontFee' THEN 'Purchase'

  • WHEN "lineItem/LineItemType" = 'RIFee' THEN 'Purchase'

  • WHEN "lineItem/LineItemType" = 'SavingsPlanRecurringFee' THEN 'Purchase'

  • WHEN "lineItem/LineItemType" = 'Usage' THEN 'Usage'

  • WHEN "lineItem/LineItemType" = 'SavingsPlanCoveredUsage' THEN 'Usage'

  • WHEN "lineItem/LineItemType" = 'SavingsPlanNegation' THEN 'Usage'

  • WHEN "lineItem/LineItemType" = 'DiscountedUsage' THEN 'Usage'

  • WHEN "lineItem/LineItemType" = 'BundledDiscount' THEN 'Usage'

  • WHEN "lineItem/LineItemType" = 'Discount' THEN 'Usage'

  • WHEN "lineItem/LineItemType" = 'PrivateRateDiscount' THEN 'Usage'

  • WHEN "lineItem/LineItemType" = 'EdpDiscount' THEN 'Usage'

  • WHEN "lineItem/LineItemType" = 'Credit' THEN 'Adjustment'

  • WHEN "lineItem/LineItemType" = 'Refund' THEN 'Adjustment'


default_value: ''''''

ChargeClass

N/A


ChargeDescription

lineItem/LineItemDescription


ChargeFrequency

N/A

default_value: ''
value_list:

  • key: Refund


  value: One-Time

  • key: Purchase


  value: One-Time

  • key: Anniversary


  value: Recurring

ChargePeriodEnd

lineItem/UsageEndDate


ChargePeriodStart

lineItem/UsageStartDate


CommitmentDiscountCategory

N/A


CommitmentDiscountId

N/A


CommitmentDiscountName

N/A


CommitmentDiscountQuantity

N/A


CommitmentDiscountStatus

N/A


CommitmentDiscountType

N/A


CommitmentDiscountUnit

N/A


ConsumedQuantity

N/A


ConsumedUnit

pricing/unit


ContractedCost

N/A


ContractedUnitPrice

N/A


EffectiveCost

N/A


InvoiceIssuerName

bill/InvoicingEntity


ListCost

N/A


ListUnitPrice

pricing/publicOnDemandRate


NULL

NULL


PricingCategory

N/A


PricingQuantity

N/A

conditions:

  • WHEN "reservation/TotalReservedUnits" is not null THEN "reservation/TotalReservedUnits"


default_value: '"lineItem/UsageAmount"'

PricingUnit

N/A


ProductId

lineItem/ProductCode


ProductName

N/A


ProviderName

bill/BillingEntity


PublisherName

N/A


RegionId

product/region


RegionName

N/A


ResourceGroupName

N/A


ResourceId

lineItem/ResourceId


ResourceName

N/A

it will pick the value after last slash('/')

ResourceType

N/A


ServiceCategory

lineItem/ProductCode

Picks service Category based on service Name

ServiceName

lineItem/ProductCode


ServiceSubcategory

N/A


SkuId

product/sku


SkuMeter

N/A


SkuPriceDetails

N/A


SkuPriceId

N/A


SubAccountId

lineItem/UsageAccountId


SubAccountName

NULL


X_BilledCostInUSD

N/A


X_BillingExchangeRate

N/A


X_bill_bill_type

bill/BillType


X_estimate_type

NULL


X_frequency

N/A


X_line_item_line_item_type

lineItem/LineItemType


X_line_item_net_unblended_cost

NULL


X_line_item_usage_amount

lineItem/UsageAmount


X_manufacturing_emissions_tonnes_co2e

NULL


X_partnerName

N/A


X_pricingModel

N/A


X_pricing_public_on_demand_cost

pricing/publicOnDemandCost


X_pricing_public_on_demand_rate

N/A


X_pricing_rate_code

pricing/RateCode


X_pricing_rate_id

pricing/RateId


X_product

N/A


X_product_purchase_option

NULL


X_reservation_effective_cost

reservation/EffectiveCost


X_reservation_reservation_a_r_n

N/A


X_reservation_reservation_arn

NULL


X_reservation_total_reserved_units

reservation/TotalReservedUnits


X_reservation_unused_amortized_upfront_fee_for_billing_period

reservation/UnusedAmortizedUpfrontFeeForBillingPeriod


X_reservation_unused_recurring_fee

reservation/UnusedRecurringFee


X_savings_plan_savings_plan_arn

savingsPlan/SavingsPlanARN


X_savings_plan_savings_plan_effective_cost

savingsPlan/SavingsPlanEffectiveCost


X_savings_plan_total_commitment_to_date

savingsPlan/TotalCommitmentToDate


X_savings_plan_used_commitment

savingsPlan/UsedCommitment


X_total_combined_emissions_tonnes_co2e

NULL


X_usage_electricity_consumption_kwh

NULL


X_usage_emissions_tonnes_co2e

NULL


X_water_consumption_cooling_litres

NULL


X_water_consumption_electricity_production_litres

NULL


customDate1

NULL


customDate2

NULL


customDate3

NULL


customDate4

NULL


customDate5

NULL


customNumber1

NULL


customNumber2

NULL


customNumber3

NULL


customNumber4

NULL


customNumber5

NULL


customString1

NULL


customString2

NULL


customString3

NULL


customString4

NULL


customString5

NULL


tags

N/A