Application of Business Intelligence
Towards Achieving Competitive Advantage through the Application of Business Intelligence
Business Intelligence (BI) plays an important role in providing decision makers with increased ability to take advantage of all available information by making correct and accurate strategic decisions. Making this kind of decisions keeps businesses competitive. Many researches discuss the importance of implementing Business intelligence (BI) solutions.
Analytic reporting and data mining tools are parts of Business Analytics which can serve data integration, analytics applications and data warehouse. . The motivation for advantage in business analytics is extensive.
Telecommunications operators in particular can greatly benefit if they consolidate and gain global visibility across their numerous, detailed and varied data. The most obvious expected benefits are improved operations processes such as customer service, marketing research, and budgeting. Recent research shows that companies which leveraged business analytics have achieved Return on Investment (ROI) in excess of 100%. Thus it is easy to justify investment in these tools.
This investigation is based around a literature survey on Business Intelligence, Business Analysis and Data Warehousing which leads to the identification of strategic issues, challenges and factors to be studied when a Business Intelligence solution is sought by an industry. As a result of the experience and understanding gained a Business solution is designed and developed for Telecom operators that build on wide knowledge in the telecom industry to deliver a system that fits well with Telecom operator's business models, operational processes and decision-making requirements.
Chapter 1: Introduction
“In today's highly competitive and increasingly uncertain world, the quality and timeliness of an organisation's business intelligence (BI) can mean not only the difference between profit and loss but even the difference between survival and bankruptcy.” (Moss & Atre, 2003)
The term BI originated from Gartner Group in 1989 by Howard Dresner and it was in that period (early 90's) that BI emerged within the industrial world. Academic interest came later in the mid-90 and evolved greatly ever since (Ou & Peng 2006); (Golfarelli, Rizzi & Cella 2004). (Howson 2008) describes BI as; “Business Intelligence allows people at all levels of an organisation to access, interact with, and analyse data to manage the business, improve performance, discover opportunities, and operate efficiently”.
Over many decades the corporate world used to gather and store massive amounts of data. They believed that such data had been very important. Indeed, their data were very important but they were unable to use them in solving lots of problems. They were sure that the information they need for taking decisions was hidden in their data but they could not dig it out. So, the data were really valuable but they could rarely benefit from them because they were unable to analyse them. To benefit from the data, organisations need tools to retrieve, summarise and interpret such data to be meaningful and useful for decision making.
To fulfill such needs, Business Intelligence (BI) concepts began to arise. BI tools were developed to deal with raw data extracting the required information and knowledge in the form of tables, reports, graphs, pies, charts, diagrams, etc. When BI is deployed effectively, all that data become a strategic asset.
We find in competitive environments, the mobile telecommunications operators are focusing on subscriber requirements not only formative service contributions but also determining the network and impact the organisational arrangement of the mobile telecommunications operators to focus on specific types of subscribers.
Competition has resulted in two distinct trends in the telecommunications industry:
* Strengthening of position
Carriers trying to get new market share by offering new services, in an effort to compete, perhaps through merging with other success carrier or acquisitions
Offering multiple services like form of packages or bundles of products based on customers' requirements or specific sales areas.
To achieve competitive advantage Telecommunications companies have to use BI solutions like data warehousing, business analytics, performance and strategy and user interface.
The typical data warehousing environment involves extraction and loading of data from multiple transactional systems into a data warehouse (or data mart), which is in turn accessed by BI tools and analytic applications.
A query on data in the data mart can be 100 times more performing than a similar query in a normalized relational schema typically found in transactional systems.
It is also common not to allow end users direct access to the transaction processing systems to perform business analytics to avoid degradation in transactional system performance when analytic processing is conducted.
1.2. Why Business Intelligence?
Business Intelligence (BI) in a typical context refers to the use of Business Analytics to significantly enhance decision making at all levels of a business. Business Analytics includes a group of software applications such as reporting and data mining tools which are supported by data integration, data warehousing and analytic applications.
Rapidly evolving technology, razor-thin profit margins, intense global competition and eroding customer loyalty mean that in order to survive and thrive, telecommunications companies must be smarter and more agile than ever.
The motivation for investment in BI is varied. Telecom operators in particular can greatly benefit if they consolidate and gain global visibility across their numerous, detailed and varied data. The most obvious expected benefits are improved operations processes such as customer service, marketing research, and budgeting. A recent research shows that the companies that leveraged business analytics have achieved ROI in excess of 100%, so the investment in these tools is easy to justify.
To support decision-making processes, such as marketing operation, or customer relationship management, it is necessary for organisations to deploy suitable solutions to support the decision-making requirements of end users. A data warehouse or data mart provides the essential source for a fact-based decision support system.
1.3. BI Applications
There are two types of BI solutions the first one is Technology solutions which distribute information with business framework, but without the strong process or application context of business solutions. The other one is BI business solutions goes beyond deliverance of information to fill a key role in business outcomes.
1.3.1. Technology solutions
Decision Support Systems (DSS):
Support managerial decision-making - usually day-to-day tactical
Executive Information Systems (EIS):
Provide metrics-based performance information - consolidation, forecasting, analysis, etc. - to support decision making at the senior management level
Online Analytical Processing (OLAP):
Tools to support business analysts with capability to perform multidimensional analysis of data (e.g., “what if” analysis of a business problem)
Managed Query & Reporting:
Provides quick and easy access to business data with such capabilities as predefined reporting applications, wizard driven data access, report formatting and templates, web-enabled query tools sets and end user report writers and publishers
examines data to discover hidden facts in databases using techniques such as machine learning, statistical analysis, pattern/relationship recognition to the most atomic level data; mining tools infer predictive and descriptive information
Operational Data Services:
Close the loop of business value by enhancing operational processes and systems, and by providing operational services such as operational reporting that would otherwise require reconciling data from multiple operational databases.
1.3.2. BI Business Solutions
Knowing the customer, maximizing customer value, measuring customer satisfaction and loyalty, etc
Evaluating the market space, customers, products, competitors, targeted marketing, etc
To optimize and streamline the ways that a business uses its human resources, financial, equipment,
Sales Channel Analysis
To devise, implement and evaluate sales and marketing strategies, then use feedback to continuously enhance the sales process
Business metrics and analysis for activities such as quality improvement, defect analysis, capacity planning, asset management, etc
Understanding and predicting trends and patterns those provide business advantage, such as: purchasing trends, e-commerce on-line behaviour, etc.
Supply Chain Analysis
Benchmark, monitor and enhance supply chain activities from materials ordering through product/service delivery
1.4. BI Components Framework
The BI components framework identifies the parts of a BI program and the relationships among them. The framework consists of three layers:
The components needed for BI to fit seamlessly into business organizations, processes, and activities.
Administration & Operation Layer
The components that connect technical components with business components
The technical components needed to capture data, turn data into information, and deliver that information to the business.
The business layer is made up of:
* Business Requirements The reasons to implement BI, and the kinds of results needed including information needs, essential business metrics, etc.
· Business Value The benefits anticipated from or achieved by BI including such things as increased revenue, improved profit margins, risks mitigated or avoided reduced costs,
* Program Management The ongoing activities of managing the BI program for maximum business value including establishing enterprise structures and standards, synchronizing multiple and parallel projects, realigning with changing business needs, etc.
* Development The project activities that create and deploy BI and DW products including methodology, project decomposition, project success measures,
Administration & Operation Layer
The administration & operation layer is composed of:
* BI Architecture Frameworks, standards, and conventions that describe BI environment components and the relationships among them including business, data, technology, organizational, and project architectures.
* Business Applications Business processes and procedures that access and/or receive information and employ that information to achieve business results.
* Data Resource Administration Policies, procedures, and processes for data governance including data owner, steward, and custodial responsibilities.
* BI & DW Operations Execution, monitoring, and maintaining acceptable quality, availability, and performance of the DW and BI functions and services
The implementation layer consists of:
* Data Warehousing Systems, processes, and procedures to integrate data and prepare it to become information.
· Information Services Systems, processes, and procedures that turn data into information and deliver that information to the business.
1.5. Why Have a Data Warehouse?
The data warehouse is a main enabler of BI and one of its cornerstones. The two main components of a data warehouse are its Data Model and its extraction, transformation and loading (ETL) processes. The data warehouse is typically loaded from multiple transactional systems then accessed by BI tools and analytic applications. Since this data consolidation process typically includes data quality assurance, the resulting data warehouse becomes the reliable company-wide.
Another important benefit is that a query on data in the data warehouse can be hundreds of times faster than a similar query in a normalized relational schema typically found in transactional systems.
It is also common not to allow end users direct access to the transaction processing systems to perform business analytics to avoid degradation in transactional system performance when analytic processing is conducted.
Yet another benefit is that data warehouses allow for storage of historical data that is usually purged from the source transaction processing systems at certain intervals while the data warehouse storage is normally sized for multiple years of data.
Finally, multi-dimensional BI tools such as Discoverer will not operate efficiently on top of transactional systems, but need the data imported and structured in a special way inside a data warehouse to produce acceptable performance (fact tables, dimensions, materialized views, OLAP cubes, etc).
Above are some of the reasons Telecom operators have opted to invest in Data Warehouse solution, to gain the power to understand and better serve their subscribers as well as having fact-based marketing and financial decision making ground.
1.6. Aims and Objectives
* To identify business intelligence components, and their interactions in the efforts to provide improved competitive advantages.
* To study the effect of using BI tools by telecommunications carriers on performance (customer care, marketing, increased market share)
* Study available research literature and practitioners' reports on BI in order to Identify the main BI factors, their attributes as well as their interactions
* Identify the currently used BI tools and their expected benefits
* Apply business intelligence tools for customer care management data and marketing data and study their effects on performance measured in terms of increased market share against other companies.
* Develop context dependent guidelines for the selection and use of the most suitable BI tools
1.7. Outcomes and deliverables
A report detailing the following:
* Literature review
* Identification of Business Intelligence components, attributes and their interactions
* A discussion of Business Intelligence tools and their expected benefits
* An explanation of applying Business Intelligence tools through the analysis of case studies
* An evaluation of using Business Intelligence tools
* Context dependent guidelines for the selection and use of the most suitable Business Intelligence tools
1.8. Project Type:
Research and Evaluation Project, Industry based
1.9. Research methodology
Case study of developing a Data Mart for Call Detail Record (CDR) analysis to identify customer's behaviour based on implementing proposed solution on Churn Management Analysis model to help the CRM and Marketing Departments in determining their plans and promotions,
This investigation uses mainly the action research approach. In addition, a literature review is presented. In action research, the researchers interact with a project to apply a data mart as one of business intelligence tools and in-depth information is elicited.
Chapter 2: Literature Review
2.1. What is Data Warehouse
“Data Warehousing is an architectural construct of information systems that provides users with current and historical decision support information that is hard to access or present in traditional operational data stores. The need for data warehousing from Business perspective In order to survive and succeed in today's highly competitive global environment: Decisions need to be made quickly and correctly, the amount of data doubles every 18 months, which affects response time and the sheer ability to comprehend its content and rapid changes To provide the organizations with a sustainable competitive advantage Customer retention, Sales and customer service, Marketing and Risk assessment and fraud detection” (Alex Berson & Stephen J.Smith, 1997)
AS we see here, author builds his definition on the purpose of Data Warehouse, which is decision support information, the Data Warehouse architecture which is easy access to get needed information for users and which business areas will be effected by using Data Warehouse.
“The Data Warehouse is nothing more than the union of all the constituent data marts.”
(Ralph Kimball, et al, 1998)
Here both previous author are defining Data Warehouse according to their contents as a group of data sources assuming that all data store roles - intake, integration, distribution, access, and delivery
“A data warehouse is a subject-oriented, integrated, time-variant, nonvolatile collection of data in support of management decisions” (W.H. Inmon 1999)
Here previous author is defining Data Warehouse according to their characteristics, which are subject-oriented, time variant, nonvolatile collection of data to achieve easy queries through large business databases.
“The concept of data warehousing is really quite simple. Data from older systems is copied into a new computer system dedicated entirely to analyzing that data. Normally, the data warehouse will store a substantial amount of historical data. Users of this system are able to continuously ask or query it to retrieve data for analysis. The intent of the data analysis is to better understand what is happening, or what did happen, within a company or organization. The value of better understanding is better decision making. There is a tangible value to better decision making for every organization across all industries.” (Paul Westerman, 2000)
AS we see here, author builds his definition on the purpose of Data Warehouse, which is decision support system for organisations by analyse data, and the relation between Data Warehouse and Operational Database which is come from. But required data not copied from Operational Database, but it is processed before inserting into Data Warehouse.
“The definition of Data Warehouse is data-oriented and does not include all the processes connected with the Data Warehouse technology. In order to get a process oriented definition, the term Data Warehousing became more popular: Warehousing refers to a set of processes or an architecture that merges related data from many operational systems to provide an integrated view of data that can span multiple business divisions. Connected with the Data Warehouse concept appears in 1993 the term OLAP (Online Analytical Processing) that is “a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to react the real dimensionality of the enterprise as understood by the user.” (J. Moreira, J. Sousa / Investigac¸ ˜ao Operacional, 2006)
According to previous author, the definition is based on the Data Warehouse functionality and technologies processes which are used in Data Warehouse, that more than definition but going deeply into technical architecture.
“A data warehouse is the collection of data extracted from various operational systems, transformed to make the data consistent, and loaded for analysis. With some business users, “data warehouse” has become a dirty word, associated with “expensive,” “monolithic,” and of no business value. Other terms, such as reporting database and data mart, are also used and may sound less monolithic to some business stakeholders. In reality, they both serve similar purposes but might have different scope and technical architectur”e. (Howson, 2008)
Here we see that the author define the Data Warehouse from business point of view which declare that Data Warehouse is costly without business value, but that definition is so simple and consider as criticism of data warehouse.
Finally, we can find that data warehouse brings the data from the underlying heterogeneous databases, so that a user only needs to make queries to the warehouse instead of accessing individual databases. The co-operation of several processing modules to process a complex query is hidden from the user. Effectively, a data warehouse is built to provide decision support functions for an enterprise or an organisation.
2.2. What is Business Intelligence
“Business Intelligence is the conscious, methodical transformation of data from any and all data sources into new forms to provide information that is business driven and results oriented.” (Biere, 2003)
We can find that this definition is declaring the general framework of BI technologies according to Input and Output prospective, but without mention of working processes
“BI is neither a product nor a system. It is an architecture and a collection of integrated operational as well as decision-support applications and databases that provide the business community easy access to business data.” (Moss & Atre, 2003)
We see that this definition is declaring the purpose of BI which is decision-support applications with mentioning the general architecture of BI
Many organisations are faced with unprecedented growth in the sheer amount of internal and external data available to them. In many instances, organisations create information systems to deal with business requirements as these develop, often leading to many disparate systems. As a result, many organisations end up with voluminous data about their business but relatively little business knowledge (Harrington, in Nemati 2005: 66).
We see that this definition is declaring the needs of different organisations to BI to transforming their several Data sources into effective business knowledge which is based on huge business data.
“In particular, BI means leveraging information assets within key business processes to achieve improved business performance. It involves business information and analysis that are: Used within a context of key business processes, Support decisions and actions and Lead to improved business performance” (S. Williams, & N. Williams, 2006)
We see that this definition is declaring the needs of different organisations to BI to analyse their information through business processes to use analysis results in decision making to get the best improvement in performance so improving organisation profit. This is based on the purpose of using BI.
“BI provides a means for extracting information from the clutter that would be useful for reporting and decision-making purposes. Not surprisingly, the information technology (IT) industry coined the phrase ‘business intelligence',” (Chou, Tripuramallu & Chou 2005:344).
We see that this definition is explaining the BI as organising mess data to useful in reporting and decision-making purposes
“BI is a broad category of application programs and technologies for gathering, storing, analysing, and providing access to data.” (Z. Michalewicz, et al 2006)
We can find this definition of the general working processes into BI applications. Without explaining how to execute mention processes and given input or required output.
“Business Intelligence (BI) is an umbrella term that combines architectures, tools, databases, applications and methodologies. It is, like IT and MIS, a content-free expression, so it means different things to different people.” (E. Turban, et al 2008)
So, we can define Business Intelligence as a collection of applications which facilitate organisations for learning knowledge from their existing data.
Chapter 3: Business Intelligence
As illustrated in 1, data is organizational facts periodically collected by organisations in the form of bits, numbers, symbols and objects. Information can be extracted by processing and restructuring data, i.e. Information is organised data. And, knowledge is generated by integrating information to get facts, relationships that have been discovered. So, knowledge is the main component of any decision-making process.
The general goal of most BI systems is to access data from different sources, transform these data into information then into knowledge and finally provide an easy-to-use graphical user interface to display this knowledge. So, a BI system is responsible for preparing information from data then mining data to get knowledge which is presented in a friendly way to enhance the user's ability to make good decisions.
BI can transform an environment that is reactive to data to one that is proactive. A major goal of the solution is to automate and integrate as many steps and functions as possible. Another goal is to provide data for analytics that are as tool-independent as possible. (Biere, 2003)
3.1.1. Business Requirements for BI
Business Context - Drivers, Goals, and Strategies
Forces that cause a need to act
* improved competition
* altering regulations
* altering economy
* Amalgamation or acquisition
* Altering marketplace
* Altering workforce
* Altering technology
Desired outcomes of actions
* Improved market share
* Improved customer share
* improved customer retention
* cost reduction
* improved revenue
* improved profit margins
* reduced cycle time
Planned means to achieve goals:
* Competitive pricing
* Innovative product packaging
* Customer loyalty programs
* Outsourcing and partnerships
* New sales channels
* supply chain optimization
* New delivery channels
3.1.2. Business Value
The Business Case for BI
The minimum business case for BI demands evidence that benefits are achievable, showing that investment will create a positive return, a foundation to assess results and measure their value, and a means to quantify and allocate costs. Both cost and value can be elusive. Some aspects of each are readily measurable. Cost and value assessments need to be performed at the start of a BI program, and continuously throughout the life of that program.
Valuation models and metrics include:
Return on Investment (ROI)
ROI is the most common and readily understood of BI program valuation models. Measuring ROI throughout the life of a BI program provides basic metrics to assess BI effectiveness but little information to take corrective action or make improvements. In its simplest form, ROI can be expressed as comparison of value received (revenues generated and costs avoided) with costs incurred within a designated time period. ROI measures for BI programs include many challenges first in identifying the impact of information, then in determining the value of those impacts. Cost determination may also be elusive, in particular identifying and quantifying indirect costs.
Return on Assets (ROA)
ROA complements ROI for valuation of a BI program. Where ROI measures value relative to expenses, ROI compares value received from BI (revenue generated, costs and risks avoided) with the value of assets essential to BI deployment. Assets to be considered go beyond the obvious technical infrastructure - hardware, software, storage, etc. - to include the data used to provide BI information. BI offers opportunities to increase the value realized from data assets.
Total Cost of Ownership (TCO)
TCO models provide a structure to capture all costs associated with a BI program. Cost categories include hardware, software, staffing, and services. All costs including development, deployment, operation, support, and enhancement are included. TCO is total cost of the program from inception to the present. Measuring TCO for multiple time periods (program-to-date, year-to-date, current fiscal period, etc.) provides complete and substantial cost measures to be used in ROI and ROA calculations.
Total Value of Ownership (TVO)
TVO models provide a structure to capture value of all benefits derived from a BI program. Value categories include revenue received, costs avoided, and cost of risks avoided. All Measuring TVO for multiple time periods (matching those for TCO measures.) provides complete and substantial value measures to be used in ROI and ROA calculations.
Time to Payback
Time to payback is a predictive measure that estimates the point in time when TVO will be equal to TCO for a BI program. Regularly estimating time to payback and monitoring changes in the estimate is useful when managing the BI program.
3.1.3. Business Analytics
Business Metrics and Business Management
Business metrics are specific, defined, quantifiable indicators of performance or behaviour in some aspect of a business. Metrics that are aligned with business goals and defined as standard measures of those goals are most useful for business management. A higher education institution, for example, may set a goal of having all undergraduate students complete a degree in four years. Time-to-degree metrics measuring by academic discipline, student demographics, financial aid availability, etc. - help to measure achievement of the goal and identify actions that will help to improve time-to-degree performance. Periodic measures over time supply information about trends, provide feedback about effectiveness of previous actions, and help to plan future actions.
Three general business management disciplines are common. BPM has two distinct meanings. It has been used for both Business Process Management and Business Performance Management. The two are similar in their use of metrics as part of business management discipline.
They differ in goals, scope, and kinds of metrics. BAM - Business Activity Management (or Monitoring) is yet another metrics-based discipline unique goals, scope, and kinds of metrics. Customer Relationship Management (CRM) is a customer-focused approach to meeting business goals. Supply Chain Management (SCM) focuses on product/service delivery sequences to meet business goals.
Business Process Management
This BPM discipline applies metrics to a single business process to maximize its contributions to overall business goals. The internal workings of the process are invisible, so the metrics are about things external to the “black box” - the product, customers, suppliers, inputs, events, etc.
Business Performance Management
This BPM approach applies similar metrics across multiple business processes to maximize goal oriented performance across the enterprise. This version of BPM also employs metrics for products, customers, etc. but with an enterprise perspective. It recognizes dependencies among processes, and acknowledges that many processes may have overlapping suppliers, events, and inputs.
Business Activity Management
BAM applies metrics within a single business processes to optimize that process to best achieve business goals. Where BPM treats the process as a “black box,” BAM looks inside the box. BAM metrics measure activities and the workforce that performs those activities.
Customer Relationship Management
CRM applies metrics to maximize customer value, enhance customer satisfaction, and increase customer retention. It focuses on measures of customers and business interactions with those customers - customer value, customer loyalty, customer satisfaction, customer behavior, etc.
Supply Chain Management
SCM applies metrics across multiple business processes to optimize the entire sequence of activities that supply a product or service to a customer. The sequence from materials ordering, through manufacturing, to product delivery involves a complex set of activities and dependencies among those activities. Metrics related to demand, materials, inventory, warehousing, resources, and delivery all have a role in SCM.
Performance Dashboards for Information Delivery
Business analytics supply the metrics necessary to support disciplines like BMP, BAM, CRM and SCM. Performance dashboards are a popular way to present analytics in a concise form for executive and management review and action. Key performance indicators (KPI) metrics are the fundamental elements of performance dashboards. KPIs are presented in visual formats that highlight current performance, trends, forecasts, and alerts clearly and concisely. Ideally, the dashboard is integrated with underlying analytic applications to support drill-down to selected detail.
Dashboard metrics are based on a short list of KPIs that the business has determined to be the most important variables in achieving business goals. Some common metrics include:
* Financial - profitability, sales by location, revenue growth trends, and average product cost, margins by product/service line, etc.
* Market - market share, marketing campaign effectiveness, purchasing trends, customer base changes- % of new vs. existing, etc.
* Customer - satisfaction, product availability, and product returns,
* Logistics - order to delivery time, JIT inventory levels, supply chain vendor performance
* Resource Performance - personnel productivity, return on key assets, production capacity, regulation compliance,
Some BI technology vendors offer dashboards that can be purchased and customized to the specific goals and KPIs of a business. Alternatively, the dashboard can be a custom product developed by the IT department using metric data management and visualization technologies.
Where multiple analytic tools and applications have been deployed in a non-integrated form, a dashboard may prove to be a useful portal to a variety of analytic information and metrics. A portal of this type may drive standardization of business metrics and consistency of analytic applications across the organization.
Scorecards for Information Delivery
Scorecards are applications that generate and display key performance indicators and other business analytics through the context of a specified methodology for comparing business performance to business goals. A scorecard is:
* Used to manage business performance within and across processes and organizations,
* Methodology based,
* Integrated into the overall BI environment.
Using the Balanced Scorecard methodology as an example, the organization is viewed from four perspectives: Financial, Customer, Business Processes and Learning & Growth. Objectives, metrics, and targets are defined for each view. Then data is collected and analyzed for each component, providing a comprehensive framework of cohesive and coherent metrics that represent the business vision and strategies. In this example, some of the metrics could be:
* Financial - total sales cost of sales, percentage revenue by product and increased profitability.
* Customer - customer satisfaction, customer service, brand awareness, percentage of repeat shoppers and new shoppers.
* Business Process - percentage of back orders, on time delivery, average order cycle,
* Learning and Growth - training funds, percentage trained and untrained, time between courses, hours of training by employee and number of mentors per employee.
Some vendors offer scorecard products1 but technology does not make a scorecard culture. Business-driven, top-down development that addresses training, standards, strategies, goals, metrics, targets, systems, and data collection is essential to successful scorecard deployment. Sustained effort, patience, and leadership are necessary to adopt the disciplines of scorecard management and to become a scorecard culture.
Chapter 4: BI THEORETICAL FRAMEWORK
Each of these components is described in the following sections.
4.1.4. Data Warehouse
220.127.116.11. Data Warehouse Characteristics
Data Warehouse is trend of getting data after processing to be integrated, subject oriented, non-volatile, time-variant, accessible, meets business information needs and process of turning data into information.
Data Warehouse provides a complete source of information for the business. Provides needed data without accessing multiple sources, using several technology platforms with potentially inconsistent data.
Data and information is organised and presented as business subjects aligned with information needs.
The warehouse contains historical information and current information of the. Structures and intervals are kept consistent across time, allowing time specific analytics such as trend analysis.
The warehouse provides information stability just get information written to the warehouse is not overwritten.
Warehouse is to provide easily accessible information to business people.
Meets Business Information Needs:
Data Warehouse provides an organised data source, against which a variety of standard tools can be applied by business knowledge workers to manipulate
18.104.22.168. Warehousing Data Stores
Central Data Warehouse (HUB)
As previously discussed, Inmon defines a data warehouse “a subject oriented, integrated, non-volatile, time-variant, collection of data organized to support management needs.” (W. H. Inmon, Database Newsletter, July/August 1992) the intent of this definition is that the data warehouse serves as a single-source hub of integrated data upon which all downstream data stores are dependent. The Inmon data warehouse has roles of intake, integration, and distribution.
Kimball's Definition (BUS)
Kimball defines the warehouse as “nothing more than the union of all the constituent data marts.” (Ralph Kimball, et. al, The Data Warehouse Life Cycle Toolkit, Wiley Computer Publishing, 1998) This definition contradicts the concept of the data warehouse as a single-source hub. The Kimball data warehouse assumes all data store roles - intake, integration, distribution, access, and delivery
Differences in practice
Given these two major definitions of the data warehouse - Inmon's (hub-and-spoke architecture) and Kimball's (bus architecture), what are the implications with regard to the five roles of a data store - intake, integration, distribution, access and delivery?
Table 1 Main Differences between Inmon and Kimball according to roles of Data Store
fills the intake role, but may be downstream from staging area
Fills the intake role - downstream from “backroom” transient staging
Primary integrated data store with data at the atomic level
Integration through standards and conformity of data marts
Designed and optimized for distribution to data marts
Distribution is insignificant because data marts are a part of the data warehouse
May provide limited data access to some “power” users
Specifically designed for business access and analysis
Not designed or intended for delivery
Supports delivery of information to the business
22.214.171.124. Data Warehousing Architectures
Hub vs. Bus Architecture
Hub & Spoke Architecture
The hub-and-spoke architecture provides a single integrated and consistent source of data from which data marts are populated. The warehouse structure is defined through enterprise modeling (top down methodology). The ETL processes acquire the data from the sources, transform the data in accordance with established enterprise-wide business rules, and load the hub data store (central data warehouse or PSA). The strength of this architecture is enforced integration of data.
Table 2 Pros & Cons of Hub& Spoke Architecture
* Produces a flexible enterprise architecture
* Retains detail data in relational form
* Eliminates redundant extracts from operational data sources
* Integration is consistent and enforced across data marts
* Requires considerable front end analysis long start-up time
* Warehouse grows large quickly - high startup costs and maintenance
* Design to delivery time is too long
The Bus Architecture relies on the development of conformed data marts populated directly from the operational sources or through a transient staging area. Data consistency from source-to-mart and mart-to-mart are achieved through applying conventions and standards (conformed facts and dimensions) as the data marts are populated. According to Kimball, the warehouse manager establishes, through a very short data architecture design phase, a focused and finite overall data architecture, which defines the scope of integration for the complete warehouse. The manager then oversees construction of each data mart. Over time, as enough data marts are developed “the promise of an integrated enterprise data warehouse” is realized. The strength of this architecture is consistency without the overhead of the central data warehouse.
Table 3 Pros & Cons of BUS Architecture
* Integration done where and when the business needs it
* Less up front modeling required
* Start up costs are less
* Higher risk of data inconsistency
* Standards don't have enterprise view
* May have to rework existing data marts as operational sources change
126.96.36.199. Business Intelligence Processes
Data Access Processes
Data access processes are those activities performed by business people who want to receive data that they will analyze, interpret, or use locally and individually. These processes are usually performed by people who have some skill in working directly with data. They are generally not strategic processes, but tactical or operational in nature. Data access processes are supported by tools with ad hoc query, report generation, and data retrieval capabilities. A single data access process may use several of these features. For example, a process to send a customer survey:
• Perform an ad hoc query to determine the number of customers who meet particular selection criteria.
• Repeat queries while refining selection criteria until the number of customers matches the desired size of the survey population.
• Use a report generator to create and format a report of customers to receive the survey that includes name, address, etc.
• Use data retrieval features to download the report in a digital format.
• Merge the digital report with the survey to format and print personalized surveys to be mailed to each customer.
This example describes a business process. The purpose is to produce a survey - not simply to retrieve data. From determining the survey population to producing the survey document, the process is facilitated by data access capabilities.
Information Delivery Processes
Information delivery processes differ from data access processes in two very significant ways: (1) They provide information - not just data; and (2) They are initiated by automated systems - not by individuals. Further, information delivery processes are likely to participate in strategic and tactical activities, where data access processes work in the range of tactical to operational. Information delivery technologies include:
• OLAP, which delivers metric information for interactive analytical,
• report publish & subscribe capabilities,
• dashboards and scorecards that present performance indicators, business metrics, alerts, trends, and forecasts in visual formats,
• Analytic applications that package many information delivery capabilities for specific business purposes.
Information delivery processes enable business activities such as fraud detection, supply chain optimization, performance management, etc.
Data mining is a process of knowledge discovery - “a means for finding new intelligence from collections of data. … The process of discovering patterns that lead to actionable knowledge from large data sets through one or more traditional data mining techniques, such as market basket analysis, or clustering.” (David Loshin, 2003)
Data mining is a process performed by business people, although it may be enabled by technology and assisted by IT staff. Fundamental to discovery of new knowledge is the ability to recognize discoveries in context of the business situations where they provide value and can drive effective business actions. While data mining may be goal-oriented or less specific the knowledge being sought is always that which is not yet known to the business person.
Technology is an essential part of data mining; without it, people simply do not have the capacity to examine large collections of data and uncover implicit patterns. Data mining software applies logic and algorithms to inductively identify patterns and relationships inherent in data, and sometimes to infer rules from those patterns. Data mining enables discovery and facilitates awareness. People then interpret the patterns and inferences, and apply judgment to achieve insight and understanding.
BI technology can provide analytics (trends, alerts, forecasts, and other business metrics) but technology can't act upon those metrics. Analytics are applied in context of business processes (sales, marketing, delivery, product development, etc.) and functions (managing customer relations, managing the supply chain, etc.). Like data mining, application of analytics is a process performed by business people. Awareness of trends and predictions, determination of responses, and decisions about the form and implementation of those responses are all part of applied analytics. Ultimately, the value of analytics is in the actions that result from them, not in the metrics themselves. Only by acting upon the information provided does BI provide value, increase effectiveness of business tactics, and enhance the strategic position of an enterprise.
188.8.131.52. Data Warehouse Environment
Data warehouses comprise a multifaceted environment that spans the information systems spectrum from operational transaction systems to systems designed for executive and front-line decision makers. There are four main elements of a data warehouse environment:
1. Source systems
3. Data warehouse repository
4. Reporting tools and portals
1. Source Systems
Source systems provide the raw material for the data warehouse and business intelligence systems. The design and implementation of these applications is outside the scope of the data warehouse, and source systems are typically treated as black boxes. Some of the considerations we have with regard to source systems are listed below.
· The amount of time available to extract data from the system:
Source systems such as ERP and CRM systems have limited windows of opportunity to perform maintenance or execute long-running data extractions. Those constraints must be balanced against the need to regularly refresh the data warehouse.
· The ability to execute incremental extractions:
Some legacy systems may not support extractions based on timestamps or other indicators of new or modified data. In these cases, the next phase (ETL) must distinguish new and modified data from existing data already in the data warehouse.
· Multiple sources of information:
Multiple applications may maintain overlapping information, such as customer records. Prior to building the data warehouse, designers need to determine whether a particular record will be the record of source or whether partial data from multiple sources must be combined to form a record of source.
Operational systems have often evolved independently of each other. The adoption of enterprise software such as ERP and CRM systems provides the basis for consistent data sources in many organisations. However, legacy systems with inconsistent data representations are still common and need to be reconciled within the data warehouse environment. This is one of the responsibilities of ETL systems.
ETL stands for Extraction, Transformation and Load Systems. The development and management of ETL processes often require a majority of the time and resources in a data warehouse project. The extraction process focuses on getting data out of the source systems and into a staging area for further processing. The transformation process uses raw data from multiple source systems, scrubbing and reformatting the data to create a consistent data set. Some of the most common operations are applying consistent coding schemes, removing duplicate records, reformatting text data, sorting, calculating derived date attributes, looking up foreign key references, joining data streams, and aggregating data. After applying a series of transformations, the cleansed data is loaded into the data warehouse in the final format familiar to business intelligence users.
3. Data Warehouse Repository
The data warehouse repository is the long-term, historical, integrated database that supports business intelligence operations. Most are built on relational database management systems and advanced users combine them with OLAP systems as well.
4. Reporting Tools and Portals
Reporting tools provides us with defined reports that are generated automatically and distributed to users according to advanced OLAP tools.
o Ad Hoc Query Tools
These systems allow users to run parameterized reports on an as-needed basis or to create their own custom reports. Ad hoc query tools are suitable when end users have a solid understanding of the underlying business process modeled in the data warehouse and seek a detailed review of information.
Dashboards are reporting tools that display key performance indicators. These are especially useful for executives and others who need a high-level view of an organisation or operation.
o Visualisation Tools
Visualisation tools combine the best of both ad hoc query tools and dashboards. Like dashboards, visualisation tools can simultaneously provide a high-level view of several measures. Users can drill into the detail of underlying information. Visualisation tools are suitable for both analytic and no analytic users.
A portal can provide the flexible access to data warehouses needed by executives and line of-business managers throughout an organisation. Portals are ideal delivery vehicles for business intelligence reporting. Portals are easily customized to provide quick access to frequently used reports.
4.1.5. Business Analytics
Bi technology can provide analytics capabilities which analyzing stored data.
184.108.40.206. Online Analytical Processing (OLAP)
It is a capability that focuses on analysing and exploring data.
While reporting tools focus on accessing data for monitoring purposes. OLAP change the focus from “what” is happening, to exploring “why” something is happening. To declare that the “why,” users didn't know in accurate way what information they are searching for and instead will search and drill within a data set to display specific details.
OLAP gives us interactive analysis by different dimensions and different levels of detail.
220.127.116.11. Data Mining
Data mining is a process performed by business people. Technology is an essential part of data mining; without it, people simply do not have the capacity to examine large collections of data nor uncover implicit patterns.
4.1.6. Performance and Strategy
Cherry Tree & Co., 2000 mention that Organisations are increasingly utilising analytical tools in conjunction with CRM applications to improve the effectiveness of marketing campaigns. BI tools offer a strategic, tactical and functional framework to address business analysis needed across the entire organisation. 3 summarises a few of the characteristics that influence the need for advanced analytical capabilities. While this is an oversimplification of the numerous benefits and analysis, it does provide an illustration of the value recognized throughout the organisation. One specific example pointing to the value of increased analytical capabilities is the use of tools to evaluate marketing campaigns.
4.1.7. User Interface
To benefit from the above mentioned BI components; users need an easy-to-use interface. The user interface is used to present information in a way that is easy to read. It presents graphs, charts, tables, etc. that show the organisation's performance. Visual technologies are used to make the user interface more attractive and understandable to users.
Chapter 5: Business Intelligence Solution Overview
With the rapid growth and continued emergence of new telecom services, having an analytical platform to give management full visibility of their business and support decision making is considered a big challenge.
The BI solution:
§ Is a suitable solution that copes with current and future business and technology requirements
§ Quickly realizes ROI
§ Achieves timeliness & quality of data
§ Is a one source for all information
§ Attains proper authentication of data
§ Offers user-friendly reporting and analytical tools
5.1. General Features
In this section we'll explain each feature in proposed solution from business point of view
5.1.1. Data Model
The BI product includes a “subscriber-centric” data model which is adapted to the telecom industry where emphasis is to understand the subscriber past behavior to detect patterns and to guide marketing campaigns, new services, credit policy, and customer service, ultimately maximizing revenue and customer loyalty and minimizing churn.
5.1.2. User Interfaces
BI solution uses Oracle Discoverer version 10g for its Presentation and Analytics layer. Discoverer is now recommended by Oracle for both standard reports and analytical reports with a choice of 3 user-friendly clients: desktop, web, and web plus. Each tool gives users different level of features and control to match the business role. Charts and 1-click export to Excel are standard features.
BI solution uses Oracle Portal to provide an advanced web interface for all reports. The BI solution Portal includes a KPI dashboard that clearly shows where company key metrics currently stand in relation to the company objectives.
With easy-to-manage ETL processes enabling simplified operation and quality control, the BI solution offers near-real-time access to be validated and quality controlled business data for trend analysis. BI solution also offers real-time reporting on transactional data when such immediate access is needed.
Analytical reports and portal-based dashboards with KPIs and score cards such as this will keep the decision-makers informed and give them the tools to drill-down, analyze, and make the right informed decisions.
5.1.3. ETL Performance and Data Quality
ETL performance is a key factor in evaluating any BI solution. There is a limited time window every day for ETL and the system should perform within that window now and under future heavier loads. BI solution achieves this goal by using at its core high-performance ETL code generated by Oracle Warehouse Builder which leverages the full features of the Oracle database to produce extremely fast ETL. BI solution includes ETL, which is tested in live Telecom environments.
This ETL performance however should not come at the price of compromised data quality. BI solution uses high-performance ETL code generated by Oracle Warehouse Builder which includes data cleaning and transformation as part of the ETL and adds custom ETL monitoring tools that allow operators to efficiently validate the data for completeness before making it available to end-users reports.
A most important feature of BI solution is that it creates enough metadata as part of its ETL processes to build a solid audit trail that company revenue assurance department can use to validate and prove the accuracy and completeness of the data. With all data clearly published in one place and certified by revenue assurance, the BI has what it takes to be accepted as the “One Source” of information for the company.
5.2. Features for Business Users
5.2.1. Features for Sales, Marketing and Finance Users
This section lists examples of reports (Analytics Layer) that can be developed on top of the BI solution Telecom Data Warehouse. The BI solution Data Model shall provide adequate data to develop these listed reports, from which the customer can pick-and-choose the ones it wants or propose new ones that best fit its business needs and particular requirements. The BI solution can provide any report as long as an adequate data source is available.
BI solution implementation methodology follows a phased approach that quickly puts usable components in the hands of users. During the detailed analysis phase which follows the customer's acceptance of the solution proposal, BI team shall work with the customer's IT and business users to produce a drawing of each report. BI team shall subsequently identify the data source(s) for each report, and based on this analysis plus the customer's business priorities reach a consensus on which reports will be delivered in which phase.
18.104.22.168. Call Analysis
1. Distribution of Calls
* Traffic trend (by call type, by hour, by date, by day type, by weeks, by months).
* Operator incoming/outgoing minutes.
* Usage patterns - time of day (peak, off peak).
* Hourly usage patterns.
* Total minutes used in any time period.
* Average call duration (calls < 1min, 1-2 min and so on).
* International roaming breakup.
* Calls by Tariff Zone.
* Calls by Base Stations.
2. Distribution of Services
3. Interconnect Analysis (traffic by interconnect partner/trunk/destination).
22.214.171.124. Prepaid Vouchers - Scratch Cards
1. Scratch Cards
* Recharging denomination trends, with possible linking of trend to subscriber demographic and usage profile (such as heavy SMS users or heavy International callers).
* Recharging time trends (number of days between two card scratched) by subscriber profile (if profile is known).
* Number of cards sold by different distribution channels (assuming ERP system tracks scratch card distribution by serial number).
1. History of Subscriber Base
* Split based on categories and types (normal, VIP, corporate, etc).
* Daily Activations/re-activations/disconnections.
* Aging of subscribers by category.
2. Geography of Subscriber Base
* Connection locations.
* Location splits (number of customers per location).
3. Service usage by subscriber category
4. Behavior of Churn Subscribers
* Churn based on ARPU Segments.
* Churn based on Demographics.
* Churn based on credit and payment history.
* Churn based on history of technical problems and complaints.
126.96.36.199. Revenue Analysis
1. Charges, invoices, sales (business volume).
2. Settlements with Partners
* Volume of charges, account balance for dealers, roaming partners, content providers etc.
3. Profitability of Tariffs
* Average Revenue Per User (ARPU) analysis with an option to view from and to a particular date.
* Number of subscribers in different balance segments.
4. Debt analysis
188.8.131.52. Distribution Channels
POS and dealers analysis
* Daily Sales.
5.2.2. Features for Customer Service and Contact Center
BI solution handles all metrics for a multi-channel contact center that includes phone, IVR, fax, email, and web contacts. Here are few of the many metrics BI solution can produce:
184.108.40.206. Phone Metrics
* % Service Level
* ASA (Average Answer Delay)
* Average Talk Time
* Average Hold Time
* Total Work Time
* Average Work Time
* Average Not Ready Time
* AHT (Average Handling Time)
* And many more …
220.127.116.11. IVR Metrics
* % of Calls transferred to the contact center
* % of Calls handled by IVR
* % of Calls abandoned by IVR
* % of terminated Calls - Wrong password
* % of terminated Calls - Wrong Credit Card
* % of terminated Calls - Wrong Option
* % of Calls per language
18.104.22.168. Complaint Metrics
* Number of Complaint per subscriber
* Average Time to assign
* Average Time to pending
* Average Time to solve
* Average Time to resolve
* Average Time to reassign
Metrics such as number per subscriber, average handling time, % of channel exceeding the service level, etc., are also handled for Fax, E-mail, and Web channels.
5.3. Specialized BI Applications / Modules
This section lists some specialized modules that are planed as part of the BI solution product to further leverage the BI solution data warehouse for complex analysis that the standard Discoverer analysis tool may not fully cover, or in case complex business logic is best represented programmatically while keeping the user interface simple.
5.3.1. Churn Analysis and Prediction
Churn is best predicted when based on all relevant and measurable factors such as a particular combination of tariff plan, usage pattern, demographics, credit rating, and history of call center complaints. The more of these factors are captured in the data warehouse, the more sophisticated the analysis and the more accurate the prediction.
The BI solution can capture all above mentioned churn factors and use the Discoverer capabilities to find the common characteristics of churning subscribers. So the BI solution provides the infrastructure that enables churn analysis, and provides a basic user interface for this purpose.
BI solution roadmap includes plans for a specialized module capable of simplifying for the business user the complex task of churn analysis and prediction.
5.3.2. Marketing Campaign Management and Scoring
The BI product can include a Campaign Management and Analysis module that will leverage data in the BI solution warehouse to perform what-if analysis before the campaign launch followed by measurement of campaign actual impact on sales, usage and revenues.
5.3.3. Revenue Assurance
Revenue Assurance requirements are built into the BI solution design from the ground up. Any data in the BI solution data warehouse is verifiable through a clear lineage and ETL audit trail. The customer's revenue assurance department can and should leverage the BI solution data structure features to certify the data in the BI solution warehouse so business users will maintain a high level of confidence in this data and adopt it as the “one version of the truth”.
5.4. System Architecture
As a result of this investigation we present a Business Intelligence Solution which has been designed and developed for Telecommunications operators that build on wide knowledge in the telecommunication industry to deliver a solution that fits well with Telecommunications operator's business models, operational processes and decision-making requirements. The architecture, technologies and functions are shown in 4. The developed BI solution uses the latest technologies, concepts, and best practices in the area of Data Warehousing and Business Analytics to deliver a high performance solution while keeping operations and maintenance cost at low levels.
This BI solution extracts data from mobile operator switches; this data contains detailed information about the services provided to operator subscribers, including data of RCP, MMS and GPRS. The dynamic reports, generated by the BI solution, depend mainly on this data which is being extracted from the switches during the mediation phase, in a binary Call-Detail Record (CDR) files format. The data within these files is the primary data which represents the input data for the BI solution. This primary data requires special processing or transformation in order to generate the dynamic reports, which are considered the final output of the BI solution. This transformation process consists of three main phases, which all lead to the system final output: the dynamic reports. So, as shown in 1, the system comprises three main subsystems:
1. Mediation Subsystem: transforms the binary data of the CDR files into ASCII data. Processing this data, then the Mediation Subsystem loads this data into ASCII files.
2. Data Warehouse Subsystem: operates on the ASCII data, loaded from the Mediation Phase. Processing this data, the BI solution is then filled with valuable information about the services provided to subscribers.
3. Business Reporting Subsystem: works on the information within the Data Warehouse to generate the required dynamic reports.
5.5. Data Warehouse Subsystem
The Data Warehouse Subsystem is responsible of handling the ASCII files loaded from the Mediation Subsystem, processing these files through two phases: The Files to Staging phase and the Staging to Production phase. Finished both phases, the BI solution will be filled with valuable information about the services provided to subscribers enabling the Reporting Subsystem generate the dynamic reports.
5.5.1. Staging Database
The Staging Database act as intermediary database tables that are being filled with files loaded from the mediation phase. There is a staging table for each CDR file type:
5.5.2. Production Data Warehouse
The Production Data Warehouse represents the data mart fact tables, from which we can generate the dynamic reports of the BI solution. There are two fact tables:
The FACT_CALL: is dedicated to data attributes related to RCP, GPRS and MMS
The FACT_DCALL: is dedicated to data attributes related to MMS and GPRS only.
5.5.3. Files to Staging Mapping
The Files to Staging Mapping (F2S) is stored procedures that operate on files loaded from the medication phase. The F2S Mapping process fills the staging tables with the needed data.
5.5.4. Staging to Production Mapping
The Staging to Production Mapping (S2P) is stored procedures that operate on the staging tables, extracting data and forming the Data Warehouse fact tables.
5.6. Files Manager
The Files Manager is an application designed to manage and control the workflow of both the Files to Staging mapping (F2S) and Staging to Production (S2P) mapping processes, for all the CDR files' types loaded from mediation: RCP, MMS and GPRS.
5.6.1. F2S (Staging)
5.6.2. S2P (Production)
The Staging to Production (S2P) Phase runs the S2P Mapping stored procedure. This stored procedure works on the staging tables to load data into the fact tables. The S2P runs for one day (1) by default. If you want to run the S2P mapping for more than 1 day, from the Files Manager, specify the number of days as required.
5.6.3. Gap and Overlap
Two main concepts to explain within the Files Manager program are the Gap and Overlap concepts. Gap indicates that one file or more has been missing while running the Files to Staging or Staging to Production services. An Overlap refers to the occurrence of duplication or repetition of data in two or more files while running the Files to Staging or Staging to Production services.
5.7. Reports Examples
5.7.1. Call and Charge Summary
This report is declaring calls duration by minutes and amounts for each postpaid and prepaid subscribers according to time dimensional, also you can drill-down to display it by year, months, weeks and days
5.7.2. Call Minutes Summary with Drill-Down on Calendar Dimension
This report is declaring calls duration by minutes for all subscribers according to time dimensional, also you can drill-down to display it by year, months, weeks and days
5.7.3. Drill from Any Summary into the Underlying Details
This report is declaring subscriber number with called number, call duration by minutes and seconds also call charge as drill down from call summary
5.7.4. Voucher Analysis
This report display the usage of vouchers (Recharge Cards) by several fees according to time dimension as sales amount for each specific period
5.7.5. Top N Analysis
This report display the top subscriber profitability per each day also ranked those subscribers accordingly,
Chapter 6: CASE STUDY
Now day's Mobile telecommunications operators are facing highly Competition. Mobile operator's profits and ARPU (Average Revenue Per User) are facing remarkable challenges. The Customer's requirements become more diversified, quality of service become more cogent and stringent. To improve mobile operator's competitiveness and customer value, BI technologies can be useful by storing all data into data warehouse and applied business analytical approaches. In this case study we will implement proposed BI solution to get complete view for Call Detailed Record (CDR) data because Call detail records describe customer usage behaviour because (CDRs) have more information to describe customer behavior than billing system data. One of the challenges which are faced by Mobile telecommunications operators is Churn management, Churn prediction and management is serious in hard-hitting mobile telecommunications markets. To be competitive in this tough market, Mobile telecommunications operators must have excellent prediction model to predict possible churners and then Mobile Operator can take appropriate proactive actions to preserve valuable subscriber.
6.2. Call Detailed Record (CDRs)
Every time a call is placed on a telecommunications network, descriptive information about the call is saved as a call detail record. The number of call detail records that are generated and stored is huge. Given that several months of call detail data is typically kept online, this means that tens of billions of call detail records will need to be stored at any time.
Call detail records include sufficient information to describe the important characteristics of each call. At a minimum, each call detail record will include the originating and terminating subscriber numbers, the date and time of each call and its duration. Call detail records are generated in real-time and therefore will be available almost immediately for Business Intelligence. This can be contrasted with billing data, which is typically made available only once per month.
6.3. Churn management
Berson et al. (2000) mentioned that ‘customer churn' is a term used in the Mobile telecommunications operators to denote the subscriber movement from one Operator to another, and ‘churn management' is a expression that describes an operator's process to retain significant subscribers. Also, Kentrias (2001) mentioned that the expression churn management in the telecommunications services industry is used to describe the procedure of securing the most significant subscribers for a company.
Mobile telecommunications operators can weigh up its subscribers by their payments and focus preservation management only on those valuable subscribers, or score the entire customer base with predilection to churn and prioritise the retention strategy based on profitability and churn predilection.
Subscribers become “churners” when they terminate their subscription and move their business to another Mobile telecommunications operator. That is, churning is the process of subscriber turnover. This is a major concern for Mobile telecommunications operators with many subscribers who can easily switch to other competitors.
The cost of churn in the telecommunication industry is large. In addition, it costs a great deal more to win new subscribers than it does to preserve current ones. And, frequently, a new subscriber will churn away before the operator can fully recover its gaining costs. It is clear that spending money holding on to existing subscribers is more efficient than acquiring new subscribers.
As a result, churn management has emerged as a vital competitive weapon. With effective churn management, a Mobile Operator is able to find out what kinds of subscribers are most likely to churn, and which ones are most likely to remain loyal.
The case study concerns implementing a Churn Analysis based upon data BI solution to analyse the subscriber database of a Mobile telecommunications operator and predict subscriber turnaround.
Business Intelligence can be used in churn analysis to achieve two main tasks:
* Predict whether a particular subscriber will churn and when it will happen;
* Understand why particular subscribers churn.
These two tasks are referred to as “prediction” and “understanding”. They represent the two most important aspects of Business Intelligence in use today.
By predicting which subscribers are likely to churn, the Mobile Telecommunications Operator can decrease the rate of churn by offering the subscriber new incentives to stay. By understanding why subscribers churn the Mobile Telecommunications Operator can also work on improving their service so as to satisfy these subscribers prior to time. Additionally, Business Intelligence tools can help in choosing the best strategy to use with churning subscribers according to efforts and cost.
Before starting the processes of prediction or understanding we have to collect or gather the data. Which what was done in the data warehouse, which is a large storage area of clean, non volatile and operational data. We can determine the information that is stored in a data warehouse for using in churn analysis includes:
* Subscribers demographics information, i.e., age, gender, marital status, location, etc.
* Call information: call duration at different times of whole day, number of different types of calls such as long distance and local calls.
* Subscriber billing information, contain all subscriber payments for used services.
* Other service information, that is, what special plans the subscriber is registered on, e.g. special rates or free SMS.
* Voice and data services registered by the subscriber, e.g., broad-band services, virtual private networks (VPN), etc.
* Subscriber complaint information: how many times subscriber is calling Customer service for uncertain billing, dropped calls, slow service provisioning, bad special services, and so on.
* Credit history.
6.4. BI Solution Business Discovery
A mobile operator, located in Yemen, has about 600,000 mobile Subscribers. The operator wanted to analyse their all mobile Subscribers because of competition last year. Therefore, a project team was formed to take charge of this project. I held interview with different marketing and CRM employees to understand their perception towards the targeted system (Refer to Appendix A)
6.5. BI Solution Imlementation
The first thing need to do is to define an index set of elements which describes mobile Subscribers utilization behavior.
The data of call detail records illustrate subscriber usage behaviour. They record the data of every call detail records for each mobile subscriber. The data of some call detail records are shown in table 4.
Table 4: Mobile Call Detail Records (CDR).
Getting the data of call detail records from BI solution data warehouse, according to demographics of subscribers, the report will be sown as Table 5
Table 5: Report describe mobile Subscribers utilization behavior
Local calling fee
On Net calling
On Net calling
On Net calling
Off Net calling
Off Net calling
Off Net calling
of calls to 111
of calls to 111
Dropped Calls Number
number of calls
Total duration of calls
Here, 111 is the call center number. Call diameter is defined as the number of different mobile customers called by or calling to one mobile customer in one month. This indicates the activeness of a mobile subscriber.
Define the ratio of calling number and called number, the ratio of local calling duration and long distance calling duration, and so on.
Referring to Table 6, then all mobile subscribers are distributing into 3 distinguishable groups using the Business Intelligence software tools.
The main characteristics of some groups are shown in Table 6.
Table 6: The characteristics of selected groups
Minutes Of Usage per user on the average
Local call percentage
On Net call percentage
Off Net call percentage
GPRS traffic volume
Number of short message
Dropped Calls Number
The number of calls to 111
Here, MOU is Minutes Of Usage per user on the average.
Local call percentage = (Local call duration + Local called duration)/ MOU.
On Net call percentage = On Net calling duration / MOU.
Off Net call percentage = Off Net calling duration / MOU
Dropped Call percentage = Dropped Call number / Total number of calls
From the data of table 3, the following facts can be deduced.
* Group1, group 2 and group 3 are top, medial and low value mobile subscribers respectively.
* Off Net duration percentage and On Net duration percentage and dropped calls number of Group1 are obviously over the average index of total customers. Moreover, the call diameter of group 1 is also over the average index of total subscribers or other group subscribers.
The marketing managements can use the facts above to predict which subscribers are likely to churn, For Example, from their usage behavior, we can see that the mobile subscriber of group1 are calling other competitors' subscribers (Off Net) so they are most likely to churn incase they'll get better calling rates. Also dropped call number is a good reason to move all subscribers to churners to avoid poor quality of service.
The mobile subscribers of group 3 have low intense ability and marketing managements need to encourage these subscribers to use more mobile service, in case they are facing problems in using current services which need more improvement.
Chapter 7: Conclusion
In telecommunication most of markets are nearly saturated and there is no place for subscriber growth. Now, the focus is more on increasing subscriber's satisfaction and loyalty instead of acquiring new subscribers. This makes CRM is focus number one for mobile operators and drives to manage the subscriber throughout the product life cycle and subscriber life cycle as well.
We can conclude that Business Intelligence is able to support organisations in gaining competitive advantage through handling information. BI as an approach enables management to analyse, control, report and forecast. BI has great capability of analysing enormous volumes of data, extracting the applicable information then converting them into knowledge. BI enables the usage of all the information the company holds, to proactively respond to changes. Telecommunications operators should be able to make their critical business decisions quickly. Such business agility depends on fast, unrestricted access to the huge amounts of data that accumulates every day about subscribers and network usage.
In this research a small part of BI was chosen, which is data mart using the OLAP technology to achieve business requirements to explore and analyse Call detail records (CDRs) explain subscriber's usage behavior. They have more information to explain subscriber behavior than billing system data. To solve their business problem this is Churn Management.
Churn Management is the pain for Mobile Telecommunications operators these days, around 20 % of total number of subscribers are churners. They have to find out and understand why those subscribers left service. The operators have to find out the reasons behind the churn also, Mobile Telecommunications Operators have to be able to predict probable churners to take positive actions to retain valuable subscribers.
In this research, we proposed BI solution to get predictive model for Telecommunications churn management.
Our practical evaluation shows that Business Intelligence tools can successfully support Mobile Telecommunications Operators to make more precise churner predictions. However, the successful churn prediction model only supports Mobile Telecommunications Operators to know which subscribers are about to discontinue , Successful churn management must also include efficient preservation actions. Mobile Telecommunications Operators need to build up attractive preservation programs to satisfy those subscribers. Furthermore, integrating churn score with subscriber fragment and applying subscriber value also helps Mobile Telecommunications Operators to design the accurate strategies to preserve important subscribers.
Also Data warehouse can be useful more than Data Mart. Data Warehouse contains all data sources, can provide Mobile Operator with complete vision about subscribers not only subscriber usage behaviour but can gives us the integration of the profitability of that subscriber, like bill payment and subscriber segment and subscriber loyalty. Business Intelligence tools can be useful in many Marketing and CRM fields, such as credit card fraud recognition, credit score, similarity between churners and retention programs, and loyalty management. We expect to see more Business Intelligence applications in business management, and more complicated Business Intelligence tools will be developed as business complexity increases.
Finally we like to mention the characteristics of Successful Business Intelligence applications must be:
Attend to Strategic Positioning
Make it a Business Initiative
Practice “user first” Design
Create New Value
Attend to Human Impacts
Focus on Information and Analytics
Practice Active Data Stewardship
Manage BI as a Long-Term Investment
Reach Out with BI/DW Solutions
7.2. Critical Appraisal
This research was initiated in an attempt to introduce a proposed model for Churn Management System in telecom Industry.
Churn Management System is famous and has popular use in telecom industry due to fact that all mobile markets are almost saturated and competition is very tuff. Here Churn Management System comes as a way to retain the existing customers and decrease churn rate.
Existing of Churn Management System and its rewards will enhance business performance and profitability as a result of minimise churn rates and retain current subscriber.
The system consists of four parts:
* Mediation Subsystem: transforms the binary data of the CDR files into ASCII data. Processing this data, then the Mediation Subsystem loads this data into ASCII files.
* Data Warehouse Subsystem: operates on the ASCII data, loaded from the Mediation Phase. Processing this data, the Data Mart is then filled with valuable information about the services provided to subscribers.
* Business Reporting Subsystem: works on the information within the Data Warehouse to generate the required dynamic reports.
* Churn prediction model : working on dynamic reports to get required predictions
Finally I did my best in mention project report to complete in Professional and quality form. But I feel that, there is better like adding more than one implementation to get more accurate study results, also customer requirements putting in specific scope, I want to add more technologies to proposed solution like implementing Data Warehouse with several data sources to cover all Mobile Operator analytical reporting to achieve complete competitive advantage for all business areas not only Churn Management.
7.3. Reflective learning journal
· The mirror
I spent several years working in Telecommunications field and Business Intelligence as several roles; also I did a lot of projects in several industrial. In this project I discovered that there were a lot of things I didn't know. I learned that there was a deep study I didn't know which are reaching my knowledge and experience. I learned how to manage my time to complete several tasks in short time, this project enable me to create a record of the connections and meanings which I made by engage in study experiences. Learning to critically analyse is a hard skill to master, to be able to look at your own experiences and know what you need to improve on can be hard to undertake. This cooperative has provided the opportunity to improve these skills. I kept a notes and reflective journal to express my thoughts and feelings and to reflect on activities, responsibilities and certain situations. These records helped in the varying coop assessment and presentations but also allowed me to track personal changes and improvements in the way I deal with situations. To reflect back on my work activities and the perceptions and attitudes I had towards them allowed me to read deeper into situations and experiences and identify areas of personal growth.
· The microscope
I realize some important things about this experience. I needed to re-consider the questions that I developed. I didn't set them up in appropriate format or according to what we had learned in this module. I did my best in mention project report to complete in Professional and quality form. But I need more technical capabilities to make it better as expected. However I got more experience in technical sides which were lost while working in management area. This project adding great value to my ability of research prepare required report based on researches and reading. I discovered that I have great ability to learn by several methods.
· The binocular
At the first of working on this project I had feeling that I can't finish it with great and quality form, day by day I discovered that I can do anything and got confidence. For example I installed ORACLE DB and ORACLE Data Warehouse Builder on my computer to work anywhere and finish required tasks. It has enabled me to identify specific problems and successes in my learning. I aim to continue to critically reflect in my future career as I have found it most beneficial to my personal development.
Berson, A. & Smith S. J. (1997). Data Warehousing, Data Mining, and Olap, 1st edition. New York: McGraw-Hill, Inc.
Berson, A. , Smith S. J. & Thearling, K. (2000). Building data mining applications for CRM. New York: McGraw-Hill.
Biere, M. (2003). Business Intelligence for the Enterprise. Prentice Hall PTR.
Cherry Tree & Co. (2000). Business Intelligence - The Missing Link. Cherry Tree & Co.
Chou, D.C. , Tripuramallu, H. B. & Chou, A. Y. (2005). BI and ERP integration. Information Management and Computer Security , 340-349.
Howson, C. (2008). Successful Business Intelligence: Secrets to Making BI a Killer App. McGraw-Hill Professional.
Inmon, W. H. (1999). Building the Operational Data Store . John Wiley & Sons Inc.
Moreira, J. M. & De Sousa, J. F. (2006). Applying Data Warehousing Technology to Support Planning and Control on Mass Transit Companies. pp. 23-43.
Kentrias, S. (2001). Customer relationship management. Retrieved from The SAS perspective: www.cm2day.com
Kimball, R. ,Reeves, L. , Ross, M. & Thornthwaite, W. (1998). The data warehouse lifecycle toolkit. New York: John Wiley & Sons, Inc.
Loshin, D. (2003). Business Intelligence. Morgan Kaufmann Pub.
Moss, L. T. & Atri, S. (2003). Business Intelligence Roadmap. Addison-Wesley.
Michalewicz, Z. , Schmidt, M. , Michalewicz , M. & Chiriac , C. (2007). Adaptive Business Intelligence. Springer.
Nemati, H. R. (2005). The expert opinion: an interview with Emilie Harrington, Senior Manager, Accenture's Business Intelligence Practice. Journal of Global Information Technology Management , 66-71.
Turban, E. , Leidner, D. , McLean, E. & Wetherbe, J (2008). Information Technology for Management: Transforming Organizations in the Digital Economy, 6th Edition, John Wiley & Sons.
Williams, S. & Williams , N. (2007). The profit impact of business intelligence. Morgan Kaufmann.
Westerman, P. (2000). Data Warehousing: Using The Wal-mart Model (the Morgan Kaufmann Series In Data Management Systems).
APPENDIX A: INTERVIEW QUESTIONS
Below is a summarised table for questions held in the interviews with IT director who responsible for our project?
Question1. What are project objectives?
Answer: implementing a model for predicting probable churners to retain and determines churn reasons
Question2. What is project scope?
Answer: analyse Call Detailed Record (CDR) to get subscribers usage behaviour
Question3. What the required technology?
Answer: limited Data Mart
Question5. Why not using Data Warehouse?
Answer: project has limited budget can't be exceeded
Question6. Can we add billing data and signaling data to required data mart?
Answer: as I told you before, project has limited budget can't be exceeded also that can affect on time frame which is determined in RFP by 3 months
Question7. How many reports are required for CDR analysis?
Answer: all reports which are related to usage behaviour
Question8. Do you have template for reports?
Answer: No, Reports which were presented are good
Question9. Are we will add hardware to proposal?
Answer: No, we have required hardware
Question10. Is there any question from your side?
Answer: No, Thank you