Calculation of overuse in the billing CloudPortal Services Manager

Calculation of overuse in the billing CloudPortal Services Manager - Report

In CloudPortal Services Manager v11 there have been some major improvements to the billing functionality that offer more flexibility for system users to control what appears in their billing statements.

When a process of collecting usage data is in use for a service such as Hosted Exchange, it will collect usage statistics server hosting the service and store this use in the transactional database CloudPortal services Manager, appointed OLM. It is then possible to use this data to use for billing purposes, which may contain values ​​such as disk usage in MB for each mailbox.

The page Schema Service in v11 was an added feature which was called billing units, which allow a CloudPortal Services Manager user 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 line per customer in the report to represent the instance of the service provisioned to the client, with the stock code and sale price for that line read from the map customer. In addition, there will be a line by the user displayed in the billing relationship with the Stock Code and sale price for these lines read from the selected user plan.

The column quantity in the billing report will be based on the supply state of the body or of the user, with a supply state of "unfunded" resulting in an amount of 0, or an amount of 1 to other status. It may be more appropriate to change the pricing model at the base of the amount of usage data, such as the use of value MB disc that is collected for Hosted Exchange.

To do this, you can go to the Schema page Hosted Exchange service and expand the billing Units section. Then you can change the billing unit, which is based on the user level and the user, and in the Advanced Settings section, you can change the amount to be based on a meter by selecting the radio button, then selecting the appropriate counter the drop down list is activated. For Hosted Exchange This is appointed DiskUsageMB.

Once you click Save in the billing unit, then Save the service diagram of the page, the change has been made although it will not immediately visible in the billing report. To see this change, you must first go to page Warehouse Report-Configuration-Data in CloudPortal Services Manager and click the "Data transfer from views to the Data Warehouse" link wait while the status indicator to turn from orange to green.

Once this is complete, you can run the client or retail dealer report billing detail in the report of the menu. The date settings for the report will default to the date of yesterday continues to view users with an amount of 1 based provisioning status. Indeed, the change in the billing unit made will not affect historical data in the billing report. Because the data transfer process was executed today, you'll need to change the date settings on the report at today's date, so that the current set of data will be recovered. Once this has been done, you should see the amount display values ​​in megabytes rather than just one for each line. You will also need to fix the selling price of a relevant value in the user plane so the total price will be calculated correctly.

A further amendment to the billing report could be to show an amount of overage which is calculated for each customer, based on a comparison between the mailbox disk limit for the customer and a sum of disk usage values ​​for each client users. To achieve this objective concerns the coding of this calculation, which can be implemented as a custom view such as the one below 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 ON ANSI_NULLS
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
OTHER CASE ISNULL (SUM ([sd] [Value]), 0)
WHEN 0 THEN 0
OTHER 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

When this view was created in OLM database, it must be registered with CloudPortal Manager services so that it can be used as a billing unit. To do this is to run a stored procedure named [dbo]. [sp_BillingUnit_Manage] when the view name will be provided as a parameter as well as additional descriptive information to define the purpose of the view and its resultset. The parameters for this stored procedure are presented below:

EXECUTE [dbo] [sp_BillingUnit_Manage]
@BaseView = [dbo] [vw_rpt_HE_CustomerPlan_Instance_ExcessUsage]. '
@ = UnitLabel '. For instance - Excess use '
@ ServiceName =' HE '
@ ServiceType =' pending '
@ = PlanType client map'
@ = Key1ColumnName [ObjectID] '
@ Key1Prefix =' '
@ Key1PropertyGroups =' service. Service {} ',
@ = Key2ColumnName [CustomerPlanObjectID]'
@ Key2Prefix = 'PackageTemplate. Service {} ',
@ Key2PropertyGroups =' _ CP '
@PricingKeyColumnName = [CustomerPlanObjectID]'
@QuantityColumnName = [ExcessUsageMB]

After view was recorded by the execution of the stored procedure, it will appear in the drop-down list on the page unit of billing unit for the Hosted Exchange service. To include excess usage calculated in the billing report, navigate to the Exchange Hosted Service Schema and click New billing unit and select customer Plan options above framework. Select the newly added Per instance - Excess Usage item in the Unit dropdown list. The amount will automatically default to "ExcessUsageMB". In the dropdown list of the selling price, select 'Price per MB' and specify a unit code as "EXCESS". Save the billing unit should look like the example below and save the service schema.

To see the change in the billing report, you must first go to report -Setting-Data Warehouse Page in CloudPortal Services Manager and click the "transferring data from views to the data warehouse" link and wait for the process to complete.

for the report to view data relevant for billing unit you want to specify the price per MB and the storage mailbox (MB) properties for each client plan.

Tweet