xDR import

Service providers can charge customers and include charges on the customer’s bill for services provided via 3rd party (e.g., roaming vendors) or legacy equipment that does not support RADIUS/Diameter real-time charging protocols and can only provide a list of CDRs or charges in a file. In such a case, service providers can import СDRs from external sources into PortaBilling that will process these CDRs and apply charges for customers’ service usage.

You can import CDRs (also called xDRs) for a variety of services, such as voice calls, mobile/broadband data transfer, messaging, IPTV, and virtually any other service, such as “hours of on-site consulting”, where the customer is charged based on the measured amount of consumed service. The following file formats are supported for xDR import:

• CSV,
• fixed-width,
• TAP3,
• Generic XML (adjustments to specific structure of XML document may be required),
• Binary ASN1 files generated by Huawei MSoftX3000.

xDRs that you import can include or not include the pre-calculated charged amounts for the consumed services. If charged amounts are not included, PortaBilling will calculate them according to the rates set in the tariff. If you import xDRs that already contain charged amounts (provided by your vendor), PortaBilling will just add charges to a customer using the provided amounts.

The biggest challenge when importing xDRs is data conversion. For example, if your vendor provides phone numbers in the local format (e.g., a US number is sent as 2065551234), it has to be transformed into E.164 format (12065551234) to ensure it is rated correctly (and not confused with a phone number in Egypt where the country code is 20). To do a simple conversion of any input field of an input xDR record, you can define your custom transformation rules using expressions, written in Perl programming language, stored in a local configuration file.

If you need complex data transformations, they can be implemented via a custom Perl module. For example, you can split a single xDR into several xDRs. Say a mobile network operator sends you an xDR for a call from user A to user B. In PortaBilling, it can be split into two separate xDRs – one for user A (xDR for their outgoing call) and another one for user B (xDR for their incoming call). Also xDR import supports merging several xDRs into a single xDR. For example, if a source file contains separate xDRs for each conference participant, these xDRs can be merged into a single xDR (used to charge the customer, who owns the virtual meeting room, for the conferencing service).

On the PortaBilling UI, you can see imported xDRs for the specific period and xDR processing errors.

xDRs are imported to PortaBilling by the utility called xDR Mediator.

The source files with raw xDRs are uploaded via FTP or SFTP (Secure FTP - preferred) to the specific folder (defined as incoming) on PortaSwitch server either by your vendor; or downloaded automatically by the xDR Mediator from a vendor’s external system.

When a source file with xDRs appears in the incoming folder, the xDR Mediator processes the file (parses the xDR data and applies the custom data transformation rules). Then, the xDR Mediator arranges the xDRs into collections (a collection is a batch of xDRs from one or several files).

Depending on the configuration, a collection can include xDRs from a single source file or all files received during a specific period, e.g., 30 minutes. For example, if network equipment generates a file, every 5 minutes, with information about the calls that happened during this time – it's not very convenient for your administrators to deal with these files individually; so, the xDRs from numerous small files can be accumulated for processing into a single collection for easier management after import to PortaBilling. Also, xDR Mediator searches each collection for xDRs to merge, thus allowing to merge partial xDRs from different source files.

The xDR Mediator sends these xDR collections to PortaBilling Online Charging System (OCS) using RADIUS protocol. For each xDR, PortaBilling identifies the account that consumed the service and calculates the charged amount according to defined rates (or PortaBilling can use the charged amount provided by the vendor). Then, PortaBilling adds the final xDR (resulting xDR) to the customer record to which the account belongs.

Let’s consider the xDR import in more detail so that the engineers who work with PortaBilling and are responsible for xDR import maintenance can follow the whole process step-by-step.

xDR Mediator that performs xDR import consists of three main components:

• Extraction utility – parses input data from the source files, performs the data conversion according to the custom transformation rules, and then arranges xDRs into collections.
• xDR Storage – stores xDRs collections.
• Rating utility – sends xDRs collections to PortaBilling for processing via RADIUS and receives notifications about the import status.

The diagram below illustrates the xDR import process step by step.

Step 1. The source file with xDRs is saved to the incoming folder.

The Queue Manager constantly monitors the incoming folder for new source files. Whenever it detects a new source file, it checks whether it is a duplicate (i.e., if this file has already been processed – this is required to avoid double-charging if the remote side sent the same file more than once) and queues it for processing. When several source files are added to the folder, they are ordered alphabetically and added to the end of the queue (this does not affect the previously queued files).

