JPH10301935A - Data processing method - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a data processing method capable of eliminating the necessity of preprocessing such as the generation of an index and easily executing multi-dimensional operation even in the case of executing multi-dimensional operation of the data of a detailed level. SOLUTION: Data related to 'branch' and 'product' included in data existing in a real table 3 are obtained based on the category of hierarchical information 22 and added to the table 3 as a virtual string 23. Then a multi-dimensional totalization result 24 totalizing the contents of all totalization items in the table 3 is obtained by executing the sort post-totalization of respective totalization items in the table 3 as sorts.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a data processing method, and more particularly to an improvement in a data processing method suitable for query processing and totaling processing of a multidimensional database management system.

[0002]

2. Description of the Related Art Generally, a so-called multidimensional database performs data processing using an index in order to extract multidimensional data at high speed. There are the following two methods for using the index. Unlike a relational database that holds statement-level data, the minimum aggregation level data required for multidimensional operations is pre-loaded from a relational database, etc., and based on these data, data used in multidimensional operations is stored. A multidimensional database generated internally. For data held in such a multidimensional database, an index is simultaneously generated when data is loaded or internally generated, and queries for multidimensional operations are performed at high speed.

[0003] Further, a multidimensional database which holds detailed level data like a normal relational database and performs multidimensional operations by extending relational database operations. In such a multidimensional database, an index required for a join used in the multidimensional operation is generated in advance, and a query for the multidimensional operation is performed at high speed. There are two methods described above.

As an example of performing conventional data processing using an index in order to extract multidimensional data at high speed as described above, there is a method disclosed in Japanese Patent Laid-Open No. Hei 4-229371. In this example, it is disclosed that the search is performed by changing the hierarchical level of the classification items. However, a classification item list corresponding to an index is required as a search means.

[0005]

Since the conventional data processing method is configured as described above, it is necessary to generate an index as preprocessing when performing a multidimensional operation from detailed level data. Furthermore, in the former case, in which data of the minimum aggregation level required for the multidimensional operation is previously loaded from a relational database or the like, it is necessary to load the aggregation level data from the detail level data. When a large amount of new detailed data is added every day, these preprocessing must be performed each time, and in a large-capacity multidimensional database, the processing becomes very heavy. There is a problem that immediacy is lost due to monthly processing.

SUMMARY OF THE INVENTION The present invention has been made to solve the above problems, and eliminates the need for preprocessing such as generating an index even when performing multidimensional operations on data at the detailed level. An object is to provide a multidimensional operation function easily.

[0007]

According to the data processing method of the present invention, a hierarchical structure between a data file based on sequential organization of detailed data composed of a plurality of tabulated item data as one unit to be tabulated and the tabulated item data is provided. A hierarchical structure information table which is set in advance, and referring to the hierarchical structure information table based on the total item data, searching for the total item data of an upper layer or a lower layer from the total item data, the obtained total item The detailed data is sorted using the data as a key, and the total processing is performed in the total item data unit.

[0008] The present invention further includes a data file based on sequential organization of detailed data composed of a plurality of total item data as one unit to be totalized, and a hierarchical structure information table in which a hierarchical structure between the total item data is set in advance. A step of totalizing the detailed data for every possible combination including the above total item data to create total data, referring to the above hierarchical structure information table based on the above total item data, and referring to the upper layer of the total item data A step of retrieving total item data; a step of sorting the total data by using the obtained total item data as a key; and performing a totaling process for each total item data.

[0009] Further, there is provided a data file by sequential organization of detailed data composed of a plurality of total item data as one unit to be totalized, and a hierarchical structure information table in which a hierarchical structure between the total item data is preset. A step of totalizing the detailed data for every possible combination including the above total item data to create total data, referring to the above hierarchical structure information table based on the above total item data, and referring to the upper layer of the total item data A step of retrieving total item data, a step of inserting the obtained total item data and its identification code into the detailed data, a step of sorting the total data using the above identification symbol as a key, and performing a total process in the total item data unit. is there.

