Real-time OLAP applications for building commercial systems based on ODS

Abstract: Based on the Data Storage Technology of ODS in the middle layer of DB and DW, a real-time OLAP system for commercial systems is designed from the perspective of application. ODS is used to overcome the problem that DW is too bloated for decision-making and is not suitable for enterprises' real-time middle-level decision-making.

Key words: Data Warehouse (DW), operational data storage (ODS), Real-Time Online Analytical Processing (OLAP)

  1. Question proposal

The topic-oriented concept of Data Warehouse (DW) not only provides a globally consistent data environment to effectively support enterprise operation and management decision-making, but also provides a historical
Data, comprehensive data processing, and puts forward an effective solution. The concept of Data Warehouse is also clearly divided into two different types of data processing: Operational Processing and analytical processing.
Two-layer architecture of DB-DW. However, there are many situations, the two-layer architecture of DB-DW does not cover all the data processing requirements of enterprises, because although the data processing of enterprises can be roughly divided into operations
Production and analysis, but these two types are not distinct, and there is also a cross between them. For example, some are operational, however, it is not suitable for operation-type dB, and some are analysis-type databases.
But not suitable for DW.

For example, the decision-making system of a pharmaceutical sales company we developed, the company manager is required to solve the problem of what goods should be purchased, the recent profit of various products, the customer
And so on. To answer these questions, he must first determine whether the stock of the drug is sufficient, understand the recent sales of the drug, and compare the inventory and sales of other drugs.
. If we put this decision analysis process in the original application-oriented distributed dB system, we may not necessarily obtain accurate and consistent information from each department, coordination and cooperation between different departments is required.
However, if you place the data in DW for analysis, it will be time-consuming and there will be a lot of unnecessary data retrieval.

The above problems can be solved by the middle-layer ODS (operational data storage) of DB-DW. Like DW, It is a topic-oriented, integrated data
Environment, and the current detailed data is also included in the operational dB. The establishment of ODS-based Real-time OLAP applications is a solution for middle-level decision analysis. It can be well adapted
Frequent, medium, and low-level decision-making and analysis applications of enterprises.

2. ODS technology and real-time OLAP

2.1 operation data storage (ODS)

Is a  that supports daily global applications of enterprises. ODS data is subject-oriented, integrated, variable, and current or near-current.
Four basic features. ODS is a data storage technology between DB and DW. Compared with the scattered dB for application, the Data Organization Mode in ODS is similar to that in DW.
Topic and integration, so the data entering ODS is converted and integrated like the data entering the data warehouse. In addition, ODS only stores current or near current data. If necessary, you can
Data in ODS is added, deleted, and updated. Although the data in DW is also subject-oriented and integrated, the data is generally not modified, therefore, the differences between ODS and DW mainly reflect data variability.
And current.

  2.2 Real-Time OLAP

ODS is mainly generated to meet the needs of enterprise-level global applications, and its applications are mainly used for real-time "OLAP" data processing.

We implement OLAP on DW mainly for long-term trend analysis. dw contains a large amount of data, so the OLAP application runs for a long time.
In the daily operation of an enterprise, it is often necessary to make non-strategic middle-level decisions to achieve the daily management and control of the enterprise. For example, the manager of a pharmaceutical sales company needs to check the sales of medicines every week, drug sales in various regions
In this decision-making process, there is no need to refer to too much historical data, mainly to refer to the current or compare the current data, but also to relatively fast execution speed, etc, this analysis can be determined.
It is called "real-time OLAP ". Obviously, using DW not only makes the running efficiency intolerable, but also makes it difficult to accurately reflect the real situation in the near future. The establishment of ODS overcomes the bloated DW system and processing time.
Long and not suitable for real-time OLAP, providing medium-level decision makers with quick and accurate analysis information.

  2.3 Implementation Mechanism of conversion from dB to ODS

In the architecture of the DB-ODS, the implementation mechanism of ODS is manifested in its recording of the data transfer relation defined by the system, as shown in 1. In an operating environment
Distributed dB records are filtered to form a records system of the ODS system and provide data to the ODS system. The record system defines the data transmitted to ODS in the original scattered dB and specifies
Data table. Through the definition of ODS, data scattered in the dB of the application can be copied to ODS, so that the records in the original scattered dB form topic-oriented records in ODS. ODS dimension
It protects an analyticdb environment, making data processing much easier and requiring little technical support.

