Data warehouse dimensional modeling for customer service business

. The daily traffic volume of the State Grid 95598 Customer Service Center exceeds 800,000. In order to manage and store these massive data, we must carefully handle it. The call from electric customers implement the busine ss philosophy of “Quality service is the lifeline of electric power enterprise”, and carry out related data aggregation, processing, statistics, conversion and analysis based on data warehouse, and fully explore and analyze these with big data analysis technology. The potential value in the data guides the optimization of business processes and improves the service quality and efficiency of the business center.


Introduction
With the development of big data information technologies such as distributed computing, OLAP (Online Analytical Processing) and data mining, the value of data can be explored, and data warehouse plays a pivotal role in the big data ecosystem [1]. The establishment of the data warehouse will provide strong technical support for the upper-level statistical analysis application [2]. Data warehouse is an effective data analysis tool, which has better performance than database in accessing and managing massive data [3].
The 95598 customer service center covers a wide geographical area, involving many departments, units and systems. The information data summarized by the customer service system is different in data definition and classification standards of various departments and units, and it is easy to form information islands that cannot realize information sharing [4]. The 95598 customer service center database has sufficient service data. Data Warehouse technology can further explore the potential value of these data by integrating the customer service center historical service metadata in the database, and provide comprehensive and integrated information for enterprise decision-making [5] to guide business processes. Improve, save data query time and cost.

Business topics
The 95598 customer service center user incoming process, fault handling process, fault handling query process and customer complaint process are shown in Figure 1 and Figure 2. The decision-making problem of 95598 customer service system relationship includes the occurrence of faults in a certain time/location; the quality of the faults handled by departments and employees in a certain time/location; and the analysis of the complaints of departments/employees. To this end, it is necessary to analyze the business processes and related data such as fault report, fault handling and complaints of the customer service center.
Follow the design philosophy of "top-down, step-by-step refinement" to determine the theme of the data warehouse. By analyzing the business process of the 95598 customer service center, the contents of the customer service center system data warehouse are divided into three subject areas: user error reporting, fault handling, and customer complaints, and the corresponding attributes are analyzed in each topic. In the subject of user calls, the entity that stores the customer information, including the customer ID, customer name, customer category, customer contact number, customer contact address, and obstacle reporting content.
In the subject of troubleshooting, the entity that stores the fault handling entities, including maintenance employee information, troubleshooting results, and so on.
In the subject of customer complaints, the entity that store customer complaints entities, including staff information, department information, and complaint handling results, and so on.
The topics of the three themes are named CstReport, FaultDeal, Complaint, respectively.

Determining dimensions
The granular design in the data warehouse modeling process has a great impact on the amount of data in the data warehouse. The size of the granularity determines the level of detail of the data that the data warehouse can retrieve and the scope of the data query. Therefore, it is necessary to rationally design the data granularity. The dual-grained design refers to retaining both full data and lightly aggregated data, that is, storing data at different granularities. Therefore, in the data granularity design in the customer service system, both the lowest fine-grained data and the lightly aggregated data are stored. The finest granularity of customer service data, that is, a user's incoming call record, a faulty maintenance record, and so on. The lowest fine-grained data storage time is retained for 3 years (or other reasonable duration), and low-fine-grained data maintains data detail and supports accurate data aggregation [6]. The lightly aggregated data retention time can be set longer (for example, 5 to 10 years), while the lowest fine-grained data is not necessary after a long time, so the lowest fine-grained, lower-fine granularity The data can be dumped to keep the data store with enough storage space.r Users of the data warehouse can analyze the facts through the dimension table, and the data in the different dimension tables provides different analysis angles for the fact table.
According to the data format of the 95598 customer service system, this paper constructs seven dimension tables in the data warehouse, which are fault dimension table, customer  dimension table, geographic dimension table, time dimension table, maintenance dimension  table, employee dimension table and department dimension table. The dimension table  defines the attributes under each topic, including the attribute name, attribute ID, data composition, data length, and primary key.

Overall architecture design
This paper builds a data warehouse model for the 95598 customer service business. The overall architecture of the data warehouse is shown in Fig.2 STAGE data access layer, database access data source data warehouse. The DWD (Data Warehouse Detail) data detail layer, also known as the ODS (Operation Data Store) basic data layer, is responsible for cleaning and precipitating the data of the STAGE layer, storing data in a subject-oriented manner, and storing historical incremental data or full data. . Since the data of the ODS layer is set for the topic, the ODS layer can implement OLTP for global data.
DWB (Data Warehouse Basis) is a light summary layer. As a transition layer between the ODS layer and the MDS layer, the DWB layer gently summarizes the data of the ODS layer and abstracts some common dimensions, such as time, region, department, employee, etc. In these dimensions, do light data integration, such as complaints that a department suffers every day, customer information summary of a certain month of a certain month, etc., a slight summary can make data query more efficient. The DWS (Data Warehouse Summary) theme layer, a wide table divided by business and topic, is the subject of the data warehouse. Data with a higher degree of aggregation under a certain topic based on data aggregation of the DWB layer can provide services for query, OLAP, data analysis, etc. of upper-layer applications.
DM (Date Market) data market layer, DM layer as DWS access layer is used to extract data from the data warehouse directly to the user, facing departments or teams in each system. ADS (Application Data Store) application layer: Provides users with visual data query and analysis services based on DWS and DM based on the analysis business needs of users.

Logical modeling
The logical modeling methods of data warehouse mainly include E-R (entity) modeling, 3NF (third paradigm) modeling, and dimensional modeling. Dimensional modeling is a modeling method for analytical databases, data warehouses, and data marts. It has the response performance of large-scale and complex queries, and can quickly respond to analytical needs. Therefore, this paper chooses the dimensional modeling method to build a data warehouse model for the 95598 customer service system. According to the business process analysis and determination in 2.1, the theme 95598 customer service system data warehouse logic model is shown in Fig.3 A data warehouse contains one or more fact tables. The fact table may contain business sales data, such as data generated by a cash registration firm, which typically contains a large number of rows. The main feature of the fact table is the inclusion of digital data (facts), and this digital information can be aggregated to provide data about the unit as history.
The dimensional modeling method has three modes: star, snowflake and constellation. Since there are only three service fact tables and a set of dimension tables, and the dimension tables between the fact tables have a reuse relationship, the data warehouse of the 95598 customer service center is used. Establish a constellation model. It can be seen from Fig. 3

Physical modeling
Based on the physical implementation of the data warehouse logical model in 3.2, we have used Power Designer for the physical modeling. This paper only shows part of the models because of the limited-space. As shown in table 1 and table 2.

Summary
In order to meet the OLAP requirements of 95598 customer service system for business data, in order to improve the service quality and decision-making ability of customer service business system, this paper builds a customer service business system data warehouse model based on the integration of multi-heterogeneous data, and integrates the business process of customer service center. Based on the analysis, the data warehouse system architecture of the customer service business system is constructed. In this paper, the dimensional modeling method is adopted in the modeling method, and the theme, dimension and granularity are gradually determined according to the data warehouse design concept of "top-down and step-by-step refinement", and the corresponding logical model and physical model are established.