JP2000187665A - Information deriving method and its system in data base system and storage medium - Google Patents

Abstract

PROBLEM TO BE SOLVED: To allow even an end user who does not have any special knowledge related with a data base to freely derive necessary data from the data base. SOLUTION: A virtual file 31 stores the definition of designation used for the regular task of a user defined by the user for various kinds of derivation items whose derivation is desired from a data ware house(DWH) 22, storage information in the DWH 22 for management items in a data file stored in the DWH 22 which is necessary for the derivation of the derivation items, and a relation between the designation and the information. An inquiry processing engine 32 prepares an SQL (structured query language) sentence for inquiring desired data corresponding to a query (inquiry) using the designation of the item used for the regular task from a user who operates a user editor 41 by using the virtual file 31, and transmits it to the DWH 22, and transmits the data derived from the DWH 22 being the answer to the user editor 41.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a technique for managing a database, and more particularly to a technique for enabling an end user who does not have specialized knowledge about a database to freely derive necessary data from the database. About.

[0002]

2. Description of the Related Art In a database system, SQL (Structured Query Language) is often used to manage a database. SQL provides functions such as data definition, data manipulation, and transaction management in a database.

[0003] In SQL, a data file is treated as a table. In other words, it is assumed that the database adopting SQL is composed of a set of tables. The tables that can be defined in SQL include a base table and a view table. A base table refers to an existing table, and a data file corresponding to the table is physically stored on a storage device constituting a database.
On the other hand, a view table is a virtual table, and a data file corresponding to the table is a virtual file that is not physically stored on the storage device. The view table is a definition in which a user derives necessary data from data contained in one or more base tables in accordance with the purpose of search and analysis of his / her own business using an SQL statement.

The advantages of using SQL for database management are listed below. Improve security by restricting user access to base tables. For example, a view table is created without defining items including data that should be kept secret according to the user, such as personnel information and salary information. Hide the complexity of the structure of the base table on the database from the user.

For example, only a single view table obtained by combining a plurality of base tables is presented to a user. Inquiry function to database by easy-to-understand SQL statement. For example, by utilizing the join of tables, which is a basic operation of data manipulation in an SQL statement, a user can retrieve even data that spans a plurality of tables and is distributed and accumulated in a database. .

[0006] Data warehouses (hereinafter, also referred to as DWHs) have attracted attention in recent years in connection with the improvement in computer performance and the reduction in capacity and cost of storage media.

[0007] The DWH is a database having a large amount of information in which the history of business activities such as the sales results of merchandise has been accumulated in the order of several months to several years. The purpose of this study is to provide useful support information for subsequent corporate management and sales activities by analyzing from various viewpoints. In order to effectively use the DWH, a database system that allows a user (end user) to directly access the DWH and derive necessary data flexibly for data analysis is required.

[0008]

As described above, the DW
In a database system having H, a system that allows an end user to freely derive necessary data from the database is particularly required.
However, end-users, not to mention SQL,
In most cases, even the structure of the database system used is unknown. Therefore, under the current circumstances, except for some users who understand these well, or users who can receive individual support from the system administrator,
The database was not fully utilized.

A view table may be prepared by a system administrator as necessary, for example. The view table entity is specified by an SQL statement by the system administrator using the name of the base table in the system. Because it was created, end users who do not know the specific structure of the database system to be used will not be able to understand the contents of the view table, making it extremely inconvenient for end users to use such a view table. there were.

[0010] In view of the above problems, it is an object of the present invention to enable end users who do not have specialized knowledge about a database to freely derive necessary data from the database. .

[0011]

According to an information deriving method in a database system according to the present invention for solving the above-mentioned problems, a database for storing a plurality of data files mutually associated by management items of the data files is provided. Names individually defined by the user for various derived items that the user who wants to derive from the user, and information based on the data structure in the database about the management items required for deriving the derived items And a procedure for defining the association between the name and the information in association with each other, and a management item necessary for deriving a derived item corresponding to the inquiry item according to an inquiry item created by selecting the name. Acquiring the contents of the information from the database using the information defined by the definition procedure; and A step of deriving a derived item from the eyes of the content corresponding to the query items, is characterized in that it has a.

In this method, the names of various derived items that the user of the database desires to derive are individually defined by the user. Perform using the name defined by himself. Information required to derive the derived items defined by the user from the database is defined, for example, in association with the name defined by the user by the system administrator. Is directly converted into, for example, an SQL sentence and sent to a database. Therefore, the user does not need to understand the structure and the like of the database, and can freely derive necessary data from the database.

An information deriving system for implementing the information deriving method in the database system described above,
The present invention also includes a storage medium storing a program for causing a computer to execute the above-described information deriving method in the database system.

Further, a user who uses a database storing a plurality of data files, which are mutually associated by management items of the data file, individually obtains various derived items desired to be derived by the user. An area for storing a name to be defined, and information based on a data structure in the database for the management item required for deriving the derived item, wherein the information defining the association with the name data is stored. And an area to be
Also referred to to obtain from the database the contents of the management items required to derive the derived items corresponding to the inquiry items created by the selection from the name, the computer-readable storage medium also characterized by the fact that This is the scope of the present application.

This storage medium indicates a storage medium in which information defined by a definition procedure in the above-described information deriving method is stored.

[0016]

Embodiments of the present invention will be described below with reference to the drawings. FIG. 1 is an overall configuration diagram of a database system embodying the present invention. This database system is a system having a data warehouse. The system shown in FIG. 1 includes a data server 11, an application server 12, and a client 13.