[0010] Further, there is provided a data file based on sequential organization of detailed data composed of a plurality of total item data as one unit to be totalized, and a hierarchical structure information table in which a hierarchical structure between the total item data is set in advance. Tallying the detailed data for every possible combination including all of the above-mentioned total item data to create total data, selecting the total item data to be totaled from the above total item data, based on the above total item data A step of referring to the hierarchical structure information table to search for a total item data of a higher layer or a lower layer from the total item data,
Inserting the obtained tabulated item data and its identification symbol into the detailed data; sorting the tabulated data using the discrimination symbol as a key; and performing tabulation processing in the tabulated item data units.

Furthermore, the hierarchical structure information table includes a level name for each level, an element name belonging to the level,
It includes the order relation between those elements.

[0012] Further, the detailed data included in the elements arranged in a predetermined order is totaled for every combination including the totaling item data by an arbitrary selection.

Further, when the total value obtained by the above totaling process is zero, zero data is held as the total value.

[0014]

BEST MODE FOR CARRYING OUT THE INVENTION

Embodiment 1 FIG. Hereinafter, Embodiment 1 of the present invention will be described in detail with reference to the drawings. In the specification database (hereinafter referred to as specification DB) system according to the first embodiment, when installation and setup are completed, one environment illustrated in FIG. 1 is generated. In FIG. 1, reference numeral 1 denotes a catalog, and only one environment can be created in one PC Server, and only one catalog 1 exists in the environment.
Under the catalog 1, a plurality of schemas 2 can be generated and deleted. All information relating to the schema 2 and the like generated under the catalog 1 is managed as catalog 1 information by the specification DB system.

In the schema 2, a plurality of base tables 3 can be generated and deleted. Schema 2 contains the SET SCHEM
A can set the default schema. If you do not set an explicit default schema, the schema determined by the system will be the default schema.

In each schema 2, a category 4 as a hierarchical structure information table can be generated and deleted. Category 4 expresses the hierarchical structure of data and the order between elements (real key values included in the base table 3) at each level, and is composed of N named levels.
Each level has a list of elements belonging to that level and an order relation between the elements. Further, the levels have a relationship for each element, thereby expressing a hierarchical relationship. Which column of which category 4 to look at is set in a category function on a unique database language in the detail DB system. FIG. 2 is an example showing the structure of category 4, 11 is “level” such as section, name, etc., 12 is “element” such as general affairs department, personnel section, etc., 13 is “relationship” between each element, and 14 is the same level Indicates the “order” of each element in the above.

The base table 3 is a fixed-length sequential file composed of a plurality of columns 5 (fields). Only the base table 3 can be defined in the specification DB system. However, a "category function" for realizing a virtual column 5 that allows the viewpoint of the program of each column to be freely changed is set.

By applying the "category function" to column 5, it is possible to access the base table with virtual column 5. By simply defining the view of the column 5 through the category 4, the treatment of each column 5 can be changed for each application program. Specifically, by applying the above category 4 to the column 5 constituting the base table 3 using the category function, the column 5 not directly stored in the base table 3 makes it appear as if it exists. is there. As a result, it is possible to change the key arrangement, select a totaling level, and the like.

Next, the flow of the entire multidimensional processing will be described. What is called a dimension in the world of a multidimensional database often has a hierarchical structure, and can be searched and counted at various hierarchical levels. For example, taking a personal name as an example, if the individual belongs to a company, once the name is determined, the section or department to which the person belongs is uniquely determined. In other words, even if only the personal name is stored in column 5 of the base table 3, if the hierarchical structure of the personal name is defined separately, the search or the totaling using the department or section, which is the upper level of the hierarchy, as a key Processing becomes possible. Specification D in Embodiment 1
In the B system, these operations can be performed on the detail-based base table 3. In particular, by introducing category 4 which is a hierarchical structure of virtual columns in order to realize the same function as the dimension in such a multidimensional database, it is possible to freely change the view of the base table 3 which is raw data. Can be.

