Data warehouse design purpose and data warehouse design purpose

Data warehouse design purpose and data warehouse design purpose

The purpose of data warehouse design or the criteria for measuring success:

1. The data warehouse must make the information of the Organization easy to access.

2. The data warehouse must display information of the Organization in a consistent manner.

3. Data Warehouses must be widely adaptive and easy to modify.

4. The data warehouse must assume the most basic role in effective recommendation decisions.

5. the premise that the data warehouse is accepted by the Business Group is that it is regarded as successful.

Differences between data warehouses and databases

In short, databases are designed for transactions and data warehouses are designed for topics.

Databases generally store online transaction data, while data warehouses generally store historical data.

Database Design is designed to avoid redundancy as much as possible. Generally, it is designed to comply with the rules of the paradigm. Data Warehouse design is intended to introduce redundancy and adopt an anti-paradigm design.

A database is designed to . A data warehouse is designed to analyze data. Its two basic elements are dimension tables and fact tables. Dimensions are the definitions of these things, such as time, department, and dimension tables. The fact table contains the data to be queried and the dimension ID.

In terms of concept, it is a bit obscure. Any technology serves applications, which can be easily understood in combination with applications. Take banking as an example. The database is the data platform of the transaction system. Every transaction made by the customer in the bank will be written into the database and recorded. Here, we can simply understand it as using database accounting. A data warehouse is a data platform for analysis systems. It obtains data from the transaction system and summarizes and processes the data to provide decision-making basis for decision makers. For example, the current deposit balance of a bank's branch is what happens in a month. If there are more deposits and more consumption transactions, it is necessary to set up an ATM in the region.

Apparently, the transaction volume of a bank is huge, usually measured in millions or even tens of millions of times. The transaction system is real-time, which requires timeliness. It takes tens of seconds for the customer to save a sum of money, which requires the database to store data for a short period of time. The analysis system is post-event. It must provide all valid data within the specified time period. The data is massive, and the aggregation and calculation are slower. However, as long as the data can be effectively analyzed, the goal is achieved.

A data warehouse is generated in order to further explore data resources and make decisions when a large number of databases exist. It is by no means a "large database ". What are the differences between data warehouses and traditional databases? Let's take a look at the definition of data warehouse by W. H. Inmon: a topic-oriented, integrated, time-related, and unchangeable data set.

"Theme-oriented": traditional databases mainly process data for applications and may not store data based on the same topic. Data Warehouses focus on data analysis and are stored Based on topics. This is similar to the difference between a traditional farmer's market and a supermarket-cabbage, radish, and coriander are sold at a stall if they are sold at a small price, cabbage, radish, and coriander are separated. That is to say, the food (data) in the market is collected (stored) by vendors (applications), while the supermarket stores food by type (with the same subject.

"Time-related": When the database saves information, it does not emphasize that there must be time information. The data warehouse is different. for decision-making purposes, the data in the data warehouse must indicate the time attribute. In decision making, the time attribute is very important. They are also customers who have purchased Nine-car products. One is that they have bought nine-car products in the last three months, and the other is that they have never bought nine-car products in the last year. This is different for decision makers.

"Unchangeable": the data in the data warehouse is not up-to-date, but comes from other data sources. The data warehouse reflects historical information, rather than the daily transaction data processed by many databases (some databases, such as the telecom billing database and even real-time information processing ). Therefore, the data in the data warehouse is rarely or never modified. Of course, adding data to the Data Warehouse is allowed.

The emergence of data warehouses is not to replace databases. Currently, most data warehouses are managed by relational database management systems. Databases and Data Warehouses complement each other.

In addition, the purpose of the data warehouse solution is to serve as the basis for front-end query and analysis. Due to the large redundancy, the storage required is also large. To better serve front-end applications, the data warehouse must have the following advantages; otherwise, it is a failed data warehouse solution.

