Usage-based optimization of network traffic and data warehouse size

• US 20040181509A1
• Filed: 03/13/2003
• Published: 09/16/2004
• Est. Priority Date: 03/13/2003
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Dettinger, Richard D., Stevens, Richard J., Will, Eric W., Kulack, Frederick A.
• Granted Patent: US 7,627,597 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

The present invention generally provides systems, methods, and articles of manufacture for maintaining a data warehouse having a plurality of fields updated with data from one or more data sources. Rather than automatically update every field of data available in the warehouse, a limited subset of fields identified through their involvement in queries issued against the warehouse are updated. By limiting the fields that are updated, the network bandwidth required to transmit the updates to the data warehouse may be reduced. Further, by removing fields from the data warehouse that are not in use, the size of the data warehouse may be reduced.

Claims

1. A method for maintaining a data warehouse having a plurality of fields for storing data from one or more data sources, comprising:

monitoring queries issued against the data warehouse; and

selectively updating the data warehouse with data, from one or more of the data sources, for a limited subset of fields involved in the monitored queries.

2. The method of claim 1, wherein the monitored queries are abstract queries comprising one or more logical fields, each corresponding to a physical field of the data warehouse.

3. The method of claim 1, wherein selectively updating the data warehouse comprises sending a request to one or more of the data sources for the data.

4. The method of claim 1, further comprising removing, from the data warehouse, one or more fields not involved in the monitored queries.

5. The method of claim 1, further comprising updating, based on the monitored queries, usage characteristics indicative of when fields corresponding to the usage characteristics were last involved in a query.

6. The method of claim 5, wherein the limited subset of fields comprises fields last involved in queries within a predetermined time period, as indicated by the usage characteristics.

7. A method for maintaining a data warehouse, comprising:

receiving updates for a plurality of fields of the data warehouse, the updates comprising data from one or more data sources;

monitoring queries issued against the data warehouse;

identifying, based on the monitored queries, one or more fields of the data warehouse that have not been involved in queries for a predetermined time period; and

sending a request to discontinue receiving updates for the identified fields.

8. The method of claim 7, further comprising, subsequent to sending the request to discontinue receiving updates for the identified fields:

detecting the involvement of at least one of the identified fields in a monitored query; and

sending a request to begin receiving updates for the at least one identified field.

9. The method of claim 8, further comprising automatically issuing the query against the data warehouse, in response to receiving an update for the at least one identified field.

10. The method of claim 7, further comprising, subsequent to sending the request to discontinue receiving updates for the identified fields:

detecting the involvement of at least one of the identified fields in a monitored query; and

generating, based on the monitored query, a partial query involving the at least one identified field; and

issuing the partial query against one of the data sources.

11. The method of claim 7, further comprising removing one or more of the identified fields from the data warehouse.

12. The method of claim 8, further comprising restoring at least one previously removed field to the data warehouse, in response to detecting the involvement of the previously removed field in a monitored query.

13. A computer-readable medium containing a program which, when executed by a processor, performs operations for maintaining a data warehouse having a plurality of fields for storing data from one or more data sources, the operations comprising:

monitoring queries issued against the data warehouse; and

selectively updating the data warehouse with data, from one or more of the data sources, for a limited subset of fields involved in the monitored queries.

14. The computer-readable medium of claim 13, wherein selectively updating the data warehouse comprises sending a request to one or more of the data sources for the data.

15. The computer-readable medium of claim 13, wherein:

selectively updating the data warehouse comprises sending, to the one or more data sources, an indication of the limited subset of fields involved in the monitored queries; and

the one or more data sources are configured to automatically send updated data for the limited subset of fields.

16. The computer-readable medium of claim 15, wherein the indication of the limited subset of fields comprises a list of fields not currently in use, as determined by the monitored queries.

17. The computer-readable medium of claim 16, wherein the operations further comprise identifying, for inclusion in the list of fields not currently in use, one or more fields that have not been involved in queries for a predetermined amount of time.

18. The computer-readable medium of claim 15, wherein the operations further comprise removing, from the data warehouse, one or more fields that have not been involved in the monitored queries for a predetermined amount of time.

19. The computer-readable medium of claim 18, wherein the operations further comprise:

detecting the involvement of a previously removed field in a monitored query, wherein the data warehouse does not have current data for the previously unused field; and

presenting a user with at least two options for issuing the monitored query involving the previously unused field.

20. The computer-readable medium of claim 19, wherein at least one of the options comprises:

storing the monitored query involving the previously unused field;

requesting data from one of the data sources to update the previously unused field; and

issuing the query after receiving the requested data.

21. The computer-readable medium of claim 19, wherein at least one of the options comprises:

generating, based on the monitored query involving the previously unused field, a partial query involving the previously unused field; and

issuing the partial query against one of the data sources.

22. A database system comprising:

a data warehouse comprising fields of data containing data originating from one or more data sources;

one or more applications configured to issue queries against the data warehouse; and

a warehouse manager configured to limit the number of fields of the data warehouse that are updated with data from the one or more data sources based on usage of the fields in queries issued against the data warehouse.

23. The database system of claim 22, wherein the warehouse manager is further configured to remove, from the data warehouse, fields not involved in queries issued against the data warehouse for a predetermined period of time.

24. The database system of claim 22, further comprising at least one replication manager configured to automatically send data from one or more of the data sources to update fields of the data warehouse.

25. The database system of claim 24, wherein the warehouse manager is configured to limit the number of fields that are updated with data from the one or more data sources by sending, to the replication manager, a request to discontinue sending data to update fields of the data warehouse not involved in queries issued for a predetermined period of time.

26. A database system comprising:

a data warehouse comprising physical fields of data containing data originating from one or more data sources;

a data repository abstraction component comprising logical fields mapped to corresponding physical fields of the data warehouse;

one or more applications configured to issue abstract queries against the data warehouse, the abstract queries based on logical fields of the data repository abstraction component; and

a warehouse manager configured to monitor the involvement of the logical fields in abstract queries issued by the one or more applications and limit the number of physical fields of the data warehouse that are updated with data from the one or more data sources, based on the monitored involvement.

27. The database system of claim 26, wherein the data repository abstraction layer further comprises a plurality of usage characteristics, each indicative of the involvement of a corresponding logical field in abstract queries.

28. The database system of claim 27, wherein the usage characteristics provide an indication of when corresponding logical fields were last involved in an abstract query.

29. The database system of claim 28, wherein the warehouse manager is configured to limit the number of physical fields that are updated with data from the one or more data sources to physical fields corresponding to logical fields last involved in an abstract query within a predetermined period of time, as indicated by the corresponding usage characteristics.

30. The database system of claim 28, wherein the warehouse manager is configured to remove, from the data warehouse, one or more physical fields corresponding to logical fields that have not been involved in abstract queries within a predetermined period of time, as indicated by the corresponding usage characteristics.