Construction of Business Intelligence System Based on ERP
2021-04-15 21:01:33
With the progress of enterprise informatization, the application of ERP has become more and more extensive, and a military industrial enterprise has also completed the ERP management of modules such as inventory, production, procurement, sales, and finance. From the perspective of software, ERP is an advanced enterprise management information system that integrates the three major resources of enterprise logistics, capital flow and information flow. Its management content involves enterprise production control, logistics management and finance Management and other aspects. However, due to the limitations of its management ideas and implementation strategies, even a mature ERP system is not everything in the actual business application of the enterprise. ERP is an operation process-oriented information system. From a technical point of view, it is an online transaction processing system (OLTP, On-Line Transaction Process), which realizes the automation of business processes; from a management point of view, it breaks the corporate department The boundaries between them meet the needs of managers to master, plan, control and allocate all the resources of the enterprise. However, due to the limitations of the ERP system focusing on transaction processing, in the increasingly complex operation management of enterprises, the only very weak analysis function is difficult to meet the needs of enterprise managers to have a deep understanding of the company's operation status, and obtain timely and effective decision support . Therefore, due to the needs of management, enterprises are forced to
There is a need for a solution for analysis and decision-making, and business intelligence systems have emerged in response to this demand.
1. Business Intelligence
1.1 Business Intelligence Concept
Business Intelligence (BI) is abbreviated as BI. It was first proposed by Howard Dresner of Garther Group in 1989. It defines business intelligence as a type of data warehouse (or data mart), query report, data analysis, data mining, data backup and Recovery and other components, technologies and applications to help companies make decisions. BI technology provides technologies and methods to help companies quickly analyze data, including collecting, managing, and analyzing data, turning this data into useful information, and then distributing it throughout the enterprise for decision-makers to use. The realization process of business intelligence is the process of data storage and flow. The data flow of intelligent business system has the following parts:
(1) Data acquisition: source data collection, screening, sorting, conversion and storage.
(2) Data management: Mainly responsible for the internal maintenance and management of the data warehouse, which covers the organization of data storage, data maintenance, data distribution, data security, data extraction, data cleaning, data conversion, etc. Data is realized through data management Extraction, purification, filtering and data standardization.
(3) Data analysis: This stage is the stage of realizing the intelligence of the business intelligence system. It mainly uses online analytical processing and data mining technology to summarize and multi-dimensionally analyze the data extracted from the data warehouse to mine the hidden knowledge behind the data .
(4) Information display: It is to show the decision knowledge obtained by the above data analysis to users or enterprise managers to support management and decision-making.
1.2 Core technologies of business intelligence
(1) Data warehouse
A data warehouse is a subject-oriented, integrated, time-related, and unmodifiable data collection in enterprise management and decision-making. Unlike other database applications, the data warehouse is more like a process of integrating, processing, and analyzing business data distributed throughout the enterprise. The data warehouse is the foundation of business intelligence, and many basic reports can be generated from it, but its greater use is as a data source for further analysis.
(2) Online analysis and processing
Online analytical processing (OLAP) is the main application of the data warehouse system, supporting complex analytical operations, focusing on decision support, and providing intuitive and easy-to-understand query results. It can truly access high-speed, unified and interactive information for enterprise users to reflect all the characteristics of the enterprise, so as to obtain software technology for in-depth analysis of data.
(3) Data mining
Data Mining is the process of discovering and extracting hidden information or knowledge from large databases or data warehouses through data extraction, transformation, analysis (ETL) and other modeling processes. It has the functions of discovery and prediction .
2. Demand analysis of business intelligence system
Business intelligence is a powerful tool for enterprise decision analysis and the second stage of the enterprise data life cycle. The implementation of business intelligence relies on a large amount of real and effective data, and the ERP system is the best provider of this data source. Building business intelligence on the basis of ERP system raw data, making discrete business data related to each other according to subject needs, and storing it in the data warehouse in a structure that is easy to extract and query, allowing users to quickly analyze according to different perspective methods.
Determining the business requirements of the business intelligence system, that is, analyzing the theme, is the first step in the successful implementation of the system. The business requirements of the system are defined based on the business requirements of a military industrial enterprise and the basis of ERP implementation:
(1) Inventory theme
Analysis of inventory fund occupancy: analysis of fund occupancy of raw materials storage center, workshop secondary warehouse, finished product center and other warehouses; analysis of inventory fund backlog: analysis of raw material storage center, workshop secondary warehouse, finished product center and other warehouses Age analysis.
(2) Production plan completion theme
Analysis of the completion of the production plan: analysis of the completion of the production tasks of each branch and each main system unit.
(3) Supporting themes
Tracking analysis of engine delivery progress: analysis of the annual plan, delivery status, and in-service status of the whole machine; analysis of the progress of each key component and part: support for short board query, can use the existing system production cycle, after querying short board parts, push back Delivery time of the engine; analysis of the completion of the internal support of the engine (based on continuous units); analysis of the condition of the external support of the engine: analysis of the progress of each key part and part, which can support the rapid inquiry of short-board parts After the short board parts are reached, the delivery time of the engine is reversed.
(4) WIP theme
Analysis of WIP: Analysis of WIP occupancy. Analysis of WIP level = (beginning at the beginning of the period + input this month) / (completed in the month + scrapped).
(5) Sales theme
Sales and delivery analysis: analysis of sales and delivery.
(6) Procurement theme
Purchasing analysis: Analyze the purchase arrival situation.
(7) Financial theme
Financial statement analysis: balance sheet analysis, profit and loss statement analysis, cash flow statement analysis; comprehensive operation analysis: income analysis, cost analysis, expense analysis, inventory analysis, and other business analysis.
3. Design and Implementation of Business Intelligence System
3.1 Overall system architecture design
Based on the above demand analysis, the following overall architecture design is given for a military enterprise business intelligence system, as shown in Figure 1.
Figure 1 Overall architecture design of business intelligence system
A military industrial enterprise business intelligence system uses the ERP system as the basic data source. The data is collected from the ERP system inventory, procurement, sales, production, finance and other major module business data, as well as the secondary development of summary and statistical data, and the data is standardized After that, it is extracted from the system in a uniformly defined format, then it is cleaned, converted and calculated, and then loaded into the data warehouse of the system to form the basic data layer of the system. The basic layer data is then filtered and summarized into the data mart or multi-dimensional database to form a derived data layer. Then modeling is carried out according to the analysis requirements, using OLAP tools and data mining tools to analyze the data of the derived data layer, find out the hidden patterns in the data, and finally display the analysis results through the visual interface to realize the function of the ERP business intelligence system.
3.2 Detailed system solution
A military industrial enterprise's business intelligence system selected Oracle's business intelligence Oracle BIEE suite as an intelligent analysis tool, and ODI as ETL (Extract, Transform,
Load) tool. First, use ODI to extract, clean, convert and calculate the data, and then import it into the BIEE database, which is what we call a data warehouse, for data modeling. This modeling process is implemented in Oracle BIEE through the BI Administration Tool , Used to analyze the data imported into the BIEE database. Finally, create a report on the BIEE presentation end, use appropriate data analysis tools to analyze the data, realize automatic summary, classified query and graphical analysis of historical data (including histograms, pie charts and line charts, etc.), and realize roll up and drill down And OLAP analysis of slices, as well as fixed-point early warning and comparative analysis and prediction. On the BIEE server side, different viewing permissions are designed according to the permissions of different personnel. The data is processed by the business intelligence BIEE system to form a multi-level, multi-dimensional, flexible and dynamic final analysis result.
Specifically divided into the following steps to achieve:
(1) Data preparation
Extract the data in the ERP database. There are not only the original business data from the basic tables of the ERP system, but also the data written to the temporary table by writing a program package according to the actual needs of the analysis subject, or the view created by writing SQL, and these data are jointly used as business The basic data source of the intelligent system is used for the next data management.
(2) Data management
Data management is the process of data ETL (Extract, Transform, Load), namely extraction, conversion, and loading. Extraction: Extracting the required data from the source system, these source systems can be homogeneous or heterogeneous, and the target system is usually a relational data warehouse. Conversion: The data of the source system is converted into the format required by the target system according to the analysis purpose. The process is data cleaning and data processing. Load: Load the converted data to the target database. As the basis for online analysis and data mining. The entire ETL process is like constructing a pipeline between the source system and the target system. Data flows continuously in this pipeline. Here we use ODI as the EEE tool of the system to import data into the BIEE database. After the data is imported, define the ETL plan of the data, and periodically extract the data and write it to the data warehouse.
(3) Data analysis
The structure of the data model is created by the Administration Tool of BIEE, and the dimension table and the fact table are established in the BIEE database. The dimension table is a structure to classify the data to observe the data from a specific perspective; fact The table is what we are concerned about. Establish a fact table star relationship diagram to analyze the data of the imported BIEE database. Build the model to create the physical layer, logical layer and presentation layer.
(4) Information display
The front-end report is displayed through a WEB browser, and the graphical interface is friendly. Business personnel, not developers, can easily complete the design of the report through BI-Answer. It supports multiple display methods including graphics, reports, cross-tabs, and report display. Finally, BI-Dashboard is used to complete the design of the display dashboard, providing a personalized display page for information display.
3.3 Data security design
3.3.1 Data security group design
Data access security control can control the range of data accessed by different types of people. The data security group establishes groups and levels in the BIEE data model, and assigns data security to groups at different levels.
3.3.2 Functional safety group design
The functional security control settings can control the permissions of different types of people to access the report. The functional security group is set on the BIEE presentation server, and different access permissions are restricted by the group.
3.3.3 User security design
User security is controlled by the data security group and the functional security group. By hooking users under different groups, different users can access different functions and different data ranges.
4. Conclusion
Through the implementation of a military enterprise ’s business intelligence system, business intelligence technology is applied on the basis of the ERP system, so that isolated and scattered business data are related to each other in historical order, and stored in an efficient and easy-to-query structure for enterprise users You can quickly analyze according to different query methods to get the data information you need to use. It can be seen that the ERP system provides a wealth of data sources to the business intelligence system. At the same time, the ERP system also needs to use business intelligence data analysis tools to analyze and integrate the original data, and the complete closed-loop system that constitutes decision-making and execution turns the data into pairs. The beneficial information and knowledge of the enterprise, thereby improving the optimal use of the company's information, provides a favorable basis for decision-making for enterprise decision-makers, reduces decision-making risk, and greatly improves the competitiveness of the enterprise.
Quality series caster
Ningbo Mywin Caster Co., Ltd. , https://www.mywin-caster.com