The data server 11 has a core database 2
1, a data warehouse (DWH) 22. The backbone database 21 is a database that is generally referred to as a backbone business system or an accounting system in a financial institution, and is a data update target in a business system (not shown) that executes transaction processing that occurs daily.

As described above, the DWH 22 is a database that stores a history of corporate activities. In the present embodiment, it is assumed that the DWH 22 has acquired a history of data updates performed on the core database 21. .

The application server 12, which is particularly relevant to the present invention, comprises a virtual file 31, and an inquiry processing engine 32. The virtual file 31 stores a virtual file for each purpose. The details will be described later.

The query processing engine 32 refers to the virtual file 31, creates an SQL statement for querying desired data according to a query (query) from the end user who operates the user editor 41, and sends it to the DWH 22. A processing engine for executing a process of passing data derived from the DWH 22 that is the answer to the user editor 41, and a standard computer component such as a storage device such as a CPU, a ROM and a RAM, and an input / output interface. Has been realized. Details of the processing executed by the inquiry processing engine 32 will be described later.

The client 13 comprises a user editor 41 operated by an end user and an administrator editor 42 operated by a system administrator.
The user editor 41 sets the user setting items of the purpose-specific virtual file stored in the virtual file 31,
It is an information terminal used for querying data (inquiry) to H22 and referring to derived data as an answer.

The administrator editor 42 stores the virtual file 31
Is an information terminal used to edit the purpose-specific virtual file based on the setting by the end user for the purpose-specific virtual file stored in the.

Next, the purpose-specific virtual file will be described. Virtual file for each purpose (hereinafter, SOVF: Subject Or)
When the end user requests the database to derive the desired data, the user can request the derivation of the desired data with the name of the item used in his or her usual work. It is a logical file created for the purpose.

FIG. 2 shows the SOVF file structure.
The SOVF has the structure shown in FIG. 3A, and has four areas of a file property 51, a classification item 52, a tally item 53, and a fixed item 54 when roughly classified.
In addition, in the SOVF shown in FIG. 3A, each of the classification item 52, the total item 53, and the fixed item 54 has n items. It does not matter.

The file property 51 contains the SOV
Information describing the outline of F is set. Each item belonging to the file property 51 is set by the end user using the user editor 41. The contents of each item will be described.

In the file name, a name for identifying the SOVF is set. For the management target, for example, an aggregation target such as a product in a product management operation is set.
This is an item which is a primary key for identifying a record of a file in the database. Note that the item names set here must be set as item names in the classification items 52 described later.

The comment can be freely entered and set by the end user. The owner sets the name of the end user who is the owner (creator) of the SOVF, and sets the creation date of the SOVF as the creation date.

The classification items 52 include, for example, in a product management business, product items, product genres, business partners,
Such items as one viewpoint which is one viewpoint in data analysis desired by the end user and which can be one unit of data totaling are set.

The aggregation item 53 includes, for example, the number of sales, the amount of sales, the number of stocks, and the like in the product management business.
Items that can be total values resulting from data analysis desired by the end user are set.

In the fixed item 54, for example, in a merchandise management operation, items such as a fixed price, a release date, and the like, for which a fixed value for the above-mentioned management target is uniquely obtained are set.

FIG. 4B shows the structure of one item for the classification item 52, FIG. 5C shows the structure of one item for the total item 53, and FIG. The structure of one item is shown. Of the items constituting each item, the item name, slip / form name, and comment are set by the end user using the user editor 41, and the items other than these are set by the system administrator using the administrator editor 42. Set.

The items shown in FIGS. 3B, 3C and 3D will be described together. The item name in the classification item sets the name of an item which is one viewpoint in data analysis desired by the end user.

The item name in the tally item sets the name of an item that can be a tally value resulting from data analysis desired by the end user. As the item name in the fixed item, the name of an item for which a fixed value is uniquely obtained for the management target is set.

The end user can easily understand the contents of the item by setting the item name in each of the items with the name used by the end user in a normal business.

As the slip / form name, for example, in the product management business, a name of a slip or a form, such as an order slip and a delivery slip, which uses the above-mentioned item names in the normal business by the end user is set. It is provided as an example for the system administrator to understand the contents of the item.

The comment can be freely entered and set by the end user, and is used, for example, to explain the details of the item names described above. Item Classification The items below are set by the system administrator after referring to the above-mentioned items set by the end user and fully understanding the intention of the end user who set each item.

In the item classification, any one of a classification item, a total item, and a fixed item is set by a system administrator. In consideration of the specific contents indicated by the item names set by the end user, it is conceivable that the classification, totalization, and fixed classification are not appropriate. However, even in such a case, it is less confusing for the end user if the setting place by the end user is left as it is without correction. The item classification is for the system administrator to give an appropriate classification to which the specific content indicated by the item name considered by the end user should originally belong.

For the reasons described above, FIG.
In the structure of each of the three types of items shown in (c) and (d), if a classification item is set in the item classification, regardless of the classification, aggregation, and fixed classification performed by the end user , The administrator setting items have the structure shown in FIG. In addition, when a total item is set in the item classification, the administrator setting item has the structure shown in FIG. 4C, and when a fixed item is set in the item classification, the administrator setting item is set. Have the structure shown in FIG.

For the physical file name and the physical item name, the name of the file in the DWH 22 in which the item corresponding to the item name set by the end user is stored, and the actual name of the item on the file are set. I do.

