Billing and Subscription Management

Importing Data for Usage-Based Subscriptions

18 views September 28, 2018 October 31, 2018 0

Data Preparation

Usage Summary Records

Create an excel file with the columns as listed in this table below. A sample file has been provided for your convenience. These columns match those found in usage files downloaded from Microsoft Azure.

Column Name Source Special Instructions Description
Charge Start Date MS Azure File
Charge End Date MS Azure File
Meter Category MS Azure File
Meter Sub-category MS Azure File
Meter Name MS Azure File
Meter Region MS Azure File
SKU MS Azure File
Unit MS Azure File
Consumed Quantity MS Azure File
Included Quantity MS Azure File
Within Commitment MS Azure File
Overage Quantity MS Azure File
Currency MS Azure File
Overage MS Azure File
Commitment Rate MS Azure File
Rate MS Azure File
Value MS Azure File Format as Text
Name Manual Input Friendly description for Usage Summary Record
Customer Manual Input Reference to Account Name in Work 365
Subscription Manual Input Reference to a usage-based Subscription in Work 365 (Subscription ID
Markup % Manual Input Copy value from subscription specified above.

 

Note the following while preparing Usage Summary records:

  • Remove double quotes, if any, from the import file.
  • Fields such as Customer, Subscription, and Currency are lookup fields. Reference active records that already exist in the Work 365 environment.

Once you are ready with the file, Go to File > Save As and save it as a CSV file. This CSV file will be used in the next step.

Usage Summary Records

 

Usage Detail Records

Create another excel file with the columns as listed in this table below. A sample file has been provided for your convenience. These columns match those found in usage files downloaded from Microsoft Azure.

Column Name Source Special Instructions Description
Usage Date MS Azure File
Meter Category MS Azure File
Meter Id MS Azure File
Meter Sub-category MS Azure File
Meter Name MS Azure File
Meter Region MS Azure File
Unit MS Azure File
Consumed Quantity MS Azure File
Resource Location MS Azure File
Consumed Service MS Azure File
Resource Group MS Azure File
Instance Id MS Azure File
Tags MS Azure File
Additional Info MS Azure File
Service Info 1 MS Azure File
Service Info 2 MS Azure File
Name Manual Input Friendly description for Usage Detail Record
Subscription Manual Input Reference to the Subscription record in Work 365
Customer Manual Input Reference to the Account record in Work 365
Charge Start Date Manual Input Copy the date from the corresponding Usage Summary records
Charge End Date Manual Input Copy the date from the corresponding Usage Summary records

 

Note the following while preparing Usage Summary records:

  • Remove double quotes, if any, from the import file.
  • Fields such as Customer, Subscription, and Currency are lookup fields. Reference active records that already exist in the Work 365 environment.

Once ready, Go to File > Save As and save it as a CSV file (the same way it was done for Usage Summary).

 

Importing your data

Go to Dynamics 365 Navigation menu > Work 365 > Subscription Management > Usage Summary.

Select “Import Data” from the options at the top.

 

Import Data

 

This will start a wizard to import Usage Summary records. Browse and upload the CSV file prepared in the previous step and proceed through the wizard.

Select “Default (Automatic Mapping)”.

Select Data Map

Map the entity and entity fields on the following screens as below:

Source Data File Work 365 Record Types
{your-csv-file} Usage Summary

 

Source Fields Work 365 Fields
Charge End Date Charge End Date
Charge Start Date Charge Start Date
Commitment Rate Stays unmapped
Consumed Quantity Consumed Quantity
Currency Currency (Map to Currency Code; remove other fields)
Customer Customer
Included Quantity Included Quantity
Markup % Markup %
Meter Category Service Name
Meter Name Resource Name
Meter Region Region
Meter Sub-category Service Type
Overage Stays unmapped
Overage Quantity Overage Quantity
Rate Post Tax Effective Rate
SKU SKU
Subscription Subscription (Map to Subscription ID; remove other fields
Unit Stays unmapped
Value Post Tax Total
Within Commitment Stays unmapped

 

Follow the instructions on the wizard and complete the process. Ignore if you receive any warning due to some unmapped fields.

Optionally, save the Data Mapping for future use.

 

Repeat the process for Usage Detail.

Map the entity and entity fields as below:

Source Data File Work 365 Record Types
{your-csv-file} Usage Detail

 

Source Fields Work 365 Fields
Additional Info Stays unmapped
Charge Start Date Charge Start Date
Charge End Date Charge End Date
Consumed Quantity Consumed Quantity
Consumed Service Stays unmapped
Customer Customer
Instance Id Stays unmapped
Meter Category Service Name
Meter Id Resource ID
Meter Name Resource Name
Meter Region Region
Meter Sub-category Service Type
Resource Group Stays unmapped
Resource Location Stays unmapped
Service Info 1 Stays unmapped
Service Info 2 Stays unmapped
Subscription Subscription (Map to Subscription ID; remove other fields
Tags Stays unmapped
Unit Stays unmapped
Usage Date Usage Date

 

Verifying the Import Process

Navigate to the Usage Subscription record referenced in the import data sheet. The imported records should show up on the Subscription screen.

verify 1

verify 2

 

 

Was this helpful?