Real-Time Rating and Cost Based Routing in OpenSIPS 3.1

While there are numerous external, open-source rating and billing engines available in the wild (e.g. CGRateS, ASTPP), having a quick and easy way of putting a price for a call, without relying on external applications, can be a valuable asset to have.

With the addition of the rate_cacher module, OpenSIPS 3.1 gains the ability of caching database provisioned client and vendor ratesheets. With this information readily available in memory, we are able to real-time get the cost per minute for a call and compute the cost per call at the time of call termination.

Furthermore, we can use the module to filter or re-order our PSTN vendors from a cost point of view on a per destination basis, so that the script writer can real-time choose which vendors to pick for a call, based on their desired profit margin for that call.

During this tutorial, we will go through provisioning the rate_cacher module, then we’ll go into using it in the OpenSIPS script for putting a price for a call. Furthermore, we’ll dive into reloading the module, we’ll look at how to do cost based routing and also look at it from a performance point of view.

Rate_Cacher Provisioning

At the core of the rate_cacher module, we are dealing with 3 major entities. Clients, Vendors and Ratesheets. SIP-Wise, the Client is the entity originating the call to our SIP system, the Vendor is the entity to which we can terminate a SIP call and the Ratesheet contains all the price information per each destination.

Clients are defined (by default) in the rc_clients database table. The DB structure is as follows :

mysql> desc rc_clients;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id             | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| client_id      | char(64)         | NO   | UNI | NULL    |                |
| wholesale_rate | int(11) unsigned | NO   |     | 0       |                |
| retail_rate    | int(11) unsigned | NO   |     | 0       |                |
+----------------+------------------+------+-----+---------+----------------+

Each client is defined by its client_id name, and can have two ratesheets assigned to it, one wholesale and one retail. What this allows you to do is to offer the same client two different routing qualities ( and obviously two different pricing / rating models ).

Vendors are defined ( by default ) in the rc_vendors database table. The DB structure is as follows :

mysql> desc rc_vendors;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| vendor_id   | char(64)         | NO   | UNI | NULL    |                |
| vendor_rate | int(11) unsigned | NO   |     | 0       |                |
+-------------+------------------+------+-----+---------+----------------+

Each vendor is defined by it’s vendor_id, and has a single ratesheet assigned to it.

Ratesheets are listed in the rc_ratesheets table. The DB structure is :

mysql> desc rc_ratesheets;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ratesheet_table | char(64)         | NO   | UNI | NULL    |                |
| currency        | char(64)         | NO   |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

The table contains all the defined ratesheets in the system. A ratesheet is defined by a table containing all the price information, along with the currency used by the respective ratesheet. Speaking in SQL terms, rc_clients.wholesale_rate along with rc_clients.retail_rate and rc_vendors.vendor_rate column are all foreign keys pointing to rc_ratesheets.id.

An individual ratesheet is defined in the database as follows :

mysql> desc rc_demo_ratesheet;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| prefix      | char(64)         | NO   | UNI | NULL    |                |
| destination | char(128)        | NO   |     | NULL    |                |
| price       | float            | NO   |     | 0       |                |
| minimum     | int(11) unsigned | NO   |     | 0       |                |
| increment   | int(11) unsigned | NO   |     | 1       |                |
+-------------+------------------+------+-----+---------+----------------+

An entry in the ratesheet specifies the price, minimum and increment specified for the numeric prefix column, having a string name of destination. The price field is the rate per minute, while the minimum field specifies the minimum number of seconds that a call will be rated for, while increment specifies the rating increment for this destination.

With all of the above provisioning details in mind, let’s add one carrier and two vendors, each with it’s individual ratesheets.

mysql> select * from rc_clients \G
*************************** 1. row ***************************
            id: 1
     client_id: my_client
wholesale_rate: 1
   retail_rate: 1

I’ve added a new client, named them ‘my_client’ and attached the same ratesheet with ID 1 for both wholesale and retail rate.

Then I’ve created the ratesheet with ID 1, set it’s currency to USD and assigned its price info to the rc_client_ratesheet table :

mysql> select * from rc_ratesheets where id=1 \G
*************************** 1. row ***************************
             id: 1
ratesheet_table: rc_client_ratesheet
       currency: USD

Table rc_client_ratesheet contains just 2 prefixes, for demo purposes :

mysql> select * from rc_client_ratesheet \G
*************************** 1. row ***************************
         id: 1
     prefix: 40727
destination: ROMANIA MOBILE VODAFONE
      price: 0.05
    minimum: 1
  increment: 1
*************************** 2. row ***************************
         id: 2
     prefix: 44
destination: United Kingdom
      price: 0.02
    minimum: 60
  increment: 60

Thus, we’ve decided to rate my_client with 0.05 USD per minute for calls going to the 40727 prefix, and with 0.02 USD per minute for calls going to the UK.

Furthermore, we’ll create a new vendor, let’s call it my_vendor and assign them with ratesheet ID 2.

mysql> select * from rc_vendors where id=1 \G
*************************** 1. row ***************************
         id: 1
  vendor_id: my_vendor
