Query engine and method for querying data using metadata model

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

What is claimed is:

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.

Description

FIELD OF THE INVENTION

The present invention relates generally to a query engine and method for querying data, and more particularly, to a query engine and method for querying data using a metadata model which models underlying data sources.

BACKGROUND OF THE INVENTION

It is known to use data processing techniques to design information systems for storing and retrieving data. Data is any information, generally represented in binary, that a computer receives, processes, or outputs. A database or data warehouse is a shared pool of interrelated data. Information systems are used to store, manipulate and retrieve data from databases.

Traditionally, file processing systems were often used as information systems. File processing systems usually consist of a set of files and a collection of application programs. Permanent records are stored in the files, and application programs are used to update and query the files. Such application programs are generally developed individually to meet the needs of different groups of users. Information systems using file processing techniques have a number of disadvantages. Data is often duplicated among the files of different users. The lack of coordination between files belonging to different users often leads to a lack of data consistency. Changes to the underlying data requirements usually necessitate major changes to existing application programs. There is a lack of data sharing, reduced programming productivity, and increased program maintenance. File processing techniques, due to their inherent difficulties and lack of flexibility, have lost a great deal of their popularity and are being replaced by database management systems (DBMSs).

A DBMS is a software system for assisting users to create reports from data stores by allowing for the definition, construction, and manipulation of a database. The main purpose of a DBMS system is to provide data independence, i.e., user requests are made at a logical level without any need for knowledge as to how the data is stored in actual files in the database. Data independence implies that the internal file structure could be modified without any change to the users' perception of the database. However, existing DBMSs are not successful in providing data independence, and requires users to have knowledge of physical data structures, such as tables, in the database.

To achieve better data independence, it is proposed to use three levels of database abstraction. With respect to the three levels of database abstraction, reference is made to FIG. 1.

The lowest level in the database abstraction is the internal level 1. In the internal level 1, the database is viewed as a collection of files organized according to an internal data organization. The internal data organization may be any one of several possible internal data organizations, such as B.sup.+ -tree data organization and relational data organization.

The middle level in the database abstraction is the conceptual level 2. In the conceptual level 2, the database is viewed at an abstract level. The user of the conceptual level 2 is thus shielded from the internal storage details of the database viewed at the internal level 1.

The highest level in the database abstraction is the external level 3. In the external level 3, each group of users has their own perception or view of the database. Each view is derived from the conceptual level 2 and is designed to meet the needs of a particular group of users. To ensure privacy and security of data, each group of users only has access to the data specified by its particular view for the group.

The mapping between the three levels of database abstraction is the task of the DBMS. When the data structure or file organization of the database is changed, the internal level 1 is also changed. When changes to the internal level 1 do not affect the conceptual level 2 and external level 3, the DBMS is said to provide for physical data independence. When changes to the conceptual level 2 do not affect the external level 3, the DBMS is said to provide for logical data independence.

Typical DBMSs use a data model to describe the data and its structure, data relationships, and data constraints in the database. Some data models provide a set of operators that are used to update and query the database. DBMSs may be classified as either record based systems or object based systems. Both types of DBMSs use a data model to describe databases at the conceptual level 2 and external level 3.

Data models may also be called metadata models as they store metadata, i.e., data about data in databases.

Three main existing data models used in record based systems are the relational model, the network model and the hierarchical model.

In the relational model, data is represented as a collection of relations. To a large extent, each relation can be thought of as a table. A typical relational database contains catalogues, each catalogue contains schemas, and each schema contain tables, views, stored procedures and synonyms. Each table has columns, keys and indexes. A key is a set of columns whose composite value is distinct for all rows. No proper subset of the key is allowed to have this property. A table may have several possible keys. Data at the conceptual level 2 is represented as a collection of interrelated tables. The tables are normalized so as to minimize data redundancy and update anomalies. The relational model is a logical data structure based on a set of tables having common keys that allow the relationships between data items to be defined without considering the physical database organization.

A known high level conceptual data model is the Entity-Relationship (ER) model. In an ER model, data is described as entities, attributes and relationships. An entity is anything about which data can be stored. Each entity has a set of properties, called attributes, that describe the entity. A relationship is an association between entities. For example, a professor entity may be described by its name, age, and salary and can be associated with a department entity by the relationship "works for".

Existing information systems use business intelligence tools or client applications that provide data warehousing and business decision making and data analysis support services using a data model. In a typical information system, a business intelligence tool is conceptually provided on the top of a data model, and underneath of the data model is a database. The data model of existing information systems typically has layers corresponding to the external level 3 and the internal level 1. Some data models may use a layer corresponding to both the external level 3 and the conceptual level 2.

