In CloudPortal Services Manager v11 there have been some major improvements to billing functionality that provide much more flexibility to users of the system to control what appears in their billing reports.

Where a usage data collection process is in use for a service such as Hosted Exchange, this will collect usage stats from servers hosting the service and store this usage into the CloudPortal Services Manager transactional database, named OLM. It is then possible to use this usage data for billing purposes, which may contain values such as the disk usage in MB for each mailbox.

The Service Schema page in v11 has had some new functionality added that has been called Billing Units, which will enable a user of CloudPortal Services Manager with administrative privileges to define how a service should appear in the billing report.

By default, the Hosted Exchange service is configured to display one row per Customer in the report to represent the instance of the service provisioned to the Customer, with the Stock Code and Sales Price for this row read from the Customer Plan. Additionally, there will be one row per User displayed in the billing report with the Stock Code and Sales Price for these rows read from the selected User Plan.

The quantity column in the billing report will be based on the provisioning status of the instance or user, with a provisioning status of “Not Provisioned” resulting in a quantity of 0, or a quantity of 1 for any other status. It may be more appropriate to change the pricing model to based the quantity on usage data such as the disk usage in MB value that is collected for Hosted Exchange.

To do this, you can go to the Service Schema page for Hosted Exchange and expand the Billing Units section. Then you can edit the Billing Unit that is based on User Plan and User, and in the Advanced Settings section you can change the Quantity to be based on a Counter by selecting the radio-button, then by selecting the appropriate Counter from the drop-down list that is enabled. In the case of Hosted Exchange this is named DiskUsageMB.

Once you click Save on the Billing Unit and then Save on the Service Schema page, the change will have been made although it will not immediately be visible in the billing report. To view this change you will first need to go to the Report-Configuration-Data Warehouse page in CloudPortal Services Manager and click the “Transfer data from views to data warehouse” link then wait for the status indicator to change from orange back to green.

Once this has completed you can run the Customer Detail or Reseller Detail billing report from the report menu. The date parameters for the report will default to yesterday’s date which will still display the users with a quantity of 1 based on provisioning status. This is because the Billing Unit change made will not affect historical data in the billing report. Because the data transfer process was executed today you will need to change the date parameters on the report to today’s date so that the current set of data will be retrieved. Once this has been done you should see the quantity displaying values in MB rather than just a 1 for each row. You will also need to set the Sales Price to a relevant value in the User Plan so that the Total Price will be calculated correctly.

An additional modification to the billing report could be to display an excess usage quantity that is calculated for each customer, based on a comparison of the mailbox disk limit for the customer and a sum of the disk usage values for each of the customer’s users. To achieve this does involve coding this calculation, which can be implemented as a custom view such as the one below that can be added to the OLM database.


USE [OLM]
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_rpt_HE_CustomerPlan_Instance_ExcessUsage]'))
DROP VIEW [dbo].[vw_rpt_HE_CustomerPlan_Instance_ExcessUsage]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_rpt_HE_CustomerPlan_Instance_ExcessUsage]
AS


SELECT
[s].[ObjectID] AS [ObjectID],
[cp].[ObjectID] AS [CustomerPlanObjectID],
'Customer Plan' AS [PlanType],
MAX ( CAST ( [c].[Label] AS NVARCHAR(255) ) ) AS [Description],
MAX ( CAST ( [cp].[PackageName] AS NVARCHAR(255) ) ) AS [Name],
CASE ISNUMERIC ( ISNULL ( NULLIF ( MAX ( [limit].[Value] ), '' ), '0') + '.0e0' )
WHEN 1 THEN
CASE CAST ( MAX ( [limit].[Value] ) AS FLOAT )
WHEN 0 THEN 0
ELSE CASE ISNULL ( SUM ( [sd].[Value] ), 0 )
WHEN 0 THEN 0
ELSE SUM ( [sd].[Value] ) - MAX ( [limit].[Value] )
END
END
ELSE 0
END AS [ExcessUsageMB]
FROM [dbo].[Services] [s] with (nolock)
INNER JOIN [dbo].[Customers] [c] with (nolock) ON [s].[CustomerID] = [c].[CustomerID]
INNER JOIN [dbo].[PackageTemplate] [cp] with (nolock) ON [s].[ServiceID] = [cp].[ServiceID]
INNER JOIN [dbo].[PropertyValues_Flat] [limit] with (nolock) ON [limit].[ObjectID] = [cp].[ObjectID]
INNER JOIN [dbo].[Properties] [limitproperty] with (nolock) ON [limit].[PropertyID] = [limitproperty].[PropertyID]
INNER JOIN [dbo].[ServiceUsers] [su] with (nolock) ON [s].[ServiceID] = [su].[ServiceID]
INNER JOIN [dbo].[vw_StatsData] [sd] with (nolock) ON [su].[ObjectID] = [sd].[ObjectID]
WHERE [s].[Name] = 'HE'
AND [s].[ResellerService] = 0
AND [limitproperty].[Name] = N'MailBoxDiskLimit'
AND [sd].[Counter] = N'DiskUsageMB'
AND [sd].[DateTimeOfData] = DATEADD ( D, -1, DATEDIFF ( D, 0, getdate() ) )
GROUP BY [S].[ObjectID], [cp].[ObjectID]

GO

Once this view has been created in the OLM database, it will need to be registered with CloudPortal Services Manager so that it can be used as a Billing Unit. To do this involves executing a stored procedure named [dbo].[sp_BillingUnit_Manage] to which the name of the view will be provided as a parameter along with some additional descriptive information to define the purpose of the view and its resultset. The parameters for this stored procedure are shown below:

EXECUTE [dbo].[sp_BillingUnit_Manage]
@BaseView='[dbo].[vw_rpt_HE_CustomerPlan_Instance_ExcessUsage]',
@UnitLabel='Per Instance - Excess Usage',
@ServiceName='HE',
@ServiceType='Instance',
@PlanType='Customer Plan',
@Key1ColumnName='[ObjectID]',
@Key1Prefix='',
@Key1PropertyGroups='Service.{Service}',
@Key2ColumnName='[CustomerPlanObjectID]',
@Key2Prefix='PackageTemplate.{Service}',
@Key2PropertyGroups='CP_',
@PricingKeyColumnName='[CustomerPlanObjectID]',
@QuantityColumnName='[ExcessUsageMB]'

After the view has been registered by executing the stored procedure, it will then appear in the Unit drop-down list on the Billing Unit page for the Hosted Exchange Service. To include the calculated excess usage in the billing report, go to the Service Schema page for Hosted Exchange and click New Billing Unit, then select Customer Plan from the options at the top of the frame. Select the newly added ‘Per Instance – Excess Usage’ item from the Unit drop-down list. The Quantity will automatically be defaulted to ‘ExcessUsageMB’. From the Sales Price drop-down list, select ‘Price per MB’ then specify a Unit Code such as ‘EXCESS’. Save the Billing Unit which should look like the example below and then save the Service Schema.

To view this change in the billing report you will first need to go to the Report-Configuration-Data Warehouse page in CloudPortal Services Manager and click the “Transfer data from views to data warehouse” link then wait for the process to complete.

For the report to display useful data for this billing unit you will need to specify the Price per MB and Mailbox Storage (MB) properties for each Customer Plan.