FIG. 3 shows an outline of the multidimensional processing.
Is a base table, and it is assumed that initially, there are only total items of “manufacturer”, “date”, and “sales”. Next, "branch"
A request is also made for the "product" to be counted, and the virtual column 23 is processed as if it exists in the base table 3 based on the category 4 of the hierarchy information 22. Next, the totaled items of the base table 3 are sorted and then totaled as a sort key, thereby obtaining a multidimensional totaling result 24 of all the totalized items of the base table 3. The above operation is
All are controlled by the application program 25.

The column 5 constituting the base table 3 is roughly classified into two. One is a column that holds information for identifying the name of an object represented by a master code.
Examples include employee codes and names, both of which are used to identify personal names. The second is a column that holds information for identifying the state of an object represented by a numerical value. Examples of this include selling prices and sales quantities. In a typical multidimensional database, the former are called "dimensions" and the latter are called "variables."
Although the treatment is clearly divided, in the general SQL world and the specification DB system, both of them are mixed in one line. When performing aggregation processing with GROUPBY in the detail DB system, the column specified as the key of GROUP BY has dimensions,
The column specified in the set function corresponds to the variable.

Next, category 4 will be described in detail.
By applying category 4 to column 5 in base table 3, it is possible to obtain the same effect as "dimension" in a multidimensional DB. The categories include the following information: (1) Category name (2) Level name for each hierarchy, list of elements belonging to that level, and order relation between elements (3) List of relation of elements between levels (4) Special value of each level ("ALL" Etc.) The category name and the level name are used when applying the category function. The category name may be unique within the schema, and the level name may be unique within the category. The element 12 at each level is a key item in search and tabulation, and the order can be defined between elements at the same level. However,
This order is significant only when ROLLUP and CUBE aggregation is performed, and when aggregation is not performed or simple GROUP BY, the normal order for each data type is applied. This makes it possible to set the output order of the result of the roll-up aggregation described later for each user.

By defining the relationship with the element 12 at the next higher level when defining the element at each level, a hierarchical structure as category 4 is defined. By defining this relationship, it is possible to search and count at a higher level. As a way to actively use this function, if the items you need to process are limited among the elements 12 in column 5, prepare a category for yourself and use only the necessary elements 12 as level elements 12. It is also possible to define.

A special value "ALL" logically exists in category 4, which is used as a value representing "medium plan" or "grand total" when a roll-up or cube aggregation is executed. . FIG. 4 shows the correspondence between the base table 3 and the category 4.
Since it exists inside, it can be associated as it is.

In the specification DB system according to the first embodiment, a category function for realizing a virtual column that does not actually exist is provided with respect to a normal column constituting the base table 3.
In order to use the category function, it is necessary to define the above-mentioned category 4, and then map column 5 and any level in category 4 to the base table 3 to which the category 4 is applied. By changing the viewpoint of one column 5, it is possible to define a plurality of hierarchical structures, so that the relationship between category 4 and column 5 is N: 1. this is,
This indicates that a completely different category such as "organization" and "post" can be applied to the column of name.
A plurality of levels can be defined in one category 4, and a category function can be applied to each level. This is within the category of organizations,
It shows that multiple levels of "department", "section", and "name" can be defined, and that the category function can be applied to any of the levels.

Order 14 between elements 12 defined in category 4
ROLLUP on the column obtained by applying the category function
Valid only when or CUBE tally is performed. The category function can map not only the value to the element 12 of each level in the category 4 but also the sub-element of each level.

FIG. 5 shows the relationship between a column and a virtual column obtained by applying a category. By applying a category function from a name to an organization (section), a virtual name of the section is obtained. This conceptually shows that a column is generated and a virtual column of a department name is generated by applying a category function from a name to an organization (department).

FIG. 6 specifically shows the relationship between the columns and the virtual columns. The names on the columns are changed by applying a category function from the name to the organization (section). Shows how it is generated as a section name.