Existing data models are used for the conceptual design of databases. When a system designer constructs an information system, the designer starts from a higher abstraction level 3 and moves down to a lower abstraction level 1, as symbolised in FIG. 1 by arrows.

That is, the system designer first performs logical design. At the logical design stage, the designer considers entities of interest to the system users and identifies at an abstract level information to be recorded about entities. The designer then determines conceptual scheme, i.e., the external level 3 and/or conceptual level 2 of a data model. After the logical design is completed, the designer next performs physical design. At the physical design stage, the designer decides how the data is to be represented in a database. The designer then creates the corresponding storage scheme, i.e., the structure of a database, and provides mapping between the internal level 1 of the data model and the database.

Existing business intelligence tools thus each provides a different paradigm for retrieving and delivering information from a database. Accordingly, it is difficult to share information in the database among different business intelligence tools.

It is common that in a single organization, each group of users has its own established information system that uses its corresponding database. Thus, the single organization often has multiple databases. Those databases often contain certain types of information which are useful for multiple groups of users. Such types of information may include information about business concepts, data retrieval, and user limits and privileges. However, each information system was designed and constructed in accordance with specific needs of the group, and may use a different business intelligence tool from others. These differences in the information systems and business intelligence tools used do not allow sharing the information already existing in the databases among multiple groups of users.

In addition, these existing business intelligence tools use different ways of retrieving data from the underlying database. Thus, it is not possible, without major modifications, to use an existing business intelligence tool to retrieve data from a database which is built for a different business intelligence tool.

Accordingly, it is desirable to provide a query engine which allows use of different business intelligence tools or client applications to retrieve data from shared data sources.

SUMMARY OF THE INVENTION

The present invention is directed to a query engine which formulates a data source query by interacting to model objects having business intelligence contained in a metadata model representing underlying one or more data sources.

According to one aspect of the present invention, there is provided 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 comprises 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. The query engine also comprises a query engine component for translating the query specification into a data source query which is applicable to the data sources, based on model objects in the metadata model having business intelligence.

According to another aspect of the present invention, there is provided 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 comprises 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 model objects in the metadata model having business intelligence.

Other aspects and features of the present invention will become apparent to those ordinarily skilled in the art upon review of the following description of specific embodiments of the invention in conjunction with the accompanying figures.

BRIEF DESCRIPTION OF THE DRAWINGS

Embodiments of the invention will now be described with reference to the accompanying drawings, in which:

FIG. 1 is a diagram showing a structure of metadata model;

FIG. 2 is a diagram showing a reporting system in accordance with an embodiment of the present invention;

FIG. 2A is a diagram showing functions of the metadata exchange and transformations shown in FIG. 2;

FIG. 2B is a diagram showing examples of objects contained in the metadata model shown in FIG. 2;

FIG. 3 is a diagram showing an example of a query engine shown in FIG. 2;

FIG. 4 is a diagram showing an example of functions of the query engine;

FIG. 4A is a diagram showing an example of functions of the transformations shown in FIG. 2;

FIG. 4B is tables showing an example of a set of transformations;

FIG. 5 is a diagram showing concept of the transformations;

FIG. 6 is a diagram showing an implementation structure;

FIG. 7 is a chart showing flags used in the metadata model;

FIG. 8 is a diagram showing examples of source and target of a transformation;

FIG. 9 is a diagram showing an example of a data access layer;

FIG. 10 is a table representing the process state

FIG. 11 is a table showing an example of results of a step of a transformation;

FIG. 12 is a table showing an example of results of a step of the transformation;

FIG. 13 is a part of the table of FIG. 12;

FIG. 14 is a part of the table of FIG. 12;

FIG. 15 is a table showing an example of results of a step of the transformation;

FIG. 16 is an example of tables;

FIG. 17 is a diagram showing examples of source and target of a transformation;

FIG. 18 is a diagram showing examples of source and target of a transformation;.

FIG. 19 is a diagram showing examples of source and target of a transformation;

FIG. 20 is a diagram showing examples of source and target of a transformation;

FIG. 21 is a diagram showing examples of source and target of a transformation;

FIG. 22 is a diagram showing an example of a model to which a transformation is applied;

FIG. 23 is a diagram showing an example of a process state;

FIG. 24 is a diagram showing an example of a process state;

FIG. 25 is a diagram showing relations of objects;

FIG. 26 is a diagram showing examples of source and target of a transformation;

FIG. 27 is a diagram showing an example of relations of objects;

FIG. 28 is a diagram showing an example of source of a transformation;

FIG. 29 is a diagram showing an example of target of the transformation;

