The field generally relates to updating reports and more specifically to updating real-time data and historic data in reports.
Reporting and analysis techniques in an organization create massive data in a data warehouse. The data warehouse uses Extract Transform Load (ETL) applications to extract, transform and load the data into the data warehouse. The data in the data warehouse is in the format suitable for a user to analyze whereas in a transaction database the data is stored in the form of transactions. The data warehouse ETL jobs are resource consuming which may negatively affect the performance of the transactional system. Based on the organizational need, the ETL jobs are run in a batch either daily or weekly. Therefore historic data in the data warehouse includes data up to the last ETL job that was run. It is not feasible to run the entire ETL job for the data warehouse to get real-time data associated with the transactional system.
Though there are Enterprise Information Integration (EII) tools available which can provide real-time data they have to be implemented separately from the ETL tools. When implemented separately there is no link between the data warehouse and the EII tool. Therefore, the historic data from the data warehouse and the real-time data from the transaction system cannot be combined together in the report.
The user may want integration of real-time data from the transaction databases and the historic data from the data warehouse for some critical business decisions.
Various embodiments of systems and methods for updating real-time data in reports are described herein. The methods and systems involve receiving a request to view both historic data and the real-time data in a 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 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 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.
These and other benefits and features of embodiments of the invention will be apparent upon consideration of the following detailed description of preferred embodiments thereof, presented in connection with the following drawings.
The claims set forth the embodiments of the invention with particularity. The invention is illustrated by way of example and not by way of limitation in the figures of the accompanying drawings in which like references indicate similar elements. The embodiments, together with their advantages, may be best understood from the following detailed description taken in conjunction with the accompanying drawings.
Embodiments of techniques for updating real-time data to a report are described herein. In the following description, numerous specific details are set forth to provide a thorough understanding of various embodiments. One skilled in the relevant art will recognize, however, that the invention can be practiced without one or more of the specific details, or with other methods, components, materials, etc. In other instances, well-known structures, materials, or operations are not shown or described in detail to avoid obscuring aspects of the invention.
Reference throughout this specification to “one embodiment”, “this embodiment” and similar phrases, means that a particular feature, structure, or characteristic described in connection with the embodiment is included in at least one embodiment of the present invention. Thus, the appearances of these phrases in various places throughout this specification are not necessarily all referring to the same embodiment. Furthermore, the particular features, structures, or characteristics may be combined in any suitable manner in one or more embodiments.
A data warehouse is a database designed for query and report analysis rather than for transaction processing. The data warehouse includes historic data derived from a transaction system. The data warehouse may also include data from other data sources. The data warehouse environment usually includes an extraction, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, and other applications that manage the process of gathering data and delivering it to business users.
A report refers to information automatically retrieved (i.e., in response to a query) from a data source (e.g., a database, a data warehouse, and the like), where the information is structured in accordance with a report schema that specifies the form in which the information should be presented.
ETL describes software tools that combine functions of extract, transform and load to migrate data. For example, the migration may be from one database to another database or to a data warehouse. ETL is a specific data transformation process. Extracting refers to the process of reading the data from a source (e.g., a database, data warehouse and the like). Transforming is the process of converting the extracted data from its previous form into the form it needs to be in and cleansing it so that it can be placed in the target (e.g., a new database or data warehouse). Transformation occurs by using rules or lookup tables or by combining the data with other data. Loading is the process of updating the data into the target.
Enterprise Information Integration (EII) tool retrieves data from several databases to create a single presentation. EII is a process of integrating information from different sources. The process relies on data abstraction to provide a single interface (known as uniform data access) for viewing all the data within an organization, and a single set of structures and naming conventions (known as uniform information representation) to represent this data; the goal of EII is to get a large set of heterogeneous data sources to appear to a user or system as a single, homogeneous data source. The EII tool allows a user to access data in different formats such as structured, semi-structured and unstructured. Structured data includes server based data. Semi-structured data includes emails and spread sheets. Unstructured data includes text documents and multimedia information. EII tools have the ability to combine data in different formats. For instance, EII tools can combine historic data from the data warehouse with the real-time data from the transaction systems. For instance, consider a user requesting to view a report including sales revenue for a period of three months. The sales revenue of the past three months is stored as historic data in a data warehouse whereas the sales revenue for the current time is generated dynamically from the transaction database.
On some occasions, combination of historic data and real-time data is required by a user to make business decisions with the most current information at hand. In such scenarios, the user requests for a report to be updated with both the historic data and the real-time data. To achieve this, the historic data from a legacy system and the real-time data from the transactional system may need to be combined in a meaningful way.
When a request comes from the user to view both historic data and real-time data, the ETL-EII module identifies one or more data objects associated with the user request. The ETL-EII module identifies a target table associated with the identified one or more data objects by comparing each data object with the data objects in the target table. The target table is a table including end results of several ETL jobs. Once the target table is found, the ETL-EII module initiates a back tracking process for the one or more ETL jobs which created the target table. The transformations in the ETL jobs are tracked to a source table. Identifying the transformations in the ETL jobs enables the user to request the real-time data for the data objects present in the transformations. Generally, the ETL jobs are run in batches according to a user need. The data warehouse house includes ETL jobs up to a last batch job run. The last batch job run of the ETL jobs is a most recent job run. According to one embodiment, the delta ETL job is a last batch of ETL jobs uploaded to the data warehouse. An upload timestamp of the delta ETL job is determined by ETL metadata. Based on the upload timestamp of the delta ETL job, a data warehouse query is generated. Based on the transformations in the identified ETL jobs and the upload timestamp of the delta ETL job, a real-time query for the transaction system is generated. Both the queries are nested within a single query. The queries are executed by the EII module.
The result of the data warehouse query is the historic data up to the upload timestamp of the last delta ETL job. The historic data is retrieved from the data warehouse. The result of the real-time query is the real-time data of the transformations in the identified ETL jobs. Using the upload timestamp of the delta ETL job, the transactional database presents data since the upload of last delta ETL job. For instance, if the most recent batch of ETL jobs was uploaded to the data warehouse on May 1, 2010 at 5:00 pm, the upload timestamp of the delta ETL job will be May 1, 2010 5:00 pm. When this timestamp is used by the real-time query to retrieve real-time data from the transactional database, the data updated after 5:00 pm of May 1, 2010 is retrieved. The report is updated with the real-time data and the historic data.
According to one embodiment, the report provides the user an option to select between a historic data report from the data warehouse and real-time data from the transaction databases on using a refresh option. When the user initiates the refresh option a flag is enabled. According to one embodiment, when the user requests a warehouse connection the flag is set to 0, else if the user requests an EII connection (i.e. real-time data from the transaction database along with the historic data from the warehouse) the flag is set to 1.
In an embodiment, the user request includes receiving a data event from the user. The data event triggers an action in the report.
In an embodiment, receiving the request includes receiving a data event from the user. The data event triggers an action in the report. The data event is used in the real-time query during the execution to trigger an action. In another embodiment, receiving the request includes activating a refresh option in the report.
At decision block 330, whether the data object is tracked to a source table is verified. If yes, the process ends. If not, the back tracking process at 325 is continued until the source table is found.
For viewing real-time data, the target table Dest1 associated with the data objects TD.A and TD.B is identified. First, the data object TD.A is back tracked to identify ETL jobs linked to it. In business scenario 400, the data object TD.A is tracked to ETL job2. The transformations in the ETL job2 are identified. The transformation for data object TD.A is a subtraction involving data objects TT.A and T3.A. The transformations of the ETL jobs are tracked until they reach a source table. For instance, tracking the data object T3.A ends when the source table (i.e. table 3) is reached.
Since the data object TT.A did not reach the source table, the data object TT.A is again tracked to ETL job1. The transformation for the data object TT.A is a summation involving data objects T1.A and T2.B. When all the data objects comprising TT.A (e.g., T1.A and T2.B) reached the source tables (i.e. table 1 and table 2, respectively) the tracking process ends. The real-time data is requested for data objects T1.A, T2.B and T3.A. The real-time data is retrieved from the transaction database.
The user has also requested an event to be triggered when data object TD.B becomes 1. The data object TD.B is back tracked to ETL job 2. The transformation of TD.B is a summation involving data objects TT.B and T3.B. Tracking the data object T3.B ends when the source table (i.e. table 3) is reached. The data object TT.B is tracked to ETL job1. The transformation of the data object TT.B is a summation involving data objects T1.B and T2.A. The tracking process ends when the source tables (i.e. table 1 and table 2) are reached. The real-time data for the data objects T3.B, T1.B and T2.A are retrieved and an event is triggered when the data object TD.B=1.
The real-time data for the identified data objects is retrieved based on the upload timestamp of the delta ETL job. The real-time data since the upload of last delta ETL job is retrieved from the transaction database. The historic data for the data object TD.A present in the data warehouse is also retrieved.
Consider a scenario, where a report includes profits of an industrial segment. When a user requests for a real-time data for profit of a particular industrial segment, the data objects associated with the profit are identified. The data objects of the profit may be revenue and expenses. The revenue and expenses data objects are back tracked to identify the other data objects associated with them. The other data object of the revenue may be sales generated. The other data object of the expenses may be expenses for packing and shipping. Assume that the most recent batch of delta ETL jobs including details of the profit was uploaded at 5:00 pm on May 2, 2010. Based on the upload timestamp of the delta ETL job, a database query is generated and the historic data for profit for a particular industrial segment is obtained as the result up to 5:00 pm of May 2, 2010. Now, the real-time data is fetched for transformations (i.e. sales generated, packing and shipping) of revenue and expenses from the transactional database. The real-time data results include real-time data (i.e. any change made to the data objects) after 5:00 pm of May 2, 2010. The real-time data is merged along with the historic data and presented to the user.
A processor (not shown in the figure) in communication with the memory 515 may include instructions for the ETL-EII module 520, the report module 525, the event module 530 and an update module 540 to perform the required operations. The data warehouse 505 includes historic data. The transaction database 510 includes real-time data. The ETL-EII module 520 identifies one or more data objects associated with the request. According to one embodiment, the ETL-EII module 520 checks the semantics of the identified one or more data objects from the semantic layer 535. The report module 525 also provides an option to choose between a data warehouse connection and a real-time connection.
The ETL-EII module 520 identifies a target table associated with the identified one or more data objects. One or more ETL jobs linked to the identified one or more data objects are identified by the back tracking process. Identifying ETL jobs linked to the requested one or more data objects enables ETL operations to be performed on the required data objects rather than performing the ETL operations for the entire data warehouse. The one or more ETL jobs include transformations of different one or more data objects. The one or more ETL jobs are tracked back till it reaches a source table. The ETL-EII module 520 also determines a delta ETL job associated with the ETL job. The delta ETL job is the most recent batch of ETL jobs that were uploaded to the data warehouse. The upload timestamp of the delta ETL job is determined by ETL metadata. The ETL-EII module 520 generates a query for the data warehouse 505 based on the upload timestamp of delta ETL job. A real-time query for the transaction database 510 is generated based on the transformations in the ETL jobs and the upload timestamp of the delta ETL job. Both the queries are nested and executed by the EII module. The historic data and real-time data are generated. Both the real-time data and the historic data are updated to the report generated in the report module 530 using the update module 540. The real-time data is in the format like that of the data in the data warehouse.
In an embodiment, an event module 530 receives a data event from a user to trigger an action in the report. The event module 530 polls the transaction database 510 at regular intervals to trigger the action. In another embodiment, the real-time data may be requested by the event module 530.
Some embodiments of the invention may include the above-described methods being written as one or more software components. These components, and the functionality associated with each, may be used by client, server, distributed, or peer computer systems. These components may be written in a computer language corresponding to one or more programming languages such as, functional, declarative, procedural, object-oriented, lower level languages and the like. They may be linked to other components via various application programming interfaces and then compiled into one complete application for a server or a client. Alternatively, the components may be implemented in server and client applications. Further, these components may be linked together via various distributed programming protocols. Some example embodiments of the invention may include remote procedure calls being used to implement one or more of these components across a distributed programming environment. For example, a logic level may reside on a first computer system that is remotely located from a second computer system containing an interface level (e.g., a graphical user interface). These first and second computer systems can be configured in a server-client, peer-to-peer, or some other configuration. The clients can vary in complexity from mobile and handheld devices, to thin clients and on to thick clients or even other servers.
The above-illustrated software components are tangibly stored on a computer readable storage medium as instructions. The term “computer readable storage medium” should be taken to include a single medium or multiple media that stores one or more sets of instructions. The term “computer readable storage medium” should be taken to include any physical article that is capable of undergoing a set of physical changes to physically store, encode, or otherwise carry a set of instructions for execution by a computer system which causes the computer system to perform any of the methods or process steps described, represented, or illustrated herein. Examples of computer-readable media include, but are not limited to: magnetic media, such as hard disks, floppy disks, and magnetic tape; optical media such as CD-ROMs, DVDs and holographic devices; magneto-optical media; and hardware devices that are specially configured to store and execute, such as application-specific integrated circuits (“ASICs”), programmable logic devices (“PLDs”) and ROM and RAM devices. Examples of computer readable instructions include machine code, such as produced by a compiler, and files containing higher-level code that are executed by a computer using an interpreter. For example, an embodiment of the invention may be implemented using Java, C++, or other object-oriented programming language and development tools. Another embodiment of the invention may be implemented in hard-wired circuitry in place of, or in combination with machine readable software instructions.
A data source is an information resource. Data sources include sources of data that enable data storage and retrieval. Data sources may include databases, such as, relational, transactional, hierarchical, multi-dimensional (e.g., OLAP), object oriented databases, and the like. Further data sources include tabular data (e.g., spreadsheets, delimited text files), data tagged with a markup language (e.g., XML data), transactional data, unstructured data (e.g., text files, screen scrapings), hierarchical data (e.g., data in a file system, XML data), files, a plurality of reports, and any other data source accessible through an established protocol, such as, Open DataBase Connectivity (ODBC), produced by an underlying software system (e.g., ERP system), and the like. Data sources may also include a data source where the data is not tangibly stored or otherwise ephemeral such as data streams, broadcast data, and the like. These data sources can include associated data foundations, semantic layers, management systems, security systems and so on.
In the above description, numerous specific details are set forth to provide a thorough understanding of embodiments of the invention. One skilled in the relevant art will recognize, however, that the invention can be practiced without one or more of the specific details or with other methods, components, techniques, etc. In other instances, well-known operations or structures are not shown or described in detail to avoid obscuring aspects of the invention.
Although the processes illustrated and described herein include series of steps, it will be appreciated that the different embodiments of the present invention are not limited by the illustrated ordering of steps, as some steps may occur in different orders, some concurrently with other steps apart from that shown and described herein. In addition, not all illustrated steps may be required to implement a methodology in accordance with the present invention. Moreover, it will be appreciated that the processes may be implemented in association with the apparatus and systems illustrated and described herein as well as in association with other systems not illustrated.
The above descriptions and illustrations of embodiments of the invention, including what is described in the Abstract, is not intended to be exhaustive or to limit the invention to the precise forms disclosed. While specific embodiments of, and examples for, the invention are described herein for illustrative purposes, various equivalent modifications are possible within the scope of the invention, as those skilled in the relevant art will recognize. These modifications can be made to the invention in light of the above detailed description. Rather, the scope of the invention is to be determined by the following claims, which are to be interpreted in accordance with established doctrines of claim construction.