System for aggregating data and a method for providing the same

• US 8,825,593 B2
• Filed: 03/26/2010
• Issued: 09/02/2014
• Est. Priority Date: 03/31/2009
• Status: Active Grant

• Current Assignee: Trapeze Software Inc.
• Original Assignee: Trapeze Software ULC (Constellation Software Incorporated)
• Inventors: Dodds, Matthew, Paul, Chacko Kattithara
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): VU, THONG H
• Application Number:
  US12/748,143
• Publication Number:
  US 20100250485A1
• Time in Patent Office: 1,621 Days
• Field of Search: 707/101, 707/4, 707/10, 707/3, 707/102
• US Class Current: 707/602
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A system for aggregating data from a plurality of operational databases, and a method for providing the same, are provided. The system includes a data store storing a collection of pre-defined extraction scripts. The extraction scripts identify data available for extraction from a plurality of operational database products, and including parameters for specifying which of the data to extract. A data warehouse server executes the extraction scripts to extract, merge, transform and load the specified data from the plurality of operational databases into a multidimensional database.

Claims

1. A system for aggregating data from a plurality of operational databases, comprising:

a data store storing a collection of pre-defined extraction scripts, each of said pre-defined extraction scripts identifying data available for extraction from a separate one of a plurality of operational database products, said extraction scripts including parameters for specifying which of said data to extract; and

a data warehouse server coupled to a set of operational databases corresponding to a first subset of said operational database products, said data warehouse server retrieving a database schema from each of the operational databases in the first subset of operational database products and, if the pre-defined extraction scripts and the database schemas are the same, executing a second subset of said pre-defined extraction scripts corresponding to said first subset of operational database products to extract, merge, transform and load said specified data from said operational databases into a multidimensional database.

2. The system of claim 1, wherein said extraction scripts identify fields and tables where said data is stored in said operational database products and the extraction scripts and the database schemas are the same if they identify the same fields and tables.

3. The system of claim 2 wherein, if the pre-defined extraction scripts and the database schemas are not the same, the extraction script is modified to identify the same fields and tables as the database schema, and all data from said operational databases is extracted, merged, transformed and loaded into the multidimensional database.

4. The system of claim 3 further comprising a system configuration utility providing an interface for modifying said extraction scripts and said extraction management script.

5. The system of claim 4 wherein, if the pre-defined extraction scripts and the database schemas are not the same, the system configuration utility creates a new extraction script and presents a user the interface to select the fields and tables to extract.

6. The system of claim 5 further comprising an extraction script repository, wherein the extraction script that is replaced by the new extraction script is maintained in the extraction script repository.

7. The system of claim 5 wherein the extraction management script is updated with the new extraction script.

8. The system of claim 2, wherein said data store further includes an extraction management script identifying said second subset of said extraction scripts for execution, and subroutines for execution to ensure that the appropriate tables and fields are present or created in the multidimensional database, by the data warehouse server.

9. A method for providing a system for aggregating data from a plurality of operational databases via a data warehouse server, comprising:

providing a collection of pre-defined extraction scripts, each of said pre-defined extraction scripts identifying data available for extraction from a separate one of a plurality of operational database products, said extraction scripts including parameters for specifying which of said data to extract;

retrieving a database schema from each of plurality of operational database products and, if the pre-defined extraction scripts and the database schemas are the same;

executing a first subset of said pre-defined extraction scripts corresponding to a second subset of said operational database products, said second subset of said operational database products corresponding to a set of operational databases coupled to a data warehouse server, to extract, merge, transform and load said specified data from said operational databases into a multidimensional database.

10. The method of claim 9, further comprising:

identifying, in said extraction scripts, where said data is stored in said operational database products.

11. The method of claim 9, further comprising:

providing an extraction management script identifying said first subset of said extraction scripts for execution.

12. The method of claim 11, wherein said extraction management script identifies said second subset of said extraction scripts using parameters.

13. The method of claim 11, wherein said extraction management script includes address information for identifying the location of said operational databases.

14. The method of claim 11, wherein said extraction management script includes login credentials for connecting to said operational databases.

15. The method of claim 9, further comprising:

providing an interface for modifying said extraction scripts and said extraction management script.

16. A system for aggregating data from a plurality of operational databases, comprising:

a data store storing a collection of pre-defined scripts identifying configuration information for said system, each of said scripts corresponding to one of a plurality of operational database products, said scripts including parameters for modifying said configuration information; and

a data warehouse server coupled to a set of operational databases corresponding to a first subset of said operational database products, said data warehouse server retrieving a database schema from each of the operational databases in the first subset of operational database products and, if the pre-defined extraction scripts and the database schemas are the same, executing a second subset of said scripts corresponding to said first subset of said operational database products to extract, merge, transform and load data from said operational databases in accordance with said configuration information to generate a multidimensional database.

17. The system of claim 16, wherein said configuration information includes extraction information identifying data to be extracted and its location in said operational databases, merge information specifying merges to be performed on said extracted data, transformation information specifying transformations to be performed on said extracted data, and loading information specifying how said transformed data is to be loaded.

18. The system of claim 17, wherein said scripts comprise:

at least one extraction script specifying said extraction information; and

at least one transformation script specifying said merge information, said transformation information and said loading information.

19. The system of claim 18, wherein said at least one extraction script is XML-based.

20. The system of claim 18, wherein said merge information in said at least one transformation script specifies an order in which said data extracted from said operational databases is merged.

21. The system of claim 20, wherein said at least one transformation script is an SSIS script.

22. The system of claim 18, further comprising:

an extraction management script identifying a third subset of said extraction scripts for execution, said third subset of said extraction scripts corresponding to said first subset of said operational database products.

23. The system of claim 22, wherein said extraction management script includes address information for identifying the location of said operational databases.

24. The system of claim 22, wherein said extraction management script includes login credentials for connecting to said operational databases.

25. The system of claim 16, further comprising:

a system configuration utility providing an interface for modifying said parameters in said scripts.

26. A method for aggregating data from a plurality of operational databases via a data warehouse server, comprising:

providing a collection of pre-defined scripts identifying configuration information for said system, each of said pre-defined scripts corresponding to one of a plurality of operational database products, said scripts including parameters for modifying said configuration information;

retrieving a database schema from each of plurality of operational database products;

and, if the pre-defined extraction scripts and the database schemas are the same, then executing a first subset of said scripts corresponding to a second subset of said operational database products, said second subset of said operational database products corresponding to a set of operational databases coupled to a data warehouse server, to extract, merge, transform and load data from said operational databases in accordance with said configuration information to generate a multidimensional database.

27. The system of claim 26, wherein said configuration information includes extraction information identifying data to be extracted and its location in said operational database products, merge information specifying merges to be performed on said extracted data, transformation information specifying transformations to be performed on said extracted data, and loading information specifying how the transformation data is to be loaded.

28. The method of claim 27, wherein said providing comprises:

providing at least one extraction script specifying said extraction information; and

providing at least one transformation script specifying said transformation information.

29. The method of claim 28, further comprising:

providing at least one extraction management script identifying a third subset of said extraction scripts for execution, said third subset of said extraction scripts corresponding to said second subset of said operational database products.

30. The method of claim 27, further comprising:

providing an interface for viewing and modifying said extraction, merge, transformation and loading information in said scripts.