Patent Publication Number: US-2006004842-A1

Title: Apparatus and method for creating new reports from a consolidated data mart

Description:
This application is a continuation-in-part of pending U.S. Ser. No. 10/328,744, filed Dec. 23, 2002, entitled “Apparatus and Method for Creating New Reports from Discrete Reports”. 
    
    
     BRIEF DESCRIPTION OF THE INVENTION  
      This invention relates generally to the generation of reports. More particularly, this invention relates to a technique for using reports as a data source in order to create consolidated reports based upon individual reports.  
     BACKGROUND OF THE INVENTION  
      There are a number of commercially available products to create reports from data stored in a database. Business Objects Americas of San Jose, Calif., sells a number of widely used report generation products, including Crystal Reports™, Crystal Analysis™, Crystal Enterprise™, and Business Objects Enterprise™.  
      Known report generation tools access a database each time a report needs to be generated. This can result in large numbers of database accesses that slow down the report generation process. In addition, since a single database is used, it is difficult to decentralize database activity.  
      Another limitation associated with existing report generation tools is that the creation of a new form of report commonly requires the rebuilding of the database schema, which can be complex and time consuming. Another problem with existing report generation tools is that they have difficulty merging data from multiple databases into a single report. This problem is especially acute in the case where the databases are from different vendors (e.g., Oracle®, IBM®, Microsoft®).  
      Databases have their data updated on a regular basis. While this is important from the standpoint of data consistency, it creates difficulties in obtaining snapshots of data at different points in time.  
      In view of the foregoing, it would be highly desirable to provide an improved technique for generating reports to overcome some of the limitations associated with existing report generation tools.  
     SUMMARY OF THE INVENTION  
      The invention includes a computer readable memory with a consolidated data mart generator to generate a consolidated data mart based upon an analysis of a repository of individual reports. A report generation tool produces a report via access to the consolidated data mart.  
      The invention also includes a method of forming a report by analyzing a repository of individual reports, generating a consolidated data mart based upon data collected during the analyzing, and producing a report based upon information within the consolidated data mart.  
      The invention includes an alternate method of forming a report wherein a report repository is defined with individual reports produced from database information. Selected data is loaded from various individual reports of the report repository to form a consolidated data mart. A report based upon information within the consolidated data mart is then produced.  
      The invention provides a technique that allows data from individual reports to be accessible by standard reporting tools. That is, the selection of data from individual reports to form a consolidated data mart results in a data repository that can be used by standard reporting tools. The invention facilitates the creation of reports that show trends across data found in various reports. The invention is able to access and process snapshots of data. The invention allows the merging of data from multiple databases, having either similar or disparate formats. In addition, the invention facilitates the decentralization of database computational activity. 
    
    
     BRIEF DESCRIPTION OF THE FIGURES  
      The invention is more fully appreciated in connection with the following detailed description taken in conjunction with the accompanying drawings, in which:  
       FIG. 1  illustrates the operation of consolidated report generation in accordance with an embodiment of the invention.  
       FIG. 2  illustrates processing operations performed in accordance with an embodiment of the invention.  
       FIG. 3  is an example of portions of reports used to create a consolidated report in accordance with an embodiment of the invention.  
       FIG. 4  illustrates a user interface that may be used to create a consolidated report in accordance with an embodiment of the invention.  
       FIG. 5  illustrates another user interface that may be used to create a consolidated report in accordance with an embodiment of the invention.  
       FIGS. 6-9  illustrate specific reports that may be used to create a consolidated report in accordance with an embodiment of the invention.  
       FIG. 10  illustrates a consolidated report created from the information of the specific reports of  FIGS. 6-9  in accordance with an embodiment of the invention.  
       FIG. 11  illustrates the operation of expanding information in a consolidated report in accordance with an embodiment of the invention.  
       FIG. 12  illustrates the result of drilling through information within the report of  FIG. 11  to obtain a detailed base report.  
       FIG. 13  illustrates a computer network architecture that may be utilized to implement embodiments of the invention.  
       FIG. 14  illustrates a consolidated report architecture that utilizes a report as a root object in accordance with an embodiment of the invention.  
       FIG. 15  illustrates a consolidated report architecture that utilizes a root object in accordance with an embodiment of the invention.  
       FIG. 16  illustrates the generation of a consolidated report from a consolidated data mart in accordance with an embodiment of the invention.  
       FIGS. 17-30  illustrate various Graphical User Interfaces that may be used to invoke the operations of the invention and to display the results of the operations of the invention. 
    
    
      Like reference numerals refer to corresponding parts throughout the several views of the drawings.  
     DETAILED DESCRIPTION OF THE INVENTION  
       FIG. 1  illustrates the operation of consolidated report generation in accordance with an embodiment of the invention. In accordance with the prior art, a database  20  is accessed by a conventional report generation tool  22  to produce a set of reports  24 A- 24 N. In the prior art, if a new report is to be created, the report generation tool  22  must be used to access the database  20  to create a new report.  
      In accordance with the invention, a consolidated report generator  26  is used to produce a consolidated report  28  based upon the information contained in the previously generated reports  24 A- 24 N. In other words, the consolidated report generator  26  does not access the data in the database  20 ; rather, the consolidated report generator  26  uses the data in the reports  24 A- 24 N as a data source to produce a new consolidated report  28 .  
       FIG. 2  illustrates processing operations associated with an embodiment of the invention. Initially, a report repository of individual reports is created or defined from database information (block  30 ). A report repository is simply a collection of reports. The individual reports may be created with a conventional report generation tool, such as Crystal Reports™, Crystal Analysis™, Crystal Enterprise™, or Business Objects Enterprise™ sold by Business Objects Americas, San Jose, Calif.  
      By way of example,  FIG. 3  illustrates four individual reports  32 A- 32 D. Each individual report provides information for a quarter of a calendar year. Thus, there is a first quarter report  32 A, a second quarter report  32 B, a third quarter report  32 C, and a fourth quarter report  32 D. In this example, each report  32  has a customer field  34 , an employee field  36 , an employee address field  38 , and an orders field  40 , as shown in  FIG. 3 . The orders field  40  has a set of sub-fields, including an order ID field  42 , an order amount field  44 , a customer ID field  46 , a quarter sales field  48 , an order date field  50 , a required date field  52 , a ship date field  54 , a courier website field  56 , a ship via field  58 , a shipped field  60 , a Purchase Order number field  62 , and a payment received field  64 .  
      Returning to  FIG. 2 , the next processing operation is to select consolidated report criteria (block  70 ).  FIG. 4  illustrates a graphical user interface  72  that may be used in accordance with an embodiment of the invention to facilitate this operation. The graphical user interface  72  may be used to display a series of reports in a repository of reports. In this example, the available reports  32 A- 32 D correspond to the reports  32 A- 32 D shown in  FIG. 3 . In one embodiment of the invention, specific fields in the selected reports are then selected. The graphical user interface  74  of  FIG. 5  shows a set of fields corresponding to the fields illustrated in the exemplary reports of  FIG. 3 . In this example, the quarter sales filed  48  is selected.  
      Alternate techniques may be used to select consolidated report criteria. In another embodiment of the invention, a prompt is provided for a data source. For example, the data source may be a report file path or a path of a directory that contains multiple report files. Preferably, a data context for a report path can be defined to limit the amount of viewed information. For example, a special character (e.g., “?”) can be appended to a report path to limit information to selected fields of a report. Thus, for example, assuming a base report grouped by region, to see only the Quarterly sales records for the U.S. in a report, the following report path and data context can be used: c:\\Directory Name\Report Name.rpt?datacontext=/Country[US]. This example assumes a base report that is grouped by country and customer. Specifying the sum of sales in a country within the base report results in a consolidated report listing this information. One can generate trend data for all countries (e.g., datacontext=/Country[*]) or trend data for a selected country (e.g., datacontext=/Country[US]).  
      In one embodiment of the invention, a consolidated report of the invention is created through the following instruction sequence: 
          1. On a standard toolbar, click New.     2. In a Reports Gallery, click As a Blank Report and then OK.     3. In a Database dialog box, expand Create New Connection and click OLE DB.     4. From a Provider list, select a Rowset Provider and click Next.     5. In a Data Source field, enter the name of the reports file(s) to use in the consolidated report.     6. Click Finish. (A data source called Reports is added to the OLE DB folder.)     7. Add the Reports data source to the Selected Tables area and click OK.        

      At this point, a consolidated data source is ready for use as any other data source for report creation. If the Reports node in the Field Explorer is expanded, the fields from each report specified as a data source may be displayed.  
      Preferably, special fields are provided to track specific information about the report fields in the data source. Example special fields include: 
          Report Path—This field shows the path and name of the report for the current record.     Group Path—This field shows the group level and record number of the current record in the report.     Record Number—This field shows the record number of the current record in the report.     Data Date—This field shows the date the data was last refreshed in the report.     Data Time—This field shows the time the data was last refreshed in the report.        

      Returning now to  FIG. 2 , the next processing operation is to analyze individual reports within a report repository based upon selected consolidated report criteria (block  80 ). Executable code is invoked to perform this operation. This results in the generation of a consolidated report (block  82 ).  
       FIG. 3  illustrates a consolidated report  28 . In particular, the figure illustrates a consolidated report  28  with a first quarter sales field  90 , a second quarter sales field  92 , a third quarter sales field  94 , a fourth quarter sales field  96 , and a total sales field  98 , which represents the sum of the individual sales fields. Observe here that the consolidated report  28  was created based upon data in individual reports  32 A- 32 D, instead of accessing a database. Therefore, a new report is available without rebuilding the database schema.  
      The consolidated report  28  shows trends across data found in time stamped instances of other reports. Thus, the consolidated report  28  has captured snapshots of data over an extended period of time. It should be further appreciated that the database source of the individual reports used to generate the consolidated report  28  is irrelevant. Thus, the consolidated report  28  can be created from individual reports that were derived from disparate databases. Accordingly, the invention allows the merging of data from multiple databases, having either similar or disparate formats. It can also be appreciated that the invention reduces database accesses as new reports are generated from individual reports, not database accesses. In addition, the invention facilitates the decentralization of database computational activity. For example, individual reports may be created in branch offices and then transmitted to a central office. At the central office, a consolidated report is generated based upon the received reports from the branch offices.  
      Returning once again to  FIG. 2 , a final optional operation is displayed. In particular, an operation may be implemented to drill through to a source report (block  100 ). Preferably, each field in a consolidated report  28  has an associated link to its source report. By activating this link, the user is returned to the source report, thereby allowing drill down processing. Arrow  102  of  FIG. 3  illustrates a link between the first quarter sales field  90  and its corresponding field  48  in source report  32 A.  
      Now that the general features of the invention have been described, attention turns to a more specific example for the purpose of more fully illustrating the features and advantages of the invention.  FIG. 6  illustrates a quarterly sales report. As shown in the design/preview window  103 , this sales report specifies quarterly sales for North America (NA), Europe (EU), and the Asia Pacific (Asia Pac). The report window  104  illustrates a breakdown for North American sales by country, including the U.S. and Canada. The report also reflects a total sales amount for North America for the first calendar quarter ending Mar. 31, 2002. Similarly, the report reflects a breakdown for European sales by country, including England, France, and Germany. A total sales volume for the European region for the first quarter is also supplied. Similar information is provided for the Asia Pacific region.  FIGS. 7-9  reflect the same information for the subsequent quarters of 2002.  
      Using the previously described techniques, a consolidated report can be produced from the individual reports of  FIGS. 6-9 . The resultant consolidated report is shown in  FIG. 10 . The consolidated report  105  of  FIG. 10  illustrates quarterly sales and total sales for each region (i.e., Asia Pacific, Europe, and North America). The tabular numeric data is also supplied in a graph  106  in this example.  
      In accordance with an embodiment of the invention, the reports used to generate the regional data can be invoked. By way of example,  FIG. 11  illustrates an expanded regional data report  107 . In this example, the expanded regional data is for Europe and includes individual quarterly and total sales for England, France, and Germany.  
      In accordance with an embodiment of the invention, further expansion or drill through to source reports can be accomplished. For example, if the link associated with the third quarter sales for France is activated, then the user receives the report of  FIG. 8 . If the user activates the France link  108  of  FIG. 8 , then a detailed sales report for France in the third quarter is supplied, as shown in  FIG. 12 . The detailed report  109  of  FIG. 12  provides information on the individual sales transactions that produced the third quarter sales total (i.e., $765,332.34) for France.  
       FIG. 13  illustrates a computer network architecture that may be utilized to implement embodiments of the invention. The computer network architecture  110  includes a client computer  112 . The client computer has a central processing unit  114  connected to a network connection circuit  116  via a bus  118 . The network connection circuit  116  is also connected to a network transport medium  119 , which may be any wired or wireless transport medium. A set of input/output devices (not shown) is also attached to the bus  118  to allow a user to input and view data.  
      A memory  120  is also connected to the bus  118 . The memory  120  stores a set of executable programs. One executable program is a query module  122  (e.g., a report generation tool). The query module  122  utilizes standard techniques to produce a set of reports  124 A- 124 N.  
      The query module  122  may access one or more databases resident on the client computer  112 . However, in this example, the query module  122  accesses one or more databases resident on server computer  130 . Server computer  130  includes a central processing unit  132  connected to a network connection circuit  134  via a bus  136 . The network connection circuit  134  is also connected to the network transport medium  119 . A set of input/output devices (not shown) is also attached to the bus  136  to allow a user to ipnut and view data. A memory  138  is also connected to the bus  136 . The memory  138  stores one or more databases  140 A- 140 N.  
      A consolidated report generator  150  may be executed from either the server computer  130  or the client computer  112 . In this example, the client computer  112  includes a consolidated report generator  150  with executable instructions to implement the operations described herein. This results in the creation of one or more consolidated reports  152 A- 152 N. Additional consolidated reports may be formed from the information contained in individual consolidated reports.  
       FIG. 14  illustrates a consolidated report architecture that utilizes a report as a root object in accordance with an embodiment of the invention. A report object  160  is used to produce report instances  162 A- 162 N. The consolidated report generator  150  uses the report object  160  to define fields for a consolidated report, as discussed in connection with  FIGS. 3-5 . The consolidated report generator  150  also produces a consolidated report  164  based upon data contained within the report instances  162 A- 162 N, as previously discussed.  
       FIG. 15  illustrates a consolidated report architecture that utilizes a root object in accordance with an embodiment of the invention. A report object  160  is used to produce report instances  162 A- 162 N. The consolidated report generator  150  is used to produce a first instance of a consolidated report  164  and a second instance of a consolidated report  166  based upon the data in the report instances  162 . A consolidated report object  168  operates as a parent object to the consolidated report instances  164 ,  166 .  
      Different object architectures can be used to implement various consolidated report functionality. For example, consolidated reports can be programmed to run at a scheduled time. At the scheduled time, the new object inherits the superset of all instances of the parent. Security may also be implemented through various object architectures. For example, a user may be prohibited from generating a consolidated report unless the individual has access to the parent object.  
      The consolidated report generation techniques of the invention may employ any number of architectures.  FIG. 16  illustrates one alternate architecture. As in the case of  FIG. 1 , a database  20  is accessed by a report generation tool  22  to produce a set of reports  24 A through  24 N. Then, instead of using a consolidated report generator  26  to produce a consolidated report  28 , as shown in  FIG. 1 , a consolidated data mart generator  1600  is used to produce a consolidated data mart  1602 . The consolidated data mart generator  1600  includes executable instructions to select data from individual reports  24  to produce a consolidated data mart, which may store the selected data in a table or similar structure. Thus, the consolidated data mart is a data repository that stores data selected from report instances. The consolidated data mart provides formatted data that is accessible by any number of report generation tools. Thus, the consolidated data mart operates to make data from individual reports accessible to a variety of report generation tools.  
      The consolidated data mart generator  1600  may be configured to provide incremental updates to the consolidated data mart  1602 . For example, the consolidated data mart generator  1600  may be configured to incrementally upload data to the consolidated data mart  1602  each time a new report instance is created.  
      The consolidated data mart generator  1600  may also include executable instructions to produce a semantic layer  1604 , which includes metadata associated with the data in the consolidated data mart  1602 .  
      A consolidated report generator  1626  then produces a report  1628  by accessing the consolidate data mart  1602 . That is, unlike previous embodiments in which a consolidated report generator  26  accessed individual reports  24 , the consolidated report generator  1626  of this embodiment accesses the consolidated data mart  1602 , which stores data from individual reports  24 . The consolidated report generator  1626  is not a specialized tool, rather it may be a standard report generator. The term consolidated report generator  1626  is used to denote that the resultant report includes data that has been consolidated from various report instances.  
      The consolidated report generator  1626  may also access the semantic layer  1604  in the process of generating a consolidated report  1628 . The consolidated report  1628  typically reflects the differences in data between different report instances.  
      In one embodiment, the report instances  24  contain metadata for the report and historical data content, which is preserved as a snapshot of how a business is performing. For example, with key financial data, the information in a set of report instances can satisfy the purpose of establishing an audit trail.  
      The history of report executions captures key information requirements for operational metrics of a business. Because standard corporate reports generally access transactional systems directly, and because these reports are tied closely to people&#39;s job responsibilities, these reports contain the numbers and data with which everyone is familiar. Perhaps more significantly, then, data in report instances can be used to establish simple, personalized, trending and correlation analyses for a wide range of users who need to read past reports. Currently, one must manually copy data into spreadsheets in order to examine trends or to perform a tracking analysis. The consolidated data mart  1602  obviates this manual operation. The consolidated data mart  1602  formats data from individual report instances in such a manner that the data can be consumed by other applications or tools that could not have otherwise utilized the data from individual report instances as a data source.  
      It is theoretically possible to expose report instances directly as a database source (e.g., an OLEDB data source) in order to extract historical data. However, this approach would be severely restricted by performance and scalability issues that could not be overcome with currently available technologies. The embodiment of  FIG. 16  not only makes it possible to rapidly and efficiently facilitate consolidated reporting, it also allows a user to drill through to a particular report instance to retrieve detail information.  
      The current embodiment allows report instances to be efficiently targeted as a data source for data mart construction. This embodiment is scalable and has high performance. This embodiment of the invention also provides for incremental loading. Users can schedule reports and pick up the delta (i.e., the changes) between instances to execute trending and correlation analyses on scheduled report instances. In one embodiment, the system maintains metadata to identify where the report and report data originates.  
      The consolidated data mart generator may be configured in any number of ways. The following description relates to one embodiment of the consolidated data mart generator  1600  that has been commercialized by Business Objects Americas of San Jose, Calif. The consolidated data mart generator  1600  may be implemented with the Crystal Data Adapter™, which is a plug-in to the Business Objects Data Integrator Adapter™ software developer kit sold by Business Objects Americas. The Crystal Data Adapter browses a report repository (e.g., a Crystal Enterprise system) and selects reports for which grouping schema can be imported. Internally, the Crystal Data Adapter uses the Java Crystal Enterprise™, Java Business Objects Enterprise™ and/or Java Enterprise Report Object Model™ software developer kits, all sold by Business Objects Americas, to find available reports and to create metadata on their grouping structure.  
      Executable instructions associated with the consolidated data mart generator  1600  query the Data Integrator repository to obtain a list of available Crystal Data Adapter data stores (and the imported report metadata). A list of available target data stores (e.g., data marts) to which summarized instance data can be written is then queried. Report instance data is then read and written (according to the grouping schema) to a specified data mart.  
      The consolidated data mart generator  1600  may also be configured to produce a semantic layer  1604 . For example, the consolidated data mart generator  1600  may be configured to generate a Business Objects Universe on top of the target data mart. This may be implemented using the Business Objects Designer software developer kit. The universe is aware of the aggregation structure of the report summary fields in the data mart. The universe also contains dynamically generated hyperlinks, which link each summary value back to the original report instance (at the exact location in the report from which the summary value was extracted). These hyperlinks are also aggregate aware and dynamically link to the summary level being viewed in the report.  
      The operation of the consolidated data mart generator  1600  is more fully appreciated with the following example.  FIG. 17  illustrates a set of corporate documents that a user may access. The corporate documents include a “TESales” report  1700 . Note that there is a link  1702  entitled “History” associated with the report. This indicates that the report has historical instances that can be accessed through a drill down operation. As previously discussed, a report instance is a snapshot in time of the operational data from which it is derived. Most companies run their reports on a scheduled basis, such as every night, week, or month. Clicking on the history link  1702  results in a display of the historical instances of the report, as shown in  FIG. 18 . Observe in  FIG. 18  that a report was generated on the first day of each month. A user may select a report instance to view the operational (sales) data captured on a specified date.  FIG. 19  illustrates one such report, corresponding to the Aug. 1, 2003 report selected from  FIG. 18 .  
      A consolidated data mart  1602  may be formed through the following operations. A consolidated data mart generator  1600  may be invoked, as shown in  FIG. 20 .  FIG. 20  illustrates a consolidated data mart generator  1600  in the form of the previously discussed, commercially available, Data Integrator. A source of reports, in this case, “Crystal Source”  2000  is specified.  
       FIG. 21  illustrates information associated with the specified source of reports. In this example the TESales” report is selected to be integrated or imported into a data mart, as shown in  FIG. 21 .  
       FIG. 22  illustrates the selection of a module of the consolidated data mart generator  1600 . In particular, the figure illustrates the selection of a data mart accelerator from a tools drop down window, which results in the GUI of  FIG. 23 . The “TESales” report is once again selected, as shown in  FIG. 23 .  FIG. 23  illustrates that there are four aggregate tables (Grand Totals, Product Division, Business Unit, Category) associated with the report.  
      As previously discussed, the consolidated data generator  1600  may be configured to produce a semantic layer  1604 .  FIG. 24  illustrates one technique for generating an aggregate aware universe. In particular,  FIG. 24  illustrates the selection of a field  2400  that generates a universe based on a data flow target.  
       FIG. 25  illustrates the output produced after the data mart accelerator of  FIG. 22  is executed.  FIG. 25  illustrates a repository  2500  with a “JOB_TESALES”. Panel  2502  illustrates a series of tables forming a data mart formed in response to the foregoing processing.  FIG. 26  illustrates that data from the TESales report instances have been moved into several target tables.  
       FIG. 27  illustrates the TESales universe.  FIG. 27  illustrates tables containing report instance data including date data. The date information is important in helping end users perform trending analyses.  
      A consolidated report generator  1626  may then be invoked to create reports from the consolidated data mart.  FIG. 28  illustrates a TESales trend report showing unit sales trends for the last 28 months. Entries  2800  of  FIG. 28  are enlarged in  FIG. 29 . Each of these entries may be selected to invoke the report instance corresponding to the selected data.  FIG. 30  illustrates the underlying Aug. 1, 2002 sales report selected in  FIG. 29 .  
      An embodiment of the present invention relates to a computer storage product with a computer-readable medium having computer code thereon for performing various computer-implemented operations. The media and computer code may be those specially designed and constructed for the purposes of the present invention, or they may be of the kind well known and available to those having skill in the computer software arts. 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 and holographic devices; magneto-optical media such as floptical disks; and hardware devices that are specially configured to store and execute program code, such as application-specific integrated circuits (“ASICs”), programmable logic devices (“PLDs”) and ROM and RAM devices. Examples of computer code 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 hardwired circuitry in place of, or in combination with, machine-executable software instructions.  
      The foregoing description, for purposes of explanation, used specific nomenclature to provide a thorough understanding of the invention. However, it will be apparent to one skilled in the art that specific details are not required in order to practice the invention. Thus, the foregoing descriptions of specific embodiments of the invention are presented for purposes of illustration and description. They are not intended to be exhaustive or to limit the invention to the precise forms disclosed; obviously, many modifications and variations are possible in view of the above teachings. The embodiments were chosen and described in order to best explain the principles of the invention and its practical applications, they thereby enable others skilled in the art to best utilize the invention and various embodiments with various modifications as are suited to the particular use contemplated. It is intended that the following claims and their equivalents define the scope of the invention.