1. High Efficiency. The analysis data requested by the customer is generally divided into days, weeks, months, quarters, and years. It can be seen that the daily data for the cycle requires the highest efficiency and requires 24 hours or even 12 hours, the customer can see yesterday's data analysis. Some enterprises often encounter problems with poorly designed data warehouses because of their large daily data volume. data can only be provided 1-3 days later. Obviously, this is not acceptable.

2. Data quality. Customers should look at various types of content... the remaining full text>
 
How to design and create a CRM-oriented data warehouse?

1. CRM System

1.1 CRM Introduction

A complete CRM can be divided into three parts: Operational CRM, collaborative CRM, and Analytical CRM. Operational CRM is the most basic functional system in CRM. It provides the process management function of the entire CRM, mainly to provide customer-centric marketing, sales, service and support and other business process automation. Collaborative CRM is mainly manifested in the customer service center, with the computer telephone integration technology as the core, this allows customers to interact with enterprises more quickly and effectively through telephone, fax, E-mail, and Web sites.

Analyticdb CRM integrates customer-related data stored in operational CRM, collaborative CRM, other enterprise application systems, and external data sources to build a customer-centric data warehouse, obtain a consistent view of customer data within the enterprise, and obtain customer knowledge through query and report analysis, OLAP analysis, and data mining based on integrated customer data, it provides customers with personalized products and services, improves customer satisfaction and loyalty, and maximizes customers' lifetime value. This article focuses on Analytical CRM.

1.2 inevitability of applying data warehouses in CRM

The data warehouse is the central link of CRM and even the soul of CRM. It stores various internal and external data of the enterprise, the source data is organized into a consistent, time-varying, and customer information database for Optimal Analysis. Through OLAF analysis and data mining, the hidden rules of a large number of customer information are discovered, it provides support for enterprises to make business decisions. On the other hand, it effectively isolates the CRM business platform from the analysis platform so that the business database can focus on transaction processing, it not only improves the efficiency of transaction processing, but also optimizes the analysis and processing capabilities.

In traditional enterprise transaction processing systems, each department retains part of the data according to its own transaction processing needs, and the relationship between each module is not close. Although some of the customer's information can also be obtained from these systems, but far from meeting the needs. For example, for a typical analysis of customer behavior, it is usually necessary to analyze more data that is accumulated daily and reflects historical changes, however, the traditional data warehouse system is difficult to achieve at this point (whether from data storage or from data integration ). Therefore, the introduction of data warehouses is inevitable.

1.3 architecture of analytic CRM

Introduce the Data Warehouse Technology to the management and organization of customer information, that is, to establish a customer information mining warehouse for CRM application systems, it realizes the integration and unification of customer information from various internal and external application segmentation, which is the basic task of Analytical CRM. 1 shows the architecture of Analytical CRM. Among them, the customer information data warehouse is the core of analyticcrm. Its task is to extract data from the OLTP system and convert the extracted data in a unified format, load data to the data warehouse environment (the preceding three steps are called ETL, namely extract, transform, load, extraction, conversion, and load), and manage and maintain data in the data warehouse. Finally, through OLAP analysis and data mining of such data, enterprise managers can obtain a lot of valuable information to better serve customers.

When building a data warehouse, we use an extensible data warehouse architecture, that is, the middle layer includes two types of databases: one is a data warehouse that contains multiple topics, and the other is a data mart that is subordinate to a specific topic. As shown in figure 1, here we have designed data marketplaces based on four topics in the data warehouse. Using a scalable architecture can shorten the data warehouse construction cycle and reduce costs, in addition, this avoids the poor scalability of directly establishing a data mart without creating a data warehouse, and it is difficult to maintain synchronization between multiple data marketplaces.

2 customer information data warehouse design

The first step to design the customer information data warehouse is to establish the topic. A topic is an abstract concept. It is an object that combines, classifies, and analyzes and exploits data in an enterprise information system at a higher level. To design a data warehouse, you must first start with the data in the operating environment and determine the topic of the Data Warehouse Based on the actual needs of decision-making support. According to the functions of the analyticdb CRM, the customer information data warehouse contains the full text of the customer...>