UPDATING HISTORIC DATA AND REAL-TIME DATA IN REPORTS

• US 20110313969A1
• Filed: 06/17/2010
• Published: 12/22/2011
• Est. Priority Date: 06/17/2010
• Status: Abandoned Application

• Current Assignee: Business Objects Incorporated (SAP SE)
• Original Assignee: Business Objects Incorporated (SAP SE)
• Inventors: RAMU, GOWDA TIMMA
• Application Number:
  US12/817,218
• Publication Number:
  US 20110313969A1
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

Disclosed are methods and systems for updating a report with real-time data. The method and systems involve receiving a request to view both historic data and real-time data in the report, identifying one or more data objects associated with the request, identifying one or more ETL jobs associated with the identified one or more data objects, determining a delta ETL job associated with the identified one or more ETL jobs and a upload timestamp of a delta ETL job, generating a data warehouse query based on the upload timestamp of the delta ETL job, generating a real-time query based on the transformations in the one or more ETL jobs and the upload timestamp of the delta ETL job, executing queries of the data warehouse query and real-time query to obtain historic data and real-time data and updating the report with both real-time data and historic data.

Claims

1. An article of manufacture including a computer readable storage medium to tangibly store instructions, which when executed by a computer, cause the computer to:

receive a request to view a historic data and a real-time data in a report;

identify one or more data objects associated with the request;

identify one or more extract, transform and load (ETL) jobs associated with the identified one or more data objects, the one or more ETL jobs comprising transformations of a different one or more data objects;

determine a delta ETL job associated with the identified one or more ETL jobs and an upload timestamp of the delta ETL job, wherein the delta ETL job is a most recent batch of ETL jobs uploaded to a data warehouse;

based on the upload timestamp of the delta ETL job, generate a data warehouse query for the data warehouse;

based on the transformations in the one or more ETL jobs and the upload timestamp of the delta ETL job, generate a real-time query for a transaction database;

execute the data warehouse query and the real-time query to obtain the historic data and the real-time data; and

update the report with the historic data and the real-time data.

2. The article of manufacture in claim 1, wherein receiving the request comprises receiving a data event from a user for triggering an action in the report.

3. The article of manufacture in claim 1, wherein receiving the request comprises activating a refresh option on the report.

4. The article of manufacture in claim 1, wherein identifying the one or more data objects associated with the request comprises identifying a target table.

5. The article of manufacture in claim 1, wherein the real-time data comprises data since the upload of last delta ETL job.

6. The article of manufacture in claim 1, wherein the transformations of the one or more ETL jobs are tracked to a source table.

7. A computer system for updating historic data and real-time data to a report, the system comprising:

a data warehouse to store the historic data;

a transaction database to store the real-time data;

a processor;

a memory in communication with the processor, storing;

a report module to receive a request to view both the historic data and the real-time data in the report;

an extract, transform and load-enterprise information integration (ETL-EII) module to;

identify one or more data objects associated with the request;

identify one or more ETL jobs associated with the identified one or more data objects, the one or more ETL jobs comprising transformations of a different one or more data objects;

determine a delta ETL job associated with the one or more ETL jobs and an upload timestamp of the delta ETL job, the delta ETL job is a most recent batch of ETL jobs uploaded to the data warehouse;

based on the upload timestamp of the delta ETL job, generate a data warehouse query;

based on the transformations in the identified one or more ETL jobs and the upload timestamp of the delta ETL job, generate a real-time query for the transaction database;

execute the data warehouse query and the real-time query to obtain the historic data and the real-time data; and

an update module to update the report with both the historic data and the real-time data.

8. The computer system of claim 7, wherein the ETL-EII module identifies a target table associated with the identified one or more data objects.

9. The computer system of claim 7, wherein the transformations of the one or more ETL jobs are tracked to a source table.

10. The computer system of claim 7, wherein the memory comprises an event module to receive a data event from a user.

11. The computer system of claim 10, wherein the event module triggers an action in the report based on the data event.

12. The computer system of claim 7 further comprises a semantic layer.

13. The computer system of claim 12, wherein the semantic layer comprises semantics of the identified one or more data objects.

14. A computerized method for updating historic data and real-time data to a report, the method comprising:

identifying one or more data objects associated with a user request to view both the historic data and the real-time data;

identifying one or more extract, transform and load (ETL) jobs associated with the identified one or more data objects, the one or more ETL jobs comprising transformations of a different one or more data objects;

initiating a backtracking of the transformations in the identified one or more ETL jobs;

determining a timestamp of a most recent data warehouse update associated with the identified one or more ETL jobs;

based on the timestamp of the most recent data warehouse update, retrieving the historic data from a data warehouse up to the timestamp of the most recent data warehouse update;

retrieving the real-time data for the transformations in the one or more ETL jobs from a transaction database since the timestamp of the most recent data warehouse update; and

merging the historic data and the real-time data.

15. The computerized method of claim 14, wherein the user request comprises receiving a data event from the user.

16. The computerized method of claim 15, wherein receiving the data event from the user comprises triggering an action in the report.

17. The computerized method of claim 14, wherein identifying the one or more data objects comprises identifying a target table.

18. The computerized method of claim 14, wherein initiating backtracking of the identified one or more ETL jobs comprises tracking the ETL jobs to a source table.

19. The computerized method of claim 14, wherein the timestamp of the most recent data warehouse update is determined by an ETL metadata.

20. The computerized method of claim 14, wherein merging the historic data and the real-time data comprises updating the report with the historic data and the real-time data.