面向煤矿安全监控的数据仓库关键技术

Key technologies of data warehouse for coal mine safety monitoring

针对煤矿安全监控系统因采用操作型数据存储方法而导致无法有效利用海量数据,且数据分析能力较差等问题,研究了面向煤矿安全监控的数据仓库关键技术。根据煤矿安全监控业务需求,提出了煤矿安全监控数据仓库的功能结构,设计了超限分析、调校分析、异常数据分析、测点网络中断分析和人员管理分析五大业务主题。采用事实星座模型建立了煤矿安全监控数据仓库的逻辑模型,分主题设计了事实表和维度表,采用SQL Server建立了数据仓库物理模型。根据煤矿安全监控数据仓库特点,提出了数据抽取、转换和加载策略,采用不同的数据抽取规则分主题进行数据抽取,对不同来源的数据进行格式转换、清洗和排序,在数据加载过程中进行预加载、加载和加载后处理操作。

Due to the adoption of operational data storage method,the coal mine safety monitoring system can’t use massive data effectively and the data analysis capability is poor.In order to solve the above problems,this paper proposes the key technologies of data warehouse for coal mine safety monitoring.According to the business requirements of coal mine safety monitoring,the functional structure of coal mine safety monitoring data warehouse is proposed.Moreover,the five business subjects are designed,including overrun analysis,calibration analysis,abnormal data analysis,measuring point network interruption analysis and personnel management analysis.The logical model of coal mine safety monitoring data warehouse is established by using the fact constellation model.The fact table and dimension table are designed by subject.The physical model of data warehouse is established by using SQL Server.According to the characteristics of coal mine safety monitoring data warehouse,data extraction,conversion and loading strategies are proposed.The different data extraction rules are used to extract data by subject.The data from different sources are processed through format conversion,cleaning and sorting.In the process of data loading,pre-loading,loading and post-processing operations are carried out.