vendor_rate: 2

Create the vendor’s ratesheet entry in the rc_ratesheets table.

mysql> select * from rc_ratesheets where id=2 \G
*************************** 1. row ***************************
             id: 2
ratesheet_table: rc_vendor_demo_ratesheet
       currency: USD

And then provision the vendor’s price per minute info.

mysql> select * from rc_vendor_demo_ratesheet \G
*************************** 1. row ***************************
         id: 1
     prefix: 4072
destination: ROMANIA MOBILE VODAFONE
      price: 0.03
    minimum: 1
  increment: 1

Using the Rate_Cacher module in the OpenSIPS script

First we need to load the rate_cacher module, along with a mandatory database module ( here I’m using MySQL ). Then you need to tell the module where are your client, vendors and ratesheets tables located. There is a separate DB URL for each of them, so you can properly split your database ( should you want to ). In my example I’m using all of them in the same database :

loadmodule "db_mysql.so"
loadmodule "rate_cacher.so"
modparam("rate_cacher","rates_db_url","mysql://root@localhost/opensips")
modparam("rate_cacher","clients_db_url","mysql://root@localhost/opensips")
modparam("rate_cacher","vendors_db_url","mysql://root@localhost/opensips")

Then, for example at INVITE time, you can fetch the price information on a per client and vendor basis :

$avp(dialled_no) = "4072700423485";
$avp(client_id) = "my_client";
$avp(is_wholesale) = 1;
$avp(vendor_id) = "my_vendor";

if (get_client_price($avp(client_id),$avp(is_wholesale),$avp(dialled_no),$avp(prefix),$avp(dst),$avp(price),$avp(min),$avp(inc))) {
    xlog("RATING: we've matched $avp(prefix) ($avp(dst)) with a price per min of $avp(price) , $avp(min) , $avp(inc) \n");
}
if (get_vendor_price($avp(vendor_id),$avp(dialled_no),$avp(vprefix),$avp(vdst),$avp(vprice),$avp(vmin),$avp(vinc))) {
    xlog("RATING: we've matched vendor $avp(vprefix) ($avp(vdst)) with a price per min of $avp(vprice) , $avp(vmin) , $avp(vinc) \n");
}

Taking into account the previous provisioning that we’ve done, the above OpenSIPS script will print to syslog :

Rating: we've matched 40727 (ROMANIA MOBILE VODAFONE) with a price per min of 0.05000000 , 1 , 1
Rating: we've matched vendor 4072 (ROMANIA MOBILE VODAFONE) with a price per min of 0.03000000 , 1 , 1

Now that we have the information loaded, we can store it in dialog variables, and then at BYE time, we can compute the proper price for the call ( with a little help from the mathops module ) :

if (is_method("BYE")) {
    if ($DLG_lifetime > $dlg_val(client_min)) {
        # we are above minimum, round to nearest increment
        math_ceil("$DLG_lifetime / $dlg_val(client_inc)",$avp(total_inc));
        math_eval("$avp(total_inc) * $dlg_val(client_inc) * $dlg_val(client_price) / 60",$avp(client_price));
    } else {
        # below minimum, rate at minimum seconds
        math_eval("$dlg_val(client_min) * $dlg_val(client_price) / 60",$avp(client_price));
    }
    xlog("RATING: Client price per call is $avp(client_price) \n");
}

Similar to the client rating, we can proceed to compute the vendor price and we can store the values in the CDR, should we want to.

Reloading and Querying Rate_Cacher from the MI

Similar to how we can fetch the prices from the OpenSIPS script, we can use the OpenSIPS-CLI tool to fetch client and vendor prices :

# /usr/local/bin/opensips-cli -x mi rc_getClientPrice my_client 1 4072700423485
{
    "prefix": "40727",
    "destination": "ROMANIA MOBILE VODAFONE",
    "price": 0.05,
    "minimum": 1,
    "increment": 1,
    "currency": "USD"
}

Similar for vendor prices :

# /usr/local/bin/opensips-cli -x mi rc_getVendorPrice my_vendor 4072700423485
{
    "prefix": "4072",
    "destination": "ROMANIA MOBILE VODAFONE",
    "price": 0.03,
    "minimum": 1,
    "increment": 1,
    "currency": "USD"
}

When it comes to reloading, the rate_cacher takes a granular approach, in the sense that you can reload individual client and vendor ratesheets, without having to reload the entire cached data set.

For example, if we want to add a new vendor along with a new ratesheet, we will have to first do the provisioning in the database :

mysql> select * from rc_vendors where id=2;
+----+-----------------+-------------+
| id | vendor_id       | vendor_rate |
+----+-----------------+-------------+
|  2 | my_other_vendor |           3 |
+----+-----------------+-------------+
mysql> select * from rc_ratesheets where id=3;
+----+---------------------------+----------+
| id | ratesheet_table           | currency |
+----+---------------------------+----------+
|  3 | rc_vendor2_demo_ratesheet | USD      |
+----+---------------------------+----------+
mysql> select * from rc_vendor2_demo_ratesheet;
+----+--------+-------------------------+-------+---------+-----------+
| id | prefix | destination             | price | minimum | increment |
+----+--------+-------------------------+-------+---------+-----------+
|  1 | 4072   | ROMANIA MOBILE VODAFONE |  0.06 |       1 |         1 |
+----+--------+-------------------------+-------+---------+-----------+

