基于数据仓库星形模式的广东省高速公路一张网资金结算情况分析系
摘要 星形模式是基于关系数据库的数据仓库中的一个著名概念，由于星形连接模式的设计思想能够满足人们从不同观察角度（维）分析数据的需求，所以在基于关系数据库的数据仓库的设计中广泛地使用了星形模式。本文主要介绍《广东省高速公路一张网资金结算情况分
摘要 星形模式是基于关系数据库的数据仓库中的一个著名概念，由于星形连接模式的设计思想能够满足人们从不同观察角度（维）分析数据的需求，所以在基于关系数据库的数据仓库的设计中广泛地使用了星形模式。本文主要介绍《广东省高速公路一张网资金结算情况分析系统》中的数据仓库技术。
关键词 数据仓库；星形模式；维度
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
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
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1378
52
What does WeChat's Do Not Disturb mode do?
Feb 23, 2024 pm 10:48 PM
What does WeChat Do Not Disturb mode mean? Nowadays, with the popularity of smartphones and the rapid development of mobile Internet, social media platforms have become an indispensable part of people's daily lives. WeChat is one of the most popular social media platforms in China, and almost everyone has a WeChat account. We can communicate with friends, family, and colleagues in real time through WeChat, share moments in our lives, and understand each other’s current situation. However, in this era, we are also inevitably faced with the problems of information overload and privacy leakage, especially for those who need to focus or
Use ddrescue to recover data on Linux
Mar 20, 2024 pm 01:37 PM
DDREASE is a tool for recovering data from file or block devices such as hard drives, SSDs, RAM disks, CDs, DVDs and USB storage devices. It copies data from one block device to another, leaving corrupted data blocks behind and moving only good data blocks. ddreasue is a powerful recovery tool that is fully automated as it does not require any interference during recovery operations. Additionally, thanks to the ddasue map file, it can be stopped and resumed at any time. Other key features of DDREASE are as follows: It does not overwrite recovered data but fills the gaps in case of iterative recovery. However, it can be truncated if the tool is instructed to do so explicitly. Recover data from multiple files or blocks to a single
Open source! Beyond ZoeDepth! DepthFM: Fast and accurate monocular depth estimation!
Apr 03, 2024 pm 12:04 PM
0.What does this article do? We propose DepthFM: a versatile and fast state-of-the-art generative monocular depth estimation model. In addition to traditional depth estimation tasks, DepthFM also demonstrates state-of-the-art capabilities in downstream tasks such as depth inpainting. DepthFM is efficient and can synthesize depth maps within a few inference steps. Let’s read about this work together ~ 1. Paper information title: DepthFM: FastMonocularDepthEstimationwithFlowMatching Author: MingGui, JohannesS.Fischer, UlrichPrestel, PingchuanMa, Dmytr
How to use Excel filter function with multiple conditions
Feb 26, 2024 am 10:19 AM
If you need to know how to use filtering with multiple criteria in Excel, the following tutorial will guide you through the steps to ensure you can filter and sort your data effectively. Excel's filtering function is very powerful and can help you extract the information you need from large amounts of data. This function can filter data according to the conditions you set and display only the parts that meet the conditions, making data management more efficient. By using the filter function, you can quickly find target data, saving time in finding and organizing data. This function can not only be applied to simple data lists, but can also be filtered based on multiple conditions to help you locate the information you need more accurately. Overall, Excel’s filtering function is a very practical
Google is ecstatic: JAX performance surpasses Pytorch and TensorFlow! It may become the fastest choice for GPU inference training
Apr 01, 2024 pm 07:46 PM
The performance of JAX, promoted by Google, has surpassed that of Pytorch and TensorFlow in recent benchmark tests, ranking first in 7 indicators. And the test was not done on the TPU with the best JAX performance. Although among developers, Pytorch is still more popular than Tensorflow. But in the future, perhaps more large models will be trained and run based on the JAX platform. Models Recently, the Keras team benchmarked three backends (TensorFlow, JAX, PyTorch) with the native PyTorch implementation and Keras2 with TensorFlow. First, they select a set of mainstream
Slow Cellular Data Internet Speeds on iPhone: Fixes
May 03, 2024 pm 09:01 PM
Facing lag, slow mobile data connection on iPhone? Typically, the strength of cellular internet on your phone depends on several factors such as region, cellular network type, roaming type, etc. There are some things you can do to get a faster, more reliable cellular Internet connection. Fix 1 – Force Restart iPhone Sometimes, force restarting your device just resets a lot of things, including the cellular connection. Step 1 – Just press the volume up key once and release. Next, press the Volume Down key and release it again. Step 2 – The next part of the process is to hold the button on the right side. Let the iPhone finish restarting. Enable cellular data and check network speed. Check again Fix 2 – Change data mode While 5G offers better network speeds, it works better when the signal is weaker
The vitality of super intelligence awakens! But with the arrival of self-updating AI, mothers no longer have to worry about data bottlenecks
Apr 29, 2024 pm 06:55 PM
I cry to death. The world is madly building big models. The data on the Internet is not enough. It is not enough at all. The training model looks like "The Hunger Games", and AI researchers around the world are worrying about how to feed these data voracious eaters. This problem is particularly prominent in multi-modal tasks. At a time when nothing could be done, a start-up team from the Department of Renmin University of China used its own new model to become the first in China to make "model-generated data feed itself" a reality. Moreover, it is a two-pronged approach on the understanding side and the generation side. Both sides can generate high-quality, multi-modal new data and provide data feedback to the model itself. What is a model? Awaker 1.0, a large multi-modal model that just appeared on the Zhongguancun Forum. Who is the team? Sophon engine. Founded by Gao Yizhao, a doctoral student at Renmin University’s Hillhouse School of Artificial Intelligence.
The first robot to autonomously complete human tasks appears, with five fingers that are flexible and fast, and large models support virtual space training
Mar 11, 2024 pm 12:10 PM
This week, FigureAI, a robotics company invested by OpenAI, Microsoft, Bezos, and Nvidia, announced that it has received nearly $700 million in financing and plans to develop a humanoid robot that can walk independently within the next year. And Tesla’s Optimus Prime has repeatedly received good news. No one doubts that this year will be the year when humanoid robots explode. SanctuaryAI, a Canadian-based robotics company, recently released a new humanoid robot, Phoenix. Officials claim that it can complete many tasks autonomously at the same speed as humans. Pheonix, the world's first robot that can autonomously complete tasks at human speeds, can gently grab, move and elegantly place each object to its left and right sides. It can autonomously identify objects
