Usage File Format

Updated: May 21, 2020
Contents

Introduction

CloudBlue Connect uses XLSX (Open XML) file format for the bulk import and export operations in the Usage management module. The same format of the file can be used for both manual and automated flows and file defines the scope of the processing transaction, i.e. once uploaded, the whole file gets validated and either accepted or rejected.

Data Formats

Currency: ISO 4217

Currency in the file follows the ISO 4217 standard – https://en.wikipedia.org/wiki/ISO_4217.

Timestamps: ISO 8601 in UTC

All timestamps in the file follow the ISO 8601 format in the UTC timezone (and ONLY UTC is allowed).

See https://www.unixtimestamp.com/ and https://en.wikipedia.org/wiki/ISO_8601 for details.

File Format

Usage Report contains contain the following tabs:

  1. instructions – with explanation of other tabs and self-learning materials
  2. general – with general attributes applicable to the whole file
  3. categories – with definitions of the categories used in the product with the minimum requirement to have at least 1 category
  4. records – actual table with charge records to be applied in the system
  5. summary – contains summary of the charge records per Item as illustrated below:

Note that systems must reference those tabs by names and not assume the order of these tabs.

1. Instructions Tab

Instructions tab contains general information about the file format.

2. General Tab

General information tab contains global attributes that are used to interpret the rest of the file data.

#attribute_idattribute_value
1report_nameConsumption report for November 2019
2report_noteAnything vendor things is needed
3report_idRID-12345-12345
4report_start_time_utc10/20/19 0:00
5report_end_time_utc11/20/19 0:00
6usage_schemadefines the schema: ‘TR’, ‘CR’, ‘PR’ or ‘QT’
7marketplace_account_id10000012
8currencyCurrency defined by ISO 4217 (https://en.wikipedia.org/wiki/ISO_4217) e.g. ‘USD’
9product_idPRD-12345-67890
10product_nameBest Product
11marketplace_idMKT-1234-1234
12marketplace_nameUnited States
13hub_idHB-1234-1234
14hub_nameNA Production
15vendor_account_idVA-1234-1234
16vendor_account_nameBest Vendor
17provider_account_idPA-1234-1234
18provider_account_nameBest Provider
19distribution_contract_idCRD-12345-12345

Note that some of the fields like currency are conditional to the usage_schema used.

3. Categories Tab

Categories definition tab contains lookup table to be used in other parts of the same file. At least 1 category must be defined. Example table:

category_idcategory_namecategory_description
computeComputational ResourcesAll of your virtual machines and containers fall into this category.
storagePersistance ServicesAll of your blob storages are accounted to this category.
dbDatabasesAll of various databases fall into this category

Where all IDs, Names and Description are defined by the Vendor.

4. Records Tab

Records tab contains actual list of charge records to be applied to the Commerce system in a generalized table as illustrated below:

With the following columns:

  1. record_id – (required) globally unique record ID that can never used again.
  2. record_note – (optional) optional note that is associated with the charge record, could be used to store additional notes and comments associated with the line.
  3. start_time_utc – (required) timestamp in UTC that represents beginning of the time period for the usage file.
  4. end_time_utc – (required) timestamp in UTC that represents end of the time period for the usage file.
  5. item_name – (required) human readable name of an Item defined by Vendor.
  6. item_id – (required) globally unique ID of an Item automatically assigned by Connect.
  7. item_mpn – (required) ID of an Item assigned by the vendor unique in the scope of a given Product, mutable (i.e. can be changed by the Vendor in the lifetime of a product)
  8. category_id – (required) reference to the category from the ‘categories’ tab.
  9. item_unit – (required) – string representing unit of measure for the item.
  10. asset_external_id – (required) ID of the asset assigned by Provider in the scope of a given commerce system instance (Hub).
  11. asset_external_uid – (required) globally unique ID of an asset in Provider’s systems.
  12. asset_recon_id – (optional) see Primary Reconciliation ID tag for the Fulfillment Parameter.
  13. quantity – (required) means actual unit quantity, could be same as amount for some vendors in TR, CR and PR schemas when they don’t support reporting of both values.
  14. amount – (required for TR,  CR and PR) represents total monetary amount in the currency specified in general corresponding to the line. Might not be equal to the quantity * unit_price from the vendor.
  15. tier – (required for TR) represents tier where charge record needs to be applied, applicable only in TR schema.

Where columns amount and tier are being used conditionally for different usage schemas as illustrated in the following paragraphs.

Usage Schemas

QT Schema

This schema is the simplest one as only Quantity component of the usage file is being used. Thus neither amount nor tier columns are being used, only quantity:

all of the calculations in the Provider systems are performed based on the quantity column.

PR Schema

This schema is very likely where most of the industry is heading to with Azure Modern and AWS taking the lead. In the PR schema Vendor reports T0 Price and other Tiers are calculated based on that value. Amount in this schema represents T0 price (price to the subscription owner):

and tier column is not applicable here.

CR Schema

In this schema, Vendor reports Cost in the scope of the distribution contract and prices for other tiers are calculated based on that.

Amount in this schema represents Distribution Contract Price (price to distribution contract owner). Note that marketplace_account_id (defined in ‘general’) plays an important role in this schema as cost must be applied from that level down:

amount in this schema represents price of Vendor to the Contract account and tier column is not applicable here.

TR Schema

In this schema, charges for all tiers are reported by the Vendor and are just applied by the Provider systems. Charges table uses ‘tier’ column in this case and allows to report charges to multiple tiers as illustrated in the following example:

where the same charge of the quantity 15.75 is applied to 3 tiers of the accounts with different amount.

Is this page helpful?
Translate with Google
Copied to clipboard