We can tell the rate_cacher module to add the vendor by running:

# /usr/local/bin/opensips-cli -x mi rc_addVendor my_other_vendor
"OK"

And then we will have to reload its assigned ratesheet:

# /usr/local/bin/opensips-cli -x mi rc_reloadVendorRate my_other_vendor 3
"OK"

And only then we can query the new information for our new vendor.

# /usr/local/bin/opensips-cli -x mi rc_getVendorPrice my_other_vendor 4072700423485
{
    "prefix": "4072",
    "destination": "ROMANIA MOBILE VODAFONE",
    "price": 0.06,
    "minimum": 1,
    "increment": 1,

The module has similar reloading functionality for the client adding & reloading.

Cost based Routing with the Rate_Cacher module

Now that we have one provisioned client and two vendors, we can proceed to taking a look at how the cost based routing works – note that while the client’s price for ROMANIA MOBILE VODAFONE is $0.05 , my_vendor has a price of $0.03 ( and is thus profitable ) while my_other_vendor has a price of $0.06 and would cause a loss of $0.01 per minute.

The rate_cacher module allows the script writer to filter a list of vendors on a per destination basis, excluding the vendors which do not have a certain profitability margin.

For example, keeping in mind the provisioned ratesheets from before,take the following OpenSIPS script :

$avp(dialled_no) = "4072700423485";
$avp(client_id) = "my_client";
$avp(is_wholesale) = 1;

$avp(our_vendors) = "my_other_vendor,my_vendor";
$avp(minimum_margin) = 0;
if (cost_based_filtering($avp(client_id),$avp(is_wholesale),$avp(our_vendors),$avp(dialled_no),$avp(minimum_margin),$avp(out_vendors))) {
    xlog("Cost Routing: We should route to the following vendors : $avp(out_vendors) \n");
}

Since our configured minimum_margin AVP is set to 0, rate_cacher will only output the profitable vendors ( for which we are not losing money ). Thus, the portion of script outputs :

Cost Routing: We should route to the following vendors : my_vendor

since my_other_vendor does not meet the minimum profitability margin.

If we change my_other_vendor’s ratesheet in the database to have a lower price than the client’s :

mysql> select * from rc_vendor2_demo_ratesheet \G
*************************** 1. row ***************************
         id: 1
     prefix: 4072
destination: ROMANIA MOBILE VODAFONE
      price: 0.04
    minimum: 1
  increment: 1

and we reload the vendor’s ratesheet :

# /usr/local/bin/opensips-cli -x mi rc_reloadVendorRate my_other_vendor 3
"OK"

then the above OpenSIPS script would now output

Cost Routing: We should route to the following vendors : my_other_vendor,my_vendor

since both vendors are now profitable.

Still, we may notice that my_other_vendor is still more expensive than my_vendor, while both are still profitable for us. If we want to re-order the vendors based on their profitability, we can use the below OpenSIPS script :

$avp(dialled_no) = "4072700423485";
$avp(client_id) = "my_client";
$avp(is_wholesale) = 1;

$avp(our_vendors) = "my_other_vendor,my_vendor";
$avp(minimum_margin) = 0;
if (cost_based_ordering($avp(client_id),$avp(is_wholesale),$avp(our_vendors),$avp(dialled_no),$avp(minimum_margin),$avp(out_vendors))) {
xlog("Cost Routing: We should route to the following vendors : $avp(out_vendors) \n");
}

The script will output :

Cost Routing: We should route to the following vendors : my_vendor,my_other_vendor

Note that my_vendor is now first in the list since it is more profitable than my_other_vendor.

Rate_Cacher performance overview

In production environments, and especially in heavy trunking environments, dealing with many clients and vendors and with full A-Z ratesheets, the amount of information that needs caching can get very big.

That’s why, first of all, the rate_cacher module takes a granular approach on reloading rating information, since it would not be feasible to reload the whole cached information when there is a small rating change just for one client or vendor.

Also, the rate_cacher module is pretty fast in querying the ratesheets, as well as memory efficient in storing them in memory.

On a testing environment with ~ 1000 clients and vendors, loading a full dataset of ratesheets of ~12 million DB rows, the rate_cacher module uses 4GB of RAM to store all of data.

When it comes to testing the raw performance of the rate_cacher module, when querying via the MI layer we’ve achieved over 50k rating queries per second.

Conclusions

The rate_cacher module opens a whole new world of functionalities for OpenSIPS when it comes to loading ratesheets assigned to your clients or vendors, putting a price for your calls and ordering / filtering your vendors based on your desired profitability margin.

Feel free to share with us any use case we might have missed during the development of this module and, of course, any feedback is greatly appreciated!