Comstar-United Telesystems (Comstar-OTS) (IBM WebSphere)

Creation and implementation of an information and analytical system

Situation

Continuous increase in a subscriber base and desire of the Company to provide to clients a competitive packet of products and high-quality service resulted in need of creation of analytical decision making support system with the subsequent integration it in uniform CIS of the organization.

Business challenge

Perform a possibility of deep analytics of indicators of the traffic arriving from the functioning billing systems of affiliated enterprises, creation of the consolidated statements for the purpose of acceptance of effective management decisions.

As the company was formed in 2004 by merge of three independent organizations, the problem of consolidation of information was very relevant. Each enterprise had the accounting systems storing approximately identical data, but in different formats and codings. Sometimes information was duplicated while uniform reference books were absent. Besides, the companies used different database management systems (DBMS), working under the different operating systems (OS). In an information architecture of preparation of the reporting billing and accounting programs acted as sources and also ERP system Oracle E-Business Suite. Before creation of a uniform information and analytical system procedural reports for company management in a format of tables Microsoft Excel prepared the staff of IT department. Such mode of work led to a number of problems by preparation of the corporate reporting Comstar-OTS. First, technologies of collecting and generation of the reporting depending on the specific system inherited from the organization included in group differed. Secondly, in systems existing at that time it was very difficult to consolidate data on the enterprises which entered into group, to implement own analysis techniques and calculation of any indicators. Besides, all reports then prepared directly using these operational accounting systems that strongly overloaded them.

Choice of technological solutions

Need for a uniform information and analytical system arose because of the listed above problems. For consolidation of data it was decided to use industrial ETL means. ETL (Extract — Transform — Load) represents process in management of data warehouses, including extraction of data from external sources, their transformation and cleaning according to requirements of a business model of the enterprise and at last loading of the processed information in storage.

As the ETL solution it was selected by software of IBM WebSphere DataStage. Owing to the developed circumstances from software work with diverse sources and data sinks and also independence of the hardware-software platform was required. Besides, in the selected product specialists Comstar-OTS noted a visual development environment of ETL procedures, simplicity in a support and maintenance software, its scalability and a possibility of process optimization of data loading.

As means of the analysis and information representation was decided to use the Business Objects platform (this company in 2007 was purchased by SAP). As selection terms possibilities of creation of reports and multidimensional data analysis served in terms of data domain familiar to users, scalability and flexibility of the solution, ensuring collective work both through "thin", and through "thick" clients and also differentiation of access rights. Invited to a role of the supplier of the technological solution Tern company.

Analysis of interoperator traffic

At first carried out rather small project for directorate on work with telecom operators during which single solution according to the analysis of interoperator traffic on the basis of the consolidated data from billing systems of three companies was developed. Managers of directorate had to receive convenient means of operational processing and the analysis of accumulated information and also evident analytical reports for acceptance of management decisions.

As data sources three different billing systems which at that time were in Comstar companies, MTU and "Telmos" were used. All of them relied on different DBMS — Informix, Oracle and the Microsoft SQL Server respectively. The role of IBM WebSphere DataStage consisted in taking information from different DBMS, to approve reference books, to clean and standardize data, to lead them to uniform formats and codings and to aggregate. Then information was located in the created corporate data warehouse. Daily in time determined by regulations in hours of the smallest loading of the accounting systems large volumes of information are transferred from sources to corporate storage. Necessary historical depth of storage is determined. Above we already spoke about need to unload the operational accounting systems, and the similar solution architecture removes this problem.

Then on the basis of Business Objects semantic layers through which access to reports was got by the guide and the staff of directorate to work with telecom operators were developed. The semantic layer allows the users of a system who do not have special knowledge to work with the data provided in usual business terms — for example, "income", "connection duration", etc. Upon termination of works at Comstar-OTS there was a possibility of operational monitoring and the analysis of last traffic on operators and the directions. The group uses information from the created reports for formation of price policy and optimization of routes of connections.

Analytical decision making support system

Experience of development of the system of the analysis of interoperator traffic is considered successful. Was decided to begin the second project on creation of large-scale analytical decision making support system of Management Information System (MIS). Permanent expansion of a subscriber base and also aspiration of the company to react quickly to changes of the market situation and to increase efficiency of corporate management became its premises. Also tactical targets of the project were formulated strategic. A strategic objective — creation of a performance management system of activity of the company on the basis of strategic planning, control of execution and the balanced scorecard. And tactical targets included the following items:

• automation and increase in efficiency of the management and management of the company by providing means to them for the analysis of all necessary information;
• an ability to manage the company on the basis of the analysis of key performance indicators (KPI);
• search of patterns in activity of the company and forecasting on their basis.

The project of creation of MIS consisted of three stages providing gradual expansion of a scope of areas of the analysis, user groups and data sources. At the first stage (about which we already told above) the monitoring system and the operational analysis of traffic for directorate on work with telecom operators was created. On the second the functionality of MIS is expanded for the benefit of financial and commercial blocks of group, the analysis of financial and natural performance is provided, an opportunity to provide necessary information to the top management for acceptance of management decisions is implemented. At the third stage development of a system for the benefit of all departments of the company is planned. In MIS except billing systems as data sources the system of the technical NetCracker resources and a HR system are used. Connection of a corporate ERP system, CRM system of SalesLogix and others is in the future planned. Besides, the separate system of the normative reference information (NRI) which provides a possibility of manual data scrubbing about clients as in different billing systems they are not up to the end approved also now was selected. In addition through NSI it is possible to classify measurements. IBM WebSphere DataStage, but already using a single system of NSI is responsible for data integration and their placement to corporate storage.

The developed semantic layer includes more than two hundred logical objects united in 57 classes, and allows to carry out comprehensive information analysis and to build the procedural reporting. Two groups of key indicators of a company performance — financial and natural are selected. Different types of income got to the first: with taxes and without taxes, in different currencies, with adjustments and without them, net charges. In the second — the number of clients and personal accounts and also such indicators, specific to telecommunication companies, as volume of Internet and telephone traffic, the number of channels and access points, the number of phone numbers and trunk lines, the size transferred to lease of space, etc.

Measurements are separated into the following main classes: temporary; measurements on structure of the customer base; measurements on the services provided by the company. Some measurements are absent in data sources, but are of great interest to analysts of the enterprise. For their creation the subsystem of NSI providing a possibility of additional classification of services of group is used.

Such configured classification allows to group services in services (telephony, Internet access, sale of the equipment, etc.), in technology of providing, in charge types (a subscription or installation fee, payment of traffic, etc.), in types and the direction of traffic. Except specified there is a measurement of the most bottom level — according to the qualifier of billing services. It considers features of tariffing of each service. On clients and structure of the customer base the main measurements are personal accounts of clients, types of clients and their personal accounts, belonging to this or that branch, customer segmentation on group of profitability.

Project Results

In rather short terms (eight and a half months) in Comstar-OTS group the corporate storage on the basis of data integration was constructed of the existing accounting systems, the information and analytical system providing ample opportunities of multidimensional information analysis and creation of the procedural reporting is developed and implemented in operation. The detail level of data implemented in a system allows to analyze indicators up to the personal account of the client and single billing service. Support of history of maintaining the main reference books gives opportunities for the analysis of indicators both in historical aspect, and concerning their current status. A system allows to analyze compliance between natural and financial performance. It became possible to calculate income from group of services, for example, from data transmission via the Internet. The information base for implementation of an information and analytical system of decision support on the basis of the analysis of key indicators of activity is as a result created.