Query engine and method for querying data using metadata model

• US 6,609,123 B1
• Filed: 09/01/2000
• Issued: 08/19/2003
• Est. Priority Date: 09/03/1999
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: Cognos Incorporated (International Business Machines Corporation)
• Inventors: Cazemier, Henk, Rasmussen, Glenn D.
• Primary Examiner(s): Mizrahi, Diane D.
• Assistant Examiner(s): Mofiz, Apu M
• Application Number:
  US09/654,744
• Time in Patent Office: 1,082 Days
• Field of Search: 707/2, 707/3, 707/4, 707/10, 707/100, 707/102, 707/103.R, 707/104.1, 705/1, 709/201, 709/203, 709/217, 709/316, 717/104, 717/108
• US Class Current: 1/1
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

A query engine formulates a data source query to obtain data from one or more data sources. The query engine uses a metadata model containing model objects that represent the data sources. The metadata model has a data access layer, business layer and package layer. The model objects of the business layer are constructed based on the model objects contained in the data access layer. The query engine interacts to the metadata model at the business layer, and formulates a data source query based on a query specification provided by a client application. Thus, the query engine allows use of different type of client applications to obtain reports from one or more data sources.

Claims

1. A query engine for formulating a query to obtain data from one or more data sources using a client application receiving user inputs and a metadata model containing model objects that represent the data sources, the query engine comprising:

a query specification interface for allowing the client application to generate a query specification based on a user input, and receiving the generated query specification; and

a query engine component for translating the query specification into a data source query which is applicable to the data sources, based on the metadata model which has a data access layer for containing data access layer model objects, and a business layer for containing business layer model objects; and

wherein the query specification contains reference to the business layer model objects contained in the business layer in the metadata model, and the query engine component uses the business layer model objects based on the reference contained in the query specification to translate the query specification.

2. The query engine as claimed in claim 1, wherein the query engine component returns the requested data in a multi-dimensional manifold.

3. The query engine as claimed in claim 1, wherein

the metadata model further has a package layer for containing package layer model objects.

4. The query engine as claimed in claim 3, wherein the query specification contains data access information and data layout information.

5. The query engine as claimed in claim 3, wherein the query engine component comprises a refiner for refining the query specification to formulate the data source query.

6. The query engine as claimed in claim 5, wherein the query engine component comprises a planner for generating the data source query based on the results of the refiner.

7. The query engine as claimed in claim 6, wherein the query engine component comprises an execution for executing the data source query generated by the planner on the data sources.

8. The query engine as claimed in claim 3, wherein

the query specification contains a reference to the model objects in the business layer;

and

the query engine component uses the model objects in the business layer based on the reference contained in the query specification.

9. The query engine as claimed in claim 1, wherein the data source query is in data source supporting language that are accessible through Multi Dimensional expression (MDX).

10. The query engine as claimed in claim 9, wherein the data source query is in structured query language (SQL).

11. The query engine as claimed in claim 1 further comprising a data matrix interface for receiving the data retrieved from the data sources by applying the data source query to the data sources, and providing the retrieved data in a data matrix to the client application for presenting the data to the user.

12. The query engine as claimed in claim 11, wherein the data matrix has an iterator to access an individual component in the data matrix.

13. A query engine for formulating a query to obtain data from one or more data sources using a client application receiving user inputs and a metadata model containing model objects that represent the data sources, the query engine comprising:

a query specification interface for allowing the client application to generate a query specification based on a user input, and receiving the generated query specification; and

a query engine component for translating the query specification into a data source query which is applicable to the data sources, based on the metadata model which has a data access layer for containing data access model objects, the data access model objects including table objects that describe definitions of the tables contained in the data sources, column objects that describe definitions of the columns of the tables contained in the data sources, and data access layer joins that describe relationships between the table objects;

a business layer for containing business model objects, the business model objects including entities that are constructed based on the table objects in the data access layer, attributes that are constructed based on the column objects in the data access layer, and business layer joins that are constructed based on the data access layer joins in the data access layer and relationships between the entities; and

