Systems and methods for extracting data sets from an online relational database into a data warehouse

• US 20050065944A1
• Filed: 07/15/2004
• Published: 03/24/2005
• Est. Priority Date: 07/22/2003
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Gunther, Christian, Aakolk, Michael, Adal, Nuran, Kountz, Jochen
• Granted Patent: US 7,599,903 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

Systems and methods are provided for extracting data sets from an online relational database into a data warehouse. A data query based on special timestamping technique is executed. Timestamping allows extraction of only these data sets that have changed since the last extraction point. The data sets that have been already imported stay unchanged and do not need to be deleted at the time of the new import. Extraction interval of data sets including a time window enclosed by lower and upper limits is defined. The extracted data sets that have been imported twice may be filtered out with the use of, for example, an operational data store (ODS) objects. The filtered out data sets may be available in the data warehouse for the drilldown reporting.

Claims

1. A method for providing data sets to a data warehouse, the method comprising:

a) timestamping the data sets;

b) storing the data sets in an online relational database;

c) executing a data query of the online relational database to obtain a subset of the data sets having timestamps subsequent to a first reference time, the first reference time being the time of a previous database query (t1) minus a time interval (delta); and

d) sending the subsets to the data warehouse.

2. The method of claim 1, wherein timestamping is performed by a posting system.

3. The method of claim 2, further comprising receiving the data sets from a server being coupled to a plurality of clients.

4. The method of claim 3, wherein the data sets are received by the posting system, the posting system comprising one or more servers.

5. The method of claim 2, wherein only the data sets that have been checked for consistency are entered in the posting system.

6. The method of claim 1, wherein timestamping comprises determining a timestamp value by means of synchronizing a clock of the posting system with a master clock of the online relational database.

7. The method of claim 1, wherein the said method further comprises repeating steps c) and d).

8. The method of claim 1, wherein the database query is performed repeatedly at a predefined time interval or in response to receipt of a request from the data warehouse.

9. The method of claim 1, wherein the data sets are transported to the data warehouse in the form of packets via ALE (Application Link Enabling).

10. The method of claim 1, wherein the subset comprises data sets having timestamp prior to a second reference time.

11. The method of claim 10, the second reference time comprising the time of the database query (t2) minus the time interval (delta).

12. The method of claim 10, the second reference time comprising the time of the database query (t2).

13. The method of claim 1, wherein the time interval (delta) is equal to or greater than a maximum latency time between time stamping and storage in database.

14. The method of claim 12, further comprising filtering out duplicate data sets contained in the subsets which have been received by the data warehouse as a result of previous and actual database queries.

15. The method of claim 14, wherein said filtering out duplicate data sets utilizes ODS objects.

16. The method of claim 15, further comprising providing a key information to the ODS objects to assist in filtering out redundant data subsets in the data warehouse.

25. A computer-readable storage medium holding code for performing the method of claim 1.

17. A system for providing data sets to a data warehouse, comprising:

a posting system comprising an internal clock and at least one server;

a timestamping component using the internal clock for timestamping of data sets being entered in the posting system;

an online relational database comprising one or more tables each configured to store a plurality of the data sets;

means for executing a data query of the online relational database to obtain a subset of the data sets having timestamps subsequent to first reference time, the first reference time being the time of the previous database query (t1) minus a time interval (delta); and

means for sending the subsets to the data warehouse.

18. The system of claim 17, further comprising:

means for filtering out duplicate subsets using ODS objects; and

a data warehouse database comprising one or more tables each configured to store a plurality of subsets of informational data for analysis using drilldown reporting.

19. The system of claim 17, wherein the online relational database further comprises means for synchronizing the internal clock of the posting system and a master clock of an online transaction processing system.

20. The system of claim 17, further comprising a buffer for temporary storage of extracted subsets, ODS objects, and primary keys of data subsets for filtering out duplicate data sets.

21. The system of claim 17, wherein the time interval is equal to or greater than a maximum latency time between time stamping and storage in database.

22. The system of claim 17, the subset comprising data sets having timestamp prior to a second reference time.

23. The system of claim 22, the second reference time comprising the time of the database query minus (t2) minus the time interval (delta).

24. The system of claim 22, the second reference time comprising the time of the database query (t2).