Data warehouse system

• US 8,631,042 B2
• Filed: 06/03/2008
• Issued: 01/14/2014
• Est. Priority Date: 06/03/2008
• Status: Active Grant

• Current Assignee: ServiceNow Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Le, Jian
• Primary Examiner(s): Ahn, Sangwoo
• Application Number:
  US12/132,600
• Publication Number:
  US 20090299969A1
• Time in Patent Office: 2,051 Days
• Field of Search: None
• US Class Current: 707/796
• 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, wherein

each fact table within the data warehouse has a start time column and an end time column,

each record of the fact tables has a start time value specified in the start time column, the start time value indicating an insertion time stamp of the record into the fact table,

each record of the fact tables has an end time value specified in the end time column, the end time value being one of;

an infinity time stamp indicating that the record is a current record and a non-infinity time stamp indicating that the record is a historical record, and

the fact tables within the data warehouse collectively form a single searchable copy of data warehouse data that contains both continuous historical data and current data for the data warehouse; and

in response to a query against the data warehouse, retrieving records from one or more fact tables using a pair of customized query predicates against the start time column values and the end time column values of the one or more fact tables.

2. The method of claim 1, wherein the data warehouse further includes 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 fact 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 6, wherein the Filter By clause applies to fact 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 record in a data warehouse, comprising:

providing a data warehouse, wherein

each fact table within the data warehouse has a start time column and an end time column,

each record of the fact table has a start time value specified in the start time column, the start time value indicating an insertion time stamp of the record into the fact table

each record of the fact table has an end time value specified in the end time column, the end time value being one of;

an infinity time stamp indicating that the record is a current record and a non-infinity time stamp indicating that the record is a historical record, and

the fact tables within the data warehouse collectively form a single searchable copy of data warehouse data that contains both continuous historical data and current data for the data warehouse; and

in response to determining that a record within a fact table of the data warehouse needs to be updated;

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

inserting a new record comprising updates of the record into the fact table, the new record having a current time stamp in the start time column and an infinity time stamp in the end time column.

10. The method of claim 9, wherein the data warehouse further includes one or more dimension tables.

11. The method of claim 9, wherein the current time stamp includes one or more of:

a year, a month, a date, an hour, a minute and a second.

12. The method of claim 9, wherein the infinity time stamp includes a specific set of characters.

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

a large time stamp value and a NULL value.

14. A data warehouse system, including:

a processor;

a computer-readable storage medium configured to store one or more tables in a data warehouse, wherein;

each fact table within the data warehouse includes a start time column and an end time column,

each record of the fact table has a start time value specified in the start time column, the start time value indicating an insertion time stamp of the record into the fact table,

each record of the fact tables has an end time value specified in the end time column, the end time value being one of;

an infinity time stamp indicating that the record is a current record and a non-infinity time stamp indicating that the record is a historical record, and

the fact tables within the data warehouse collectively form a single searchable copy of data warehouse data that contains both continuous historical data and current data for the data warehouse; and

a query module, the query module being configured to;

in response to a query against the data warehouse, retrieve records from one or more fact tables 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 one or more fact tables.

15. The data warehouse system of claim 14, wherein the data warehouse further includes one or more dimension tables.

16. The data warehouse system of claim 14, wherein at least one of the start time value and the end time value includes one or more of:

a year, a month, a date, an hour, a minute and a second.

17. The data warehouse system of claim 14, wherein the pair of customized query predicates includes a START₁₃ TIME value and an END_TIME value.