Upload via the rate upload wizard

Link copied to clipboard

PortaBilling offers the ability to program data processing rules (called templates) for each type of rate file you receive so that you can eliminate the need for any manual data conversion and save yourself a lot of time. Also, the rate upload process is interactive and provides a comparison between new rates and existing ones, so that you can review the rates being uploaded for validity and accuracy.

Let’s have a look at how a rate import is performed. We will perform the rate upload for the “X-Telecom” termination tariff, proceeding step by step. You can then use this as a model for uploading any other tariffs.

In PortaBilling, the proper name for the initial portion of the phone number (used to determine which location or type of phone network this number belongs to) is a destination (e.g., 44 for the United Kingdom and 442 for London, United Kingdom). In the VoIP business, the same entity is frequently called prefix – so we will use these terms interchangeably.

tip If you experience network connection issues, we recommend that you change the load balancing among the web servers to ensure seamless rate upload. To do this, open the Configuration server > web cluster > HAProxy group > hash_type option > set the consistent method.

Change load balancing for web servers

Start the import

Link copied to clipboard
  1. On the Tariff list panel, click on the tariff name.
  2. On the Edit tariff panel, click Rate upload, and then select a file to upload. Start the importStart the import

Let’s now discuss the available options and their effect.

Add or replace rates

Link copied to clipboard

This radio button controls behavior in respect to rates that were already defined in the tariff at the time of import. The default is to add new rates into the tariff. So when the new rates appear, and there is an existing rate for a certain prefix, the new rate will simply override the old one. If a rate for a certain prefix is in the tariff but is not given in the file you received from the carrier, it will stay unchanged. This mode is frequently used, since most of the time your partners will send you rate updates only, i.e., the file will contain only a small subset of prefixes for which the price has changed.

In some cases, however, you need to ensure following import that the tariff structure matches the provided file exactly so that there are no “old” rates remaining. This is normally done when your carrier changes the structure of the prefixes and sends you a “replacement” rate update. In this case, you will want to use replace mode, which uploads all the new rates and then marks rates for all prefixes not present in the uploaded file as discontinued.

The replace mode operates as follows:

  • It adds all the rates having new destinations from the uploaded file.
  • If a rate for a certain destination is present in the tariff and the uploaded file and its Effective From date is past, the rate is discontinued.
  • If a rate for a certain destination is present in the tariff and the uploaded file and its Effective From date is in the future, the tariff rate is replaced with the rate from the file. The Effective From date is then updated in accordance with the new rate.
  • If a rate for a certain destination is present in the tariff but not in the uploaded file, the rate is discontinued.
To prevent a situation where some rates would be discontinued erroneously because a new rate was not uploaded due to a data error (e.g., a typo in the price column), discontinuation of rates will take place only if there were no errors in uploading the new rates.

In our example, we assume that a normal, “incremental” rate import is being used; thus, we will be adding rates.

Effective from

Link copied to clipboard

PortaBilling allows each rate to be assigned an “effective from” date so that an automated rate change can be easily scheduled for the future. Thus if your carrier sends you a file that indicates the date when a specific rate becomes effective, this can be extracted from the file and added to the rate information in PortaBilling. Sometimes it may happen, though, that you receive the file later than expected when the moment at which the rate was supposed to become effective has already passed. In order to protect data integrity, it is impossible to upload the rate and assign an “effective from” date in the past, since xDRs for calls previously made will have already been rated according to the old rate and stored in the database (see the xDR rerating topic for more information about how this situation can be resolved). Consequently, you have the choice to either skip these rates or upload them as effective immediately. This can be adjusted by using the Rates with ‘effective from’ date from the past should be uploaded as effective immediately check box.

Skip duplicate rates

Link copied to clipboard

Sometimes carriers will include all prefixes in a rate update file, even if for some of them no change in pricing has occurred. If you upload the whole rate file, it will create duplicate rate rows. This has no effect on the way calls are billed, but it will increase the amount of entries in the rate table and, in the long run (if it occurs continually), have a negative effect on performance.

In order to prevent this, simply activate the option to skip duplicate rates. This will slightly increase the time it takes to upload the whole rate file (since PortaBilling will have to check the already existing rates and compare them with the new ones). If rate parameters such as price, preference, and so on turn out to be identical, no extra (excess) rate will be inserted. We use this mode in our example (and certainly recommend using it whenever possible).

Finally, choose the rate file and click Next. The file is uploaded to the server, and we move on to the next step.