Step 2. The Extraction utility extracts the xDR data in the source file using one of the available modules (e.g., CSV module) – each parsed xDR is represented as a set of attribute-value pairs.

Step 3. The Extraction utility moves the file from the incoming to the “processed files” folder.

Step 4. The Extraction utility transforms the xDRs data according to the defined custom transformation rules, e.g., translates CLD into E.164 format.

After the data transformation is completed, each xDR record that is being processed must include the minimum set of mandatory attributes to correctly identify the user, type of consumed service, and its details (such as the phone number dialed), etc. See the full list of mandatory attributes here.

Step 5. For each xDR, the Extraction utility checks whether there is another xDR that the current one should be merged with.

Only xDRs with the same value of the “aggregation key” are merged into a single record.

Step 6. The Extraction utility moves the xDRs into collections created in the xDR Storage.

The Extraction utility can combine xDRs in collections:

• Per file – a separate collection is created for each file in the input folder.
• Per day – a single collection is created for files that appear in the input folder during a current day.
• Per export – a separate collection is created for all the files that are placed into the input folder during each import run, e.g., every hour.

  The separate_collections option can be selected on the Configuration server web interface – see the xDR import configuration handbook.

As soon as a collection is complete according to the selected configuration option, it’s automatically marked as ready-to-import.

Step 7. The Rating utility monitors the xDR Storage for ready-to-import collections and starts processing them.

Step 8. The Rating utility sends individual ready-to-import xDRs from a collection to PortaBilling via RADIUS.

For each received xDR, PortaBilling:

• Identifies the account that consumed the service.
• Calculates the charged amount according to the defined rates to insert this amount in the final xDR. In case an imported xDR already contains the charged amount provided by the vendor, this amount will be inserted as is in the final xDR.

Note that in addition to the customer’s xDR, PortaBilling also calculates charges on the vendor’s side (to accurately reflect the cost).

Step 9. PortaBilling saves the final xDR to the database under the specific customer/account/vendor, and their balance is updated. At the same time, the import status of each xDR (imported/rejected) is sent back to the Rating utility.

The Rating utility updates the information about the import status in the xDR Storage accordingly.

Rejected xDRs

If there was an error during rating on the PortaBilling side (say no matching PortaBilling account was found), PortaBilling returns an error to the Rating utility which sends it to xDR storage. The corresponding xDRs in the collection are marked as “rejected“, and another rating attempt for these xDRs can be made later, after adjusting the settings (e.g., adding missing accounts to PortaBilling). This way, it’s possible to re-process only a small portion of erroneous xDRs.

On the PortaBilling web interface, an administrator can view the imported xDR collections. If there are any xDRs processed with an error, the administrator can re-run the rating process for these xDRs after adjusting the settings.

Say the xDR import has just been configured. The first batch of files from a vendor has been processed, and xDRs were imported as described in steps 1-9 above. To check whether the xDRs are imported successfully, the administrator opens the recently imported collection “20221215_0001” on the PortaBilling web interface and sees that there are 138 “rejected” xDRs (processed with the “Account charge error”).

The diagram below illustrates the xDR re-processing process, step by step.

Step 1. The administrator checks the PortaBilling settings and finds out that new corporate accounts weren’t added yet. The administrator adds the missing accounts.

Step 2. The administrator clicks “Re-process” to re-run the rating process for the selected xDRs with the “rejected” status.

Step 3. In the xDR storage, the xDRs are marked with “todo” status, and the xDR collection “20221215_0001” with these xDRs is marked as "updated". This status is interpreted as “ready-to-import” by the Rating utility.

Step 4. The Rating utility then places the xDR collection “20221215_0001”, which is now interpreted as ready-to-import (status “updated”), in the processing queue again.

Step 5. The Rating utility re-sends the ready-to-import xDRs (status “todo”) from the xDR collection “20221215_0001” to PortaBilling (the xDRs in this collection that were imported successfully are ignored).

Later, the administrator opens the collection again and sees that there are still 22 xDRs imported with errors. The administrator re-checks the PortaBilling configuration and finds out that an incorrect number translation rule was used. Once the number translation rule is fixed, the administrator re-runs the import again and checks the results. This time, the administrator sees that all xDRs are imported successfully.

Refer to the xDR Import Configuration handbook for the configuration details.