FIG. 30 is a diagram showing examples of source and target of a transformation;

FIG. 31 is a diagram showing an example of source of a transformation;

FIG. 32 is a diagram showing an example of target of the transformation;

FIG. 33 is a diagram showing an example of a step of a transformation;

FIG. 34 is a diagram showing an example of a step of the transformation;

FIG. 35 is a diagram showing an example of a step of the transformation;

FIG. 36 is a diagram showing an example of a step of the transformation;

FIG. 37 is a diagram showing an example of a step of the transformation;

FIG. 38 is a diagram showing the dimensions constructed as the output of the transformation;

FIG. 39 is a diagram showing the dimensions constructed as the output of the transformation;

FIG. 40 is a diagram showing examples of source and target of a transformation;

FIG. 41 is a diagram showing an example of a source model and a target model of a transformation;

FIG. 42 is a diagram showing an example of the data access layer;

FIG. 43 is a diagram showing an example of the business layer;

FIG. 44 is a diagram showing an example of functions of a query engine;

FIG. 45 is a diagram showing an example of components of the query engine;

FIG. 46 is a diagram showing an example of a query specification structure;

FIG. 47 is a diagram showing an example of a relationship between entities; and

FIG. 48 is a diagram showing the model definitions to illustrate the removal of redundant break clauses.

DETAILED DESCRIPTION OF EMBODIMENTS OF THE INVENTION

FIG. 2 illustrates a reporting system 4 to which an embodiment of the present invention is suitably applied. The reporting system 4 provides a single administration point for metadata that supports different business intelligence tools or client applications. Thus, it enables different business intelligence tools to extract and interpret data from various data sources in the same way.

The reporting system 4 includes common object services (COS) 5, a metadata exchange 10, a metadata model 15, a metadata model transformer or transformations 20, a user interface 25 and a query engine 30. The fundamental objective of the reporting system 4 is to provide a rich business-oriented metadata model 15 that allows the query engine 30 to generate the best queries of which it is capable, and allows users to build queries, reports and cubes with the aid of the query engine 30 to obtain desired reports from underlying data sources. To this end, COS 5, metadata exchange 10 and transformations 20 are provided.

Prior to describing the metadata model 15 and the transformations 20 in detail, each element of the reporting system 4 is briefly described.

COS 5 defines the framework for object persistence. Object persistence is the storage, administration and management of objects on a physical device and transfer of those objects to and from memory as well as the management of those objects on the physical device. The double head arrow from COS 5 in FIG. 2 represents that COS 5 communicates with all other elements shown in FIG. 2. COS 5 performs functions such as creating new objects, storing them on disk, deleting them, copying them, moving them, handling change isolation (check-in, check-out) and object modelling. COS 5 uses a modelling language, such as Comet Modelling Language (CML) that generates C++ code.

The metadata exchange 10 is used to obtain metadata from external physical sources. Metadata is obtained from one or more external sources of metadata. As shown in FIG. 2A, external sources of metadata may be one or more data sources 100 and/or one or more metadata sources 101. Data sources 100 contain physical data. Examples of data sources 100 include databases and files. Metadata sources 101 contain descriptive information about data sources. Metadata sources 101 are also known as metadata repositories. Metadata repositories may be third party repositories. Metadata sources 101 generally have underlying data sources 100 containing physical data. The metadata exchange 10 facilitates importation of metadata from external sources 100 and 101 into the metadata model 15. Also, the metadata exchange 10 may facilitates exportation of metadata from the metadata model 15 to external metadata repositories.

The metadata model 15 stores metadata about its underlying one or more data sources 100. It is used to provide a common set of business-oriented abstractions of the underlying data sources 100. The metadata model 15 defines the objects that are needed to define client applications that users build. The metadata model 15 provides three layers to realize three levels of abstractions of data sources 100 as described above referring to FIG. 1. The three layers are a physical layer or data access layer 102, a business layer 104 and a presentation layer or package layer 106.

Transformations 20 are used to complete the metadata model 15. For example, when a database is introduced to the reporting system 4, metadata is imported from the database into the metadata model 15. Metadata may also be imported from one or more metadata repositories or other data sources. Sufficient metadata may be imported from a database that would build only a small number of the objects that would actually be needed to execute queries. However, if such metadata does not have good mapping to the metadata model 15, then the transformations 20 can be used to provide the missing pieces to complete the metadata model 15.