When the item classification is set as a total item (case (c) in FIG. 4), the total value corresponding to the item name set by the end user does not exist in the DWH 22 and it is necessary to calculate the total value. If there is a physical file name,
A calculation formula for calculating the total value is set in the physical item name part. In the example shown in FIG. 3C, a calculation formula is set for the physical item name. The treatment of the counting items will be described later in detail.

As the type and number of digits, the data type and the required number of digits for the output of each item are set. In the relation, information indicating an association between a physical file and a physical item based on an item defined as a management target of an item corresponding to an item name set by an end user is set.

FIG. 3 is a diagram for explaining relation setting. In the configuration example of FIG. 1, the SOVF 61 is stored in the virtual file 31, and the detail files A, B, C (6
2a, 62b, 62c) and table files A, B, C
(63a, 63b, 63c) are stored in the DWH 22.

In the SOVF 61, "key 1" is a management target, "key 2" to "key 5" are respectively assigned to the item names of the classification items 1 to 4, and "item 1" is assigned to the item name of the total item 1. "2", and "item 1" and "item 3" are respectively set in the item names of the fixed items 1 and 2. Although a classification item of “key 1” also exists depending on the setting condition of the management target, it is not separately illustrated in FIG. 3, and the display of “management target” indicates the classification item of “key 1”. It is assumed that This also applies to the examples of FIGS. 4 and 7 described later.

Here, for the sake of simplicity, "key 1" to "key 2" and "item 1" to "item 3" are also physical items corresponding to the item names set by the end user. It is assumed that the relationship between the specific content indicated by the item name set by the end user and the selected classification, aggregation, or fixed classification is correct, and the item name and the physical item name match. Shall be.

The specification files A, B, C (62a, 62
b, 62c) are files stored as slip data and include items having numerical data that can be counted, and the items are SOVF
It is not always necessary to set them as 61 total items.

The table files A, B, C (63a, 6
3b and 63c) are files in which the correspondence between items is defined. In setting the relation, first, the system administrator determines a main file. As the main file, a detailed file having physical items corresponding to the management target is selected. When there are a plurality of specification files corresponding to the selection condition, a specification file expected to be frequently referred to is selected from the plurality of specification files.

In the example of FIG. 3, it is assumed that the specification file A62a is selected as the main file from the three specification files that satisfy the above conditions. Here, the remaining detail files and table files that have not been selected as the main files are referred to as sub-files in comparison with the main file.

In the figure, detailed files B and C (6
2b, 62c) and table files A, B, C (63
a, 63b, 63c) respectively have “subordinate file 1” to
The name of “slave file 5” is given.

The relations shown in FIGS. 2B, 2C, and 2D are obtained by tracing the relationship between the physical item corresponding to the set item name and the main file from the physical item of the main file. Set as shown.

In the example of FIG. 3, the item name is “key 1”.
Information indicating the contents of the “main file (key 1)” is set in the relation of the classification item (managed object). This setting indicates that the item corresponding to the item name “key 1” is a physical item that is key 1 of the detail file A62a that is the main file.

Similarly, information indicating the contents of the “main file (key 3)” is set in the relation of the classification item 2 whose item name is “key 3”. This setting indicates that the item corresponding to the item name “key 3” is a physical item of key 3 of the specification file A62a. Similarly, the relation of the classification item 4 whose item name is “key 5”
Information indicating the contents of the “main file (key 5)” is set.

Next, the setting of the relation of the classification item 3 whose item name is "key 4" will be described. In the example of FIG. 3, the item corresponding to the item name “key 4” is a physical item that is key 4 of the table file B63b that is the subsidiary file 3. In order to trace the physical item key 4 from the detail file A62a, which is the main file, as shown by a broken arrow from the detail file A62a to the table file B63b in FIG. It turns out that you should follow. Therefore, in the relation of the classification item 3 whose item name is “key 4”, information indicating the contents of the main file (key 3) → the sub file 3 (key 3 → key 4) is set, thereby having a hierarchical structure. The relationship between the detailed file A62a and the table file B63b is shown.

Setting of the relation of the fixed item 2 whose item name is "item 3" is the same, and the main file (key 5)
According to the setting of the information indicating the contents of the slave file 5 (key 5 → item 3), the item corresponding to the fixed item 2 whose item name is “item 3” may be the physical item of item 3 of the table file C63c. Is shown.

Next, a case where the physical item of another sub-file is used for the relation between the physical item of the main file and the physical item of the sub-file will be described. For example, the table file A63a having the physical item corresponding to the fixed item 1 whose item name is "item 1" does not have the physical item common to the detail file A62a which is the main file. In such a case, the relation is set using the physical item of the other slave file.

More specifically, the relation of the fixed item 1 having the item name “item 1” includes the main file (key 1) →
The setting is made using the physical items of the detail file B62b, which is the sub-file 1, such as sub-file 1 (key 1 → key 2) → sub-file 2 (key 2 → item 1), so that the main file is set. The physical item "key 1" of the specification file A62a and the physical item "item 1" of the table file A63a are associated with each other, and indicate the relationship between the specification file A62a, the specification file B62b, and the table file A63a having a hierarchical structure. .

Here, a set of physical items (a set of key 1 and key 2 in this example) used for association in the subsidiary file 1 that mediates both is called a connection key.

Similarly, for the relation of the total item 1 whose item name is “item 2”, the main file (key 3) → the sub file 3 (key 3 → key 4) → the sub file 4 (key 4 →
The relationship between the detail file A62a, the table file B63b, and the detail file C62c, which are set as item 2) and have a hierarchical structure, is shown, and the linked key at this time is “key 3 → key 4”. It is also shown.

