Business Intelligence for TelecommunicationsDataAddicts -Rohit Khandelwal (09BM8042) -Archit Mehrotra (09BM8012)

Structure Introduction about data warehousing and business intelligence Its importance in the telecommunications sector Key Performance Indicators Data Modeling - The Facts and Dimensional Tables Major Players Future of Data Management in the sector

Introduction Data Ware housing:A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process

Business IntelligenceBusiness intelligence (BI) is a broad category of applications and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions

Importance in Telecommunication Huge amount of data to be handled 5.5 users/second are added in Indian telecommunication industrySource : TRAI

Provide competitive advantage Profit Margin is thin and even small degree of efficiency improvements add up and fund investments Modeling of different scenarios

Importance in Telecommunication Address the requirements of marketing, finance, customer service, sales and engineering departments Simultaneous use of data from different departments requires an efficient data management

Regulatory requirements imposed by TRAI Stringent reporting requirement

Upcoming

technologies

increases

the

emphasis

of

Data

management number portability 3G spectrum

Importance in Telecommunication Effective segmenting, targeting and positioning Understanding Customer Usage patterns by analyzing call detail recording (CDRs) Trend analysis for area, time and product Assists to estimate revenue, volumes and margin of profit Categorizing customers into segments according to their behavior and demography that for CRM To lunch accurate marketing campaign and improved ability to target new customers. Also supports to track the performance of campaign within a period of time that is essential for improving marketing skills Constantly monitors the behavioral changes of customer so that companies can immediately response with adequate measures.

Key performance Indicators Systems and Network Performance Analysis / Capacity Planning Grade of service Service life of equipment Downtime / Time out of service Call completion ratio Average call duration (ACD) Answer-Seizure Ratio (ASR) Idle time on network Dropped callsSource: http://consultantvalueadded.com/2010/04/14/100-kpi%E2%80%99s-for-mobile-telecom-operators/ http://blog.maia-intelligence.com/2009/02/27/kpi-for-telecom-industry/ http://telecomtigers.blogspot.com/2010/03/kpi-key-performance-indicators.html

Key performance Indicators Quality / Usage (Airtime): Analysis of the volume of successful calls Mean Opinion Score Service

Coverage % of land covered with services % of population covered with services Average land unavailable to services Average population unavailable to services Access to customer serviceSource: http://consultantvalueadded.com/2010/04/14/100-kpi%E2%80%99s-for-mobile-telecom-operators/ http://blog.maia-intelligence.com/2009/02/27/kpi-for-telecom-industry/ http://telecomtigers.blogspot.com/2010/03/kpi-key-performance-indicators.html

Key performance Indicators Faults and complains (Trouble tickets analysis) % of open and level of escalation priority required % closed Mean time to resolved Work in progress Customer service level statistics

Customer Analysis ARPU (Average Revenue per User) Customer segmentation Analysis of subscriptions Top N customers Churn (No. of Subscriber who stopped using Services or left particular network) - may

increase due to number portabilitySource: http://consultantvalueadded.com/2010/04/14/100-kpi%E2%80%99s-for-mobile-telecom-operators/ http://blog.maia-intelligence.com/2009/02/27/kpi-for-telecom-industry/ http://telecomtigers.blogspot.com/2010/03/kpi-key-performance-indicators.html

Mapping of KPIs an exampleCDR: What to look for ? Attempted vs completed calls Type of call long distance or local Length of call Originating and terminating number Time of call

Data warehouse matrix

Source: The Complete guide to Dimensional modelling by Ralph Kimball, Margy Ross

Data Modeling Dimensional tables

Facts

and

Source: The Complete guide to Dimensional modelling by Ralph Kimball, Margy Ross

Facts Table Customer Transaction Services DnD Caller Tune etc. GPRS usage Call Waiting, Conferencing, forwarding

Billing Info Recharge Online payments CRM Customer complaints and service

Dimensional Tables

Tables

or

Reference

VAS (type of service, charges, usage) Customer_info table (corporate or retial, prepaid or postpaid, customer first name, customer last name, billing address, address proof, Sim_number Sim_Info (MSISDN no, other attributes) Promotions_info Circle_master, Zonal_master Payment_info

Criteria for success Sybase example Extreme performance Small Load window Support existing tools Use existing hardware Storage space Before sybaseiq spice was spending time & processing cycles to distill the data down to summary tables to make it manageable Now sybaseiq gives spice ability to analyze trends across entire Data warehouse

Major Players Teradata Corporation MAIA Intelligence Binary Semantics Ltd IDC India Ltd. TechAxes Business

Intelligence

software ElegantJ

BI s

Integrated

Business

Intelligence and Reporting Software capabilities Corporate Performance Management, Operational Business Intelligence and Enterprise Data Management System

Ingres corporation is a leading provider of open source database

management software for Telecom service providersSource: http://business.mapsofindia.com/business-intelligence/companies.html

Future of BI in telecom industry Telecom carriers worldwide, including wireline, wireless and cable operators, spent $4.4 billion on BI software, services and system integration in 2010 That figure is expected to rise by 18.2% to $5.2 billion in 2011. (According to the Yankee Group Report) Few companies are in the advanced stages of BI initiatives The recent introduction of BI appliances, such as Ingres ICE Breaker BI Appliance, offers a cost effective solution that can reduce the typical BI implementation schedule from months to weeksSource: BusIness IntellIgence for the telecommunIcatIons Industry ImprovIng the Bottom lIne and controllIng expenses - Ingres

Thank You !!