DATA WAREHOUSE SYSTEM

• US 20090299969A1
• Filed: 06/03/2008
• Published: 12/03/2009
• Est. Priority Date: 06/03/2008
• Status: Active Grant

• Current Assignee: ServiceNow Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Le, Jian
• Granted Patent: US 8,631,042 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases
• CPC Class Codes: G06F 16/2477 Temporal data queries

Abstract

Methods and apparatus, including computer program products, implementing and using techniques for analyzing historical data in a data warehouse. A data warehouse is provided. The data warehouse includes several database tables. Every database table has a start time column and an end time column. A query is issued to the data warehouse. The query includes customized query predicates against the start time column values and end time column values of a database table. A response is received from the data warehouse. The response includes table records satisfying the query and having valid (START_TIME, END_TIME) ranges that cover a fixed time point specified by the customized query predicates of the query. Methods for updating a database table in a data warehouse, and a data warehouse system are also described.

Claims

1. A computer-implemented method for analyzing historical data in a data warehouse, comprising:

providing a data warehouse, the data warehouse including a plurality of database tables, wherein every database table has a start time column and an end time column;

issuing a query to the data warehouse, wherein the query includes customized query predicates against the start time column values and end time column values of a database table; and

receiving a response from the data warehouse, wherein the response includes table records satisfying the query and having valid (START_TIME, END_TIME) ranges that cover a fixed time point specified by the customized query predicates of the query.

2. The method of claim 1, wherein the plurality of database tables includes one or more fact tables and one or more dimension tables.

3. The method of claim 1, wherein the query is expressed in a Structured Query Language.

4. The method of claim 3, wherein the query includes one or more of:

a With clause, a Select clause, a From clause, a Where clause, a Filter By clause, a Group By clause, a Having clause, and an Order By clause.

5. The method of claim 4, wherein the From clause specifies one or more tables and the Filter By clause specifies a date and time.

6. The method of claim 4, wherein a subset of tables in the From clause are marked with a special symbol.

7. The method of claim 4, wherein the Filter By clause applies to tables in the From clause that are marked with the special symbol.

8. The method of claim 1, wherein the query is expressed in a Multi-Dimensional Expression language.

9. A computer-implemented method for updating a database table in a data warehouse, comprising:

providing a data warehouse, the data warehouse including a plurality of database tables, wherein every database table has a start time column and an end time column;

receiving a record pertaining to an update of the database table;

determining whether the received record is a new record or whether the received record pertains to an update of an existing record in the database table;

in response to determining that the record pertains to an update of an existing record;

identifying the existing record, the existing record having an infinity timestamp value in the end column; and

updating an end time value for the existing record in the end time column with a current time stamp;

and

inserting the received record into the database table, including inserting a current time stamp into the start time column for the received record, and inserting an infinity time stamp into the end time column for the received record.

10. The method of claim 9, wherein the plurality of database tables includes one or more fact tables and one or more dimension tables.

11. The method of claim 9, wherein the current time stamp includes a year, a month, a date, an hour, a minute and a second.

12. The method of claim 9, wherein the infinity timestamp includes a specific set of characters determined by a database designer.

13. The method of claim 12, wherein the specific set of characters includes one of:

a large timestamp value and a NULL value.

14. A data warehouse system, including:

a database, the database comprising;

one or more database tables, wherein every database table includes a start time column and an end time column, and each record of the database table has a start time value specified in the start time column and an end time value specified in the end time column.

15. The data warehouse system of claim 14, wherein the one or more database tables include one or more a fact tables and one or more dimension tables.

16. The data warehouse of claim 14, wherein at least one of the start time value and the end time value includes a year, a month, a date, an hour, a minute and a second

17. The data warehouse of claim 14, further comprising:

a query module, the query module being operable to;

receive a query;

retrieve table records from each database table referenced by the query using a pair of customized query predicates against the start time column values and the end time column values of the database tables; and

join the table records retrieved from the database tables into a query result set.

18. The data warehouse of claim 17, wherein the pair of customized query predicates includes a START_TIME value and an END_TIME value.