A text teaches you how MES production statistics can be achieved through ODS

First, the positioning and source of ODS

Data Warehouse (DW) is created for decision support, so building a robust decision support system depends on whether a good data warehouse can be built. Although the data warehouse-based decision support system has been considered as a good solution for decision support, because this architecture completely separates the operating environment from the analysis environment, this architecture is not suitable for middle management. Personnel and applications for instant analysis processing. To this end, the concept of the Business Data Store (OperaTIonal Data Stores, abbreviated as ODS) was proposed. It makes up for the deficiency of the DB-DW two-tier architecture and forms a three-tier architecture based on DB-ODS-DW.

The business data store is a collection of data used to support the daily global application of the enterprise. It is regarded as the basis of business processing. It can feed data to the data warehouse, also known as operational data storage. Data stored in ODS has the following characteristics: subject-oriented, integrated, variable, and data is current or near current. Like DW, the data organization in ODS is subject-oriented and integrated, so ODS is built on top of the database, and data entering ODS is also extracted, transformed, and integrated. Different from the data warehouse, ODS stores current or near-current data, and can be modified online, including adding, deleting, modifying, etc. Therefore, the technical support of the two is different. ODS supports record-oriented online. Updates, in addition to ensuring the consistency of their data with the data in the original database system, require the same support technology as the application-oriented distributed database system (DB) support technology. ODS differs from traditional databases (DB) in that ODS provides globally consistent online transaction processing (OLTP), while DB is only applicable to departmental OLTP, database DB, business data store ODS and data warehouse DW. Comparison between.

Table 1 Comparison between database DB, business data store ODS and data warehouse DW

Second, the concept and role of ODS

ODS is a collection of subject-oriented, integrated, variable, and current detail data to support the enterprise's need for immediate, operational, and integrated information. Often used as a transition to a data warehouse, and one of the options for a data warehouse project.

So why do you need an ODS system? Generally, in a system architecture with ODS, ODS has the following functions:

Table 2 ODS architecture role diagram

A text teaches you how MES production statistics can be achieved through ODS

Table 3 ODS classification and feature map

A text teaches you how MES production statistics can be achieved through ODS

Third, the problem should be paid attention to in ODS application

Although ODS is a new formulation, it is not a new thing in terms of its substance. ODS is another aspect or another manifestation of decision support technology based on data warehouse. Therefore, ODS can be established using off-the-shelf data warehouse technology (DWT: Data Warehouse Technology). DWT refers to a set of methods, techniques, and tools that leverage this to create a vehicle that delivers data to end users on an integrated platform. ODS applications are mainly composed of two aspects: one is as a stand-alone solution to provide globally consistent applications for enterprises that need to integrate the operating environment; the other is as a transitional form from DB to DW, establishing a DB-based - ODS-DW's three-tier architecture decision support system solution.

Although the creation of ODS systems can use DWT technology, because ODS usually performs the functions of a traditional transaction processing environment and performs the functions of a special decision support environment, the two environments themselves are very different, and the two environments are fully utilized. The technology is directly conflicting, so the design of ODS is complex. When considering which type of ODS application to choose, whether the architecture is centralized or distributed, the development method is a â€œtop-downâ€ approach or a â€œbottom-upâ€ approach, all of which are related to the enterprise. The nature and status of the company, the size of the company, and the scope of business it operates are closely related.

Fourth, the positioning and role of ODS

ODS is mainly used to optimize report speed, integrate interactive data, and expand third-party integration to form the basis of enterprise data warehouse (EDW). ODS integrates general data, as a source of data for output and display, the role in the system is very important, in MES2.0, to save relational data, does not involve static data like PRM, or PHD Real-time data, but the data is extracted and stored in a fixed business settlement unit.

The ODS of MES not only has the performance of traditional ODS, but also takes into account the functions of some DWs. It can be said that it is a combination between the two, correspondingly to meet the needs of regional companies for integrated services, and system performance optimization. Also sacrificed some of the opposite features.

While properly controlling the granularity of data, the data is stored in DW dimensions and facts, maintaining the frequency and stability of the data. In principle, as a read-only library, the system performance has also been greatly improved, which not only satisfies the enterprise. Auxiliary decision-making, but also support the needs of middle-level business management. In order to satisfy the retention of historical data and the performance of the system, the data table is split. The data retention period in the ODS table can be modified by configuration. The data exceeding the deadline is archived into the historical data table, and further commonality will be further implemented according to the implementation process. The problem is added to the ODS.

V. Status of ODS

In the overall design stage, ODS sent e-mails collecting ODS integration data to other modules in the scope of MES. After the feedback from all parties in the overall group, the design and development of ODS was carried out. At present, ODS includes MES material balance, production scheduling, public works, There are four modules for measurement management, totaling 42 dimensions and fact tables, 6 views, 1 stored procedure, 5 header packages, 26 scheduled tasks, and 1 sequence.

Table 4 Status of ODS

A text teaches you how MES production statistics can be achieved through ODS

Sixth, ODS architecture

ODS data is derived from the basic business database. It is extracted and processed by ETL, stored in ODS, and further integrated and processed to provide production statistics reports and related display modules.

A text teaches you how MES production statistics can be achieved through ODS

Figure 1 ODS architecture

Seven, the construction principle and method of ODS

A text teaches you how MES production statistics can be achieved through ODS

Figure 2 General flow of creating data objects for ODS

At present, the ODS table and the service data table of the MES are in the same database, and the ODS is subsequently migrated to an independent table space. The dimension table in the ODS is pushed in a full amount, first deleted and then inserted, and the log information is recorded before the data is pushed. After the data is pushed, the status of the log is modified; the fact table in the ODS is pushed in an incremental manner, and the log information is recorded before the data is pushed. The start time of the push data is the current time, and the incremental manner of obtaining the service data is through the service data. The modified date field in the data retrieves the data from the last push time to the local push time period, and the status of the log is modified after the data is pushed. Data push and modify log status must be written in the same transaction, if an exception rollback occurs, and the log status is modified. After the stored procedure for pushing data is created, the stored procedure for pushing data for each module is placed in a package.

A text teaches you how MES production statistics can be achieved through ODS

Figure 3 data push

operation

Create a JOB to define the frequency of data extraction;

Define the execution time of the JOB according to the actual situation, and complete the automatic extraction of data;

It can be manually executed manually during the test.