The purpose of creating a data warehouse (DataWarehouse referred to as DW) is for decision support. Therefore, establishing a complete and reliable decision support system depends on whether a good data warehouse can be established. Although the decision support system based on data warehouse 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. People and real-time analysis processing applications. To this end, the concept of a business data store (OperaTIonal Data Stores, abbreviated as ODS) was proposed. It compensates for the inadequacy of the DB-DW two-tier architecture and forms a three-tier architecture based on DB-ODS-DW.
A business data store is a collection of data that is used to support a company's day-to-day global applications, seeing it as the basis for business processes, and it can feed data warehouses, also known as operational data stores. The data stored in the ODS has the following characteristics: theme-oriented, integrated, variable, and current or near-current data. Like DW, the organization of data in ODS is topic-oriented and integrated. Therefore, ODS is built on top of the database. Data entering ODS must also be extracted, converted, and integrated. Unlike data warehouses, ODS stores current or near-current data that can be modified online, including additions, deletions, and modifications. Therefore, technical support for both is not the same. ODS supports record-oriented online support. The update, in turn, ensures that the data is consistent with the data in the original database system, and the required support technology is as complex as the application-oriented distributed database system (DB) support technology. ODS also differs from traditional databases (DB) in that ODS provides globally consistent online transaction processing (OLTP), while DB is only applicable to department-level OLTP, database DB, business data store ODS, and data warehouse DW. Comparison between.
Table 1 Comparison among database DB, business data store ODS and data warehouse DW
ODS is a topic-oriented, integrated, variable, and current collection of detailed data that supports the enterprise's need for prompt, operational, integrated, overall information. Often used as a data warehouse transition, it is also an option for data warehouse projects.
So why do you need an ODS system? Generally in the system architecture with ODS, ODS has the following functions:
Table 2 ODS architecture diagram
Table 3 ODS classification and characteristics
Although ODS is a new formulation, in terms of its substance, it is not a new thing. ODS is another aspect or another manifestation of decision support technology based on data warehouse. Therefore, ODS can be established using an off-the-shelf data warehouse technology (DWT: Data Warehouse Technology). DWT refers to a series of methods, technologies, and tools that make use of all this to produce a vehicle that can deliver data to end users on an integrated platform. The ODS application mainly consists of two aspects: one is to provide a globally consistent application as an independent solution for enterprises that require an integrated operating environment; the other is to establish a DB-based transition from DB to DW. -ODS-DW's three-tier architecture decision support system solution.
Although DWT technology can be used to create ODS systems, ODS usually performs both the functions of a traditional transaction processing environment and the functions of a special decision-support environment. These two environments are very different from each other, and the two types of environment performance are fully utilized. The technology is directly in conflict, so the design of ODS is complex. In considering which type of ODS application to use, whether the architecture is centralized or distributed, and whether the development method is a "top-down" approach or a "bottom-up" approach, all these factors are related to the enterprise. The nature and status of the company itself, the size of the company's scale, and the scope of its business operations are closely linked.
Fourth, the positioning and role of ODSODS is mainly aimed at optimizing report speed, integrating interactive data, and extending third-party integration as a goal, forming the basis of an enterprise data warehouse (EDW). ODS integrates general data, and serves as a source of data for output and display. It plays an important role in the system. In MES2.0, it mainly stores relational data, and does not involve static data like PRM, or PHD. Instead of real-time data, data is extracted and stored in a fixed business settlement unit.
ODS of MES not only possesses the performance of traditional ODS, but also takes into account the functions of some DWs. It can be said to be a complex between the two. Correspondingly, to meet the needs of regional companies for integrated services and system performance optimization, It also sacrifices some of its features.
While properly controlling data granularity, DW's dimensions and facts are stored in the data to maintain the frequency and stability of the data. In principle, as a read-only library, the system performance has also been greatly improved, not only satisfying the enterprise. Assisting in decision-making, it also supports the needs of middle-level business management. In order to satisfy the retention of historical data and taking into account of system performance, data table splitting is performed. The data retention period in the ODS table can be modified through configuration, and the data exceeding the deadline is archived in the historical data table. Further follow-up is based on the implementation process. Problems are added to the ODS.
V. Status of ODSDuring the overall design stage, ODS sent emails that collected ODS integration data to other modules included in the scope of the MES. After feedback from the overall group, the ODS was designed and developed. At present, ODS includes MES material balance, production scheduling, and public works. The four modules of measurement management, a total of 42 dimensions and fact tables, 6 views, 1 stored procedure, 5 header packages, 26 planned tasks, and 1 sequence.
Table 4 Status of ODS
The ODS data comes from the basic business database, which is extracted and processed by the ETL and stored in the ODS. After further integration processing, the ODS data is provided to the production statistics report and related display modules.
Figure 1 ODS architecture
Seven, ODS construction principles and methods
Figure 2 The general process of creating data objects in ODS
Currently, the ODS table and service data table of the MES are in the same database, and the ODS will be migrated to a separate table space in the future. The dimension table in the ODS is pushed in a full amount manner, 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 the service data is obtained through the service data. The date of modification in the field is used to obtain the date and time of the data in the local push time period from the time of the last push. After the data is pushed, the status of the log is modified. Data push and modify the log status must be written in the same transaction, if there is an abnormal rollback, and modify the log state. After the stored procedure of the push data is created, the stored procedure of pushing data of each module is put into a package.
Figure 3 Data Push
operation
Create a JOB to define the frequency of data extraction;
Define the execution time of JOB according to the actual situation, and complete the automatic data extraction;
The test can be manually performed manually.
Archiving
Each fact table must be corresponding to the creation of a historical data archive table;
Create an archive configuration table and configure archive records;
Create JOB call archive save process, complete regular automatic archiving.
optimization
Clustered index: to ensure the integrity of the entity; speed up the operation of the database.
Figure 4: ODS Cases of Utility Power Flow Data
Non-clustered index: greatly speed up the retrieval of data; ensure the uniqueness of data records; speed up the connection between tables and tables, and achieve referential integrity between tables and tables; when using grouping and sorting clauses for data retrieval, You can reduce the time spent grouping and sorting in queries.
View: easy to query, simplify data operations, improve data security.
Indoor Outdoor Fiber Optic Cable,Indoor Outdoor Fiber,Indoor Fiber Cable,Indoor Fiber Optic Cable Types
Zhejiang Wanma Tianyi Communication Wire & Cable Co., Ltd. , https://www.zjwmty.com