The data type provided by the specification DB system in the first embodiment also provides a “DATE type” having a function similar to the concept of a category. This DATE type is a data type representing a date. FIG. 7 shows a hierarchical structure of the DATE type, and shows a hierarchical structure based on an implicit relationship according to a conventional practice.

Embodiment 2 FIG. ROLLUP and CUBE can be cited as a method of multidimensional aggregation processing. Embodiment 2 in which the present invention is applied to these methods will be described with reference to the drawings. GROUP in the database language of the detail DB system
When grouping is performed by specifying a plurality of keys in the BY, only the aggregation processing of the specified plurality of keys is performed (subtotal). ROLLUP and CUBE
In one of the specified keys, when one of a plurality of designated keys makes a round (key break), the tally processing up to that point is further summed (middle plan or grand total) and returned as a result. RO
FIG. 8 shows the difference between the output results of LLUP and CUBE. “*” In the figure means all elements included in the column, and “AL”
"L" means the sum of all the elements in the column. The expression example in FIG. 8 shows a three-dimensional (three GROUP BY clause keys) with no hierarchical relationship.

To consider a specific example of ROLLUP and CUBE,
Consider base table 3 having category 4 shown in FIG. Further, the structure of the base table 3 is as shown in FIG. 10, and is configured as a data file by sequential organization of detailed data composed of a plurality of total item data as one unit to be totaled. SQL is used as the database language.

With respect to the base table 3 shown in FIG. 10, in order to obtain a “monthly field sales table for the Katase branch from January to March”, three types of GROUP BY, ROLLUP, and CUBE shown in FIG. FIG. 12 is a list showing results obtained when a statement designating a search of the detail database is issued. In FIG. 12, (a) summarizes by GROUP BY, (b) shows ROL
Tabulation by LUP, (c) shows the tabulation result by CUBE. In (c), in the 1Q column, each of the sales of TV, video, washing machine, refrigerator, AV, home appliances, and January to March is shown. The feature is that the grand total is tabulated.
Note that “CATEGO” shown in each SQL statement of FIG.
"RY ~" indicates a category function, and a category 4 is associated with an upper layer.

Here, a processing method for obtaining a roll-up or cube as a multidimensional operation will be described. The method of finding the roll-up centering on the sorting process,
Basically, it is similar to the process of performing GROUP BY of SQL focusing on the sort process. By sorting input data and performing a totaling process for each key break, roll-up totaling can be obtained. In order to obtain an N-dimensional cube centering on the sorting process, it can be obtained by repeating a roll-up counting process N times.

First, if a roll-up aggregation process is directly performed on the detailed data, the aggregation overhead may increase. Therefore, depending on the number of detailed data, a normal GROUP BY aggregation is obtained as the first step. Will be. In FIG. 12, in (a), totals are collected for all possible combinations including total item data of TV, video, washing machine, refrigerator, AV, home appliance, and month. This GROUP BY data (or detail data)
On the other hand, when the rollup tally is performed for the field and the month as the second stage, the data is totaled for each field and month as shown in FIG. If such a roll-up operation is performed for each dimension, in the final stage, all cube aggregation results are obtained.

Here, when the total value is zero, the total value is set to zero and zero data is held as the total value. In FIG. 13, when the Tsujido branch and the Imaizumi branch have zero sales, zero counting can be performed by adding and counting data of zero sales. This zero totaling function can be selected not to be performed by designation.

