Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Tariff Type (Call Class) Classification Technique

Posted on 2004-11-28
Medium Priority
Last Modified: 2007-12-19
I have to write a program to modify a payphone call class recorded in call detail records. It is about 60,000 records a day.
So I think performance is important. I have to parse a log file using TjvCsvDataSet (JEDI) and create CSV file ready for MS SQL bulk insert. The original call class are:-
1 - Local Call
2 - STD (National Call)
4 - Bordering Call (Nearby Countries)
5 - Mobile Call

Payphone System is sometime classify incorrect class like 0,3,6 call class. I have to correct it. As the same time, I have to do a deeper classification for class 2.
  21 - Other call (begin with Non zero digit except 1234 )
  22 - STD, to fixed line (begin with these 02, 032, 032, 034, 035, 036, 037, 053, 054, 056, 074, 075, 076, 077
  222- STD, VoIP to fixed line (begin with 1234+prefix in 22 class)
  225 - STD, VoIP to mobile (begin with 1234 + prefix of 01, 06, 09, 033, 03, 055, 057, 071, 078, 079) all prefixes which are not for fixed line will be assigned to Mobile.

  4- Bordering call start with 007+Country Code+Destination number

I know that to get a best performance I have to reduce a number of checking. May be I have to sort these digit in length and start comparation from one digit .... if found then I look for a call class. As I have to copy substring from Dialed Number in Call Detail Record, Any Idea? For example

Dialed number = '1234015558950'  (original call class = 2, new = 225)

Normally Dialed number length = 9, Length <= 9 should be 21, > 9 should be 222, 225 or 4

If any one know the technique used in payphone or exchange please recommend. I want to know algorithm for best speed not size of program. Only suggestion or Idea is ok.
Question by:soapsiam
  • 3
  • 2

Author Comment

ID: 12693054
In telecommunication term, it is called as digit analysis. When you dial destination telephone number, an exchange should analyze digits and route to suitable route and classify that call.
LVL 12

Expert Comment

ID: 12693977
As it seems, you need only the first 7 digits to analyze the class (this is the worst case - 1234 + 3 digits code). Right? So the first thing is to cut the first 7 digits.

Then you have to start checking for the most frequent activity. For example if you have 70% of local calls, start checking for local codes first. At the end should be the most rare activity. Thus you will process each record better.

If I had to do it, I would prepare the file for bulk insert in TEMPORARY tables and process everything inside the database using stored procedures. If the database is running on Dual Xeon machine ... the speed will be increased (if the stored procs are OK).

Author Comment

ID: 12694200

I think then I should check for length of dialed number first, then checking based on length of possibility
9 digit -> Local Call, STD to fixed line (22) and Mobile call shall be check first.
13 digit -> STD for VoIP to fixed Line or to Mobile should be check first, then Bordering call should be check later.

I will choose your approach for one of candidate in profiler.

You would get some points.


Author Comment

ID: 12694246
One more thing Ivanov_G,

My front end PC is One Xeon with 1GB RAM (Compaq) and Server is 2 Xeon CPU with 2 GB RAM (SQL Server).
After midnigth my program should prepare the text files for bulk insert to SQL server. Now, I have 7 clients and one server.

-> invalid Chip Card cdr file for insert in InvalidChipCardCDRs for manual processing.
-> valid Chip Card cdr file for insert in ValidChipCardCDRs
-> invalid Coin Card cdr file for insert in InvalidCoinCDRs for manual processing.
-> valid Coin Card cdr file for insert in ValidCoinCDRs

All vailid cdr should be re-classify call class as the rule above. (or if possible for invalid cdr like to mobile, VOIP and bordering call).
LVL 12

Accepted Solution

Ivanov_G earned 1500 total points
ID: 12695559
About checking the lenght first - yes, you are right, I think...

The previous company I worked for was developing billing system for telecoms, so I can tell you how it processed the records:
The CDRs are copied to a specified folder and "Mediation" scripts insert them in the database. I speak about 14 billable events every day. The CDRs are inserted in temporary tables. Once they are inside the database, the Rating module takes the data and process it, depending the price plan, time zone, destination number, etc.

So I really think your logic should be inside the database. This way you can have tables with dial codes, etc. Beside that, it is obvious that your DB server is better than the front end PC, so the processing time will be less. T-SQL have rich syntax according to non-procedural programming, so all these checks can be made easily.

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question