KR20010054834A - Apparatus and method for loading database of raw traffic data in phone network - Google Patents

Abstract

PURPOSE: An apparatus and a method for loading telephone network source traffic data into a database is provided to rapidly and exactly load a DB table with source traffic data, as a preceding work to construct a telephone network source traffic information data warehouse. CONSTITUTION: A term standardization unit(101) standardizes various terms for source traffic data. An input unit(102) receives a file name for loading among the file names standardized by the term standardization unit(101). A file name parsing and processing classification unit(103) executes parsing for the inputted file name to detect an input error. A data double loading confirmation unit(104) confirms whether the data equivalent to the verified file name exist in a database. A table generation unit(105) generates a standard table for a file in the database, in case that the relevant data are the first data. A data loading and log generation unit(106) loads actual data into the generated database table and generates a log file as the result. A file compression unit(107) compresses the file if the data are loaded completely. A basic information unit(109) has basic information for the standard table. An optimization processing unit(110) adjusts an SQL(Structured Query Language) sentence according to basic information and optimizes the table generation operations of the table generation unit(105). An error processing unit(108) processes an error generated from each unit.

Description

Apparatus and method for loading database of raw traffic data in phone network}

The present invention relates to a database loading device and a room of a telephone network raw traffic data, and more particularly, to load the raw traffic data into a database (Database, or DB) to build a telephone network traffic information data warehouse (DW) It relates to an apparatus and a method.

Telephone network raw traffic data is widely used as basic data for network facility investment planning and efficient management of network resources.

Currently, the traffic data measured by the telephone network is collected and managed by CTMS / Local in each regional headquarters in the case of a local exchange, and there are 8 major exchange models (M10CN, NO.1A, AXE-10, 5ESS, 5ESS-2000). , S1240, TDX-1A / B, TDX-10A). The intercity exchange is collected and managed at one CTMS / Toll and consists of three models (NO.4, AXE-10, and TDX-10A).

Currently, it extracts and manages only busy hour data among raw traffic data collected by CTMS, and keeps other data in backup device.

However, the data required to build a telephone network traffic information data warehouse is all time zone data including busy hour.

By dividing the raw traffic data by its type, it is divided into exchange facility information, inter-station relay facility information, exchange traffic, inter-station relay traffic, other equipment traffic including signaling equipment, and exchange CPU traffic. Each data is managed in separate files for each exchange type and each regional headquarters. For example, the number of files of raw traffic data managed by 9 regional headquarters for each type for any company's local exchange is at least 9 (number of regional headquarters) * 8 (number of exchange types) * 6 (number of file types) = 432 pieces. Here, there are two to three files additionally output according to the exchange model, and a large file is divided into several sub-files and managed so that the number of files may be more than 432. Including three intercity exchanges adds more than 18 files.

To build a telephone network traffic information data warehouse, all flat files must be loaded into a DB table. Hundreds of source files are required, and in order to load data quickly and accurately with different metrics by exchange type and file type, special technical elements are needed to deal with it.

Accordingly, the present invention has been made to solve the above-described problems, and as a preliminary work for the construction of the telephone network traffic information data warehouse, the telephone network raw traffic for loading the flat traffic data into a DB table more quickly and accurately The purpose is to provide a database loading device and method of data.

In accordance with a preferred embodiment of the present invention, an apparatus for loading a database of raw network traffic data includes: term standardization means for standardizing terms for raw traffic data different for each type of exchange;

Input means for receiving a file name of raw traffic data input by a user from the standardized raw traffic data;

File name parsing and processing classification means for performing a parsing operation on the file name in the input means and classifying the input file name into a predetermined processing type;

Data overlapping checking means for checking whether data corresponding to the verified file name already exists in the database;

Table generating means for generating a standard table in a database for the file when the data is first;

Data loading and log generating means for loading actual data into the generated database table and generating a log file for the result;

File compression means for compressing a file when the loading of the data is completed;

Basic information means having basic information about the standard table;

Optimization processing means for optimizing a table generating operation in the table generating means by tuning SQL statements according to the basic information;

And an error processing means for processing errors generated during operation in the input means, file name parsing and processing classification means, data redundancy check means, table generating means, data loading and log generating means, and file compression means.

In addition, the database loading method of the raw network traffic data of the telephone network according to a preferred embodiment of the present invention, the process of inputting the file name of the raw traffic data that the user wants to load;

Checking a parsing error by parsing the input file name;

When the parsing operation is completed normally, finding all files to be loaded and checking whether the files have been previously loaded into the database one by one;

Optimizing standard table generation by generating a table generation SQL statement if the non-overlapping is not performed;

Loading all data into a database using the generated SQL statement; And

Comprising the process of compressing the file when the loading of all the files is completed.

1 is a block diagram of a database loading apparatus of the telephone network raw traffic data according to an embodiment of the present invention,