Embodiment 3 A case where the present invention is applied to a pipeline merge sorter will be described as a third embodiment. FIG. 14 shows a main part of a data flow when performing aggregation by CUBE in the pipeline merge sorter. In the figure, 31 is GROUP B
The elements to be summed are selected from the data after the summation by Y, and the summation item data is stored in a disk buffer (not shown). This data is transferred by DMA transfer to a pipeline merge sorter (FIG. (Not shown) Read multiple data into the internal buffer at once. Projection processing is executed in the pipeline merge sorter, and the contents of the record are extended to the sort key data (the left side in the buffer) and the data to be returned to the user (the right side in the buffer), and the state shown in FIG. Next, the firmware (F / W) in the pipeline / merge sorter buffers sort order keys and upper layer data as identification symbols while referring to category 4 in gaps created by projection during DMA transfer. And the state of the buffer shown in 33 is reached. When the buffer is completely filled, the data is sent to the sorting unit 34,
Sorted in the order of the sort key on the left side of the buffer. When the lower layer data from category 4 is written in the buffer, the processing contents are the same except that the writing position is different from the state shown in 33.

The result of sorting in the order of the sort keys on the left side of the buffer becomes the state of the buffer shown at 35. Here again F
The totaling process is performed by / W. Here, the totaling (roll-up totaling) by key break is performed. Finally, by returning the contents of the record on the right side of the buffer to the user, the totaling for the products and fields is completed. The cube processing is completed by executing the above processing for all the elements selected as the totals of the date, branch, and manufacturer.

Next, the operation of the third embodiment will be described. FIG. 15 is a processing flow for explaining the operation. First, GROUP BY is performed for totaling all possible combinations including all total item data from the detailed data of the base table 3 to generate total data (step). S1). next,
The total item data to be totaled is selected from the total item data and stored in the disk buffer (step S2).
Since the total item data that has not been selected is deleted, the record length is reduced. Here, since the number of elements is small, it is also possible to store all the aggregation item data in the disk buffer without selection, and in this case, it is not necessary to search the lower layer data from category 4, only the upper layer data. Search.

Next, the contents of the record are extended to the sort key data and the data to be returned to the user by the projection processing, and a space for inserting the total item data of the upper layer or lower layer of the category and the order key is set (step S3). The category is searched, and the total item data of the upper layer or the lower layer and the order key thereof are inserted into the space (step S4). Sort using the order key as the sort key (step S
5).

Next, tabulation item data targeted for the sort key is tabulated by a set operation, and a row for tabulation is added (step S6). Here, it is determined whether or not the aggregation processing has been completed for all of the aggregation item data of the selected element (step S7).
Then, the order key of the aggregation item data to be aggregated next is stored in the disk buffer. When the processing is completed, the process proceeds to step S8, and the CUBE for the selected aggregation item data is completed.

As described above, the sorting process can be performed at a higher speed by sorting the character data having a relatively long data length as the sort key without using the character data having a relatively long data length as the sort key.

[0042]

Since the present invention is configured as described above, it has the following effects.

Since the hierarchical structure information table in which the hierarchical structure between the total item data is set in advance is provided, it is possible to obtain the total value of the total item data in the upper layer or the lower layer than the total item data.

Further, since a step of totalizing the detailed data for every possible combination including all the above-mentioned total item data and creating the total data is provided, the total value of the total item data of the upper layer can be calculated more quickly. Obtainable.

Further, a step of inserting the total item data obtained by referring to the hierarchical structure information table based on the total item data and its identification symbol into the detailed data is provided, so that sorting is performed using the identification symbol as a sort key. Therefore, the total value of the total item data of the upper layer can be obtained more quickly.

Further, since the step of selecting the total item data to be totalized from the total item data is provided, the total value of the total item data in the upper layer or lower layer than the total item data can be obtained more quickly. .

Further, the hierarchical structure information table includes:
Since it is configured to include the level name for each hierarchy, the names of the elements belonging to that level, and the order relation between those elements, it is possible to obtain a total value for the total item data of a higher layer or a lower layer than the total item data. At the same time, the level of aggregation can be set based on the order relation between elements.

Further, since the detailed data is configured to be totaled for every combination including the above-mentioned total item data by an arbitrary selection, it is possible to correspond to all search conditions.

Furthermore, since the zero data is held as the total value when the total value is zero, it can be easily grasped that the total value is zero.

[Brief description of the drawings]

FIG. 1 is a configuration diagram of a specification DB system according to a first embodiment of the present invention.

FIG. 2 is a structural diagram of a category showing the first embodiment of the present invention.

