System and method for planning and generating queries for multi-dimensional analysis using domain models and data federation

• US 7,716,174 B2
• Filed: 10/31/2007
• Issued: 05/11/2010
• Est. Priority Date: 01/18/2005
• Status: Expired due to Fees

• Current Assignee: ServiceNow Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Mazzoleni, Pietro, Lee, Juhnyoung, Sairamesh, Jakka, Touma, Maroun
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): HO, BINH VAN
• Application Number:
  US11/930,917
• Publication Number:
  US 20080046419A1
• Time in Patent Office: 923 Days
• Field of Search: 707/4
• US Class Current: 707/600
• CPC Class Codes: G06F 16/24522 Translation of natural language queries to structured queries
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: Y10S 707/957 Multidimensional
• CPC Class Codes: Y10S 707/971 Federated
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99936 Pattern matching access

Abstract

Data integration and data analysis using computing equipment, software as well as hardware, includes a system and method for integrating data from various data sources, structured and unstructured, without physically creating a data warehouse and automatically generating queries for analysis of the integrated data from a multitude of different views.

Claims

1. A system for planning and generating database queries using a multitude of data sources in various formats and media, which enables multi-dimensional analyses without physically building a data warehouse, the system comprising:

a memory;

a domain model which provides a formal description of a domain of interest, comprising classes which are concepts in a domain of discourse, properties of each class describing its attributes including its relations with other classes and constraints on properties in the classes;

a mapping server which generates a domain model specific to a set of data sources by specifying mapping between entities in said domain model and entities in the data sources;

an ontology query generator which translates one or more queries in a natural language format to generate one or more ontology queries which can be understood in terms of said domain model;

an ontology query server which infers one or more answers to ontology queries by reasoning with said domain model;

a SQL query plan generator which composes one or more query plans using a standard query language for relational databases for multi-dimensional analysis including one or more of data grouping, roll-up and drill-down, pivoting, and changing the focused class of the current multi-dimensional analysis;

a data federation system which builds one or more adaptors for individual data sources which make data sources appear as relational databases and allow them to be accessed by using a standard query language for relational databases and which also optimizes query plans for performance and processes queries to retrieve data from one or more data sources; and

a report generator which provides data retrieved from data sources for multi-dimensional analysis.

2. A system for planning and generating database queries as set forth in claim 1, where said report generator provides data retrieved from data sources as a display for a user on a graphical user interface.

3. A system for planning and generating database queries as set forth in claim 1, where a domain model specific to a set of data sources is a domain model augmented with mapping information between entities in the domain model and entities in the data sources.

4. A system for planning and generating database queries as set forth in claim 1, where multi-dimensional analysis includes:

one or more multi-dimensional views on a fact data class across its dimensions;

aggregation on the fact data class;

roll-up and drill-down operations on one or more dimensions by using one or more hierarchical organizations of the dimension data;

extended analysis to one or more dimensions of the current dimensions; and

change of the current fact data class to another class in the domain model.

5. A system for planning and generating database queries as set forth in claim 1, where said mapping server automatically determines mapping information between entries in the domain model and entries in data sources by using one or more of machine learning, heuristics, and statistical techniques applied to instance data.

6. A method for planning and generating database queries for multi-dimensional analysis using domain models and data federation comprising the steps of:

creating a domain model for a domain of discourse to support multi-dimensional analysis;

mapping between entities in the domain model and entities in data sources;

building one or more adaptors for individual data sources which make data sources appears as relational databases and allow the data sources to be accessed by using a standard query language of relational databases;

composing one or more queries for multi-dimensional analysis;

translating the one or more queries in a natural language and generating one or more ontology queries which can be understood in terms of the domain model;

inferring one or more answers to the ontology queries by using a reasoning component and the domain model;

composing one or more SQL query plans for multi-dimensional analysis including one or more data grouping, roll-up and drill-down, pivoting, and changing the focused class of the current multi-dimensional analysis;

submitting the SQL query plans to a data federation system for processing and retrieving data from one or more data sources; and

providing the retrieved data for multi-dimensional analysis.

7. A method for planning and generating database queries for multi-dimensional analysis as set forth in claim 6, further comprising optimizing the query plans for improved performance.

8. A method for planning and generating database queries as set forth in claim 6, where said providing the retrieved data comprises displaying the retrieved data for a user on a graphical user interface.

9. A method for planning and generating database queries as set forth in claim 8, further comprising the user interacting with the retrieved data and composing one or more subsequent queries in a natural language for iteratively providing subsequent queries.

10. A memory storing a program of machine-readable instructions executable by a digital processing apparatus to perform operations for planning and generating database queries for multi-dimensional analysis using domain models and data federation, the operations comprising:

creating a domain model for a domain of discourse to support multi-dimensional analysis;

mapping between entities in the domain model and entities in data sources;

building one or more adaptors for individual data sources which make data sources appears as relational databases and allow the data sources to be accessed by using a standard query language of relational databases;

composing one or more queries for multi-dimensional analysis;

translating the one or more queries in a natural language and generating one or more ontology queries which can be understood in terms of the domain model;

inferring one or more answers to the ontology queries by using a reasoning component and the domain model;

composing one or more SQL query plans for multi-dimensional analysis including one or more data grouping, roll-up and drill-down, pivoting, and changing the focused class of the current multi-dimensional analysis;

submitting the SQL query plans to a data federation system for processing and retrieving data from one or more data sources; and

providing the retrieved data for multi-dimensional analysis.

11. A memory storing a program of machine-readable instructions executable by a digital processing apparatus to perform operations for planning and generating database queries for multi-dimensional analysis using domain models and data federation as set forth in claim 10, further comprising the operation of optimizing the query plans for improved performance.

12. A memory storing a program of machine-readable instructions executable by a digital processing apparatus to perform operations for planning and generating database queries for multi-dimensional analysis using domain models and data federation as set forth in claim 10, where the operation of providing the retrieved data for multi-dimensional analysis comprises the operation of displaying the retrieved data for a user on a graphical user interface.