DBMS Billing Integration

The DBMS billing integration outputs usage data to a database table for billing. The database table may be hosted on any suitable MySQL database server (although we would recommend that you do NOT use the Abiquo server's Database server).

For basic install and configuration instructions, see DBMS Quickstart.

Create the 'target' Database, and User Account for access

On your target database server, ensure that you create a database schema to hold the billing data, and a user account to access it. The following MySQL SQL can be used to help you do that:

CREATE DATABASE abiquo_billing CHARACTER SET=utf8;
CREATE USER 'bill'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON abiquo_billing.* TO 'bill'@'%';

You should adjust the follow values to suit your requirements:

abiquo_billing
bill
password

Configure DBMS Connector behavior

The settings for controlling the behavior of the DBMS connector are described below.

Property	Default	Description
dbms_connection_url		Location of the the 'target' DBMS server (that will hold the generated billing data) e.g. dbms_connection_url=jdbc:mysql://127.0.0.1:3306/abiquo_billing
dbms_user		User for connecting to the 'target' DBMS server (that will hold the generated billing data) e.g. dbms_user=bill
dbms_password		User for connecting to the 'target' DBMS server (that will hold the generated billing data) e.g. dbms_password=mypassword
dbms_jdbc_driver	dbms_jdbc_driver	Driver for connecting to the 'target' DBMS server (that will hold the generated billing data). Currently only the default value of 'com.mysql.jdbc.Driver' is supported.
table_name	abiquo_billing_data	The name of table in which the generated billing data is stored in the target database. Changing this setting after the billing integration has been run may cause a new table to be generated, however the new table will not contain any old/existing data.
validate_table	true	If true, the Billing Integration will check that the DBMS table exists, and that it contains suitable database columns to hold the configured data. It is strongly recommended that the default value of "true" is used.
mysql_ddl	true	If the DBMS connector determines that the target table does not exist, it will automatically try to create it. If this property is set to true, use MySQL-specific data defintion language (DDL) to create the table. The default value of 'true' is recommended for MySQL target databases.
provider_mapping_name		This is the name of the billing attribute name (defined in the billing_account_mapping table) which the DBMS connector will obtain the Billing System customer ID from. e.g. provider_mapping_name=ZUORA_ACCOUNT_NUMBER
include_usage_costs	false	Include usage costs for a pricing template. When set to true, the DBMS connector will include additional costing columns in its output, with one additional column for each usage defined in the pricing template. Values that are not defined in the pricing templates are not covered by billing when including usage costs: 'external storage' usage does not include costings; these should be costed via storage tier usage. hypervisor custom usages Values that have charging periods independent of the billing charge period are not covered by billing: minimum charge standing charge This is because the charging periods cannot be adjusted to the billing periods without risk of calculation error.
datacenter	false	If Physical Datacenter Billing for grouping Abiquo Datacenters is enabled in the billing.properties file, it must also be enabled in the associated Billing Connector. If true, this property will include datacenter details in the connector output. The default value of this property is ‘false’. Note: If this value is enabled but Physical Datacenter Billing by Abiquo Datacenter is not enabled in billing.properties, then datacenter details will be recorded as ‘n/a’.
start_period	true	Mandatory column. Timestamp of the start of the accounting period
end_period	true	Timestamp of the end of the accounting period (exclusive)
abiquo_type	true	The 'type' of the Abiquo ID (ENTERPRISE or VDC)
abiquo_id	true	The Abiquo ID value
bill_system_id	true	The Billing System's Customer ID, as defined in the 'billing_account_mapping' table
start_period	true	This mandatory column appears in all reports and it is the timestamp of the start of the accounting period
Other Columns		Set boolean values to false to exclude the following columns from the DBMS table. Do NOT change the column definitions after the billing integration has been run, since the table columns are fixed when the table is created.
end_period	true	If true, include the column containing the timestamp of the end of the accounting period (exclusive)
abiquo_type	true	If true, include the column containing the 'type' of the Abiquo ID (ENTERPRISE or VDC)
abiquo_id	true	If true, include the column containing the Abiquo ID value
bill_system_id	true	If true, include the Billing System's Customer ID, as defined in the 'billing_account_mapping' table

Configure connector properties to define billing output data

To include these columns in the usage data output file, in the {connector}.properties file set these properties to "true". Then configure any associated properties, such as name and display name.

By default all quantities are per accounting period, which by default is 1 hour. So for example, a 1 CPU VM running for the full 24 hours of a DAY Accounting period will consume a quantity of 24 CPUs.

Usage data columns	Default	Description
vm	false	If true, include fee per VM for the account period
vm_on	false	If true, include fee per VM for the account period when the VM was ON
vm_off	false	If true, include fee per VM for the account period when the VM was OFF
cpu	true	If true, include amount of CPU for the account period
cpu_on	false	If true, include amount of CPU for the account period when the VM was ON
cpu_off	false	If true, include amount of CPU for the account period when the VM was OFF
mem	true	If true, include amount of Memory for the account period (MB)
mem_on	false	If true, include amount of Memory for the account period when the VM was ON
mem_off	false	If true, include amount of Memory for the account period when the VM was OFF
ip	true	If true, include amount of IPs used for the account period
vlan	true	If true, include amount of VLANs used for the account period
local_storage	true	If true, include amount of Local Storage used for the Account Period (GB)
external_storage	true	If true, include amount of External Storage used for the account period (GB)
ha	false	If true include if the VM is hosted on an Abiquo rack with high availability enabled
reserved_server_cpu	false	If true, include amount of CPU in reserved servers that have been explicitly reserved for an Enterprise
reserved_server_memory	false	If true, include amount of RAM in reserved servers that have been explicitly reserved for an Enterprise
repository	false	If true, include the amount of Repository usage (GB) by the enterprise for VM Templates and Instances (conversions are not accounted)
anti_affinity	false	If true, include if the Virtual machine is deployed in an anti-affinity layer to ensure it is deployed separately from other VMs in the same layer
firewall	false	If true, include the number of firewalls
loadbalancer	false	If true, include the number of load balancers
hardwareprofile_usage	false	If true, include the amount for hardware profiles used
hardwareprofile_onoff_usage	false	If true, include the amount for hardware profiles used when VMs are ON or OFF
tiered_storage	false	If true, process tiers that are defined in billing.properties file with the storage_tier_<n>_name property Define the storage tiers that you want to include in the output file, in the order that the custom columns should appear
storage_tier_<n>		Define the order of the storage tier in the output file with a number <n> starting from 1
storage_tier_<n>_name		The same case-sensitive name of the 'storage_tier_<n>_name' in the billing.properties file
storage_tier_<n>_display_name		Custom column heading
local_tier_storage	false	If true, process datastore tiers that are defined in billing.properties file with the local_tier_storage_<n>_name property Define the datastore tiers that you want to include in the output file, in the order that the custom columns should appear
local_tier_storage_<n>		Defines the order of the datastore tier in the output file with a number <n> starting from 1
local_tier_storage_<n>_name		The same case-sensitive name as the value of the 'local_tier_storage_<n>_name' in the billing.properties file
storage_tier_<n>_display_name		Custom column heading
backup_usage	false	Process backup pricing per GB usage using the backup_policy_<n> and the backup_policy_<n>_code properties
backup_policy_<n>	false	e.g. backup_policy_2=false
backup_poliy_<n>_code		e.g. backup_policy_2_code=Backup 2
draas_protection_usage	false	If true, process VMs with DRaaS protection
cost_codes	false	If true, process cost codes that are defined in billing.properties file with the cost_code_<n>_name property Define the Cost Codes that you want to include in the output file, in the order you wish those cost codes to appear
cost_code_<n>		Defines the order of the cost code in the output file with a cost code number <n> starting from 1 for the first cost code
cost_code_<n>_name		The same case-sensitive name as the 'cost_code_<n>_name' value in the billing.properties file.
cost_code_<n>_display_name		The customized display names can be values such as strings or account numbers, for example: cost_code_1_display_name = LINUX1 cost_code_2_display_name = WINDOWS10
hypervisors	false	If true, process hypervisor usage Also define the hypervisor usages in the billing.properties file with the hypervisor_<n>_name properties Define the hypervisors that you want to include in the output file, in the order that the custom columns of hypervisors should appear
hypervisor_<n>		Defines the order of the hypervisor in the output file with a hypervisor number <n> starting from 1 for the first hypervisor
hypervisor_<n>_name		E.g. hypervisor_1_name=VMX_04 The same case-sensitive name, which is the same as the value of the 'hypervisor_<n>_name' property in the billing.properties file
hypervisor_<n>_display_name		hypervisor_1_display_name=VMWareESX
custom_<n>		e.g. custom_1=ESXHypervisors If custom=true, set these properties to custom usage names to process the custom usages Also define the custom usage in the billing.properties file with the custom_usage_<n>_name properties Note that the usage number <n> does not need to match the number in the main billing properties file

Example dbms.properties

This is a sample file only. Please see the dbms.properties file on your billing system to check property values.

Click here to expand...

##############
#
# DBMS Connector specific properties
#
##############

# DBMS settings used to connect to the 'target' DBMS where the billing
# data will be stored
dbms_jdbc_driver=com.mysql.jdbc.Driver
dbms_connection_url=jdbc:mysql://sacedo40.bcn.abiquo.com:3306/abiquo_billing
dbms_user=root
dbms_password=

# Name of the DBMS table that will hold the DBMS data
table_name=abiquo_billing_data

# Controls whether table validation (and creation if doesn't exist) occurs
# during initialization. Default is 'true'
#validate_table=false

# If/when auto creating the target DBMS table, determines whether MySQL specific
# DDL is used (preferred for MySQL).   Default is 'true'
#mysql_ddl=false

# determines whether we have one row per usage, or put all usages on a single row...
row_per_usage=false

# name of the billing system property name to use as the DBMS Billing System ID value
provider_mapping_name=ENTERPRISE

# Should we include usage costings in the DBMS output?
include_usage_costs=true

# what optional information is required to qualify the billing data?
datacenter=false
end_period=true
abiquo_type=true
abiquo_id=true
bill_system_id=true


# These values determine whether particular usages will be included in the results
# Invalid Usages will be reported as 0 value...
vm=false
vm_on=false
vm_off=false
cpu=true
cpu_on=false
cpu_off=false
mem=true
mem_on=false
mem_off=false
ip=true
vlan=true
local_storage=true
external_storage=true
ha=false
reserved_server_cpu=false
reserved_server_memory=false
repository=false
anti_affinity=false
firewall=false
loadbalancer=false
hardwareprofile_usage=false
hardwareprofile_onoff_usage=false

tiered_storage=false
#storage_tier_1=false
#storage_tier_2=false
#storage_tier_2_name=Tier 2
#storage_tier_3=false
#storage_tier_4=false

# Local Tiered Storage Usages
local_tier_storage_usage=false
#local_tier_storage_1=false
#local_tier_storage_2=false
#local_tier_storage_2_name=Tier 2 <--- Configure the non-default Tier names, if required...
#local_tier_storage_3=false
#local_tier_storage_4=false

# backup Usages
backup_usage=false
#backup_policy_1=false
#backup_policy_2=false
#backup_policy_2_code=Backup 2 <--- Configure the Policy code
#backup_policy_3=false
#backup_policy_4=false

# DRaaS usage
draas_protection_usage=false

# Only need to include the cost codes that are required in the DBMS table
cost_codes=false
#cost_code_1=true
#cost_code_1_name=Monowall

# Only need to include the hypervisor usages that are required in the CSV file
# Optional - the entries below have been overriden to use 'friendly' names
hypervisors=false
hypervisor_1=false
hypervisor_1_name=VMX_04
hypervisor_1_display_name=VMWareESXi
hypervisor_2=false
hypervisor_2_name=KVM
hypervisor_3=false
hypervisor_3_name=ORACLE_VM
hypervisor_3_display_name=OracleVM
hypervisor_4=false
hypervisor_4_name=XENSERVER
hypervisor_4_display_name=XenServer
hypervisor_5=false
hypervisor_5_name=HYPERV_301
hypervisor_5_display_name=HyperV
hypervisor_6=false
hypervisor_6_name=AMAZON
hypervisor_6_display_name=AmazonAWS


# Only need to include the custom usages that are required in the DBMS table
custom=false
custom_1=false
custom_1_name=RepositoryUsageGB