Next, the setting of the analysis axis and the lower item names in FIG. 2B will be described. For example, in the product management business, depending on the item set as a classification item, such as “product genre” ⊃ “product item” ⊃ “(individual) product”, it is included between one item and another item. May have a relationship. The analysis axis and the lower item names set this inclusion relation.

In the analysis axis, the inclusion relation is specified, and the system administrator freely sets a convenient name for identifying the inclusion relation. For example, for the above-mentioned inclusion relation, set a name such as "product axis", and when the items such as "year" ⊃ "quarter" ⊃ "month" are set as classification items, Set a name such as "time axis".

In the lower item name, an item name of an item that is a lower layer of the item corresponding to the item set in the item name in the inclusion relation described above is set. For example, in the merchandise management operation described above, “commodity item” and “commodity item” are set as subordinate item names of the classification items of the item names “commodity genre” and “commodity item”, respectively.

The utility of the setting for the inclusion relation will be described later. Next, with regard to the total file name shown in FIG.

FIG. 4 is a diagram for explaining the handling of total items. In the configuration example of FIG. 1, the SOVF 71 is stored in the virtual file 31, and the detailed files A and B (72, 7
3) and the table file 74 are stored in the DWH 22.

In the SOVF 71, the object to be managed, the classification item 1, the total item 1 are not shaded in FIG.
And 2, fixed item 1 are preset by the end user, and are totaled items 3 shaded.
The fixed item 2 is a hidden item set by the system administrator (an item that is not set by the end user and cannot be referred to by the end user who operates the user editor 41) for the reason described below. Items).

In the figure, the relations of the management target, the classification item 1, the total item 1, and the fixed item 1 are all set in the item of the detail file A which is the main file, as shown by the solid line arrows in FIG. is there.

Here, the item desired by the end user in the total item 2 is derived by multiplying “the total item 3” which is the physical item of the detail file B73 by “the fixed item 2” which is the physical item of the table file 74. Suppose you can do it.

However, in this example, since the end user has not set the tally item 3 and the fixed item 2 in the SOVF 71, the tally item 3 and the fixed item 2 cannot be referred to, and the tally item 2 cannot be derived as it is.

Therefore, in such a case, the system administrator sets the total item 3 and the fixed item 2 to SOVF71.
As a hidden item, and the relationship between the two items is set as “classification item 1 →
"Fixed item 2" and "management target → total item 3" are set. Then, as shown in FIG. 2C, by setting information indicating a calculation instruction of “(total item 3) × (fixed item 2)” to the physical item name of total item 2, the end user's desired It becomes possible to derive an item corresponding to the aggregation item 2 to be executed.

By the way, in the example of FIG.
Is calculated by accessing the specification file A72, the specification file B73, and the table file 74 stored in the DWH 22, which requires a great deal of access time and calculation time. However, in this calculation, the same operation is repeated every time the total item 2 is selected as described later. Therefore, once this item is derived, it is more efficient to save the result. It is.

The total file name in FIG.
As described above, this is a part for setting the name of a file created to store the tally result once derived for the purpose of improving efficiency. Note that, in the example of FIG. 1, the tally file is accumulated in, for example, the DWH 22.

Next, in the database system shown in FIG. 1, the SOV by the end user and the system administrator
The procedure for creating F will be described. FIG. 5 is a flowchart showing a procedure for creating the SOVF.

First, the procedure for setting the SOVF stored in the virtual file 31 by the end user will be described with reference to FIG. First, in step S101, file properties are set using the user editor 41, that is, the file name, management target, comment, owner, and creation date shown in FIG. 2A are set.

In the procedure from step S102 to step S103, the user editor 41 is used to set the classification, totalization, and fixed items, ie, as shown in FIG.
Item names, slip / form names, and comments shown in (b), (c), and (d) are individually set for each item.

As described above, after setting the items that need to be set by the end user in the SOVF, in step S104, the system administrator is informed of the correspondence information with the DWH 22, that is, FIGS. 2 (b), (c), ( d), requesting the setting of the administrator setting items in
The OVF creation operation ends.

Next, the procedure of the setting performed by the system administrator for the SOVF stored in the virtual file 31 will be described with reference to FIG. First, in step S201, FIG. 2 (b), (c),
In (d), all the item names are determined as to whether or not physical items (or physical items necessary for deriving items in the aggregation items) corresponding to the items set by the end user are stored in the DWH 22. To investigate.

In step S202, as a result of the investigation in the previous step, the setting work proceeds with the procedure from step S203 on the items not stored in the DWH 22, and the procedure from step S207 on the items stored in the DWH 22. To proceed with the settings.

In step S203, it is further checked whether or not physical items corresponding to the items set by the end user (and physical items necessary for deriving the items as tally items) are stored in the core database 21. The setting operation is performed for the stored items in the procedure from step S204, and the setting operation is performed for the non-stored items in the procedure from step S205.

In step S204, the file having the corresponding physical item is stored in the main database 21 from the DWH2.
Then, the setting work proceeds in the procedure from step S207.

In step S205, it is notified that the item corresponding to the item name set by the end user cannot be derived, and in step S206, the item is replaced with the SOV
Delete from F.

In step S207, FIG.
The physical file names shown in (c) and (d) are set using the administrator editor 42. In step S208,
In setting the relation, the main file is determined based on the management target setting of the file property.

In step S209, the administrator editor 4
2, the relation based on the main file determined in the previous step is set in the corresponding portion shown in FIGS. 2 (b), (c) and (d).

In step S210, FIG.
The type and number of digits of each item shown in (c) and (d) are set by the administrator editor 42. In step S211, FIG.
Item classifications shown in (b), (c), and (d) are set by the administrator editor 42 in consideration of the setting contents described above.

In step S212, the setting work proceeds in steps S213 and subsequent steps for the item whose item classification set in the previous step is "classification item", while the setting work is performed in steps S214 and thereafter for the other items. Advance.

In step S 213, the inclusion editor, ie, the analysis axis and the lower-level item names shown in FIG. In step S214, a total file name shown in FIG. 2C is set by the administrator editor 42 for an item whose item classification set in step S211 is “total item”.

As described above, when the items that need to be set by the system administrator in the SOVF are completed, in step S215, the end of the SOVF by the system administrator is notified to the end user, and the system administrator is notified. Is completed.

According to the procedure described above, the SOVF
Is completed. Next, a tallying procedure for data analysis using SOVF in the database system shown in FIG. 1 will be described.

FIG. 6 is a flowchart showing the procedure of a data inquiry process performed by the inquiry processing engine 32 in the application server 12 shown in FIG. This data inquiry process is realized, for example, by a CPU (not shown) of the inquiry processing engine 32 executing a control program stored in a ROM (not shown) of the inquiry processing engine 32.

First, the flowchart shown in FIG. 6 will be described, and then, a data totaling procedure using a specific data example will be described. In FIG. 6, step S
At 301, the SOV stored in the virtual file 31
An end user operating the user editor 41 selects an inquiry item to the DWH 22 necessary for desired data analysis from the items set in F. Here, in order that even an end user unfamiliar with the operation of the user editor 41 can easily make this selection, S
A so-called GUI (Graphical User Interface) environment is provided in which a list of items set in the OVF is displayed on the user editor 41, and necessary items are selected from the list display. Here, regarding the selection of the classification items (including the management target), the order from the lower end to the upper end of the inclusive relation considered by the end user (for example, product, item, genre,
In order).

In step S302, the virtual file 31
The setting information corresponding to the item selected in the previous step is obtained from the SOVF stored in. Step S30
In No. 3, a step for confirming the item selection by the end user is provided. For all the classification items included in the setting information for each item acquired in the previous step, the inclusion relations set for each are acquired, and the inclusion relations are inconsistent with the order selected by the end user in step S301. To see if it was correct. As a result, if it is determined that the selection order of the end user is normal, the process proceeds to step S304. On the other hand, if there is a contradiction between the inclusion relation and the selection order, the content is displayed on the user editor 41, and the current inquiry processing is terminated with an error. This determination step is provided to confirm whether the end user's recognition of the inclusion relation is appropriate.

In step S304, step S302
The physical file set in the setting information of the summary item obtained in (If the summary item that requires calculation is selected, there is a physical file to be calculated or a file in which the calculation results have already been saved. Acquires the name of the aggregation file (if any). These files are collectively called target files. In the example of FIG. 3, “detailed file A”, “detailed file B”, “detailed file C”, “table file A”, “table file B”, and “table file C” are acquired.

In step S305, only one target file is obtained from the target file name obtained in the previous step.
That is, if all physical items corresponding to the end user's inquiry item are included in only one physical file, the process proceeds to step S306. On the other hand, if there are two or more target files as in the example of FIG. 3, the process proceeds to step S307.

In step S306, an SQL sentence for inquiring each physical item in the target file corresponding to each inquiry item from the end user is automatically created, and the created SQL sentence is sent to the DWH 22 to inquire about data. The data inquiry processing ends. The derived data, which is a response from the DWH 22 to this data inquiry, is sent to the user editor 41 and displayed.

In step S307, the virtual file 31
Is detected using the management target set in the SOVF stored in the target file as a key. That is, as described above, the connection relationship between all target files is detected based on the management target and the relation set in the setting information of each item acquired in step S302.

In step S308, step S302
In the setting information corresponding to each inquiry item from the end user obtained in the above, from the relation of each item that is a classification item and a total item, a connection key indicating a connection relationship of the target file storing the classification item and the total item A set of classification items used as a group is extracted. In the example of FIG. 3, the link key “key 3 → key 4” is extracted.

In step S309, it is determined whether or not there is an inclusive relation of the set of classification items used as the concatenated key extracted in the previous step, that is, whether or not they are of the same total classification. The process proceeds to step S311. On the other hand, if there is no inclusion relationship, the process proceeds to step S310. In the example of FIG. 3, it is determined that an inclusive relation exists in “key 3 → key 4”.

In step S310, an SQL statement for querying each physical item is automatically created by combining target files corresponding to each item desired by the end user, and the created SQL statement is sent to the DWH 22 to inquire data. This data inquiry processing ends. The derived data, which is a response from the DWH 22 to this data inquiry, is sent to the user editor 41 and displayed.

In step S311, step S301
In, one target file having an item corresponding to a total item as a physical item among the inquiry items selected by the end user is selected. In the example of FIG. 3, the detail file C62c is selected as a target file having an item corresponding to the total item 1.

In step S312, step S311
With respect to the classification item that is the key of the total item of the target file selected in step, the inclusion relationship with the connection key determined in step S309 is determined. As a result, if the key is a classification item having a higher inclusive relation than the classification item currently being counted, the process proceeds to step S313. On the other hand, if not, the process proceeds to step S314.

In step S313, step S311
Without inquiring about the target file selected in, a null value (Null value) is sent to the user editor 41 as a derivation result of the total item and displayed, and the process proceeds to step S315.

In step S314, step S311
Automatically creates an SQL statement for inquiring the total item in the target file selected in step 2.
2 to perform data inquiry, and obtain derived data as a response from the DWH 22 to the data inquiry. The derived data, which is a response from the DWH 22 to this data inquiry, is also sent to the user editor 41 in parallel.

In step S315, step S311
With respect to the classification item that is the key for the total item of the target file selected in step, the inclusion relation in the connection key determined in step S309 is determined. As a result, if there is a higher total classification for a classification item having a higher inclusion relation than the key, step S3
Proceed to 16. On the other hand, if not, step S
Proceed to 317.

In step S316, step S311
With respect to the total item selected in the above step, for the classification item having a higher inclusive relation in the previous step, calculation for deriving the total item is performed using the derived data acquired from the DWH 22 in step S314, and the result is referred to as the user editor 41. To be displayed.

In step S317, step S301
It is determined whether or not the processing from step S311 has been performed on all the inquiry items selected by the end user in step. If all the processing has been completed, the current data inquiry processing ends. On the other hand, if an unprocessed inquiry item remains, the process returns to step S311 and repeats the above-described processing.

The data inquiry process is executed according to the above procedure. In the above-described processing, when an SQL statement is created and an inquiry is made to the DWH 22, the content of the inquiry is a total item, and a calculation formula is set to the physical item name of the total item in SOVF. If so, calculation is performed on the derived data obtained from the DWH 22 according to the calculation formula, and the calculation result is sent to the user editor 41 for display. In this case, if a tally file exists for the tally item, the corresponding tally file is sent to the user editor 41 and displayed instead of the inquiry to the DWH 22.

Next, the procedure of data aggregation using a specific data example will be described with reference to the flowchart shown in FIG. FIG. 7 is a diagram showing the relation of each file in an example used for explaining the procedure of data aggregation. First, the relationship between the files in this case will be described with reference to FIG.

In FIG. 7, SOVF in which necessary settings have already been completed by the end user and the system administrator
81 is an item name "product" as a classification item to be managed, an item name "item" as a classification item 1,
Further, the item name “sales result” is set as the total item 1 and the item name “sales plan” is set as the appropriate item classification as the total item 2, and stored in the virtual file 31 of the system shown in FIG. I have. Each of these items in FIG. 7 shows only the items that the end user selects for the subsequent tabulation analysis.
It is assumed that other items are actually set in 81.

The sales result file 82a, the sales plan file 82b, which are the target files related to the above-described items,
Each file of the product table 83 is stored in the DWH 22 of the system shown in FIG.

In each file, items corresponding to “product” and “sales result” are stored in the sales result file 82a as physical items, and “items” are stored in the sales plan file 82b as physical items.
In addition, the product table 83 has items corresponding to “products” and “items”, respectively. The sales record file 82a and the sales plan file 82b are detailed files.

Here, in setting the relation of each item, since the sales record file 82a is selected as the main file, the sales plan file 82b and the product table 83 are the sub-files.

The relation of each file in the example shown in FIG. 7, that is, the connection relationship, is set as follows, as shown by the solid and broken arrows in FIG. "Product": Sales performance file (product) "Item": Sales performance file (product) → Product table (product → item) "Sales performance": Sales performance file (sales performance) "Sales plan": Sales performance file (product) ) → commodity table (commodity → item) → sales plan file (item → sales plan) Here, “commodity → item” in “sales plan” is a link indicating the connection between the actual sales file 82a and the sales plan file 82b. Is the key.

It is assumed that an inclusive relation of “item” 品目 “commodity” is set. Now, the procedure of data aggregation in the case shown in FIG. 7 will be described with reference to the flowchart shown in FIG.

FIG. 8 is a diagram for explaining how data is aggregated in the case shown in FIG. In the figure, an inquiry processing engine 32 includes an inquiry item analysis unit 91, S
And a QL sentence creating unit 92.

[0112] The inquiry item analysis unit 91 performs the DWH necessary for analyzing the desired data selected by the end user.
22 is analyzed with reference to various information set in the SOVF 81, and the analysis result is passed to the SQL statement creation unit 92.

The SQL statement creation unit 92 creates an SQL statement for inquiring the DWH 22 to derive desired data based on the analysis result passed from the inquiry item analysis unit 91.

First, the inquiry item analysis unit 91 determines whether the SO
A GUI display 94, which is a list of items set in the VF 81, is displayed on the user editor 41, and the end user selects an inquiry item 93 necessary for desired data analysis from the list display (steps S301 to S301).
S302). In FIG. 8, “product”, “item”, “sales record”,
It is assumed that each item of “sales plan” is selected by the end user. It is assumed that the inclusive relation that the end user has considered is appropriate (the determination result in step S303 is “YES”).

Next, the inquiry item analysis unit 91 acquires the name of the target file required to acquire the data specified by the inquiry item selected by the end user (step S304), and The number is detected (step S305). In the example of FIG. 7, the target files are the sales record file 82a (the target file of the management target and the total item 1), the product table 83 (the classification item 1).
(The target file of step S305) and the sales plan file (the target file of the tally item 2) (the determination result in step S305 is “NO”).

Therefore, the inquiry item analysis unit 91 sets the S
The management target “product” set in the OVF 81 and the SOVF 81 corresponding to the end user inquiry item 93
Then, the relationship between the target files is detected from the relation set in the setting information (step S307).

Then, from the relation between the classification item and each item that is a totaling item, “merchandise → item” which is a concatenated key related to “item” which is a key derived for totaling item 2 “sales plan” is extracted (step). S308).

Further, the inquiry item analysis section 91 checks the SO
It is determined whether or not the setting information of the VF 81 has an inclusive relation about this concatenated key (step S309).
The inclusion relation of “item” ⊃ “goods” is determined (the determination result of step S309 is “YES”).

Thereafter, the inquiry item analysis unit 91 first selects the sales result file 82a, which is the target file for the total item “sales result”, in order to total the “sales result” for the “product” (step S311). ), The inclusion relation of the “commodity”, which is the key for deriving the “sales record” in the file, in the connection key is determined (step S312). As a result, since “product” is currently targeted for aggregation, the inclusion relation is determined that “product” is not higher than “item” (“item” is higher than “product”). (The determination result of step S312 is “NO”).

To obtain the data, the inquiry item analysis unit 91 determines the correspondence between “product” and “sales record” in the sales record file 82a and “commodity” and “item” in the product table 83. Is given to the SQL sentence creating unit 92 to create an SQL sentence (A) for inquiring of the DWH 22 about the above.
Then, the SQL statement creating unit 92 obtains the item, the product in the sales result file, and the sales result from the sales result file and the product table in the order of the items. However, the sales result file and the product table are the same record using the product as a key. The following SQL statement (A) is created and sent to the DWH 22 to inquire about data.

SELECT Item, sales record file, product, sales record FROM Sales record file, product table WHERE sales record file. Product = product table. Commodity ORDER BY Item. The DWH 22 searches for specific target information based on the SQL sentence (A), and the search result is sent back from the DWH 22 (Step S314).

Here, the inquiry item analysis unit 91 connects the “product”, which is a key for deriving “sales record”, in the sales record file 82a which is the target file for the total item 1 “sales record”. The inclusion relation in the key is determined (step S315). As a result, it is determined that the tallying of “items” having a higher inclusive relation in the link key of “product” is necessary (the determination result in step S315 is “YES”).

Therefore, the inquiry item analysis section 91
A calculation for deriving the “sales record” corresponding to the “item”, that is, the total of the product data is performed using the derived data sent from the DWH 22, and the result is used by the user editor 4.
1 for display (step S316).

Subsequently, the inquiry item analysis section 91 performs a process for the total item 2 "sales plan" (the determination result in step S317 is "NO"). The inquiry item analysis unit 91 selects the sales plan file 82b, which is the target file for the “sales plan”, in order to compile the “sales plan” for the “product” (Step S311), and uses the “sales plan” in the file. Then, the inclusion relation in the concatenated key with respect to “item” which is the key for deriving the “. As a result, since “commodity” is currently targeted for aggregation, it is determined that “commodity” is higher than “commodity” in the inclusion relationship in the concatenated key for “item” (the determination result in step S312). Is "YES").

Therefore, unlike the above-mentioned “sales result”, the inquiry item analysis unit 91 does not acquire the data of the “sales plan” for the “product”, and sets a null value as a derivation result of the “sales plan”. (Null value) is sent to the user editor 41, and a display indicating that the “sales plan” for the “product” is null is displayed (step S).
313).

Here, the inquiry item analysis unit 91 connects the “item” that is the key for deriving the “sales plan” in the sales plan file 82b that is the target file for the total item 2 “sales plan”. The inclusion relation in the key is determined (step S315). As a result, it is determined that none of the linked keys for “item” has a higher inclusive relation (“commodity” has a lower inclusive relation) and that there is no item to be totaled (determination result in step S315) Is "NO").

Thereafter, the inquiry item analysis unit 91 performs the process again on the “item” in the total item 2 “sales plan”, so performs the process again (the determination result in step S317 is “NO”).

The inquiry item analysis unit 91 selects the sales plan file 82b, which is the target file for the “sales plan”, in order to tabulate the “sales plan” for the “item” (step S311). The inclusion relation of the “item” that is the key for deriving the “sales plan” in the concatenated key is determined (step S31).
2). As a result, since “items” are currently targeted for aggregation, the inclusion relation is determined not to be higher (the determination result in step S312 is “NO”).

Therefore, the inquiry item analysis unit 91 sets “item” and “sales plan” in the sales record file 82a.
For inquiring the DWH 22 about the correspondence
An instruction to create the QL sentence (B) is given to the SQL sentence creating unit 92. Then, the SQL sentence creating unit 92 creates the following SQL sentence (B), such as “Get the sales plan value for each item from the sales plan file in the order of the items”, and sends it to the DWH 22 to inquire about the data. Do.

SELECT Item, Sales Plan FROM Sales Plan ORDER BY Item. The DWH 22 searches for specific target information based on the SQL sentence (B), and the search result is sent back from the DWH 22 (Step S314).

Here, the inquiry item analysis section 91
In the sales plan file 82b, which is the target file for the “sales plan”, the inclusion relation in the consolidation key for the “item” that is the derived key of the “sales plan” is determined, but there is no item to be aggregated (The determination result in step S315 is “NO”).

As described above, the inquiry item analysis section 91
Completes the processing for all the inquiry items (the determination result in step S317 is “YES”). FIG. 9 is a diagram illustrating the utility of the inclusion relationship set for the classification items.

FIG. 23 shows the output display result to the user editor 41 in the case described with reference to FIGS. FIG. 11A shows the result when the above-described inclusion relation of “item” ⊃ “product” is appropriately set, while FIG. 10B shows the result when the inclusion relation is not set. Is the result of

The standard (non-extended) SQL can handle only so-called normal-form tables in which rows and columns have a one-to-one correspondence. Problems may occur. FIG. 9B is just an example of such a case, in which an incorrect result is output due to an improper treatment when the aggregation hierarchy is different.

The setting of the inclusion relation gives information for taking appropriate measures in such a case. With this setting, an appropriate output as shown in FIG. 9A can be immediately obtained as a result of the above-described processing, and the system administrator (or end user) confirms the tally result, which was conventionally required. -It is possible to eliminate the need for work such as correction.

[0136]

The present invention is configured as described above in detail, so that even an end user who does not have specialized knowledge about the database can freely derive necessary data from the database. In addition, there is an effect that the system administrator can also reduce the work load of providing individual support for data analysis performed by the end user.

[Brief description of the drawings]

FIG. 1 is a diagram showing an overall configuration of a database system that implements the present invention.

FIG. 2 is a diagram showing a structure of a purpose-specific virtual file.

FIG. 3 is a diagram illustrating setting of a relation.

FIG. 4 is a diagram showing the handling of total items.

FIG. 5 is a flowchart illustrating a procedure for creating a purpose-specific virtual file.

FIG. 6 is a flowchart illustrating a procedure of a data inquiry process.

FIG. 7 is a diagram showing a relation of each file in an example used for explaining a procedure of data aggregation.

FIG. 8 is a diagram illustrating a state of data aggregation in the example illustrated in FIG. 7;

FIG. 9 is a diagram illustrating a utility of an inclusion relation set for a classification item.

[Explanation of symbols]

 11 Data Server 12 Application Server 13 Client 21 Core Database 22 Data Warehouse 31 Virtual File 32 Query Processing Engine 41 User Editor 42 Administrator Editor

Continuation of the front page (72) Inventor Toshio Higashihara 3-2-1 Sakaemachi, Hamura-shi, Tokyo Casio Computer Co., Ltd. Hamura Technical Center F-term (reference) 5B075 NK02 PP13 PP22 PQ15 QR00 QT06

Claims (7)

[Claims] 1. A user who uses a database that stores a plurality of data files that are associated with each other by a management item of the data file and individually obtains various derived items that the user wants to derive. A step of defining the defined name, the information based on the data structure in the database for the management item necessary for deriving the derived item, and the association between the name and the information, respectively, A step of acquiring the contents of the management items necessary for deriving a derived item corresponding to the inquiry item from the database using information defined by the definition procedure, according to the inquiry item created by selecting the name; Deriving a derived item corresponding to the inquiry item from the content of the management item; and Information derivation method in database system. 2. The method according to claim 1, further comprising the step of defining the inclusion relation when an inclusion relation exists between the management items. In the step of deriving the derived item, a management item necessary for deriving the derived item is provided. 2. The information deriving method according to claim 1, wherein when the inclusion relation is defined, the derived item is derived based on the inclusion relation. 3. When deriving the derived item requires a calculation based on the content of the management item, the method further comprises a step of defining a procedure of the calculation. In the step of deriving the derived item, If a calculation based on the content of the management item is required to derive the derived item, the derived item is derived by calculating based on the defined procedure of the calculation. An information deriving method in the database system according to claim 1. 4. When a calculation based on the contents of the management item is required to derive the derived item, the method further comprises a procedure for defining the calculation and a name of a file for storing the result of the calculation. In the step of deriving the derived item, when a calculation based on the content of the management item is required to derive the derived item, the calculation previously performed in a file storing the result of the calculation is performed. 2. The information deriving method according to claim 1, wherein when the result is stored, the derived result of the derived item is determined based on the result of the calculation stored in the file. . 5. A user who uses a database storing a plurality of data files which are interconnected by a management item of the data file, individually obtains various derived items desired to be derived by the user. A logical file in which a defined name, information based on a data structure in the database for the management item necessary for deriving the derived item, and a relationship between the name and the information are defined in association with each other. According to the inquiry item created by the selection from the name, the contents of the management items required to derive a derived item corresponding to the inquiry item, from the database using the information defined in the logical file And deriving means for deriving a derived item corresponding to the inquiry item from the content of the management item. Information deriving system in a database system to butterflies. 6. When read by a computer connected to a database system having a database that stores a plurality of data files that are interconnected by a management item of the data file, the computer stores the data files in the computer. A storage medium storing a program for deriving the information of the name, which is individually defined by the user for various derived items that the user using the database desires to derive, The management item required for deriving the derived item is created by selecting the name based on a definition in which the information based on the data structure in the database and the association between the name and the information are associated with each other. Management items necessary for deriving a derived item corresponding to the inquiry item according to the inquiry item Storing a program for causing the computer to execute: a procedure for acquiring the contents of the eyes from the database; and a procedure for deriving a derived item corresponding to the inquiry item from the contents of the management item. Computer readable storage medium. 7. A user who uses a database storing a plurality of data files which are interconnected by a management item of the data file and individually obtains various derived items desired to be derived by the user. An area for storing a name to be defined; and information based on a data structure in the database for the management item required for deriving the derived item, wherein the information defining a relationship with the name data is stored. And a reference to obtain, from the database, contents of management items necessary for deriving a derived item corresponding to an inquiry item created by selection from the name. Computer readable storage medium.