Figure 1 conversion from dB to ODS

The implementation mechanism for converting data from ODS to DB is not provided. This mechanism is mainly used for global enterprise operation applications.
Some parameter tables are stored in the ODS system. The relationship between them is that relevant records must be reflected to all databases during global update of ODS. In this case, ODS is an operating environment that meets the technical requirements of ODS.
Similar to the original application-oriented distributed database system, such as transaction management, block management, and data recovery.

  3 design of ODS-based Real-time OLAP application for drug sales

We know that ODS is a new data storage technology between DB and DW, which has the characteristics of DW and DB. When developing real-time OLAP systems, the development method is closer to the DW development mode.

  3.1 establish ODS

As shown in database structure 2 of the original sales database management system of the drug sales company

Figure 2 source database ER Model

Access97 databases are classified into several databases, including finance, sales, and inventory.

According to the previous analysis, the sales region is preferred. The customer and the drug are the three most concerned topics in the sales field, and the data involved in this field in the transaction database is transferred
To ODS. ODS (mainly against DW) logical models are classified into three types: Star Model, snowflake model, and hybrid model. We used the star model when developing the ODS logical model. A simple star
The shape model consists of a fact table and several dimension tables, and the complex star model may include hundreds of dimension tables. The star model defines data entities from the perspective of supporting business decision-making. It can objectively reflect business operations in entities.
Compared with the last two models, the rule and attribute of rows are relatively simple and easy to understand and accept. Figure 3 shows the logic model of the system.

Figure 3 ODS Star Model

When defining the record system, we mainly consider how to allocate the attributes of the topic domain to the application system, here, we mainly want to allocate the attributes of each topic to the sales and inventory subsystems in the operating environment of drug sales.

  3.2 data collection

The data collection process spans the distributed dB operating environment and ODS analysis environment. The data collection process in this system is relatively simple. You only need to define the ing between the two according to the ODS record system and the ODS record system, and transmit the data in the dB to ODS, here we use the DTs data conversion service of SQL Server 7.0, SQL
DTS (data transformation services) of Server 7.0 provides data extraction, conversion, and loading functions. Use SQL
Server
7. 0 Input and Output Wizard to create a data transmission package, and use snapshot (snapshot type) during copying to clear and recreate data in ODS. Because the data volume is not large, it can be well executed.
Row efficiency.

  3.3 Implementation of the System user interface

The end user interface is implemented using Delphi 5.x. Here are a few advantages. Delphi provides a set of decision support components for comprehensive and multi-level analysis of data. These decision support components include:

. Tdecisioncube is a multi-dimensional data warehouse.

. Tdecisionquery is similar to tquery and is used to connect to a database.

. Tdecisionsource is similar to tdatasource and can provide data source connection for pivot tables, grids, charts, and other components.

. Tdecision.pdf is used to display multi-dimensional data in the form of a grid.

. Tdecisiongraph displays data in charts. You can organize charts based on different fields.

By connecting the created ODS data table to the tdecisioncube, you can use other decision components to display multi-dimensional grids and charts, A clear analysis interface.

  3.4 System Architecture

Figure 4 System Architecture

4. Conclusion

The introduction and application of ODS technology provides a solution for enterprises to carry out real-time OLAP in their daily operations so that enterprises do not need to build a "bloated" DW, in this way, we can make some non-strategic middle-level decisions to achieve daily management and control of the enterprise, and get a fast response speed.

At the same time, when building DW, enterprises can consider the three-layer mode of DB-ODS-DW to develop DW, and further develop high-level OLAP Decision-making System with global application, to complement the overall and real-time decisions of an enterprise.

References

[1] Wang Shan and other data warehouse technologies and Online Analytical Processing Science Press 1998.6
Building the Data Warehouse Machinery Industry Publishing House, translated by W. H. inmon/Wang zhihai, 2000.5
[3] michaelcorey michaelabbey/hope book creation room translation SQL Server 7 data warehousing Beijing hope electronics Publishing House 2000.1