a package layer for containing package model objects, the package model objects including a package model object that defines a subset of the business model objects.

14. The query engine as claimed in claim 13, wherein

the query specification contains a reference to the business model objects in the business layer; and

the query engine component uses the business model objects based on the reference contained in the query specification.

15. The query engine as claimed in claim 13, wherein the query specification contains data access information and data layout information.

16. The query engine as claimed in claim 13, wherein the query engine component comprises a refiner for refining the query specification to formulate the data source query.

17. The query engine as claimed in claim 16, wherein the refiner uses the business joins defined in the business layer.

18. The query engine as claimed in claim 17, wherein the refiner uses an entity of subtype contained in the business layer to provide a business view of relationship having an optional cardinality of zero-to-many.

19. The query engine as claimed in claim 17, wherein the refiner simplifies expressions of the query specification by using a one-to-one join relationship.

20. The query engine as claimed in claim 17, wherein, when the refiner locates multiple joins between two entities, the refiner assigns a weight to each join between the two entities, and calculates a join with a lightest weight to include it in the data source query.

21. The query engine as claimed in claim 20, wherein the refiner assigns a light weight to a join of containment type.

22. The query engine as claimed in claim 20, wherein the refiner assigns a heavy weight to a join of reference type.

23. The query engine as claimed in claim 20, wherein the refiner assigns a medium weight to a join of association type.

24. The query engine as claimed in claim 17, wherein the refiner locates a redundant join using cardinalities of the joins defined in the business layer, and removes from the query specification a redundant break clause referring to the redundant join.

25. The query engine as claimed in claim 17, wherein the refiner locates a join which can be breaked using cardinalities of the joins defined in the business layer, and expands the join into a number of break levels.

26. The query engine as claimed in claim 17, wherein an entity in the business layer has a filter, and the refiner adds the property of the filter to the data source query.

27. The query engine as claimed in claim 16, wherein the query engine component comprises a planner for generating the data source query based on the results of the refiner.

28. The query engine as claimed in claim 27, wherein the query engine component comprises an execution for executing the data source query generated by the planner on the data sources.

29. The query engine as claimed in claim 13, wherein the refiner identifies a collapsed entity in the business layer, and rejects any query specification using an invalid collapsed entity.

30. The query engine as claimed in claim 29, wherein the refiner identifies an invalid collapsed entity when the entity lacks appropriate join information referring to a join in the data access layer that allows the refiner to specify a driver data source.

31. The query engine as claimed in claim 13, wherein the refiner locates a driver data source by examining join information in the data access layer, the refiner includes in the data source query the driver data source and other data sources referenced in the query specification.

32. The query engine as claimed in claim 13, wherein the refiner identifies in the business layer a foreign attribute that refers to an attribute of another entity, and prevents the non-foreign attribute, which contains little or no user understandable value from being viewed by the user in the package layer.

33. The query engine as claimed in claim 13, wherein the refiner identifies a reusable calculation defined in the business layer to obtain aggregate information, and reuses the reusable calculation in the data source query.

34. The query engine as claimed in claim 13, wherein

the query specification contains a reference to the package model objects in the package layer;

and

the query engine component uses the package model objects based on the reference contained in the query specification.

35. The query engine as claimed in claim 34, wherein the application uses a subject item defined in the package layer to reference objects defined in the business layer as a basis to formulate multi-dimensional queries that are translatable to data source query.

36. The query engine as claimed in claim 34, wherein the application uses a query path defined in the package layer to select a join path in the business layer.

37. The query engine as claimed in claim 13, wherein the data source query is in data source supporting language that are accessible through Multi Dimensional expression (MDX).

38. The query engine as claimed in claim 37, wherein the data source query is in structured query language (SQL).

39. The query engine as claimed in claim 13 further comprising a data matrix interface for receiving the data retrieved from the data sources by applying the data source query to the data sources, and providing the retrieved data in a data matrix to the client application for presenting the data to the user.

40. The query engine as claimed in claim 39, wherein the data matrix defines an iterator to access an individual component in the data matrix.