FIG. 3 is a schematic explanatory diagram of multidimensional processing according to the first embodiment of the present invention.

FIG. 4 is an explanatory diagram showing a relationship between a category and a base table according to the first embodiment of the present invention.

FIG. 5 is a configuration diagram of a column and a virtual column according to the first embodiment of the present invention.

FIG. 6 is an explanatory diagram illustrating a relationship between a column and a virtual column according to the first embodiment of the present invention;

FIG. 7 is a structural diagram illustrating a DATE-type hierarchical structure according to the first embodiment of the present invention.

FIG. 8 is a comparison diagram showing the difference in the counting method according to the second embodiment of the present invention.

FIG. 9 is a configuration diagram showing contents of a category according to the second embodiment of the present invention.

FIG. 10 is a structural diagram of a specification data file according to the second embodiment of the present invention.

FIG. 11 is a list of SQL statements showing the second embodiment of the present invention.

FIG. 12 is an output list showing three types of counting results according to the second embodiment of the present invention.

FIG. 13 is an explanatory diagram of zero counting showing the second embodiment of the present invention.

FIG. 14 is a data flow diagram showing a third embodiment of the present invention.

FIG. 15 is a flowchart showing Embodiment 3 of the present invention.

[Explanation of symbols]

3 Base table (detailed data), 4 categories (hierarchical structure information table), 12 elements.

Claims (7)

[Claims] 1. A data file comprising sequential data of detailed data comprising a plurality of tabulated item data as one unit to be tabulated, and a hierarchical structure information table in which a hierarchical structure between the tabulated item data is set in advance. A step of referring to the hierarchical structure information table on the basis of the total item data and searching for the total item data of a higher layer or a lower layer from the total item data; sorting the detailed data by using the obtained total item data as a key; A data processing method characterized by performing an aggregation process in item data units. 2. A system according to claim 1, further comprising: a data file based on sequential organization of detailed data composed of a plurality of total item data as one unit to be totalized; and a hierarchical structure information table in which a hierarchical structure between the total item data is set in advance. A step of totalizing the detailed data for every possible combination including the above total item data to create total data, referring to the above hierarchical structure information table based on the above total item data, and referring to the upper layer of the total item data A data processing method comprising: searching for total item data; sorting the total data by using the obtained total item data as a key; and performing a totaling process for each total item data unit. 3. A data file according to a sequential organization of detailed data composed of a plurality of aggregation item data as one unit to be aggregated, and a hierarchical structure information table in which a hierarchical structure between the aggregation item data is preset. A step of totalizing the detailed data for every possible combination including the above total item data to create total data, referring to the above hierarchical structure information table based on the above total item data, and referring to the upper layer of the total item data A step of retrieving total item data, a step of inserting the obtained total item data and its identification code into the detailed data, a step of sorting the total data using the above identification symbol as a key, and performing a total process in the total item data unit. Characteristic data processing method. 4. A data file comprising sequential data of detailed data comprising a plurality of tabulated item data as one unit to be tabulated, and a hierarchical structure information table in which a hierarchical structure between the tabulated item data is preset. Tallying the detailed data for every possible combination including all the above-mentioned total item data to create total data, selecting the total item data to be totaled from the above total item data, based on the above total item data A step of referring to the hierarchical structure information table to search for a total item data of a higher layer or a lower layer from the total item data; a step of inserting the obtained total item data and its identification code into the detailed data; The total data is sorted using the key as a key, and the total processing is performed in the total item data unit. Data processing method. 5. The hierarchical structure information table according to claim 1, wherein a level name for each layer, element names belonging to the level, and an order relation between the elements are included. The data processing method described in 1. 6. The method according to claim 1, wherein the detailed data included in the elements arranged in a preset order is totaled for all combinations including the total item data by an arbitrary selection. Item 6. The data processing method according to any one of Items 5. 7. The data processing method according to claim 1, wherein when the total value obtained by the totaling process is zero, zero data is held as the total value.