Data warehouse which is accessed by a user using a schema of virtual tables

• US 5,675,785 A
• Filed: 10/04/1994
• Issued: 10/07/1997
• Est. Priority Date: 10/04/1994
• Status: Expired due to Term

• Current Assignee: CA, Inc. (d/b/a CA Technologies) (Broadcom, Inc.)
• Original Assignee: Hewlett-Packard Company (HP Inc.)
• Inventors: Fischer, Brian, Sturdevant, Mark, Meyer, Marc, Yoshida, Neil, Fong, Yukon, Gratiot, Mark, Randazzo, Guy, Hall, Guy Travis, Yee, Suzie Cho
• Primary Examiner(s): Kulik, Paul V.
• Assistant Examiner(s): Alam, Hosain T.
• Application Number:
  US08/317,437
• Time in Patent Office: 1,099 Days
• Field of Search: 395/600, 395/148, 395/155-161, 395/159, 395/160, 395/601, 395/602, 395/604, 395/611, 395/613
• US Class Current: 707/600
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/944 Business related
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• 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

Abstract

A database warehouse includes a database having data arranged in data tables, e.g., fact tables and reference tables. A warehouse database hub interface is connected to the database. The warehouse database hub interface presents to a user a schema of the data in the database warehouse. The schema consists of virtual tables. Arrangement of the data in the virtual tables is different than arrangement of data in the fact tables and the reference tables. A user generates queries based on the schema provided by the warehouse database hub interface. In response to a such a query for particular information stored in the database warehouse, the warehouse database hub interface modifies the query to take into account pre-computed values and the arrangement of the data within the database warehouse. Then the warehouse database hub interface queries the database warehouse using the modified query to obtain the particular information from the database warehouse. Finally, the warehouse database hub interface forwards the particular information obtained from the database warehouse to the user.

Claims

1. A method for providing access to information stored in a database warehouse, the method comprising the steps of:

(a) arranging data in data tables within the database warehouse;

(b) providing to a user, by a warehouse database hub interface, a schema of the data in the database warehouse, the schema consisting of virtual tables, wherein arrangement of the data in the virtual tables is different than arrangement of the data in the data tables, and wherein the user is allowed access to the data in the data tables only through the schema consisting of the virtual tables; and

,

(c) in response to a query from the user for particular information stored in the database warehouse, the query being based on the schema provided by the warehouse database hub interface, performing the following substeps by the warehouse database hub interface;

(c.1) receiving, by the warehouse database hub interface, the query from the user before the query is forwarded to the database warehouse,

(c.2) modifying, by the warehouse database hub interface, the query to take into account the arrangement of the data within the database warehouse

(c.3) querying, by the warehouse database hub interface, the database warehouse using the modified query to obtain the particular information from the database warehouse, and

(c.4) forwarding the particular information obtained from the database warehouse to the user.

2. A method as in claim 1 wherein substep (c.2) includes the substeps of:

adding aggregations to the query; and

,

adding defaults to the query, particular defaults added being based on summary levels of the query.

3. A method as in claim 1 wherein substep (c.2) includes the substep of:

adding limitations to the query for security purposes, the limitations being based on user and group identifications within the query.

4. A method as in claim 1 wherein substep (c.2) includes the substep of:

converting the query to utilize tables with pre-computed values and higher levels of summary.

5. A method as in claim 1 wherein substep (c.2) includes the substeps of:

locating appropriate partition sources for the query; and

,

generating a new query to each appropriate partition source located.

6. A method as in claim 1 wherein substep (c.3) includes the substeps of:

creating a result table; and

,

inserting database information into the result table as the database information is received from the database warehouse.

7. A method as in claim 6 wherein substep (c.3) additionally includes the substep of:

changing summary level of the database information in the result table to generate the particular information required by the query.

8. A method as in claim 7 wherein substep (c.3) additionally includes the substep of:

adding descriptive information as part of the particular information.

9. A method as in claim 1 wherein substep (c.2) includes adding a WHERE clause to the query in order to suppress recent changes to the data tables within the database warehouse.

10. A method as in claim 1 wherein substep (c.2) includes adding a constraint to prevent internal orders from being retrieved.

11. A method as in claim 1 wherein substep (c.2) includes replacing keyword strings with replacement values for the keyword strings.

12. A method as in claim 1 additionally comprising the following steps:

(d) recording queries from users received by the warehouse hub interface;

(e) recording modified queries forwarded from the warehouse database hub interface to the database warehouse;

(f) recording responses to the queries recorded in steps (d) and (3); and

,

(g) summarizing into tables, information recorded in steps (d), (e) and (f).

13. A method as in claim 1 wherein step (c) additionally comprises the following substeps:

;

(c.5) checking the query against a plurality of business rules;

(c.6) when the query does not obey the business rules, returning the query to the user with an error message without performing substeps (c.2), (c.3), and (c.4).

14. A database warehouse comprising:

a database which includes data arranged in data tables;

a warehouse database hub interface, coupled to the database, the warehouse database hub interface presenting to a user, a schema of the data in the database, the schema consisting of virtual tables, wherein arrangement of the data in the virtual tables is different than arrangement of data in the data tables, wherein the user is allowed access to the data in the data tables only through the schema consisting of the virtual tables, and wherein, in response to a query from the user for particular information stored in the database, the query being based on the schema provided by the warehouse database hub interface, the warehouse database hub interface intercepts and modifies the query to take into account the arrangement of the data within the database, queries the database using the modified query to obtain the particular information from the database, and forwards the particular information obtained from the database to the user.

15. A database warehouse as in claim 14 wherein the warehouse database hub interface modifies the query by adding aggregations to the query and adding defaults, based on summary level, to the query.

16. A database warehouse as in claim 14 wherein the warehouse database hub interface modifies the query by adding limitations to the query for security purposes, the limitations being based on user and group identifications within the query.

17. A database warehouse as in claim 14 wherein the warehouse database hub interface modifies the query by converting the query to search for a level of data which exists in a data table within the database.

18. A database warehouse as in claim 14 wherein the warehouse database hub interface modifies the query by locating appropriate partition sources for the query, and generating a new query to each appropriate partition source located.

19. A database warehouse as in claim 14 wherein as the warehouse database hub interface queries the database using the modified query to obtain the particular information from the database, the warehouse database hub interface creates a result table and inserts database information into the result table as the database information is received from the database.

20. A database warehouse as in claim 19 wherein the warehouse database hub interface changes summary level of the database information in the result table to generate the particular information.

21. A database warehouse as in claim 20 wherein the warehouse database hub interface adds descriptive information as part of the particular information.

22. A database warehouse as in claim 14 wherein the warehouse database hub interface modifies the query by adding a WHERE clause to the query in order to suppress recent changes to the data tables within the database.

23. A database warehouse as in claim 14 wherein the warehouse database hub interface modifies the query by adding a constraint to prevent internal orders from being retrieved.

24. A database warehouse as in claim 14 wherein the warehouse database hub interface modifies the query by replacing keyword strings with replacement values for the keyword strings.

25. A database warehouse as in claim 14 wherein the warehouse database hub interface includes:

recording means for recording queries from users received by the warehouse hub interface, modified queries forwarded from the warehouse database hub interface to the database, and responses to the queries; and

,

summarizing means for summarizing into tables information recorded by the recording means.

26. A database warehouse as in claim 14 wherein the warehouse database hub interface additionally checks the query against a plurality of business rules and when the query does not obey the business rules, the warehouse database hub interface returns the query to the user with an error message without performing modifying the query and forwarding the modified query to the database.