Fixing a Least Cost Routing implementation error
By Emin Gabrielyan on 2021-03-29
Table of Contents
Introduction
In telephony billing system, the LCR works in two phases. First, to find the rate each of the vendors offers for the number dialed by the user, the system uses the longest match against the rate list of each vendor individually. Then, after obtaining the rate of each vendor, the system routes the call to the vendor offering the lowest rate. More precisely, the system creates an ordered list of routing preferences (sorted according to the rate) and sends this list to the telephony switch for the actual call routing. This list is created on the per call basis.
An important nuance in this procedure is that in the first phase, the longest match (of the number against the list of prefixes) must occur only in the scope of each individual vendor. It is an error to carry out a longest match across all prefixes of all vendors available in the database. This will result in a match with the most specific prefix, which does not necessarily represent the vendor offering you the best rate.
I was reported that an open-source VoIP billing system ASTPP implementation commits this precise error in its LCR implementation. In this document I suggest a bypass to this problem.
Bypass
The bypass consists in aligning the lengths of all prefixes for all vendors.
The drawbacks are:
this will increase the number of the rate records for each vendor,
and it adds an additional procedure to carry out after each vendor rate update.
The advantage is:
the LCR will work as it is intended to work.
The method consists of the following steps:
Upload all your vendor rates into the billing as usual.
Every time, there is an update of vendor rates (one or all vendors), carry out this procedure.
Download all prefixes available in your database (resulting from all your vendor rates).
Remove the duplicates from the downloaded prefixes.
Obtain your so called “master list” of all prefixes.
For each vendor,
Download its rate list.
Take the master list of all prefixes.
Populate the master list of prefixes with the rates of that vendor using the longest match.
Replace the list of rates of the vendor by the master list of prefixes populated with the rates of that vendor.
Upload into the billing.
Repeat this procedure for each vendor.
So, the idea consists in creating a single master list of all prefixes resulting from all rates of all vendors and uploading this master list of prefixes into the billing for each vendor (with the corresponding rates of the vendor). See the section of an example for a better clarity.
Example
Assume you obtained from your vendor Colt the following rates.
Prefix, Rate (CHF/min)
41, 0.022
417, 0.12
And from your vendor Verizon you obtained the following rates.
Prefix, Rate (CHF/min)
41, 0.023
4178, 0.14
4179, 0.11
As a result of your procedure, you will create the following master list of all prefixes.
Prefix
41
417
4178
4179
From this master list and the original version of the Colt rates you will create the following new version of Colt rates to upload and replace the previous version.
Prefix, Rate (CHF/min)
41, 0.022
417, 0.12
4178, 0.12
4179, 0.12
Similarly, you must create the following version of the Verizon rates which must replace the original Verizon rates.
Prefix, Rate (CHF/min)
41, 0.023
417, 0.023
4178, 0.14
4179, 0.11
The line “417, 0.023” in the Verizon rates may look dangerous, but this is how Verizon will charge a call to 4177*, if the rates sent by Verizon were those shown in this example (there are specific prefixes for 4178 and 4179, and anything else that doesn’t match to these two prefixes for Switzerland will fall under the less specific prefix 41).
Nuance
A particular nuance may occur with the following situation.
Colt rates:
Prefix, Rate (CHF/min)
4121, 0.0221
4122, 0.0222
4124, 0.0224
417, 0.12
Verizon rates:
Prefix, Rate (CHF/min)
41, 0.023
4178, 0.14
4179, 0.11
From this example we obtain the following master list:
Prefix
41
4121
4122
4124
417
4178
4179
The problem in this scenario is that there is no match for “41” in the original rate list of Colt. How much Colt will charge for a call to 4131? The real answer to this question is that Colt is not terminating to 4131* because there are no matching prefixes in its original rates.
Such exceptions may never occur in a real life because the vendors usually always send a default prefix for the entire country. However, the exceptions in the rating system may turn to be awfully expensive, therefore, we better also define a procedure for this nuanced case.
We have three choices:
Do not include the unmatching prefix in the new version of the vendor rate.
Include the prefix and associate with it the highest cost of all longer prefixes sharing the same beginning.
Include the prefix and associate with it some high cost, such as 10.00 CHF/min.
Implementation
An implementation can be done in form of a python script that connects to the database downloads the master list of all prefixes and the rate tables of individual vendors, creates the new rate tables, and emails the results to the staff for reuploading the vendor rates.
Another implementation is manual, consisting in generating the CSV files for the master list of prefixes and vendor rates, processing the tables in Excel file, generating new tables for vendors, and reuploading them into the billing.
The next section demonstrates an implementation of the procedure in an Excel file.
In Excel
The following screenshot shows the result of the implementation of the procedure with Excel formulas.
In this example, we create two output rate lists from two input rate lists.
Continuing our examples, the meaning of the tables would be as follows.
Initial rate list of Colt:
prefix1
rate1
Initial rate list of Verizon:
prefix2
rate2
The extended rate list of Colt to be reuploaded:
xprefix1
xrate1
The extended rate list of Verizon to be reuploaded:
xprefix2
xrate2
The “x” in the names of the new prefixes and rates stands from extended.
Tables xprefix1 and xprefix2 are identical and represent the master list of all prefixes. This master list is constructed from the initial vendor tables prefix1 and prefix2 by merging, sorting, and removing the duplicates (these operations are available under data menu of Excel).
All named tables in the above screenshot are framed. So, there are 10 named tables in this excel file.
The explanation is given for the construction of xprefix1 and xrate1 columns. The procedure for the construction of xprefix2 and xrate2 columns is identical.
There are three distinct Excel formulas that are doing all the job.
They populate:
the first column on the right of prefix1,
the next 6 columns,
and the last column, xrate1.
The corresponding formulas are the following.
=IFERROR(MATCH(xprefix1,prefix1,0),"na")
=IF(ISNUMBER(left),left,IFERROR(MATCH(LEFT(@xprefix1,MAX(LEN(@xprefix1)-@truncat1,1)),prefix1,0),"na"))
=@IF(ISNUMBER(left),INDEX(rate1,left),10)
The first formula tries to find an exact match of a prefix in xprefix1 within the original list of prefixes of Colt. If the match is found the formula returns the line number, otherwise the formula returns text “na”.
The second formula mimics the line number if already found, otherwise it tries to find the prefix that is shorter by 1 digit. It continues in the next columns trying to find a prefix shorter by 2, 3 digits, etc. If a match is found, the next columns will refer to the line number of the previously found match. Thus, the procedure stops on the longest match.
The last formula returns the rate.
All the names defined in the workbook are shown in the next capture of the name manager. It includes the 10 tables and the name “left” referring to the left cell, defined for the aesthetics of formulas.
See also the excel file referred in this example 3.xlsx.
Glossary
VoIP, Voice over IP
LCR, Least Cost Routing
ASTPP, A Smart Tele-Phony Platform
END
***
© 4z.com