Key technologies of data warehouse for coal mine safety monitoring
-
摘要: 针对煤矿安全监控系统因采用操作型数据存储方法而导致无法有效利用海量数据，且数据分析能力较差等问题，研究了面向煤矿安全监控的数据仓库关键技术。根据煤矿安全监控业务需求，提出了煤矿安全监控数据仓库的功能结构，设计了超限分析、调校分析、异常数据分析、测点网络中断分析和人员管理分析五大业务主题。采用事实星座模型建立了煤矿安全监控数据仓库的逻辑模型，分主题设计了事实表和维度表，采用SQL Server建立了数据仓库物理模型。根据煤矿安全监控数据仓库特点，提出了数据抽取、转换和加载策略，采用不同的数据抽取规则分主题进行数据抽取，对不同来源的数据进行格式转换、清洗和排序，在数据加载过程中进行预加载、加载和加载后处理操作。Abstract: 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.
-
图 2 煤矿安全监控数据仓库功能结构
Figure 2. Functional structure of coal mine safety monitoring data warehouse
图 6 测点网络中断分析主题逻辑模型
Figure 6. Logical model of analysis subject for measuring point network interrupt
图 8 煤矿安全监控数据仓库ETL实现流程
Figure 8. ETL realization flow of coal mine safety monitoring data warehouse
图 9 煤矿安全监控数据仓库数据抽取流程
Figure 9. Data extraction flow of coal mine safety monitoring data warehouse
表 1 煤矿安全监控数据仓库主题
Table 1. Subjects of coal mine safety monitoring data warehouse
主题 主题描述 超限分析 对超限测点配置、超限程度、超限原因、超限测点的安全性等进行分析 调校分析 对调校测点配置、调校频率等进行分析 异常数据分析 对瓦斯浓度变化异常的时间、异常数据、异常原因、异常趋势等进行分析 测点网络中断分析 对测点配置及测点网络中断程度、原因等进行分析 人员管理分析 对人员工作职责和工作能力等进行分析 
下载: 导出CSV
表 2 超限分析主题事实表
Table 2. Fact table of overrun analysis subject
字段名称 字段描述 数据类型 MineName 矿名 Varchar(20) TpName 测点号 Varchar(10) Location 测点位置 Varchar(20) Type 类型名 Varchar(10) StartTime 超限开始时间 datetime EndTime 超限结束时间 datetime MaxValTime 超限最大值时间 datetime MaxVal 超限最大值 real LastTime 超限持续时间 real AverageVal 超限平均值 real Data 数据序列 Varchar(500) Reason 超限原因 Varchar(500) Staffnumber 值班人员编号 Real Operation 操作 Varchar(500)
下载: 导出CSV
表 3 超限分析主题测点维度表
Table 3. Monitoring point dimension table of overrun analysis subject
字段名称 字段描述 数据类型 MineName 矿名 Varchar(20) TpName 测点号 Varchar(10) Location 测点位置 Varchar(20)
下载: 导出CSV
表 4 超限分析主题类型维度表
Table 4. Type dimension table of overrun analysis subject
字段名称 字段描述 数据类型 Typebh 类型编号 Int Type 类型名 Varchar(10) WarnGate 预警门限 real AlarmGate 报警门限 real MaxR 量程最大值 real MinR 量程最小值 real Units 单位 Varchar(10)
下载: 导出CSV
表 5 超限分析主题原因维度表
Table 5. Reason dimension table of overrun analysis subject
字段名称 字段描述 数据类型 Reasonbh 原因编号 Int Reason 原因名称 Varchar(20)
下载: 导出CSV
表 6 超限分析主题操作维度表
Table 6. Operation dimension table of overrun analysis subject
字段名称 字段描述 数据类型 Operationbh 操作编号 Int Operation 操作名称 Varchar(20)
下载: 导出CSV
表 7 超限分析主题工作人员维度表
Table 7. Personnel dimension table of overrun analysis subject
字段名称 字段描述 数据类型 Peoplebh 人员编号 Int Name 姓名 Varchar(10) Department 部门 Varchar(10) Title 职称 Varchar(20) Capacity 工作能力 Varchar(50)
下载: 导出CSV
-
[1] 王国法,王虹,任怀伟,等. 智慧煤矿2025情景目标和发展路径[J]. 煤炭学报,2018,43(2):295-305.WANG Guofa,WANG Hong,REN Huaiwei,et al. 2025 scenarios and development path of intelligent coal mine[J]. Journal of China Coal Society,2018,43(2):295-305. [2] 新华社.国家矿山安监局: 2020年全国煤矿无重特大瓦斯事故[EB/OL]. （ 2021-01-08）[2022-01-03]. http://www.xinhuanet.com/2021- 01/08/c_1126961922.htm.Xinhua News Agency. China State Administration of Work Safety: there is no heavy and large gas accident in China in 2020[EB/OL]. （ 2021-01-08）[2022-01-03]. http://www.xinhuanet.com/2021-01/08/1126961922.htm. [3] DZEMYDIEN D,MASKELIUNAS S,RADZEVIIUS V. Approach of ensuring interoperability of multi-dimensional data warehouses for monitoring of water resources[J]. Journal of Environmental Engineering and Landscape Management,2021,29(1):9-20. doi: 10.3846/jeelm.2021.14112 [4] 何敏. 智慧矿山定义探讨[J]. 工矿自动化,2017,43(9):12-16.HE Min. Discussion on definition of wisdom mine[J]. Industry and Mine Automation,2017,43(9):12-16. [5] 张鹏. 智能矿山大数据体系建设探索[J]. 工矿自动化,2021,47(增刊1):21-23,44.ZHANG Peng. Exploration on construction of big data system for intelligent mine[J]. Industry and Mine Automation,2021,47(S1):21-23,44. [6] 孟光伟. 基于大数据技术的区域煤矿监管数据服务平台设计[J]. 工矿自动化,2021,47(10):97-102,109.MENG Guangwei. Design of a regional coal mine supervision data service platform based on big data technology[J]. Industry and Mine Automation,2021,47(10):97-102,109. [7] 张毅. 基于数据仓库原理的煤矿精细化管理研究[D]. 徐州: 中国矿业大学, 2014.ZHANG Yi. Study on coal mine management based on the principle of data warehouse[D]. Xuzhou: China University of Mining and Technology, 2014. [8] 贾国兵. 基于数据仓库的煤矿井下真三维建模与综合应用系统研发[D]. 沈阳: 东北大学, 2015.JIA Guobing. Development of real 3D modeling and integrated application system in coal mine downhole data based on the data warehouse[D]. Shenyang: Northeastern University, 2015. [9] 刘馨蕊. 矿山生产数据集成系统构建与应用研究[D]. 沈阳: 东北大学, 2013.LIU Xinrui. Research on construction and application of mine production data integration system[D]. Shenyang: Northeastern University, 2013. [10] 贾冬冬. 数据挖掘在冲击地压智能预警系统中的应用与研究[D]. 青岛: 山东科技大学, 2018.JIA Dongdong. The application and research of data mining in the impact ground pressure intelligent early warning system[D]. Qingdao: Shandong University of Science and Technology, 2018. [11] 侯杰,胡乃联,李国清,等. 基于OLAP的矿业集团生产运营决策系统构建研究[J]. 中国矿业,2016,25(11):11-15,27. doi: 10.3969/j.issn.1004-4051.2016.11.004HOU Jie,HU Nailian,LI Guoqing,et al. Construction of production operation & decision-making system for mining group based on OLAP[J]. China Mining Magazine,2016,25(11):11-15,27. doi: 10.3969/j.issn.1004-4051.2016.11.004 [12] BANERJEE S,BHASKAR S,SARKAR A,et al. A formal OLAP algebra for NoSQL based data warehouses[J]. Annals of Emerging Technologies in Computing,2021,5(5):154-161. doi: 10.33166/AETiC.2021.05.019 [13] 吴纪龙. 中医药大数据资源数据仓库构建及处方分析应用研究[D]. 北京: 北京交通大学, 2021.WU Jilong. TCM big data resources data warehouse construction and prescription analysis application research[D]. Beijing: Beijing Jiaotong University, 2021. [14] 雷博文. 基于大数据的实时数据仓库的设计与实现[D].北京: 中国地质大学（北京）, 2021.LEI Bowen. Design and implementation of real-time data warehouse based on big data[D]. Beijing: China University of Geosciences（Beijing）, 2021. [15] 许诗怡. 森林资源数据仓库管理系统研建与数据分析应用技术研究[D]. 北京: 北京林业大学, 2020.XU Shiyi. Research on forest resource data warehouse management system and data analysis and application technology[D]. Beijing: Beijing Forestry University, 2020. [16] 李伟超. 停车管理数据仓库构建与可视化分析[D]. 西安: 西安电子科技大学, 2020.LI Weichao. Data warehouse construction and visual analysis of parking management[D]. Xi'an: Xidian University, 2020. -
点击查看大图
计量
- 文章访问数: 326
- HTML全文浏览量: 97
- PDF下载量: 44
- 被引次数: 0