2A and 2B are flowcharts illustrating a database loading method of telephone network raw traffic data according to an embodiment of the present invention.

<Description of Symbols for Major Parts of Drawings>

101: term standardization unit 102: input unit

103: File name parsing and processing classification unit

104: data redundancy check unit

105: table generator

106: data loading and log generation unit

107: file compression unit 108: error processing unit

109: basic information unit 110: optimization processing unit

The above objects, features and advantages will become more apparent from the following detailed description taken in conjunction with the accompanying drawings. Hereinafter, embodiments of the present invention will be described in detail with reference to the accompanying drawings.

First, in the specification of the present invention, the CTMS (Centralized Traffic Management System) is a traffic concentration management system developed by the company of the applicant, and collects the traffic data output from each exchange in real time and generates various analysis reports. . Local exchanges operate their own local traffic centralized management systems (CTMS / Local) for each regional headquarters, while local exchanges are managed by one localized traffic centralized management system (CTMS / Toll).

In the specification of the present invention, the raw traffic data means that the traffic data output from the exchange is collected without processing.

In addition, in the specification of the present invention, a data warehouse is an information technology that integrates data accumulated in a company and external data for many years by subject and enables analysis at various angles according to user requirements without a separate application program.

In the specification of the present invention, database loading means loading a flat file into a table in a database.

1 is a block diagram of a database loading apparatus of telephone network raw traffic data according to an embodiment of the present invention.

Reference numeral 101 standardizes the table names so that the same exchange model and the same kind of files are loaded into the same table throughout the regional headquarters for convenience of later management of different data for each exchange type. An item is a term standardization unit that standardizes a measurement item to use the same field name. The role of standardization is important because it facilitates loading and subsequent data warehouse deployments.

Reference numeral 102 is an input unit for inputting a corresponding month and month to be loaded and a file name to be loaded among file names standardized by the term standardization unit 101. The input contents to the input unit 102 are input by a user's operation, and the file name input is composed of several options. You can enter a specific file name in a specific headquarters or specify multiple file names using asterisk (*). The use of asterisk (*) in entering file names is convenient for the user. The end of the input is marked using the given string.

Reference numeral 103 denotes a file name parsing and processing classification unit for parsing a file name input through the input unit 102 to detect an input error and finding all target files when asterisk (*) is entered. The file name parsing and processing classification unit 103 repeats the process of finding out the name of the local headquarters and the file type to which the file belongs, and storing it in the variable for each file name.

Reference numeral 104 denotes a data redundancy check unit that checks whether the data is already loaded in the database before loading the data. The verification process involves placing a managed table in the database and inserting the source file name and date when the data is first loaded. Whenever a stacking operation is performed, this management table is checked first, and then a stacking operation is performed to prevent data from being duplicated, thereby improving the accuracy of the stacking.

Reference numeral 105 is a table generating unit for creating a standard table having the name of the regional headquarters, the exchange model name, and the file type name classified during file name parsing. The important thing when creating the standard table is to set the parameters for the physical storage in consideration of the characteristics of the data to be inserted in the table. It is also a task of tuning SQL (Structured Query Language) statements for faster processing.

Reference numeral 106 denotes a data coordination file (data for data constituting the flat file) created with the local headquarter name, the exchange model name, and the file type name classified at the time of file name parsing when the table generation unit 105 completes the table generation. It is a data loading and log generation unit that performs a loading operation using a script file explaining the structure) and creates a log file for the loading result. The contents of the log file provide a very detailed picture of the loading results.

When batch processing a plurality of files, the operations of the data redundancy check unit 104, the table generator 105, and the data load and log generator 106 are repeatedly performed.

Reference numeral 107 denotes a file compression unit that compresses the log file generated by the data loading and log generation unit 106. The purpose of file compression is to save disk space taken up by source files.

Reference numeral 108 is an error processing unit that processes errors occurring in the units 102, 103, 104, 105, 106, and 107. When an error occurs, an error message corresponding thereto is output and abnormally terminated. .

Reference numeral 109 is a basic information section that contains basic information (average record length, characteristics of the table, ie, whether the target table is frequently inserted or deleted) or not about the table.

Reference numeral 110 is an optimization processor that optimizes performance by tuning SQL statements according to basic information.

Next, a method for loading a database of telephone network raw traffic data according to an embodiment of the present invention will be described with reference to the flowcharts of FIGS. 2A and 2B.

The user inputs the year and month of the raw traffic data to be loaded through the input unit 102 and a file name to be loaded (step 201). During input, input continues until a string indicating the end of input is entered.

If an error occurs during the input from the input unit 102, the error processing unit 108 outputs an error message corresponding thereto based on the generated error, and terminates the program abnormally (step 204).

