Interconnect Billing Systems
Background: ICBS was a telecommunications billing software company based in Ireland. Their key business was to analyze the data from all interconnect calls processed by a telecom company and product accurate billing information. "Interconnect" calls are those between telecoms carriers rather than calls made or received by a retail end user.
Calls are recorded by switches and are called CDRs (Call Data Record). Every call produces a CDR and every switch can handle millions of calls everyday. A switch creates files based on calls carried and outputs them on a daily basis, with different makes of switch producing differently formatted records. The raw files would then be sent to ICBS for analysis.
Solution: The project was broken down into five distinct sections.
File management: Polling software was deployed that continually scanned the FTP drives for new files. The file name would determine the switch type, so a naming convention would rename the file with a switch indicator and a batch number for each file that arrived. This file was then placed in a folder that was based on the switch indicator. A record for this file was held in a batch table with a status indicator for the relevant workflow step completion.
File extraction: Another application scanned the arrival drives for files, once a file was found it created a DTS object that was particular to the file type to execute asynchronously. This DTS package was executed on a drone server which would have a copy of the file; the DTS loaded the file to a table with a minimum of processing carried out at row level. Once the DTS completed, its status was recorded at batch level. A log at this level also contained execution statistics.
Transformation: Each CDR record contains fields that identify the records for certain processing. A transformation engine containing hundreds of routines were executed in sequence against the table. These would tag the CDR with information such as point of origin, international dialling code, etc, which would later be used by the rating engine.
Load: The CDR table now contained mappings, parameters and flags that identified the record for the next level of processing, rating. The completed table was loaded to another drone server and all logs were updated to reflect that the initial file has now completed the ETL process. The rating engine, which falls outside the scope of the ETL part of this project, would then commence analyzing the data.
Challenges: This project's main challenge was the number of records to be processed - certain telecoms carriers could require that millions of rows of data were processed daily. DTS allowed the load to executed at high speed in a completely controlled manner. DTS also allowed for logging and workflow management to run smoothly. The trade-off against other load methods such as a specialised c routine, which might have gained speed, was that DTS could manage the entire process and make it easily auditable and configurable, as well as flexible if future changes were required.