The user interface 25 is layered on top of the metadata model 15 as a basic maintenance facility. The user interface 25 provides users with the ability to browse through the metadata model 15 and manipulate the objects defined thereby. The user interface 25 is also a point of control for the metadata exchange 10, for executing transformations 20, and for handling check-in, check-out of model objects, i.e., changed information, as well as a variety of other administrative operation. The user interface 25 allows users for the performance of basic maintenance tasks on the objects in the metadata model 15, e.g., changing a name, descriptive text, or data type. The user interface 25 is a mechanism that involves the capabilities of the metadata exchange 10 and the transformations 20. The user interface 25 has the ability to diagram the metadata model 15, so that the user can see how objects are related.

The query engine 30 is responsible for taking the metadata model 15 and a user's request for information, and generating a query that can be executed against the underlining data sources, e.g., a relational database. The query engine 30 is basically the reason for the existence of the rest of the blocks. The objective of the query engine is to function as efficiently as possible and to preserve the semantics of the original question. A user may ask a question that is not precise. The request may be for something from "customers" and something from "products". But these may be related in multiple ways. The query engine 30 needs to figure out which relationship is used to relate "customers" and "products" to provide the user with information requested.

The use of the metadata model 15 by the query engine 30 is briefly described with reference to FIG. 3. A user uses a business intelligent tool or client application (not shown) to generate a user's request for information. Upon the receipt of the user's request, the client application generates an initial specification 35 based on the request. The specification 35 may be ambiguous. Also, it may not be in a form that can be applied to the data sources directly. Using the information that is built in the metadata model 15, the query engine 30 makes the specification 35 unambiguous and builds a query in terms of the data access layer 102 for the specification 35. This intermediate formulation of the query is also called a physical query and is subsequently translated into a data source specification language. The data source specification language may be Structured Query Language (SQL). A query in a data source specification language can be executed on the data sources. Thus, the correct data 40 may be obtained.

Metadata Model 15

The metadata model 15 is a tool to supply the common metadata administration tool, unified and centralized modelling environment, and application program interfaces for business intelligence tools. The architecture of the metadata model 15 will now be described in further detail.

Metadata contained in the metadata model 15 is also called model objects. The metadata model 15 is organized as a single containment tree or a series of containment trees. A containment tree starts at the highest level with a model object. The model object itself is at the root of the tool, and all other objects, except the relationship objects, are contained within this root objects

FIG. 2B shows the architecture of the metadata model 15. The metadata model is composed of several layers, namely, a physical layer or data access layer 102, a business layer 104 and a presentation layer or package layer 106. These layers correspond to those abstraction levels shown in FIG. 1.

The model objects contained in a higher abstraction layer may include objects which are constructed from a lower abstraction layer to the higher abstraction layer.

The model objects contained in a higher abstraction layer include objects which are constructed from a lower abstraction layer to the higher abstraction layer.

The data access layer 102 contains metadata that describes how to retrieve physical data from data sources 100. It is used to formulate and refine queries against the underlying data sources 100. The underlying data sources 100 may be a single or multiple data sources, as described above. Examples of data sources 100 include relational databases, such as Oracle, Sybase, DB2, SQL Server and Informix.

The data access layer 102 contains a part of the model objects that directly describe actual physical data in the data sources 100 and their relationships. These model objects may be called data access model objects. The data access model objects may include, among other things, databases, catalogues, schemas, tables, files, columns, data access keys, indexes and data access joins. Each table has one or more columns. Data access joins exist between tables. A data access key corresponds to a key in the data sources 100 that references one or more column names whose composite value is distinct for all rows in a table. A data access join is a relationship between two or more tables or files. Also, the data access model objects may include views, function stored procedures and synonyms, if applicable.

The data access model objects in the data access layer 102 are metadata, which are created as a result of importing metadata from data sources 100 and metadata sources 101 provided by users. Examples of metadata sources 101 include Impromptu Catalogue and Impromptu Web Query 2.12. The information of some data access objects may be available from the underlying data sources 100. Information for join relationships are not available from the underlying data sources 100. The user can customize some objects in the data access layer 102 in order to create data access joins, i.e., relationships between objects that were imported from various data sources. Also, the transformations 20 may transform the data access layer 102 to complete it.

Also, the data access layer 102 may allow users to define therein data source queries, such as SQL queries. Data source queries return a result set of physical data from underlying data sources 100. Those created data source queries are treated as objects in the data access layer 102 like tables. After data source queries are defined, a set of columns objects is generated for each data source query by the query engine 30 based on the SQL statement. Users may also define stored procedures and/or overloaded stored procedures, rather than importing them from metadata sources 101.

The business layer 104 describes the business view of the physical data in the underlying data sources 100. It is used to provide business abstractions of the physical data with which the query engine 30 can formulate queries against the underlying data sources 100.

