Intelligent coal mine data warehouse modeling method
-
摘要: 煤矿海量数据存在“数据孤岛”、关联性弱、因缺乏数据管理体系而导致数据质量差等问题，难以充分利用，无法为煤矿智能化提供分析决策支撑。数据仓库可满足煤矿多源异构数据集成需求，为煤矿智能化应用提供数据基础。通过分析煤矿数据类型、特点及实际数据智能化应用需求，研究了智能化煤矿数据仓库建模方法。首先，构建了智能化煤矿数据仓库分层架构，分析了原始数据层、明细数据层、基础指标层、服务数据层、公共维度层数据模型特点；其次，以综采工作面数据为例，从业务数据分析、应用需求分析、分层架构设计等方面阐述了数据仓库建模过程；再次，介绍了煤矿数据仓库中数据模型构建方法，即通过维度对齐、维度关联、维度化指标聚合等将原始数据转换为数据仓库维度模型，解决了不同维度的煤矿数据关联应用问题；最后，为解决煤矿数据仓库的可迁移性问题，提出了煤炭行业通用数据仓库+参数化ETL（抽取、转换、加载）方法的煤矿参数化数据仓库设计思路。在实验室环境下搭建了煤矿数据仓库平台，对山西天地王坡煤业有限公司综采工作面数据进行处理，并基于处理数据辅助机理模型分析、实现可视化管理驾驶舱，验证了智能化煤矿数据仓库的实用性；对比了原始数据模型与智能化煤矿数据仓库的性能指标，结果表明智能化煤矿数据仓库的数据组织度、模型复用度和迭代难易度均优于原始数据模型，且数据查询响应时间缩短50%以上。Abstract: The coal mine massive data has problems such as 'data island', weak correlation, poor data quality due to lack of data management system. It is difficult to make full use of the data and provide analysis and decision-making support for coal mine intelligence. The data warehouse can meet the requirements of multi-source heterogeneous data integration in coal mine, and provide data basis for intelligent application in coal mine. By analyzing the coal mine data types, characteristics and intelligent application requirements of actual data, the intelligent coal mine data warehouse modeling method is studied. Firstly, the layered architecture of intelligent coal mine data warehouse is constructed, and the characteristics of data model of original data layer, detailed data layer, basic index layer, service data layer and public dimension layer are analyzed. Secondly, taking the data of fully mechanized working face as an example, the modeling process of data warehouse is expounded from the aspects of business data analysis, application demand analysis and layered architecture design. Thirdly, the construction method of data model in coal mine data warehouse is introduced. The original data is transformed into data warehouse dimensional model through dimension alignment, dimension association and dimensional index aggregation. The method solves the application problem of coal mine data association in different dimensions. Finally, in order to solve the problem of portability of coal mine data warehouse, the design idea of coal mine parametric data warehouse based on general data warehouse in coal mine industry + parametric ETL (extraction-transformation-load) method is proposed. The platform of coal mine data warehouse in the laboratory environment is set up to process the data of fully mechanized working face of Shanxi Tiandi Wangpo Coal Industry Co., Ltd. The auxiliary mechanism model analysis and visual management cockpit are realized based on the processing data, which verifies the practicability of intelligent coal mine data warehouse. The performance indexes of the original data model and the intelligent coal mine data warehouse are compared. The results show that the data organization, model reuse and iteration difficulty of the intelligent coal mine data warehouse are better than those of the original data model, and the data query response time is shortened by more than 50%.
-
图 3 智能化煤矿数据仓库分层架构
Figure 3. Layered architecture of intelligent coal mine data warehouse
图 5 综采工作面智能化应用分析主题
Figure 5. Intelligent application analysis subjects of fully mechanized working face
图 6 综采工作面数据仓库分层架构
Figure 6. Layered architecture of data warehouse of fully mechanized working face
图 11 液压支架压力随割煤变化规律
Figure 11. Changes law of hydraulic support pressure with coal cutting
表 1 综采工作面核心数据分类
Table 1. Classification of kernel data of fully mechanized working face
数据来源 数据描述内容 采煤机 采煤机位置、机身俯仰角及倾斜角、左右牵引速度、左右滚筒高度、温度、电流等 刮板输送机 电流、电压、闭锁状态、转速、启停、故障信息等 转载机 电流、电压、闭锁状态、转矩、转速、启停、故障信息等 破碎机 电流、电压、闭锁状态、速度、启停、故障信息等 带式输送机 电流、电压、温度、转速等 液压支架 压力、行程、跟机动作、人工操作信息等 泵站 乳化液泵、喷雾泵的电流、电压、温度、转速等 
下载: 导出CSV
表 2 综采工作面采煤机位置数据
Table 2. Location data of shearer in fully mechanized working face
数据类别 标签 时间 数据值 数据状态 采煤机 位置架 2019−12−06T17:12:42.819 13 Good 采煤机 位置架 2019−12−06T17:13:20.439 14 Good 采煤机 位置架 2019−12−06T17:13:46.658 15 Good 采煤机 位置架 2019−12−06T17:14:07.437 16 Good 采煤机 位置架 2019−12−06T17:14:39.942 17 Good
下载: 导出CSV
表 3 割煤阶段维表数据结构
Table 3. Dimension table data structure of coal cutting stage
割煤
刀号方向 开始
架号开始时间 结束
架号结束时间 110 1 6 2020−04−28T05:40:41 200 2020−04−28T16:06:08 111 2 200 2020−04−28T16:06:08 6 2020−04−28T19:32:38 112 1 6 2020−04−28T19:32:38 200 2020−04−28T22:29:13 113 2 200 2020−04−28T22:29:13 7 2020−04−29T02:02:37
下载: 导出CSV
表 4 液压支架循环动作阶段维表数据结构
Table 4. Dimension table data structure of cyclic hydraulic support action
割煤刀号 架号 动作阶段 开始时间 结束时间 140 70 1 2020−05−05T01:08:18 2020−05−05T01:08:23 140 70 2 2020−05−05T01:08:23 2020−05−05T01:08:28 140 70 3 2020−05−05T01:08:23 2020−05−05T01:13:09
下载: 导出CSV
表 5 原始数据模型与煤矿数据仓库的定性对比
Table 5. Qualitative comparison between primary data model and coal mine data warehouse
指标 原始数据模型 煤矿数据仓库 数据
组织度按照数据来源组织，与
业务过程缺乏关联性按照数据域、业务过程、业务事实进行
组织，便于从业务角度理解数据模型
复用度统计分析过程基于原始
数据，逻辑实现复杂，无
法重复使用提供多层级数据模型，统计分析过程基
于服务数据层查询，新增数据可不断沉
淀到基础指标层，实现模型复用迭代
难易度数据模型随业务系统变
更，按照业务分析需要
迭代根据需求类型支持不同层级迭代，数据
源变化可迭代明细数据层，指标变化可
迭代基础指标层
下载: 导出CSV
表 6 原始数据模型与煤矿数据仓库的定量对比
Table 6. Quantitative comparison between primary data model and coal mine data warehouse
数据计算指标 查询响应时间/min 原始数据模型 煤矿数据仓库 设备能耗（粒度：1 h，跨度：48 h） 5~10 ＜1 设备能耗（粒度：1 d，跨度：30 d） ＞10 1~3 设备运行时长（粒度：1 d，跨度30 d） 5~10 1~3 数据上传量（粒度：1 h，跨度：48 h） 5~10 ＜1 工作面开机率（粒度：1 d，跨度：30 d） 5~10 1~3 工作面割煤量（粒度：1 d，跨度：7 d） ＞10 1~3 工作面推进度（粒度：1 d，跨度：7 d） ＞10 1~3 矿压分布（粒度：1 s，跨度：1 d） ＞10 3~5 液压支架支护时长（粒度：架号+1 s，
跨度：48 h）＞10 1~3 采煤机循环时长（粒度：割煤刀号+1 s，
跨度：48 h）＞10 1~3
下载: 导出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] 韩安. 基于Hadoop的煤矿数据中心架构设计[J]. 工矿自动化,2019,45(8):60-64.HAN An. Architecture design of coal mine data center based on Hadoop[J]. Industry and Mine Automation,2019,45(8):60-64. [3] 毛善君,杨乃时,高彦清,等. 煤矿分布式协同“一张图”系统的设计和关键技术[J]. 煤炭学报,2018,43(1):280-286.MAO Shanjun,YANG Naishi,GAO Yanqing,et al. Design and key technology research of coal mine distributed cooperative "one map" system[J]. Journal of China Coal Society,2018,43(1):280-286. [4] 王国法,刘峰,孟祥军,等. 煤矿智能化(初级阶段)研究与实践[J]. 煤炭科学技术,2019,47(8):1-36.WANG Guofa,LIU Feng,MENG Xiangjun,et al. Research and practice on intelligent coal mine construction (primary stage)[J]. Coal Science and Technology,2019,47(8):1-36. [5] 高士岗,高登彦,欧阳一博,等. 煤矿智能一体化辅助生产系统及关键技术[J]. 煤炭科学技术,2020,48(7):150-160.GAO Shigang,GAO Dengyan,OUYANG Yibo,et al. Mine intelligent integrated auxiliary production system and key technologies[J]. Coal Science and Technology,2020,48(7):150-160. [6] 何敏. 智能煤矿数据治理框架与发展路径[J]. 工矿自动化,2020,46(11):23-27.HE Min. Framework and development path of data governance in intelligent coal mine[J]. Industry and Mine Automation,2020,46(11):23-27. [7] 李首滨. 煤炭工业互联网及其关键技术[J]. 煤炭科学技术,2020,48(7):98-108.LI Shoubin. Coal industry Internet and its key technologies[J]. Coal Science and Technology,2020,48(7):98-108. [8] 杜毅博,赵国瑞,巩师鑫. 智能化煤矿大数据平台架构及数据处理关键技术研究[J]. 煤炭科学技术,2020,48(7):177-185.DU Yibo,ZHAO Guorui,GONG Shixin. Study on big data platform architecture of intelligent coal mine and key technologies of data processing[J]. Coal Science and Technology,2020,48(7):177-185. [9] 吴群英,蒋林,王国法,等. 智慧矿山顶层架构设计及其关键技术[J]. 煤炭科学技术,2020,48(7):80-91.WU Qunying,JIANG Lin,WANG Guofa,et al. Top-level architecture design and key technologies of smart mine[J]. Coal Science and Technology,2020,48(7):80-91. [10] BOJICIC I, MARJANOVIC Z, TURAJLIC N, et al. A comparative analysis of data warehouse data models[C]//The 6th IEEE International Conference on Computers Communications and Control, Oradea, 2016: 151-159. [11] 曾志浩,姚贝,张琼林,等. 基于Hadoop平台的用户行为挖掘[J]. 计算技术与自动化,2015,34(2):100-103. doi: 10.3969/j.issn.1003-6199.2015.02.024ZENG Zhihao,YAO Bei,ZHANG Qionglin,et al. User behavior mining based on Hadoop platform[J]. Computing Technology and Automation,2015,34(2):100-103. doi: 10.3969/j.issn.1003-6199.2015.02.024 [12] 温国锋,陈立文. 煤矿安全管理数据仓库的建立与应用研究[J]. 中国矿业,2009,18(1):95-97. doi: 10.3969/j.issn.1004-4051.2009.01.027WEN Guofeng,CHEN Liwen. On building and applacation of coal mine security management data warehouse[J]. China Mining Magazine,2009,18(1):95-97. doi: 10.3969/j.issn.1004-4051.2009.01.027 [13] INMON W H, LINSTEDT D, ELLIOT S. Data architecture, a primer for the data scientist: big data, data warehouse and data vault[M]. Amsterdam: Morgan Kaufmann, 2015. [14] 赵随海. 铁路列车调度指挥系统数据仓库体系结构的研究[J]. 铁道运输与经济,2018,40(12):55-59.ZHAO Suihai. A study on the architecture of data warehouse for the railway train dispatching command system[J]. Railway Transport and Economy,2018,40(12):55-59. [15] STAVRAKAS Y,GERGATSOULIS M,DOULKERIDIS C,et al. Representingand querying histories of semistructured databases using multidimensional OEM[J]. Information Systems,2003,29(6):461-482. [16] 马宏伟,吴少杰,曹现刚,等. 煤矿综采设备运行状态大数据清洗建模[J]. 工矿自动化,2018,44(11):80-83.MA Hongwei,WU Shaojie,CAO Xiangang,et al. Big data cleaning modeling of operation status of coal mine fully-mechanized coal mining equipment[J]. Industry and Mine Automation,2018,44(11):80-83. [17] 高金标,何利力,邹云阳. 基于分布式存储系统的Hive与Hbase的研究[J]. 工业控制计算机,2015,28(12):44-45. doi: 10.3969/j.issn.1001-182X.2015.12.021GAO Jinbiao,HE Lili,ZOU Yunyang. Hive and Hbase based on research on hadoop distributed file system[J]. Industrial Control Computer,2015,28(12):44-45. doi: 10.3969/j.issn.1001-182X.2015.12.021 -
点击查看大图
计量
- 文章访问数: 697
- HTML全文浏览量: 221
- PDF下载量: 124
- 被引次数: 0