Review file parsing

Link copied to clipboard

The purpose of this screen is to verify that the general structure of the file has been determined correctly so that the data can be properly extracted. There are different ways to organize data files: for instance, CSV files, despite their name (comma-separated values) can use semi-colons or other characters as a separator. PortaBilling attempts to make the best guess, but it is important to review the results and, if necessary, change the settings according to your preference.

Review file parsing

Multiple prefixes in the same cell

Link copied to clipboard

Typically one row in the table contains the rate information for a single phone prefix (destination) such as 447 or 447527. Sometimes you may receive a file from the vendor where multiple prefixes are placed in the same table cell as shown in the screenshot below.

Multiple prefixes in the same cell

You can easily process such files – you just need to specify which character (e.g., comma (,), semicolon (;) or something else) will be used to separate the individual prefixes in the Delimiter For Multiple Destinations field. This way, during the upload, the system will treat each occurrence as if there is a separate row in the table for each prefix.

In our case, it looks as if the file was parsed properly, so we can click Next and move on to the next step

Review data fields definition

Link copied to clipboard

This is a very important step since we need to identify where individual data elements (e.g., destination prefix or price) are located in the file, and map them to the attributes (data fields) for the rate. You can imagine what would happen if the the system was to confuse them.

PortaBilling applies some artificial intelligence, and usually guesses quite well, where the specific columns are; but, obviously, you need to check whether the data fields have been assigned to the proper columns, and adjust them if necessary.

Review data fields definition

On the right-hand panel you see the Available Fields select menu, which represents all the attributes of the rate record. You can drag and drop fields from here to the table headers in order to associate a particular data field with a column. A blue label will then remain at the top of the column, signaling that the values in this column (e.g., column A) will be used for the specified rate data field (e.g., Destination). Also, the data field in the list will be highlighted – this allows you to quickly see which data fields are already assigned.

You can also drag a label to a different column (thus associating the data field with a different column) or back to the Available Fields menu (the data field will then not receive any input values from the file).

The Format select menu allows you to adjust the way PortaBilling interprets the values in the table cells, e.g., whether a leading 00 should be stripped off or considered part of the phone prefix, or whether a comma (,) or a dot (.) should be considered as a decimal separator for numbers.

Finally, you have the post-processing rule. Here you can write a mini-program in Perl which will alter the original value, e.g., append or remove characters, increase or decrease numeric values. It can also perform advanced operations, e.g., look up the current exchange rate for the vendor’s currency and convert the value into the currency you use.

What if the file does not contain a value for a certain data field? This is another common situation: the carrier may not have included some important information (e.g., rounding intervals) in the file, or it could be that you simply need to assign the value based on your own decision (e.g., when uploading rates from carrier ABC, you will mark them as the Cheap route category). In this case, you may leave the data field unassigned to any column in the file, and simply type a value in the Default Value field.

Add a value for a certain data fieldWhat if you need to associate a single column with more than one data field? For instance, the carrier only supports a single “rounding interval” rating parameter, and so they provide a single column only (column D, in our example). In PortaBilling, however, you have First Interval and Next Interval. In this case, you will associate one data field with the column, and then click on the other data field and link it to the first one (select the first field name in the Link With menu).

Associate a single column with more than one data fieldWhat if you want to “preserve” some of the already assigned rate attributes? Quite often you will initially upload all the rates from a given carrier with a default preference (e.g., 5) or route category (e.g., Default). After that, you start to fine-tune your routing based on quality reports, testing, customer feedback, and so on. Let’s assume that, in this particular vendor’s tariff, the preference for prefix 44 was changed to 8 (to reflect the fact that this is our preferred vendor for this destination) and the preference for prefix 447 was changed to 3 (here we actually decided to move the vendor to the bottom of the routing list for this particular destination). Now, if we were to simply upload the rate file, it would assign the default value (5) for preference to all the new rates, and our subsequent routing adjustments will be lost.

To prevent this, you may specify that the default value should only be applied if there are no existing rates for this prefix in the tariff.

Preserve some of the already assigned rate attributes

Thus, the parameter value from the existing rate will be copied into the new one, so that the new rate for 44 will retain preference 8, and the rate for 447 – preference 3.

tip TIP: In PortaBilling you configure the rate information using two parameters: the price for the first interval (First Price) and the price for subsequent intervals (Next Price). Most of the wholesale carriers do the rat