How to design and create a CRM-oriented data warehouse?

1.1 Introduction to CRM

A complete CRM can be divided into 3 main parts: Operational CRM, Collaborative CRM, and Analytical CRM.Operational CRM is the most basic functional system in CRM, which provides the process management functions of the entire CRM, and is mainly to provide the customer-centered marketing, sales, service and support, and other Automation of business processes. Collaborative CRM is a customer service center as the main form of expression, computer telephony integration technology as the core, so that customers can be by phone, fax, E-mail, Web sites and other ways to interact with the enterprise more quickly and effectively.

Analytical CRM is through the operational CRM, collaborative CRM, other enterprise applications and external data sources saved in the integration of customer-related data, the establishment of a customer-centric data warehouse, to obtain a consistent view of enterprise-wide customer data, and integrated customer data as the basis for querying and reporting analysis, OLAP analysis and data mining and other means to Obtain knowledge about customers, provide customers with personalized products and services, improve customer satisfaction and loyalty, and maximize customer lifetime value. This paper mainly focuses on analytical CRM.

1.2 The inevitability of applying data warehouse in CRM

Data warehouse is the centerpiece of CRM or even the soul of CRM, which stores all kinds of data both inside and outside the enterprise and organizes these source data into a consistent, time-varying as well as maximally optimized analytical customer information base, which can be analyzed by OLAF analysis and data mining. OLAF analysis and data mining to discover the laws hidden in a large amount of customer information, providing support for the enterprise to make business decisions. On the other hand, it effectively separates the business platform of CRM from the analysis platform so that the business database can focus on transaction processing, which improves the efficiency of transaction processing and optimizes the capability of analysis processing.

The traditional enterprise transaction processing system is each department according to their own transaction processing needs to retain part of the data, and the link between the various modules is not close, although part of the customer's information can also be obtained from these systems, but far from being able to meet the needs. For example, for a typical analysis targeting customer behavior, more data accumulated on a daily basis and reflecting historical changes are usually required for effective analysis, but it is difficult to do so with the traditional data warehouse system (both in terms of the amount of data stored and the integration of data). Therefore, the introduction of data warehouses was inevitable.

1.3 Analytical CRM architecture

The introduction of data warehousing technology to the management and organization of customer information, that is, the establishment of a CRM application system-oriented customer information data mining warehouse, which realizes a variety of segmented applications from both internal and external customer information integration and unity, which is the basic task of analytical CRM. As shown in Figure 1 is the architecture of analytic CRM. Among them, the customer information data warehouse is the core of analytical CRM, its task is mainly to extract data from the OLTP system, the extracted data for a unified format conversion, the data will be loaded into the data warehouse environment (the above three steps are called ETL, i.e., extract, transform, load, extraction, transformation, loading), management and maintenance of the data in the data warehouse. . Finally, through OLAP analysis and data mining of these data, business managers can get a lot of valuable information to better serve their customers.

When building a data warehouse, a scalable data warehouse architecture is used here, i.e., the middle tier consists of two types of databases: a basic data warehouse that contains multiple topics; and a subordinate data mart that targets a particular topic. As shown in Fig. 1, 4,000 data marts are designed here according to the four topics in the data warehouse. The use of scalable architecture can shorten the construction cycle of the data warehouse, reduce the cost of expenditure, and avoid the direct establishment of data marts without the establishment of the data warehouse exists poor scalability, multiple data marts are difficult to maintain synchronization of the tongs.

2 Customer Information Data Warehouse Design

The first step in designing a customer information data warehouse is to establish a theme. Theme is an abstract concept, is at a higher level in the enterprise information system in the data synthesis, categorization and analytical use of the object. The first step in designing a data warehouse is to determine the theme of the data warehouse by starting with the data in the operational environment and combining it with the actual needs of decision support. According to the functions of the analytical CRM involved, the customer information data warehouse contains four themes: customer development, customer purchase, product and marketing. Among them, the theme of customer purchase mainly analyzes the customer's purchasing behavior from different perspectives, such as the correlation between the customer's purchasing behavior and the customer's background information, which includes the customer's education level, income level, age, gender, and whether or not the customer is married. In the customer information data warehouse model, it is designed in 3 steps, which are conceptual model, logical model and physical model design. In this paper, for an online bookstore, the complete design of this customer information data warehouse model is given as an example of customer purchase theme.

2.1 Conceptual Model Design

