Building industry approval flow analysis system and method based on offline warehouse counting

Abstract

The invention provides a building industry approval flow analysis system and method based on off-line warehouse counting, and relates to the technical field of building industry digital management. The system comprises a data source, a data acquisition layer, a data storage layer, a data processing layer, a data service layer and an application display layer. The data source is used for providing main data and business data; the data acquisition layer is used for acquiring main data and business data from a data source; the data storage layer is used for structuring, integrating and storing the main data and the business data; the data processing layer is used for processing and calculating the main data and the business data; the data service layer is used for externally providing interface service and processed and calculated wide table data; and the application display layer is used for data analysis and visual display. The method comprises the steps of data acquisition, data storage, data calculation and data service. The method has the beneficial effects that diversity of technical routes of each business system is avoided, and summarization analysis of huge approval flow data of the cross-business multi-business system is realized.

Description

Technical Field

[0001] The present invention relates to the technical field of digital management of the construction industry, and in particular to a construction industry approval flow analysis system and method based on an offline data warehouse. Background Art

[0002] The approval efficiency of each node in the approval process is an important consideration for the construction industry to optimize its business processes every year.

[0003] The construction industry has been informatized for many years, and each unit has established many management systems. These management business systems have their own approval processes, and the technical routes of each system are diverse. According to rough statistics, the number of approval processes initiated by this unit each year is about 4 million, the number of approvals is 20 million, and the scale of approval flow data is about 5G.

[0004] Due to the diversity of systems, differences in technical routes between systems and the very large scale of data, in recent years, the statistics of this indicator in our unit have been based on manual offline data statistics and calculations, which requires a lot of manpower and material resources at the end of each year.

[0005] A similar technical solution is to use ETL to extract the approval process data of each management system into a relational database, and then process and calculate the data on this database. The solution using a relational database is limited by the differences in database types between systems and the scale and size of the data. The amount of data in a single table is too large, making it difficult to store and process the data.

[0006] How to solve the above technical problems is the subject faced by the present invention. Summary of the invention

[0007] In order to address the deficiencies of the prior art, the present invention provides a construction industry approval flow analysis system and method that avoids the diversity of technical routes of various business systems and realizes the summary analysis of huge approval flow data across multiple business systems.

[0008] The technical solution adopted by the present invention to solve its technical problem is: on the one hand, the present invention provides a construction industry approval flow analysis system based on offline data warehouse, including data source, data acquisition layer, data storage layer, data processing layer, data service layer and application display layer;

[0009] The data source corresponds to the business library of each business system and is used to provide master data and business data;

[0010] The data collection layer is used to collect master data and business data from data sources;

[0011] The data storage layer is used to structure, integrate and store master data and business data, including an ODS layer of a first relational database and a Hive data warehouse;

[0012] The data processing layer processes and calculates the master data and business data in the Hive data warehouse through the Spark computing framework, including the DWD layer, DWS layer, DIM layer and ADS layer of the Hive data warehouse;

[0013] The data service layer is used to provide external interface services and process the calculated wide table data;

[0014] The application display layer is used for data analysis and visual display.

[0015] Preferably, the data collection layer includes collection via direct interface connection and collection via offline data collection tools;

[0016] Preferably, the first relational database of the data storage layer is used to store master data, and the ODS layer of the Hive data warehouse is used to store business data.

[0017] Preferably, the DWD layer of the Hive data warehouse is used to model the data that needs to be processed and calculated, and to construct wide tables; the DWS layer is used to statistically summarize the data according to certain dimensional information; the DIM layer is used to store dimensional information for classifying data, including master data information and parameter dimensional information; the ADS layer is used to store data statistical summary results under different dimensional information.

[0018] Preferably, the DWD layer, the DWS layer, the DIM layer and the ADS layer complete data push and synchronization through DataX.

[0019] Preferably, the data service layer is further provided with a second relational database for storing statistically summarized wide table data.

[0020] Preferably, the interface service provided externally by the data service layer includes at least any one of an API interface, a file interface and a message queue.

[0021] On the other hand, the present invention provides a construction industry approval flow analysis method based on offline data warehouse, including the following steps: S10, data acquisition: for the master data system that is not convenient to open the database directly, the master data is obtained by directly connecting to the API interface; for the business data system that can be connected, the Do l ph integrated by the big data platform is used Schedulers such as inScheduler are associated with ETL tools such as DataX to perform scheduled data extraction; S11, Data storage: Master data and business data are classified and stored, the collected master data is stored in the first relational database, and the business data is stored in the ODS layer of the Hive data warehouse; S12, Data calculation: Use DataX to extract the master data and business data that need to be processed and calculated, and model and construct wide tables for these data; Use SparkSQL to process the association relationship between business data and master data, and save it to the dimension table user relationship association table; Use SparkSQL to calculate the indicator data of business data, and store the calculated data in the DWS summary layer of the Hive data warehouse; The processed and calculated data is summarized according to certain dimensional information, and synchronized to the ADS layer of the Hive data warehouse through DataX; S13, Data service: Use DataX to synchronize the summarized data from the ADS layer of the Hive data warehouse to the second relational database of the data display layer, and perform visual analysis and display of the data externally through the interface services provided by the system.