The business layer 104 contains a part of the model objects that can be used to define in abstract terms the user's business entities and their inter relationships. These model objects may be called business model objects. The business model objects are reusable objects that represent the concepts and structure of the business to be used in business intelligence environments. They represent a single business model, although they can be related to physical data in a number of different data sources 100.

The business model objects consist of a business model, business rules and display rules. The business model may include entities, attributes, keys and joins. Joins may be also called join relationships. The user interface 25 can provide a view of the business model as an entity-relationship diagram. The business rules may include calculations, filters and prompts. The display rules may include elements, styles and enumeration values.

The business model objects are closely related to the data access model objects in the data access layer 102. For example, entities in the business layer 104 are related to tables in the data access layer 102 indirectly; and attributes in the business layer 104 correspond to columns in the data access layer 102. Business joins exist between entities. Each business model object has a partner in the data access layer 102, i.e., a relationship exists between a table and an entity. While the tables in the data sources 100 store data access layer objects in accordance with the design of its underlying data sources 100, the entities in the business layer 104 hold the metadata representing the business concept. Entities are collections of attributes.

Attributes of entities in the business layer 104 contain expressions related to columns of tables in the data access layer 102. An attribute is usually directly related to a single column of the data access layer 102. For example, the entity "customer" could have attributes "customer name", "customer address", and the like. In the simplest case, all the attributes of an entity in the business layer 104 are related one-to-one to the columns of a single table in the data access layer 102. However, the relationship is not always a one-to-one relationship. Also, an attribute may be expressed as a calculation based on other attributes, constants and columns. For example, an attribute may be a summary of data in other attributes, e.g., a total amount of all the orders placed by customer.

In the business layer 104, entities are related to other entities by joins. Joins are classified as one of containment, reference or association. A containment join represents a strong relationship between entities. For example, an entity OrderDetail would have no meaning without an entity OrderHeader. Thus, the entity OrderDetail is containment of the entity OrderHeader.

A reference join indicates that one entity acts as a lookup table with respect to the other. For example, OrderDetail and Products are related via a relationship. In this case, Products acts as a lookup table so the relationship is marked as a reference relationship.

An association join represents relationships between entities which are not categorised as containment or reference joins.

It is advantageous to categorize the joins into these three types because they should be treated differently when query paths are considered. For example, a reference join should not be taken as a query path because if multiple entities reference to an entity, the referenced entity could incorrectly relate the unrelated multiple entities to each other by a query path through the referenced entity. By identifying reference joins as such, query paths can easily avoid these joins.

In addition, an entity may inherit information from another entity using a technique called subtyping. A subtype entity may be specialization of its supertype entity. For example, an entity Employee is a supertype entity for a subtype entity Salesman. Generally, a subtype entity has more attributes than its supertype. In the above example, the entity Employee may have attributes EmployeeNumber, Name, and Salary; and the entity Salesman may have attributes Quota, Sales and Commission in addition to EmployeeNumber, Name, and Salary.

Entities and attributes in the business layer 104 are given user friendly meaningful names. For example, the column named CUSTNAM from the CUST table in the data access layer 102 could be mapped to Customer Name attribute contained in the Customer Entity in the business layer 104.

The ways of use of entity relationships in the metadata model 15 are different from those in conventional modelling tools. For example, in most Entity-Relationship (ER) modelling tools, the ER concept is used to provide an abstraction for defining a physical database, i.e., it is a different "view" of the physical database. Within the metadata model 15, the business layer 104 is used to provide an abstraction for reporting data from physical data sources 100.

The information of the objects of the business model in the business layer 104 is not generally available in underlying data sources 100. Usually available information in metadata sources 101 is associated with the data access layer 102, rather than the business layer 104. One thing that may be available in external metadata repositories 101 is the business names for objects in the metadata model 15. However, again these business names tend to be provided for the physical tables and columns. If they can be mapped to the appropriate business entity or attribute, they may be used.

The business rules are used to develop business intelligence applications. Calculations use a combination of attributes and expression components, and make them available to report so that the up-to-date and consistent definitions are used to execute reports.

Filters and prompts are used to restrict queries. Applying a filter to an entity or attribute limits the scope of data retrieval for all users who work with this entity or attribute. Applying a filter to an entity or attribute in conjunction with a user class limits the scope of data retrieval for the user class. Elements and styles are used to associate presentation information with an attribute.

The package layer 106 contains a part of the model objects that describe subsets of the business layer 104. These model objects may be called package model objects. These are used to provide an organized view of the information in the business layer 104. The information is organized in terms of business subject areas or by way in which it is used.