The purpose of conceptual model design in data warehouse design is to determine the topic-oriented information envelopes. The information envelopment diagram serves as a public ****, consistent and compact conceptual model design tool that clearly reflects the user's need and the various elements required to realize that need and the relationships between them. An information package diagram consists of names, dimensions, categories, and metrics, where categories express the hierarchy of dimensions.

The information package diagram of customer purchase topics in the customer information data warehouse of this online bookstore is shown in Figure 2. Among them, there are three categorization methods for books, the first two are more common, and there is another one which is categorized by the form of existence of books, which can be divided into ordinary books, Vbook and Ebook. ordinary books are traditional paper books, Ebook refers to electronic books with computers and networks as the carriers, and Vbook is a new kind of multimedia presentations, training, and business communication carriers with the functions of audio and video such as Lectures of experts in various fields, teaching examination type training courses, etc. With the popularization of computers and the development of networks, Ebook and Vbook are more and more favored by readers.

2.2 Logical Model Design

The logical model of data warehouse generally has two kinds of star model and snowflake model. The star model is a relational database-based, OLAP-oriented form of data organization for a multidimensional data model, which consists of a fact table and multiple dimension tables, and achieves higher query performance than a highly normalized design structure by executing decision-support queries using a single fact table that includes topics and multiple dimension tables that contain informalized descriptions of the facts.

The snowflake model, although more compliant with the normalized design structure than the star model, increases query complexity and reduces query performance; therefore, the star model is used here.

The star model is built on the basis of the information packet circle in the conceptual model, and the information packet diagram is converted into a star model by placing the metric entity in the information packet diagram in the center of the star model, and the dimensional entity in the information packet diagram in the periphery of the metric entity. The logical model of the customer purchase topic in this customer information data warehouse.

2.3 Physical Model Design

The physical model is the form of storage and organization of the data in the data warehouse. To design the physical model, on the basis of the star model or snowflake model, determine the structure of the fact table and the dimension table; clarify the data fields, data types, associated fields, index structure of the two; determine the storage structure of the multidimensional data set in the data warehouse, such as physical access methods, data storage structure, data storage location in order to Xiamen storage allocations such as whether or not partitioning. When designing the physical model, the factors to be considered are I/O access time, space utilization and maintenance cost.

Currently, most data warehouses are built on the basis of relational databases, and the storage of the final data is managed by the database system. In the design of this data warehouse, MSSQLServer2000 and its component analysis servers are selected as the database and data warehouse management system. The data warehouse is logically multidimensional, but in the physical storage of its multidimensional data sets can be stored in relationalonlineanalyticalprocessing(ROLAP), multidimensionalonlineanalyticalprocessing( multidimensionalonlineanalyticalprocessing, MOLAP) and hybridonlineanalyticalprocessing, HOLAP.

In this data warehouse, the storage of multidimensional datasets choose the HOLAP method, that is, the basic data is retained in the original relational database, while the aggregates are stored in the multidimensional structure on the analysis server, which not only avoids duplication of data, but also improves the query performance (because aggregates are stored in the multidimensional dataset), and has a greater impact on the performance of the data only in the case of frequent access to the detailed data.

3 Implementation

For this online bookstore, this data warehouse implementation is based on the MSSQLServer2000 platform. Through the DTS service in SQLServer, and supplemented with VBScript to realize the ETL process of importing source data into the data warehouse; through AnalysisServices to build multi-dimensional datasets, to realize the OLAP operation, to support multi-dimensional query yuanda type (multidimensionalexpression, MDX) query, and through the automatic construction of MDX statements. And by automatically constructing MDX statements, it realizes OLAP operations such as up-scrolling, down-drilling, slicing, dicing, rotating and so on.

The customer information data warehouse*** contains four themes, customer development, customer purchase, product and marketing, and an example of OLAP analysis for the customer purchase theme. In it, users can analyze 5 metrics of customer purchase quantity, amount, cost, profit and average unit price from 11 dimensions of customer location, age group, gender, marital status, occupation, annual income tier, membership star rating, book 1 by content, book 1 by publisher, book 1 by form of existence and time***.

In addition, using the pivot table service provided by AnalysisServices, users can use VB or other languages to develop their own front-end data presentation program, you can also directly use the existing tools, such as MSOffice suite of Excel, Access, to achieve the multi-dimensional data set of data presentation capabilities, so that you can easily Get a variety of statistical reports and analytical graphics. Using Excel to show the profit analysis of the purchase of different types of books by customers of different age groups in 2005.