Scheme And Design Of Database System For Qidashan Milling Plant

With advance of the strategy of"Propelling industrialization by information"in our country, the information developing level plays an important role in mining enterprises for enhancing its competition by raising its productivity. In Qidashan Plant, an integrated managing and controlling system has been built up, in which, various data from working site are collected and integrated by means of the combined information technologies and then provided to the upper ERP system for further analysis on different index to facilitate the optimization of production process and provide a reference for the managers to make strategic decision. Therefore, the collection and analysis of the data become the fundamental part of information system, and this function can be realized by the technology of database. Being a kind of dynamical data assembly, the data warehouse serves for the motive of the enterprise for its management controlling and decisions making. It is a developing trend and a prevalent search subject of using the technology of database to manage and analyze the dynamic information to realize the consequent result of integration of managing and controlling.In this paper, according to the researches on the different requirements and the analysis on the problems existing in the information system in Qidashan plant, a functional framework system on integrated management and control has been established by using the base theory of database, furthermore, a cost analysis system has also been established by designing a database, ETL and OLAP model. Based on the above work, the main study results of this paper are summarized as follow:(1) Establishment of a functional framework of the integrated managing and controlling system for Qidashan plant.The establishing system for Qidashan Plant is required to realize the function of making full use of various data sources and provide in-situ information on production and business for different management level (directors of plant and workshop) and the other related departments. So the designed system is required to include the functions of providing service for both the directors of plant and those of workshops. Using dynamic database, Yongyou U8 and production support system as data source platform, as well as using the finished ore production, cost and benefit as analysis theme, the designed system can realize the function of database maintenance, cost, benefit and output analysis, etc..(2) The basic scheme of database system for Qidashan PlantThe database system is required to be schemed according to the available conditions of technology, management and research and should run following the process of project start, requirement analysis and theme verification for setting up data model, ETL control and data analysis. It is the fundamental task to verify the theme of data warehouse which is driven by data, and such verification must satisfy the requirement for business data analysis. So three themes are verified in this paper, concerning cost analysis, production management and equipment management. Among them, the theme of cost analysis is determined for model buildup and analysis. After a careful comparsions of different softwares, the technique of Microsoft corporation is chosen for building up the database system, i.e., using Windows2000 Datacenter Server as data platform, Microsoft SQL 2000 Server Enterprise system as database server, Data Transformation Service (DTS) as transformation platform to realize the functions of data import, export and conversion, MS SQL Server 2000 Analysis Services as OLAP multi-data analysis tool.(3) The model design of data warehouse for Qidashan PlantEstablishing a database model is the first step of the database system, a correct and perfect data model is the necessary of customers and is the key factor for building up a successful database system. The designed data model for Qidashan Plant is varied into three series, i.e., conception model, logic model and physics model, and accordingly, the system structure consists of the units of collective storage and centralized management, and the designed function can be realized by running the program followed by ascending order, i.e., beginning from rendering theme and setting up data set, and finally developing a combined construction of data warehouse plus accessory data set (multi-dimensional data). The data set of cost is obtained from two ways, one is from the dynamic business system, i.e., the dynamic data of production, the other is from the summarizing data of the plant. The main cost of the plant consists of the consumptions of production process, spare parts, and various materials (including the rated and variable consumption). The data of production cost is obtained from the teams and groups of workshops and the data of spare parts is acquired from the records of the machine operators. The data warehouse is characterized by a kind of radial model which contains less connections for information search, and thus resulting in an easy management. The radial model includes two logical bodies: one is an actual data table, the other is a dimensional table (including in raw ore, electric power, supplementary materials, salary and welfare, manufacture cost, job-step and work time). Each dimensional table can be linked to the actual one by a principal bond, and the data is stored by RAID5 disk array.(4) The design of ETL and OLAP unit of database system for Qidashan PlantThe ETL tool is mainly used to establish and maintain the actual and dimensional data table, and the data transportation from different professional data source to the data warehouse can be realized by means of the fully developed ETL technology. In this paper, the function of the related data collection, conversion and load is accomplished by using MS DTS and application programming technologies. The standard relational data warehouse is converted into a radial model via three steps, including the definition of measurement and dimension, design of the dimensional and the actual tables. The data sources of the designed database system contains some more complicated data format, such as Microsoft SQL Server, Oracle and Microsoft Access data warehouses, which can be well supported by DTS system. The data format and modification procedures are carried out with DTS, and then, the handled data can be transported to the data warehouse to form a multi-dimensional data set to be analyzed by means of Analysis Services technique.(5) The development of cost analysis system for Qidashan PlantA cost analysis system is developed by using the technologies of DELPHI and SQL Server in this paper. In this system, three modules of systematic management, cost analysis and report formation are established, and a functional configuration with different dimensional tables, such as the actual production cost table, time table, job-step table, raw ore table, salary and welfare table, supplementary material table, electric power table and manufacture cost table, is designed to accomplish the cost analysis for every workshop per month. Therefore, the useful information on direct materials, employment and manufacture cost are obtained and provided to the directors for making correct and on-time decisions.