Usage File Format

Updated: October 14, 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:

instructions – with explanation of other tabs and self-learning materials
general – with general attributes applicable to the whole file
categories – with definitions of the categories used in the product with the minimum requirement to have at least 1 category
records – actual table with charge records to be applied in the system
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_id	attribute_value
1	report_name	Consumption report for November 2019
2	report_note	Anything vendor things is needed
3	report_id	RID-12345-12345
4	report_start_time_utc	10/20/19 0:00
5	report_end_time_utc	11/20/19 0:00
6	usage_schema	defines the schema:‘TR’, ‘CR’, ‘PR’ or ‘QT’
7	marketplace_account_id	10000012
8	currency	Currency defined by ISO 4217 (https://en.wikipedia.org/wiki/ISO_4217) e.g. ‘USD’
9	product_id	PRD-12345-67890
10	product_name	Best Product
11	marketplace_id	MKT-1234-1234
12	marketplace_name	United States
13	hub_id	HB-1234-1234
14	hub_name	NA Production
15	vendor_account_id	VA-1234-1234
16	vendor_account_name	Best Vendor
17	provider_account_id	PA-1234-1234
18	provider_account_name	Best Provider
19	distribution_contract_id	CRD-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_id	category_name	category_description
compute	Computational Resources	All of your virtual machines and containers fall into this category.
storage	Persistance Services	All of your blob storages are accounted to this category.
db	Databases	All 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:

record_id – (required) globally unique record ID that can never used again.
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.
start_time_utc – (required) timestamp in UTC that represents beginning of the time period for the usage file.
end_time_utc – (required) timestamp in UTC that represents end of the time period for the usage file.
item_name – (required) human readable name of an Item defined by Vendor.
item_id – (required) globally unique ID of an Item automatically assigned by Connect.
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)
category_id – (required) reference to the category from the ‘categories’ tab.
item_unit – (required) – string representing unit of measure for the item.
asset_external_id – (required) ID of the asset assigned by Provider in the scope of a given commerce system instance (Hub).
asset_external_uid – (required) globally unique ID of an asset in Provider’s systems.
asset_recon_id – (optional) see Primary Reconciliation ID tag for the Fulfillment Parameter.
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.
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.
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: