One-network fund settlement analysis system for Guangdong expressway Based on the Data Warehouse Star Model

 


One-network fund settlement analysis system for Guangdong expressway Based on the Data Warehouse Star Model

Magenfeng
(Guangdong Union Electronic Service Co., Ltd., Guangzhou 510300, China)

SummaryThe star mode is a well-known concept in the Data Warehouse Based on relational databases. the design concept of the star connection mode can meet the needs of people to analyze data from different perspectives (dimensions, therefore, the star mode is widely used in the design of data warehouses Based on relational databases. This article mainly introduces the Data Warehouse Technology in the "analysis system for settlement of one network for Guangdong expressway.

KeywordsData warehouse; star mode; dimension

 

 

1. Data Warehouse features

Data Warehouse Technology is a topic-oriented, integrated, non-updatable, and time-changing data set that better supports decision analysis and processing by enterprises or organizations. It organizes data according to different levels (granularity) to meet different analysis needs.

This article mainly introduces how to use the data warehouse technology in the fund settlement analysis system for one highway in Guangdong Province to achieve "Abnormal Flow" and its modification volume analysis and "flow upload and split" analysis. two topics.

 

2 star mode

The star mode is a well-known concept in the Data Warehouse Based on relational databases. the design concept of the star connection mode can meet the needs of people to analyze data from different perspectives (dimensions, in addition, the data warehouse is usually used to answer comprehensive questions, so the star mode is widely used in the design of the Data Warehouse Based on relational databases. For example, the general analysis of services is generally conducted by telecom operators.

 

 

3. Data Warehouse DESIGN IN THE SYSTEM

3.1 Data Organization under the DB2 database server in the operating environment of Guangdong United electronic settlement center

In Guangdong Union electronic settlement center, relationship models related to "Abnormal Flow", "flow splitting", and "flow upload"

L detailed list of precise sharding results of Guangdong Tong card

Tb_cardaccuratesplitresult (flow number, work class, exit Section..., Income section)

L detailed list of precise cash splitting results

Tb_cashaccuratesplitresult (flow number, work class, exit Section..., Income section)

L stream non-precise splitting record table

Tb_inaccuratesplitrecord..., Income section)

L split the journal exception record table

Tb_outlistspliterrorgb (flow number, work class, exit section,..., Error Type Code)

L split the History Table of flow exception records

Tb_outlistspliterrorgb_his (serial number, work class, exit section,..., Error Type Code)

L journal exception record table

Tb_outlisterrorgb (flow number, work class, exit section,..., Error Type Code)

L history of streamline exception records

Tb_outlisterrorgb_his (flow number, work class, exit section,..., Error Type Code)

L Outlet Flow Meter

Tb_outlistgb (serial number, work class, exit section,..., Upload time)

 

3.2 data warehouse design of the system

Considering the length, the steps are simplified as follows:

L Conceptual Model Design

L Logical Model Design

L Data Warehouse generation

3.2.1 defining system boundaries in Conceptual Model Design

In Guangdong Telecom's public phone center, the most urgent analysis by managers is:

L The flow upload and flow splitting of different jobs in different highway sections of the company.

L analyze the Abnormal Flow (including exceptions and splitting exceptions) uploaded by the company on each Highway Section and its modification;

 

To carry out the above analysis, the required data should include:

L analyzes the flow upload, splitting, total exceptions, and abnormal changes of each Highway Section within a period of time.

L analyze the Abnormal Flow (including exceptions and splitting exceptions) uploaded by the company on each Highway Section, and modify the uploaded Abnormal Flow (including exceptions and splitting exceptions;

L highway companies and their software developers

L information on all road sections of Guangdong expressway;

 

 

3.2.2 Logical Model Design

Because the detailed level of data is too large, the Data Warehouse is built on my pc ms SQL SERVER, coupled with the pressure of analysis task submission time, therefore, this data warehouse no longer involves the design and data mining of detailed data tables.

 

Link mode definition:

OK _xiugai (LISTNO, UPLOADTIME, OUTROADNO, SQUADDATE, ERRCODE, ERRMSG ...)

No_xiugai (LISTNO, UPLOADTIME, OUTROADNO, SQUADDATE, ERRCODE, ERRMSG ...)

Upload_split (Road Section code, road section name, work class, upload flow quantity, split flow quantity ...)

Kaifa (roadno, kaifa_name)

Tb_road (areano, roadno, roadname ,...)

 

3.2.3 data warehouse data generation

Data generation in a data warehouse consists of Data Mining in an operating environment and data generation in a data warehouse. In this step, interface programming is required to load the data in the operation environment into the data warehouse environment.

 

 

 

 

4. Data export and display of Data Warehouses

41 thousand database query Analyzer

Universal database query Analyzer, Chinese Version DB query Analyzer and English version DBQuery Analyzer. It has powerful functions, friendly operation interface, good operability, spanning a variety of database platforms and even EXCEL and text files.

You can use it to query data from ODBC data sources (including all databases on the world, TXT/CSV files, and EXCEL files. You can execute multiple DML statements and even the storage process at the same time, and the results will be returned in the table, text box, and file you set. When exporting tens of millions of data records from a database, the efficiency is no different from that of DBMS.

The Chinese version of universal database query analyzer exceeds the online downloads in Zhongguancun0.1 millionTimes, ranking in the entire database rankingsThe8-digitIn practice, I use DB query analyzer as the client to display and export data in the data warehouse.

 

4.2 Star mode of two themes in the system

 

4.3 Data Sample

Figure 6 data presentation 1

 

 

Figure 7 data presentation 2

 

Figure 8 click the "DB query analyzer" toolbar to export data 1

 

 

Figure 9 click the "DB query analyzer" toolbar to export data 2

 

 

 

5 conclusion

In the research and development process of "one-site Settlement Analysis System for Expressway in Guangdong Province", the author makes another use of the Data Warehouse Technology in practical work.

 

 

 

References:

[1] Ma genfeng · Application of Transaction Management in the universal database query analyzer in Oracle · Shanghai: Microcomputer Application 2008.4

[2] Special Recommendations for new products and tools: "universal database query analyzer" released by programmers, 2007.2

[3] Application of Transaction Management in database QueryAnalyzer to DB2 · Beijing: computer programming skills and maintenance 2011.22

[4] Ma genfeng-database query analyzer batch executes DML statements and returns more detailed information-Beijing: computer programming skills and maintenance 2011.24

[5] Wang shan-data warehouse technology and Online Analytical Processing · Beijing: Science Press, 1998.6

[6] MichaelCorey, MichaelAbbey · SQL SERVER 7 Data Warehousing · Beijing: Hope electronics Publishing House, 2000.1

[7] yuan Pengfei-SQLServer 7.0 Database System Management and Application Development · Beijing: People's post and telecommunications press, 1999.5