DBMS billing integration

1 Install the DBMS billing integration
2 Configure DBMS connector behavior
3 Configure connector properties to define billing output data
4 Example dbms.properties

The DBMS billing integration outputs billing usage data to a database table.

To install in a test environment, see DBMS quickstart.

Install the DBMS billing integration

Before you begin, prepare the required servers:

Billing server that can connect to the main Abiquo database server. This can be the Reporting server.
Database server that is NOT the Abiquo database server, with the current version of MySQL used by Abiquo

To install Abiquo DBMS billing integration, do these steps:

On the billing server, to install the Abiquo billing package, run yum install abiquo-billing
Note: Abiquo billing now uses Abiquo versions
Log in to the Abiquo database:
1. Create a user for billing and grant permissions to the user. For example:
  CREATE USER 'billing'@'%' IDENTIFIED BY 'mySecureSQLpassword1'; GRANT SELECT,EXECUTE ON kinton.* TO 'billing'@'%'; GRANT SELECT ON mysql.proc TO 'billing'@'%'; GRANT SELECT ON kinton_accounting.* TO 'billing'@'%'; CREATE USER 'billing'@'localhost' IDENTIFIED BY 'mySecureSQLpassword1'; GRANT SELECT,EXECUTE ON kinton.* TO 'billing'@'localhost'; GRANT SELECT ON mysql.proc TO 'billing'@'localhost'; GRANT SELECT ON kinton_accounting.* TO 'billing'@'localhost';
2. Define the Abiquo enterprises and VDCs to generate usage data for.
  Add a record for each enterprise or VDC to the account mapping table.
  For example, to add mappings for all enterprises you would use the following SQL:
  INSERT INTO kinton.billing_account_mapping (idAbiquo, idMappingType, billingAttributeName, billingAttributeValue) (SELECT idEnterprise, 1, 'ENTERPRISE', e.name FROM kinton.enterprise e);
  For full details, see Populate the account mapping table for billing.
  Later you should create a process to maintain account mappings
Log on to the billing database server
Do not use the Abiquo database
1. Create a separate target database and MySQL user account for the billing integration
  And grant permissions for the abiquo_billing user. For example:
  CREATE DATABASE abiquo_billing CHARACTER SET=utf8; CREATE USER 'bill'@'%' IDENTIFIED BY 'mySecureSQLpassword2'; GRANT ALL PRIVILEGES ON abiquo_billing.* TO 'bill'@'%'; GRANT ALL PRIVILEGES ON abiquo_billing .* TO 'bill'@'localhost';
Log on to the billing server and go to the folder with the billing.sh script
Edit the billing.properties file
1. Set the connector_name property to "DBMS".
2. Change the billing init period to "MONTH".
3. Enter the details of the billing user and IP address to access the Abiquo database
  For full details, see Configure billing properties
4. To change the resources that are billed, activate or deactivate usages of resources in billing.properties. See below.
Edit the dbms.properties file
1. Set the provider_mapping_name and also add the credentials for the output database
  For full details of DBMS connector configuration, see below
2. If you will later configure display of Abiquo private billing data on the dashboard, set the row per usage property to true
3. To change the resources that are billed, activate or deactivate usages of resources in dbms.properties. See below.
To generate the billing data, run ./billing.sh
To check the generated billing data, go to your billing database server and query the abiquo_billing_data table
Schedule the billing integration to run automatically. See Abiquo billing integration user guide | Schedule the billing integration to run automatically
Schedule the update of the account mapping table. See Abiquo billing integration user guide | Schedule the billing integration to update the account mapping table

Configure DBMS connector behavior

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

Property	Default	Description

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.
row_per_usage	false	If true, insert one row per usage (per ID per account period). If false, put all usages on a single row
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

Example dbms.properties

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