41. A method for formulating a query to obtain data from one or more data sources using a client application receiving user inputs and a metadata model containing model objects that represent the data sources, the method comprising:

generating a query specification based on a user input using the client application;

receiving the generated query specification; and

translating the query specification into a data source query which is applicable to the data sources, based on the metadata model that has a data access layer for containing data access layer model objects and a business layer for containing business layer model objects; and

wherein the generating step comprises providing in the query specification a reference to the business layer model objects contained in the business layer; and

the translating step comprises using the business layer model objects based on the reference contained in the query specification to translate the query specification.

42. The method as claimed in claim 41, wherein

the metadata model has a data access layer for containing data access layer model objects, a business layer for containing business layer model objects and a package layer for containing package layer model objects.

43. The method as claimed in claim 42, wherein the translating step comprises refining the query specification to formulate the data source query.

44. The method as claimed in claim 43, wherein the translating step further comprises planning generation of the data source query based on the results of the refining step.

45. The method as claimed in claim 44, wherein the translating step further comprising executing the data source query generated by the planning step on the data sources.

46. The method as claimed in claim 42, wherein

the generating step comprises containing in the query specification a reference to the model objects in the package layer;

and

the translating step comprises using the model objects in the package layer based on the reference contained in the query specification.

47. The method as claimed in claim 41, wherein the generating step comprises including in the query specification data access information and data layout information.

48. The method as claimed in claim 41, wherein the translating step translates the query specification into the data source query in data source supporting language that are accessible through Multi Dimensional expression (MDX).

49. The method as claimed in claim 48, wherein the translating step translates the query specification into the data source query in structured query language (SQL).

50. The method as claimed in claim 41 further comprising:

applying the data source query to the data sources;

receiving the data retrieved from the data sources; and

providing the retrieved data in a data matrix to the client application for presenting the data to the user.

51. The method as claimed in claim 50, wherein the step for providing the retrieved data comprising iterating access to an individual component in the data matrix.

52. A method for formulating a query to obtain data from one or more data sources using a client application receiving user inputs and a metadata model containing model objects that represent the data sources, the method comprising:

generating a query specification based on a user input using the client application;

receiving the generated query specification; and

translating the query specification into a data source query which is applicable to the data sources, based on the metadata model which has

a data access layer for containing data access model objects, the data access model objects including table objects that describe definitions of the tables contained in the data sources, column objects that describe definitions of the columns of the tables contained in the data sources, and data access layer joins that describe relationships between the table objects;

a business layer for containing business model objects, the business model objects including entities that are constructed based on the table objects in the data access layer, attributes that are constructed based on the column objects in the data access layer, and business layer joins that are constructed based on the data access layer joins in the data access layer and relationships between the entities; and

a package layer for containing package model objects, the package model objects including a package model object that defines a subset of the business model objects defining subject items and query paths.

53. A computer readable medium containing code representing instructions for formulating a query to obtain data from one or more data sources using a client application receiving user inputs and a metadata model containing model objects that represent the data sources, the code comprising:

generating a query specification based on a user input using the client application;

receiving the generated query specification; and

translating the query specification into a data source query which is applicable to the data sources, based on the metadata model that has a data access layer for containing data access layer model objects and a business layer for containing business layer model objects; and

wherein the generating step comprises providing in the query specification a reference to the business layer model objects contained in the business layer; and

the translating step comprises using the business layer model objects based on the reference contained in the query specification to translate the query specification.

54. A transmittable signal for transmitting code representing instructions for formulating a query to obtain data from one or more data sources using a client application receiving user inputs and a metadata model containing model objects that represent the data sources, the code comprising:

generating a query specification based on a user input using the client application;

receiving the generated query specification; and

translating the query specification into a data source query which is applicable to the data sources, based on the metadata model that has a data access layer for containing data access layer model objects and a business layer for containing business layer model objects; and

wherein the generating step comprises providing in the query specification a reference to the business layer model objects contained in the business layer; and

the translating step comprises using the business layer model objects based on the reference contained in the query specification to translate the query specification.