The package model objects in the package layer 106 include presentation folders and/or subjects. Each subject in the package layer 106 contains references to a subset of the business model objects that are interested in a particular group or class of users. The subset of the business model objects are reorganized so that they can be presented to the group of users in a way suitable to the group of users. Also, a user can combine references to the business model objects available from the business layer 104 into combinations that are frequently used in the user's business. User defined folders that contain these combinations of references are called user folders or presentation folders.

Presentation folders and subjects contain references to objects in the business layer 104, including entities, attributes, filters and prompts. Presentation folders create packages of information for the end user. Each package is defined for a specific purpose, e.g., one or more business intelligence applications. Designers can combine them, by functions of subjects or by group of users, in order to organize business model objects into collections of most frequently used objects, or in order to support various business intelligent tools or client applications using the reporting system 4 of the present invention as a metadata provider.

The information of the objects in the package layer 106 is not generally available in external data sources 100. The concept of organized business subject areas may exist in external metadata repositories 101. The metadata model 15 may use such a concept in the business layer or data access layer.

For all objects in the data access layer 102 and the business layer 104, business descriptive metadata may also be included. Business descriptive metadata is used to help understand the source and the meaning of the data which is being manipulated. Business descriptive metadata may include lineage, accuracy, description and refresh rules. Lineage is a history of source and processing steps used to produce data set. Refresh is update rules for refreshing aggregated or submitted data for reporting. Business descriptive metadata is used by an end user and an application designer to understand the source of the information. Business descriptive metadata includes such things as descriptions and stewards. A steward is a person or group that manages the development, approval, creation, and use of data within a specified functional area. Business descriptive metadata may also include information that can be used to relate the objects to information in external repositories 101.

Business descriptive metadata may exist in many forms in external repositories 101. General purpose repositories and business information directories collect this information as that is their raison d'etre. Warehouse Extract-Transform-Load (ETL) tools collect this information as a result of collecting the ETL specifications. The information may be duplicated or collected from a variety of sources in the metadata model 15 so that it is available directly to the user as metadata. The metadata model 15 may also include context information which can be used to retrieve information from external repositories 101.

Most objects in the metadata model 15 may be organized in a tree. Some objects model relationships between other objects. As described above, each business model object in the business layer 104 has a partner in the data access layer 102. This relationship provides the context for processing all the related information of the tables in the data access layer 102. For example, if a particular column has not been processed, transformations 20 process the column in the context of a parent relationship, i.e., build an attribute and put under the entity.

The metadata model 15 may be built using CML files. CML files are compiled into C++ code which is then compiled in the reporting system 4 to build the metadata model 15.

Transformations 20

The transformations 20 are performed to automatically construct portions of the common metadata model 15 based on the objects contained in another portion of the metadata model 15.

The transformations 20 contain a plurality of different transformations, as described below. Early in the lifecycle of a metadata model 15, the model designer will likely choose to use all or most of the transformations 20 to develop a standard model 15. As the model 15 progresses through the lifecycle, however, the number of transformations used by the designer is likely to decrease as the model 15 is customized to suit the particular needs of the application of the users.

The model designer may also determine that a transformation is not applicable to a particular metadata model. Applying this knowledge to selecting a subset of transformations 20 to execute can considerably reduce the amount of processing.

In order to facilitate these demands, it is desirable that each transformation 20 is coded as independently as possible. In the simplest of scenarios, as shown in FIG. 5, the architecture of the transformations 20 could be thought of as a pipeline 21 with a number of pumping stations en route. Instead of transporting oil or natural gas, the metadata model flows through the pipeline 21. A pumping station represents a transformation step 22. Each transformation step 22 is constructed to suit the requirements of the scenario. As new transformations are constructed, they can be added to the pipeline 21 as required. Obsolete transformation steps may be removed from the pipeline 21.

However, as development of the transformations 20 has progressed, a number of relationships have been developed between the transformations 20. Data about the model 15 that is constructed during the processing of some transformations 20 sometimes can be used by later transformations 20. The "Blackboard" pattern 80 shown in FIG. 6 ("Pattern-Oriented Software Architecture A System of Patters" by Buschmann, et. al., published by John Wiley & Sons 1996, pages 71-95) matches the requirements. The pattern 80 uses the term "Knowledge Source" 81 as the actor that manipulates the objects on a blackboard 82. The knowledge source 81 and the blackboard 82 are controlled by a controller 83 which presents results to a client 84. Each transformation 20 would be a Knowledge Source 81. The use of the pattern 80 preserves the independence of the transformations 20 as much as possible, yet recognizes that the transformations 20 are linked together by the data stored on the blackboard 82. The controller 83 is responsible for scheduling the execution of the knowledge sources 81, i.e., transformations 20.

