Wireless Telecommunications Call Records Data warehouseETL Flows K.Srikanth N.V.E.S.Murthy J.Anitha Andhra University Abstract:-The increased number of service offerings and available functionality result in an ever growing volume of call detail records (CDRs). For many services (e.g., pre-paid), CDRs need to be processed and analyzed in near real-time for several reasons, including charging, on-line subscriber access to their accounts, and analytics for predicting subscriber usage and preventing fraudulent activity. In this paper, we describe the challenges associated with near real-time extract, transform, and load (ETL) of CDR data warehouse flows for supporting both the operational and business intelligence needs of telecommunication services, and we present our approach to addressing these challenges. Keywords: Real-time business intelligence, real-time ETL 1. INTRODUCTION Gathering intelligence via real-time visibility into subscriber usage is a critical element in both business activity monitoring and real-time decision support solutions. Timeliness of the intelligence gained from real- time visibility into subscriber usage is important for revenue gain as Well as protection from revenue loss. Consider, for example, the case where the absence of a rule in the logic associated with real-time charging of voice calls results in free international calls for subscribers who dial a specific prefix. Subscriber usage in WirelessTelecommunications is recorded in call detail records (CDRs). CDRs represent a wealth of information that can be mined in order to discover patterns related to both calling behavior and service feature usage (e.g., SMS, MMS, etc.). Enabling near real-time CDR mining and analytics requires that the infrastructure responsible for extracting CDRs from the near-term storage component and inserting them into longer term storage (for billing applications as well as data warehouses for decision support and business intelligence) is able to operate in near real time. Such extractions are typically performed by customized extract, transform and load (ETL) flows. These ETL flows must address many challenges not typically found in traditional data warehousing projects. In particular: 1. Handle introduction of new service CDRs in a timely manner and with minimum changes in processing flows; 2. Handle changes to existing CDR attributes with minimal impact in processing flows; 3. Support multiple CDR versions for the same service (this occurs when different service functionality is enabled for sub- sets of subscribers); 4. Support multi-tenant solutions where either multiple services co-exist (e.g., CDMA and GSM versions of a pre-paid offering) or different versions of the same service are being offered to different subscriber groups. In this paper, we present our work in the area of real-time processing of CDRs in the context of a hosted pre-paid service for mobile virtual network operators (MVNOs). In particular, we present a flexible and extensible CDR extraction, transformation, and load solution that handles dynamic changes to CDR attributes, introduction of new service CDRs, and versioning of CDR in a simple and efficient manner. The solution can be easily generalized to support services in other domains that exhibit the same characteristics with respect to the data that needs to be processed within strict timing constraints. 2. MVNO BACKGROUND The mobile Wireless Telecommunications market is considered to be a very lucrative one. However, building a mobile network and purchasing spectrum licenses can be very expensive for companies that wish to enter this market. Fortunately, the costs associated with mobile networks and spectrum licenses have “driven” existing Mobile Network Operators (MNOs) to embrace a business model that supports leasing of their networks to third parties that wish to offer (mostly) non-competing mobile services. Such third parties are referred to as Mobile Virtual Network Operators (MVNOs). Examples of existing MVNOs include Kajeet (http://www.kajeet.com) and Boost Mobile (http://www.boostmobile.com). What is important to mention is that MVNOs may require customization to the services they offer to their subscribers quite frequently. Such customization may result in the generation of new service CDRs or changes in the information captured in existing CDRs. While such changes may not create substantial challenges in a single- tenant environment, multi-tenant environments (the most common ones in the MVNO model) must be able to accommodate service CDRs that contain different attributes for different MVNOs. 3. PROBLEM STATEMENT CDRs are generated by real-time call processing components whose top priority is to handle session signaling (e.g. connecting a caller to a callee). Because of this, CDRs are typically buffered in memory for a short period of time and then written to an operating system file in a file system K.Srikanth et al, / (IJCSIT) International Journal of Computer Science and Information Technologies, Vol. 3 (3) , 2012, 4439 - 4442 4439