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
184.108.40.206. 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
220.127.116.11. 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
18.104.22.168. 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
22.214.171.124. 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.
126.96.36.199. 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.
188.8.131.52. 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.
184.108.40.206. 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.
220.127.116.11. 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).
18.104.22.168. 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.
22.214.171.124. 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
126.96.36.199. 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:
188.8.131.52. 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 …
184.108.40.206. 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
220.127.116.11. 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 solutio
If you are the original writer of this essay and no longer wish to have the essay published on the UK Essays website then please click on the link below to request removal: