System and method of formulating queries in relational databases

• US 20050015367A1
• Filed: 04/29/2004
• Published: 01/20/2005
• Est. Priority Date: 04/29/2003
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: Cognos Incorporated (International Business Machines Corporation)
• Inventors: He, Xiaowen, Cazemier, Henk
• Granted Patent: US 7,296,040 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06Q 40/02 Banking, e.g. interest calculation or account maintenance credit or loans G06Q40/03
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99955 Archiving or backup

Abstract

The invention introduces a method and system to automatically derive the relationships between query subjects and query items within query subjects so that problems such as those that result in double courting of information are avoided. It provides for creating a report by first defining a model based on an existing database comprising two or more query subjects, each query subject comprising at least one fact query item and one primary key, then generating a hierarchy of sets and subsets of key query items within the model. Next a further fact is introduced into one of the query subjects. The expression is decomposed into sub-queries to prevent double counting of data in ambiguously defined situations. This is achieved using the introduced fact to determine that such decomposition is required. Finally, the data is accessed using the sub-queries, and a report is produced.

Claims

1. A method of creating a report in a computer-based business intelligence system, using a client application receiving user inputs and a metadata model containing model objects that represent the data sources, the method comprising the steps of:

a) defining a model based on an existing database comprising at least two query subjects, each query subject having at least one fact query item and one primary key;

b) generating a hierarchy of sets and subsets of key query items in query subjects within the model according to rules for computation of the dimensional information;

c) accepting an input request from a user to define contents of the report;

d) determining an expression to extract data from the database to fulfill the input request;

e) decomposing the expression into subqueries using the hierarchy of sets and subsets of key query items to prevent multiple-counting of data;

f) accessing the data using the subqueries; and

g) producing the report.

2. The method of claim 1 wherein the step of generating the hierarchy of sets and subsets of key query items comprises the steps of:

i. computing a unique set of determinants for a first query subject where a determinant is related to a first set of key query items;

ii. detecting the potential for multiple counting by relating a first subset of the first set of key query items of the first query subject to the second query subject;

iii. where the potential for multiple counting is detected relating a second and further subsets of the first subset of the fist set of key query items of the first query subject to another query subject; and

iv. organizing the sets and subsets of key query items into a hierarchy so that the highest level of the hierarchy has the fewest key query items and each lower level has more key query items, and each higher level has a set of key query items that is a subset of its immediate lower level, to resolve potential multiple counting of query items.

3. A system for creating a report in a computer-based business intelligence system, using a client application receiving user inputs and a metadata model containing model objects that represent the data sources, comprising:

a) means for defining a model based on an existing database comprising at least two query subjects, each query subject having at least one fact query item and one primary key query item;

b) means for generating a hierarchy of sets and subsets of key query items in query subjects within the model according to rules for computation of the dimensional information;

c) means for accepting an input request from a user to define contents of the report;

d) means for determining an expression to extract data from the database to fulfill the input request;

e) means for decomposing the expression into subqueries using the hierarchy of sets and subsets of key query items to prevent multiple counting of data;

f) means for accessing the data using the subqueries; and

g) means for producing the report.

4. The system of claim 3 wherein the means for generating the hierarchy of sets and subsets of key query items includes:

i. means for computing a unique set of determinants for a first query subject where a determinant is related to a first set of key query items;

ii. means for detecting the potential for multiple counting by relating a first subset of the first set of key query items of the first query subject to the second query subject;

iii. means for relating a second and further subsets of the first subset of the first set of key query items of the first query subject to another query subject; and

iv. means for organizing the sets and subsets of key query items into a hierarchy wherein the highest level of the hierarchy has the fewest key query items and each lower level has more key query items, and each higher level has a set of key query items that is a subset of its immediate lower level, to resolve potential multiple counting of query items.

5. A computer-readable medium having computer-executable instructions for performing a method for creating a report in a computer-based business intelligence system, using a client application receiving user inputs and a metadata model containing model objects that represent the data sources, comprising:

a) code for defining a model based on an existing database comprising at least two query subjects, each query subject having at least one fact query item and one primary key query item;

b) code for generating a hierarchy of sets and subsets of key query items in query subjects within the model the according to rules for computation of the dimensional information;

c) code for accepting an input request from a user to define contents of the report;

d) code for determining an expression to extract data from the database to fulfill the input request;

e) code for decomposing the expression into subqueries using the hierarchy of sets and subsets of key query items to prevent multiple counting of data;

f) code for accessing the data using the subqueries; and

g) code for producing the report.

6. The computer-readable medium of claim 5 further including:

i. code for computing a unique set of determinants for a first query subject where a determinant is related to a first set of query items;

ii. code for detecting the potential for multiple counting by relating a first subset of the first set of key query items of the first query subject to the second query subject;

iii. code for relating a second and further subsets of the first subset of the first set of key query items of the first query subject to another query subject; and

iv. code for organizing the sets and subsets of key query items into a hierarchy wherein the highest level of the hierarchy has the fewest key query items and each lower level has more key query items, and each higher level has a set of key query items that is a subset of its immediate lower level, to resolve potential multiple counting of query items.