Referring to FIG. 4A, the basic functions of the transformations 20 are described.

The metadata model 15 has the three layers: data access layer 102, business layer 104 and package layer 106, as described above. The transformations 20 also has three kinds: data access (physical) model transformations 112, business model transformations 114, package (presentation) model transformations 116. The transformations 20 transform metadata from the lower abstraction level 102 to the higher abstraction level 106.

A data source 100 is a source of physical definitions of physical data, i.e., a source of metadata. A data source 100 may be one or more database or other data sources. When the data source 100 is introduced into the reporting system 4, the physical definitions of the data source 100 are extracted from the data source 100 into the data access layer 102 in the metadata model 15 by the metadata exchange 10, as described above referring to FIG. 2A. The reporting system 4 may also import metadata from other metadata sources using the metadata exchange 10. Thus, data access layer objects are built in the data access layer 102 in the metadata model 15. These data access layer objects represent a solid picture of what exists in the data source 100.

However, these imported data access layer objects are inadequate to provide reports to users, i.e., the metadata model 15 is incomplete with only those imported data access layer objects and cannot be used to build reports. That is, the imported data access layer objects may not be enough to form a complete business layer 104. In order to improve the data access layer 102, the data access model transformations 112 take the data access layer objects that exist in the data access layer 102, and make changes to them and/or add new objects to complete the data access layer 102.

Then, the business model transformations 114 take the data access layer objects from the data access layer 102 and build their corresponding business layer objects in the business layer 104. However, these business layer objects that are transformed from the data access layer 102 are often inadequate to provide reports to users. In order to improve the business layer 104, the business model transformations 114 take the business layer objects that exist in the business layer 104, and make changes to apply some business intelligence to them.

The package model transformations 116 take the business layer objects from the business layer 104 and build their corresponding package layer objects in the package layer 106. Then, the package model transformations 116 prepare the package layer objects suitable for corresponding client applications. The package model transformations 116 take the package layer objects that exist in the package layer 106, and make changes to them to complete the package layer 106. The package layer objects in the package layer 106 may then be used to build reports to users by the client applications.

Thus, by the transformations 20, a physical database design is converted into a logical database design, i.e., the transformations 20 deduce what the logical intent of the model was.

The transformations 20 may also include multidimensional model transformations and general transformations as described below.

Transformation Data Recorded in the Model

Each transformation 20 records in the model 15 information about changes made during execution of the transformation 20 to avoid repeating the same activity in subsequent executions. Every object class that can be modified by the transformations 20 preferably supports an additional interface to store the transformation information.

When one object leads to the creation of another, a new relationship is created between the two objects. Transformations use the source and target object identifiers to identify the original set of objects and the resulting set of objects. Each transformation also uses two status flags. These flags are used to determine the processing flow for each object and to control the execution of a transformation over the relationship.

The first flag is a prohibit flag. If the prohibit flag is set, the transform will not modify the object during the execution of the transformation. The second flag is a processed flag. This flag records whether the transform has ever processed the object.

Data Access (Physical) Model Transformations 112

Referring to FIG. 4B, the data access model transformations 112 include a data access (physical) join constructing transformation 112a, a data access (physical) key constructing transformation 112b, a table extract constructing transformation 112c and a data access (physical) cube constructing transformation 112d.

Data Access (Physical) Join Constructing Transformation 112a

Referring to FIG. 4B, when the data source 100 contains physical tables having indexes, the metadata exchange 10 imports the physical definitions of the physical tables into the data access layer 102 of the metadata model 15. An index is a database structure used to optimize query performance by organizing the contents of specified columns into structures that facilitate quick searching.

Thus, the data access layer 102 contains the definitions of the physical tables, i.e., data access layer table objects 122 in the data access layer 102. The table objects 122 in the data access layer 102 may be called "data access layer tables". The data access layer tables 122 have indexes imported with the definitions of the physical tables from the data source 100.

The data access join constructing transformation 112a constructs join relationships 123 between the data access layer tables 122 based on the contents of their indexes. That is, the data access join constructing transformation 112a first finds columns used in the indexes in each data access layer table 122, and then for each pair of the data access layer tables 122, searches a best match of columns used in the indexes. The best match is defined primarily as the match with the largest number of matching columns. In case of ties, the match that uses the largest index wins. Columns match if their names are identical. The names are usually compared as being case insensitive. In all cases, one set of columns is a subset of the other column set as defined by the indexes of the tables. That is, one set of column names is wholly contained within the other set.

If a match is found between columns, the data access join transformation 112a joins the tables by constructing a new data access layer join relationship. The join's expression requires that the values of like named columns from the aforementioned column sets are equal.