[0022] The beneficial effects of the present invention are: avoiding the diversity of technical routes of various business systems, and realizing the summary analysis of huge approval flow data across multiple business systems. Using the offline data warehouse Hive data warehouse to store and process the approval flow data, the data storage stability and data processing efficiency are higher, multiple data formats are supported, and the maintenance and management costs are low. The Hive data warehouse can use Hadoop's HDFS to store data in a distributed manner on multiple nodes, and there will be no situation where the relational database is difficult to store when the amount of data in a single table is too large; at the same time, the Hive data warehouse adopts a distributed computing framework, combined with the Spark computing framework, which can process data in parallel, greatly improving the efficiency of data processing; the Hive data warehouse supports multiple data formats, and when processing and calculating the approval flow data, it can more conveniently process data from different sources and in different formats. For the master data system that is not convenient to open, the master data can be obtained by directly connecting to the API interface, which can better control the data access rights and effectively prevent the leakage of the master data; for the open business system, according to the actual business analysis needs and the load of the business system, the offline data collection tools and real-time data collection tools can be flexibly used to effectively ensure the timeliness of specific data while avoiding the burden on the business system due to frequent data collection. Master data is usually relatively stable and changes less frequently, while business data may change as the process progresses. Master data is stored in relational databases and business data is stored in Hive data warehouses. Classified storage can better ensure the validity and integrity of data. A variety of interfaces are provided at the data service layer to adapt to different external systems, enriching the application scenarios of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 This is a system architecture diagram of the construction industry approval flow analysis system of the present invention.

[0024] Figure 2 This is a core step diagram of the construction industry approval flow analysis method of the present invention.

[0025] Figure 3 This is a data warehouse logic diagram of an embodiment of the present invention. DETAILED DESCRIPTION

[0026] In order to clearly illustrate the technical features of this solution, this solution is described below through specific implementation methods.

[0027] See also Figures 1 to 3As shown, this embodiment provides a construction industry approval flow analysis system based on an offline data warehouse, including a data source, a data collection layer, a data storage layer, a data processing layer, a data service layer and an application display layer; the data source corresponds to the business library of each business system, providing master data and business data; the data collection layer is used to collect master data and business data from the data source; the data storage layer structures, integrates and stores the master data and business data, including a first relational database and an ODS layer of a Hive data warehouse, the first relational database of the data storage layer stores the master data, and the ODS layer of the Hive data warehouse stores the business data; the data processing layer processes and calculates the master data and business data in the Hive data warehouse through the Spark computing framework, including the DWD layer, DWS layer, DIM layer and ADS layer of the Hive data warehouse; the data service layer provides interface services and processed and calculated wide table data to the outside world; the application display layer is used for data analysis and visualization display.

[0028] The data collection layer's collection methods include interface direct collection and offline data collection tool collection. Interface direct collection uses API interface direct connection, and offline data collection tools use DataX.

[0029] The DWD layer of the Hive data warehouse models the data that needs to be processed and calculated, and constructs wide tables; the DWS layer summarizes the data according to certain dimensional information; the DIM layer stores dimensional information for classifying data, including master data information and parameter dimensional information; the ADS layer is used to store data statistical summary results under different dimensional information. Data is pushed and synchronized between the DWD layer, DWS layer, DIM layer, and ADS layer through DataX.

[0030] The interface services provided by the data service layer include at least one of an API interface, a file interface and a message queue. The data service layer is also provided with a second relational database for storing statistically summarized wide table data.

[0031] This embodiment also provides a construction industry approval flow analysis method based on an offline data warehouse, including the following steps:

[0032] S10. Data collection: For master data systems that are not convenient to open for direct database connection, the master data is obtained by direct connection with an API interface. The master data collected in this embodiment specifically includes a user information table, an organization table, and an organization-user relationship table, etc.; for business data systems that can be connected, schedulers such as DolphinScheduler integrated in the big data platform are used to associate with ETL tools such as DataX to perform regular data extraction. The business data collected in this embodiment specifically includes an old process definition table, a new process definition table, a new process deployment table, a process task table, a process information table, etc.

[0033] S11. Data storage: Master data and business data are stored in a classified manner. User information tables, organization tables, and organization-user relationship tables are synchronized to the first relational database through DataX for storage. Old process definition tables, new process definition tables, new process deployment tables, process task tables, and process information tables are synchronized to the ODS layer of the Hive data warehouse through DataX for storage.

