System for Aggregating Data and a Method for Providing the Same

• US 20100250485A1
• Filed: 03/26/2010
• Published: 09/30/2010
• Est. Priority Date: 03/31/2009
• Status: Active Grant

• Current Assignee: Trapeze Software Inc.
• Original Assignee: Trapeze Software Inc.
• Inventors: DODDS, Matthew, PAUL, Chacko Kattithara
• Granted Patent: US 8,825,593 B2
• Time in Patent Office: Days
• 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 identifying data available for extraction from a plurality of operational database products, said extraction scripts including parameters for specifying which of said data to extract; and

a data warehouse server for executing said extraction scripts to extract, merge, transform and load said specified data from said plurality of 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.

3. The system of claim 2, wherein said data store further includes an extraction management script identifying a subset of said extraction scripts for execution, said subset corresponding to said operational databases.

4. The system of claim 3, wherein said extraction management script identifies said subset of said extraction scripts using parameters.

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

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

7. The system of claim 4, further comprising:

a system configuration utility providing an interface for modifying said extraction scripts and said extraction management script.

8. 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 identifying data available for extraction from a plurality of operational database products, said extraction scripts including parameters for specifying which of said data to extract; and

executing a subset of said extraction scripts to extract, merge, transform and load said specified data from said plurality of operational databases into a multidimensional database.

9. The method of claim 8, further comprising:

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

10. The method of claim 8, further comprising:

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

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

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

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

14. The method of claim 8, further comprising:

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

15. 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, said scripts including parameters for modifying said configuration information; and

a data warehouse server for executing said scripts to extract, merge, transform and load data from said operational databases in accordance with said configuration information to generate a multidimensional database.

16. The system of claim 15, 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.

17. The system of claim 16, 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.

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

19. The system of claim 17, 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.

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

21. The system of claim 17, further comprising:

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

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

23. The system of claim 21, wherein said extraction management script includes login credentials for connecting to said subset of said operational databases.

24. The system of claim 15, further comprising:

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

25. 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, said scripts including parameters for modifying said configuration information; and

executing a subset of said scripts to extract, merge, transform and load data from said operational databases in accordance with said configuration information to generate a multidimensional database.

26. The system of claim 25, wherein said configuration information includes extraction information identifying data to be extracted and its location, 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.

27. The method of claim 26, 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.

28. The method of claim 27, further comprising:

providing at least one extraction management script identifying a subset of said extraction scripts for execution.

29. The method of claim 26, further comprising:

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