The following shows an example of the operation of the data access join constructing transformation 112a. Herein, a unique index is an index that contains unique values.

I. For each non-prohibited data access layer table:

A. Construct TableInfo:

1. Get list of columns in table and sort by name.

2. For each index:

a) Construct IndexInfo

(1) Record columns used in index, whether index is unique.

(2) Sort column list based on name.

3. Sort IndexInfo objects based on uniqueness of index, number of columns.

4. For each index:

a) If the columns of the index are not all contained within an IndexInfo object representing a unique index already associated with the TableInfo object:

(1) Add the IndexInfo object to the TableInfo object

(2) Remove columns used in index from TableInfo column list.

II. For each non-prohibited views and files:

A. Construct TableInfo:

1. For each non-prohibited view, file and stored procedure;

a) Get list of columns in table and sort by name.

III. For each TableInfo pair {T1, T2}:

A. If either T1 or T2 has not been processed by this transformation:

1. Compare unique indexes {I1 from T1, I2 from T2} to determine best match.

2. If a match is found:

a) Build a join using the matching columns.

3. Else

a) Compare unique indexes from one table with non-unique indexes from the other table {I1 from T1, I2 from T2} to determine the best match.

b) If a match is found:

(1) Build a join using the matching columns.

c) Else

(1) Compare unique indexes from one table with column list from the other table {I1 from T1, C from T2} to determine the best match.

(2) If a match is found:

(a) Build a join using the matching columns.

IV. Mark each table as transformed.

The following table shows the status flag usage.

    Object Class       Prohibit          Processed
    Table              Do not process the When performing pair-wise
    View               instance.         comparisons, avoid pairs
    Binary File                          with both objects marked as
    Ranged Binary File                   processed.
    Delimited Text File
    Fixed Text File

    Object Class  Prohibit                   Processed
    Table         Do not process this instance.
    Index         Do not process this instance. Update this instance.

    Table                              Relationship
    Order Details                      Base Table
    Fact-Orders                        Aggregate of Order Details
    Inventory
    Fact-Inventory
    Orders by Received Date, Office    Aggregate of Order Details
    Inventory by Date, Item, Region
    Orders by Received Date, Item,     Aggregate of Order Details
    Customer
    Orders by Received Date, Brand, Line Aggregate of Order Details
    and Item
    Orders by Received Date, Sales Region, Aggregate of Order Details
    Customer

    Table                              Relationship
    Order Details                      Base Table
    Fact-Orders                        Aggregate of Order Details
    Inventory                          Base Table
    Fact-Inventory                     Aggregate of Inventory
    Orders by Received Date, Office    Aggregate of Order Details
    Inventory by Date, Item, Region    Aggregate of Inventory
    Orders by Received Date, Item,     Aggregate of Order Details
    Customer
    Orders by Received Date, Brand, Line Aggregate of Order Details
    and Item
    Orders by Received Date, Sales Region, Aggregate of Order Details
    Customer

    Table                              Relationship
    Order Details                      Base Table
    Fact-Orders                        Aggregate of Order Details
    Inventory                          Base Table
    Fact-Inventory                     Aggregate of Inventory
    Orders by Received Date, Office    Aggregate of Order Details
    Inventory by Date, Item, Region    Aggregate of Inventory
    Orders by Received Date, Item,     Aggregate of Order Details
    Customer
    Orders by Received Date, Brand, Line Aggregate of Order Details
    and Item
    Orders by Received Date, Sales Region, Aggregate of Order Details
    Customer
    Orders                             Base
    Dim-Locations
    Offices                            Base
    Warehouses                         Base
    Cities                             Base
    SKU Items                          Base
    Dim-Products
    Items                              Base
    States                             Base
    Customer Sites                     Base
    Regions                            Base
    Dim-Customers
    Brands                             Base
    Countries                          Base
    Customers                          Base
    Lines                              Base
    Sales Regions                      Base
    Sales Rep Pictures                 Base
    Sales Reps                         Base
    Dim-Sales Reps
    Dim-Date

        Object Class    Prohibit                   Processed
        Entity          Do not process the instance.        NA
        Business Join   Do not process the instance.        NA

        Object Class    Prohibit                   Processed
        Business Join   Do not process the instance.        NA

    Object Class    Prohibit                   Processed
    Entity          Do not process this instance. Do not process this
                                               instance.
    Business Join   Do not process this instance. NA

    Object Class  Prohibit               Processed
    Entity        Do not process the instance,
                  or contained attributes.
    Attribute     Do not process the instance. Do not process the instance.