基于数据仓库星形模式的广东省高速公路一张网资金结算情况分析系
摘要 星形模式是基于关系数据库的数据仓库中的一个著名概念，由于星形连接模式的设计思想能够满足人们从不同观察角度（维）分析数据的需求，所以在基于关系数据库的数据仓库的设计中广泛地使用了星形模式。本文主要介绍《广东省高速公路一张网资金结算情况分
摘要 星形模式是基于关系数据库的数据仓库中的一个著名概念，由于星形连接模式的设计思想能够满足人们从不同观察角度（维）分析数据的需求，所以在基于关系数据库的数据仓库的设计中广泛地使用了星形模式。本文主要介绍《广东省高速公路一张网资金结算情况分析系统》中的数据仓库技术。
关键词 数据仓库；星形模式；维度
1 数据仓库的特点
数据仓库技术是用以更好地支持企业或组织的决策分析处理的，面向主题的，集成的，不可更新的、随时间不断变化的数据集合。它通过将数据按照不同的综合程度（即粒度）来组织，以满足不同分析的需要。
本文主要介绍一下《广东省高速公路一张网资金结算情况分析系统》中如何使用数据仓库技术来实现“异常流水”与其修改量分析与“流水上传与拆分”分析这两个主题。
2 星形模式
星形模式是基于关系数据库的数据仓库中的一个著名概念，由于星形连接模式的设计思想能够满足人们从不同观察角度（维）分析数据的需求，加上数据仓库通常用来回答综合性的问题，所以在基于关系数据库的数据仓库的设计中广泛地使用了星形模式，如电信运营商中普遍进行的话务总体分析。
3 系统中的数据仓库设计
3.1 广东联合电子结算中心操作型环境 DB2 数据库服务器下的数据组织情况
在广东联合电子结算中心， “异常流水”、“流水拆分”、“流水上传”有关的关系模式
l 粤通卡精确拆分结果明细表
tb_cardaccuratesplitresult (流水号,工班, 出口路段…, 收入路段)
l 现金精确拆分结果明细表
tb_cashaccuratesplitresult (流水号,工班, 出口路段…,收入路段)
l 流水非精确拆分记录表
tb_inaccuratesplitrecord (流水号,工班, 出口路段…, 收入路段)
l 拆分流水异常记录表
tb_outlistspliterrorgb(流水号,工班,出口路段, …,错误类型代码)
l 拆分流水异常记录历史表
tb_outlistspliterrorgb_his(流水号,工班,出口路段, …,错误类型代码)
l 流水异常记录表
tb_outlisterrorgb(流水号,工班,出口路段, …,错误类型代码)
l 流水异常记录历史表
tb_outlisterrorgb_his(流水号,工班,出口路段, …,错误类型代码)
l 出口流水表
tb_outlistgb (流水号,工班,出口路段, …,上传时间)
3.2 系统的中数据仓库设计
考虑到篇幅，各步骤经过简化如下：
l 概念模型设计
l 逻辑模型设计
l 数据仓库生成
3.2.1概念模型设计中的界定系统边界
在广东电信公话中心，管理者最迫切进行的分析主要有：
l 各高速公路路段公司不同工班的流水上传、流水拆分。
l 分析各个高速公路路段公司上传的异常流水（包含异常及拆分异常）、及其修改的情况；
要进行以上的分析，所需数据应包括：
l 分析各个高速公路路段公司在一段周期内的流水上传、拆分、异常总量、异常修改情况。
l 分析各个高速公路路段公司上传的异常流水（包含异常及拆分异常）、及上传的异常的流水（包含异常及拆分异常）流水的修改情况；
l 各个高速公路路段公司及他们的软件开发商
l 广东省高速公路所有的路段信息；
3.2.2 逻辑模型设计
因为细节级数据太大，数据仓库又建立在本人的PC机上 MS SQL SERVER 服务器上，加之迫于分析任务提交时间的压力，故本数据仓库中不再涉及细节级数据表的设计、数据挖掘。
关系模式定义：
ok_xiugai (LISTNO, UPLOADTIME, OUTROADNO, SQUADDATE, ERRCODE, ERRMSG…)
no_xiugai (LISTNO, UPLOADTIME, OUTROADNO, SQUADDATE, ERRCODE, ERRMSG…)
upload_split(路段编码, 路段名称, 工班, 上传流水数量, 拆分流水数量…)
kaifa (roadno, kaifa_name)
tb_road(areano,roadno,roadname,…)
3.2.3 数据仓库数据生成
数据仓库中数据的生成包括操作型环境数据的挖掘和数据仓库中数据的生成这两部分的内容。在这一步里所要做的工作是接口编程，以实现将操作型环境下的数据装载进入数据仓库环境。
4 数据仓库的数据导出与展现
4.1 万能数据库查询分析器
万能数据库查询分析器，中文版本《DB 查询分析器》、英文版本《DBQuery Analyzer》。它具有强大的功能、友好的操作界面、良好的操作性、跨越各种数据库平台乃至于EXCEL和文本文件。
你可以通过它查询ODBC数据源（包括世面上所有的数据库、TXT/CSV文件、EXCEL文件）的数据。你可以同时执行多条DML语句乃至存贮过程，结果会以你设定的表格、文本框、文件来返回。从数据库导出千万条数据时，效率与DBMS没有什么区别。
《万能数据库查询分析器》中文版本《DB 查询分析器》在中关村在线下载量超过10万次，位居整个数据库类排行榜中第8位，所以在实际工作中，本人用《DB 查询分析器》作为客户端，实现将数据仓库中的数据展现与导出。
4.2 本系统中的两个主题的星形模式
4.3 数据样例示意
图6 数据展现1
图7 数据展现2
图8 点击“DB 查询分析器”的工具栏按钮，即可导出数据1
图9 点击“DB 查询分析器”的工具栏按钮，即可导出数据2
5 结束语
笔者在《广东省高速公路一张网资金结算情况分析系统》的研究与开发过程中，是数据仓库技术在实际工作中的又一次运用。
参考文献：
[1] 马根峰 · 万能数据库查询分析器中的事务管理在Oracle中的应用· 上海：微型电脑应用2008.4
[2] 新产品&工具点评 特别推荐：“万能数据库查询分析器”发布 · 程序员，2007.2
[3] 马根峰 · DB QueryAnalyzer中的事务管理在DB2中的应用· 北京：电脑编程技巧与维护2011.22
[4] 马根峰 · 《DB 查询分析器》批量执行DML语句并返回更详细的信息· 北京：电脑编程技巧与维护2011.24
[5] 王珊 · 数据仓库技术与联机分析处理 · 北京：科学出版社,1998.6
[6] MichaelCorey(美),MichaelAbbey(美) · SQL SERVER 7 Data Warehousing · 北京：希望电子出版社,2000.1
[7] 袁鹏飞 · SQLServer 7.0数据库系统管理与应用开发 · 北京：人民邮电出版社,1999.5
How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AMMySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.
MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AMThe MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.
Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AMMySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.
SQL Commands in MySQL: Practical ExamplesApr 14, 2025 am 12:09 AMSQL commands in MySQL can be divided into categories such as DDL, DML, DQL, DCL, etc., and are used to create, modify, delete databases and tables, insert, update, delete data, and perform complex query operations. 1. Basic usage includes CREATETABLE creation table, INSERTINTO insert data, and SELECT query data. 2. Advanced usage involves JOIN for table joins, subqueries and GROUPBY for data aggregation. 3. Common errors such as syntax errors, data type mismatch and permission problems can be debugged through syntax checking, data type conversion and permission management. 4. Performance optimization suggestions include using indexes, avoiding full table scanning, optimizing JOIN operations and using transactions to ensure data consistency.
How does InnoDB handle ACID compliance?Apr 14, 2025 am 12:03 AMInnoDB achieves atomicity through undolog, consistency and isolation through locking mechanism and MVCC, and persistence through redolog. 1) Atomicity: Use undolog to record the original data to ensure that the transaction can be rolled back. 2) Consistency: Ensure the data consistency through row-level locking and MVCC. 3) Isolation: Supports multiple isolation levels, and REPEATABLEREAD is used by default. 4) Persistence: Use redolog to record modifications to ensure that data is saved for a long time.
MySQL's Place: Databases and ProgrammingApr 13, 2025 am 12:18 AMMySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen
MySQL: From Small Businesses to Large EnterprisesApr 13, 2025 am 12:17 AMMySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.
What are phantom reads and how does InnoDB prevent them (Next-Key Locking)?Apr 13, 2025 am 12:16 AMInnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
SublimeText3 Linux new version
SublimeText3 Linux latest version
Atom editor mac version download
The most popular open source editor
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
SublimeText3 Mac version
God-level code editing software (SublimeText3)
