System and method of formulating queries in relational databases

• US 7,296,040 B2
• Filed: 04/29/2004
• Issued: 11/13/2007
• Est. Priority Date: 04/29/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: Cognos Incorporated (International Business Machines Corporation)
• Inventors: Cazemier, Henk, He, Xiaowen
• Primary Examiner(s): Lee; Wilson
• Application Number:
  US10/835,522
• Publication Number:
  US 20050015367A1
• Time in Patent Office: 1,293 Days
• Field of Search: 707/100, 707/102, 707/104.1, 707/105, 707 1- 10, 707/101, 707/203, 707/204
• 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 and preventing multiple-counting in a computer-based business intelligence system, using a client application receiving user inputs and a metadata model containing model objects that represent data sources, the method comprising the steps of:

a) defining a model based on an existing database comprising a plurality of query subjects, each of the plurality of 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 the plurality of query subjects within the model according to 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;

wherein the step of generating the hierarchy of sets and subsets of key query items further 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 a potential for multiple counting by relating a first subset of the first set of key query items of the first query subject to a second query subject;

iii. 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 where the potential for multiple counting is detected; and

iv. orgranizing the sets and subsets of key query items into a hierarchy so that the highest level of the hierarchy has 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.

2. A system for creating a report and preventing multiple-counting 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 a plurality of query subjects, each of the plurality of 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 the plurality of query subjects within the model according to 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 subquerics; and

g) means for producing the report;

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 a 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 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 storage medium readable by a computer encoding a computer program for execution by the computer to carry out a method for creating a report and preventing multiple-counting, for use in a computer-based business intelligence system, the computer program comprising:

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

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

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

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

e) code 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) code means for accessing the data using the subqueries; and

g) code means for producing the report;

wherein the code means for generating further includes;

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

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

iii. code 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. code means for organizing the sets and subsets of key query items into a hierarchy wherein the highest level of the hierarchy has 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.