[0034] S12, data calculation: synchronize the user information table, organization table, organization user relationship table, custom organization table and leaf node organization mapping table to the DIM layer through DataX and API interface program, and obtain relevant parameter data through SparkSql and create it as a dimension table, and store it in the DIM layer; associate the collected business data with the master data of the DIM layer, and after SparkSQL processing and calculation, obtain the unit personnel process detail data table, unit personnel process detail data summary table, single person average approval time statistics table, total approval times statistics table, total process generation number statistics table, overdue approval rate statistics table. The unit personnel process detail data table, unit personnel process detail data summary table, single person average approval time statistics table, total approval times statistics table, total process generation number statistics table, overdue approval rate statistics table are synchronized to the DWS layer through DataX, and the above data are further summarized and calculated according to certain dimensions using Spark programs and SparkSql. For example, process-related data under different business modules are summarized and counted, and year-on-year data are calculated. The unit personnel process detail statistics table, the number of new process definitions statistics table, the total number of process definitions statistics table, the total number of process generation percentage statistics table of each business module, the average approval time / overdue approval rate ranking table, the overdue approval rate statistics table of each business module, the total amount calculation year-on-year table, and the total amount statistics summary table are obtained. Finally, the above data are synchronized to the ADS layer through DataX.

[0035] S13, Data Service: Use DataX to synchronize the aggregated data from the Hive data warehouse to the second relational database of the data display layer, and perform visual analysis and display of the data externally through the interface services provided by the system.

[0036] Technical features not described in the present invention can be achieved through or by adopting existing technologies and will not be described in detail here. Of course, the above description is not a limitation of the present invention, and the present invention is not limited to the above examples. Changes, modifications, additions or substitutions made by ordinary technicians in this technical field within the essential scope of the present invention should also fall within the scope of protection of the present invention.

Claims

1. A construction industry approval flow analysis system based on offline data warehouse, characterized in that: It includes data source, data collection layer, data storage layer, data processing layer, data service layer and application display layer; The data source corresponds to the master database and the business database of each business system, and is used to provide master data and business data; The data collection layer is used to collect master data and business data from data sources; The data storage layer is used to structure, integrate and store master data and business data, including an ODS layer of a first relational database and a Hive data warehouse; The data processing layer processes and calculates the master data and business data in the Hive data warehouse through the Spark computing framework, including the DWD layer, DWS layer, DIM layer and ADS layer of the Hive data warehouse; The data service layer is used to provide external interface services and process the calculated wide table data; The application display layer is used for data analysis and visual display.

2. The construction industry approval flow analysis system based on offline data warehouse according to claim 1 is characterized in that: The data collection layer uses the following collection methods: interface direct collection and offline data collection tool collection.

3. The construction industry approval flow analysis system based on offline data warehouse according to claim 1 is characterized in that: The first relational database of the data storage layer is used to store master data, and the ODS layer of the Hive data warehouse is used to store business data.

4. The construction industry approval flow analysis system based on offline data warehouse according to claim 1 is characterized in that: The DWD layer of the Hive data warehouse is used to model the data that needs to be processed and calculated and to construct wide tables; the DWS layer is used to statistically summarize the data according to certain dimensional information; the DIM layer is used to store dimensional information for classifying data, including master data information and other parameter information; the ADS layer is used to store data statistical summary results under different dimensional information.

5. The construction industry approval flow analysis system based on offline data warehouse according to claim 1 is characterized in that: The DWD layer, the DWS layer, the DIM layer and the ADS layer complete data push and synchronization through DataX.

6. The construction industry approval flow analysis system based on offline data warehouse according to claim 1 is characterized in that: The data service layer is also provided with a second relational database for storing statistically summarized wide table data.

7. The construction industry approval flow analysis system based on offline data warehouse according to claim 1 is characterized in that: The interface service provided by the data service layer to the outside includes at least any one of an API interface, a file interface and a message queue.

8. A construction industry approval flow analysis method based on offline data warehouse, characterized in that: The following steps are involved: S10, Data collection: Use direct interface connection to obtain master data, and use offline data collection tools to obtain business data; S11. Data storage: Classify and store master data and business data, store the collected master data in the first relational database, and store the business data in the ODS layer of the Hive data warehouse; S12, Data Calculation: Use DataX to extract the master data and business data that need to be processed and calculated, and model these data and construct wide tables; use SparkSQL to process the relationship between business data and master data, and save them in the dimension table user relationship association table; Use SparkSQL to calculate the indicator data of business data and store the calculated data in the DWS summary layer of the Hive data warehouse; summarize the processed and calculated data according to certain dimension information and synchronize it to the ADS layer of the Hive data warehouse through DataX; S13, Data Service: Use DataX to synchronize the calculated and summarized data from the ADS layer of the Hive data warehouse to the second relational database of the data display layer, and perform visual analysis and display of the data externally through the interface service provided by the system.

Images