When the input from the input unit 102 is completed without error, the file name parsing and processing classifying unit 103 parses the currently input file name, decompresses a compressed file, etc., and the region to which the file belongs for each file name. The headquarters name and file type name are found and stored in the corresponding variable (step 202).

Of course, if an error occurs in the parsing operation in the file name parsing and processing classification unit 103, the operation in step 204 is executed. However, if the file name parsing and processing classification unit 103 is completed without a parsing error, the data duplication check unit 104 finds all the target files in order to prevent duplication of data, and whether the files are duplicated by one file or not. Check (step 203).

If the duplication is confirmed, the optimization processing unit 110 creates a table generation SQL statement, coordinates the SQL statements and provides them to the table generation unit 105 to achieve optimal performance when generating a standard table (step 205).

Subsequently, the data loading and log generating unit 106 loads the data into the database using the generated SQL statement and generates a log file as a result (step 206). If an error occurs when data is loaded in the data loading and log generating unit 106, the process returns to step 204 described above, and the error processing unit 108 terminates the program while outputting the corresponding error message.

However, when the loading of all files is completed without error, the file compression unit 107 performs compression to save disk space (step 207). If an error occurs even during the compression operation, the error processing unit 108 outputs an error message corresponding thereto and terminates the program abnormally. If the compression operation ends without error, the operation of the present invention is normally terminated.

According to the present invention as described above, the standardization of terms for the different data for each exchange model, provides a convenient input means to the user, to prevent the overlapping to increase the accuracy of the loading, the SQL statement tuning operation considering the characteristics of the table By processing the loading operation quickly, the system has the effect of ensuring the convenience, speed and accuracy of management in loading heterogeneous and massive telephone network raw traffic data into the database.

In addition, preferred embodiments of the present invention are disclosed for the purpose of illustration, those skilled in the art will be able to make various modifications, changes, additions, etc. within the spirit and scope of the present invention, such modifications and modifications belong to the following claims You will have to look.

Claims (11)

Term standardization means for standardizing a term for different raw traffic data for each exchange type; Input means for receiving a file name of raw traffic data input by a user from the standardized raw traffic data; File name parsing and processing classification means for performing a parsing operation on the file name in the input means and classifying the input file name into a predetermined processing type; Data overlapping checking means for checking whether data corresponding to the verified file name already exists in the database; Table generating means for generating a standard table in a database for the file when the data is first; Data loading and log generating means for loading actual data into the generated database table and generating a log file for the result; File compression means for compressing a file when the loading of the data is completed; Basic information means having basic information about the standard table; Optimization processing means for optimizing a table generating operation in the table generating means by tuning SQL statements according to the basic information; And an error processing means for processing errors generated during operation in the input means, file name parsing and processing classification means, data redundancy check means, table generation means, data loading and log generation means, and file compression means. Database loader for raw network traffic data. The method of claim 1, The term standardization means standardizes the table name so that the same exchange model and the same kind of files are loaded in the same table for all the regional headquarters, and the standardized measurement items use the same field name for the items having the same meaning. Database loader for raw network traffic data. The method of claim 1, And a file name inputted to the input means is inputted as a specific file name in a specific headquarters or a plurality of files designated by an asterisk. The method of claim 1, And said predetermined type of processing (composition of the input file name) is a local headquarters name, an exchange model name, and a file type name. The method of claim 4, wherein When the data is loaded by the data loading and log generating means, the loading operation is performed by using a data adjustment file created with the regional headquarters name, the exchange type name, and the file type name classified when parsing the file name. Database stacker. The method of claim 1, And the basic information means has information on an average record length for a specific table and characteristics of whether or not the target table is a table that is frequently inserted, deleted, or updated. Inputting a file name of raw traffic data that the user wants to load; Checking a parsing error by parsing the input file name; When the parsing operation is completed normally, finding all files to be loaded and checking whether the files have been previously loaded into the database one by one; Optimizing standard table generation by generating a table generation SQL statement if the non-overlapping is not performed; Loading all data into a database using the generated SQL statement; And And compressing the file when the loading of all the files is completed. The method of claim 7, wherein The file name of the inputted raw traffic data is standardized so that the same exchange type and the same type of file are loaded in the same table for all files of the same exchange and all the local headquarters. A method of loading a database of raw traffic data on a telephone network, characterized in that the items are standardized. The method of claim 7, wherein The input file name is input to a specific file name in a specific headquarters, or a plurality of asterisks specified by the asterisk database loading method of the raw network traffic data, characterized in that the input. The method of claim 7, wherein Checking whether the file is loaded is performed by placing a management table in the database, inserting a raw file name and date when the data is loaded for the first time, and then first checking the management table whenever a loading operation is performed. Database loading method of telephone network raw traffic data. The method of claim 7, wherein And loading the data using the data control file created using the regional headquarters name, the exchange model name, and the file type name classified when parsing the file name when the data is loaded.