BUSINESS INFORMATION WAREHOUSE TOOLKIT AND LANGUAGE FOR WAREHOUSING SIMPLIFICATION AND AUTOMATION

• US 20080306987A1
• Filed: 06/05/2008
• Published: 12/11/2008
• Est. Priority Date: 06/07/2007
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Chen, Ying, Wang, Rui, He, Bin, Lelescu, Ana, Rhodes, James J.
• Granted Patent: US 8,479,158 B2
• Time in Patent Office: Days
• US Class Current: 707/102
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06Q 30/02 Marketing; Price estimation or determination; Fundraising

Abstract

A method for use with an information (or data) warehouse comprises managing the information warehouse with instructions in a declarative language. The instructions specify information warehouse-level tasks to be done without specifying certain details of how the tasks are to be implemented, for example, using databases and text indexers. The details are hidden from the user and include, for example, in an information warehouse having a FACT table that joins two or more dimension tables, details of database level operations when structured data are being handled, including database command line utilities, database drivers, and structured query language (SQL) statements; and details of text-indexing engines when unstructured data are being handled. The information warehouse is managed in a dynamic way in which different tasks—such as data loading tasks and information warehouse construction tasks—may be interleaved (i.e., there is no particular order in which the different tasks must be completed).

Claims

1. A method for information warehouse construction, comprising:

providing a language specification for information warehouse construction, the language specification having information warehouse (IW) level commands and dimension level commands; and

constructing an information warehouse based on a script comprising at least one of the IW level commands and the dimension level commands according to the language specification for information warehouse construction.

2. The method of claim 1, wherein the IW level commands define a language syntax at the granularity of the information warehouse, wherein system and database level tasks required to perform the commands are transparent to a user of the IW level commands.

3. The method of claim 1, wherein the dimension level commands define a language syntax at the granularity of facts and dimensions, wherein system and database level tasks required to perform the commands are transparent to a user of the dimension level commands.

4. The method of claim 1, wherein the IW level commands include commands for creating the information warehouse, using the information warehouse, and dropping the information warehouse.

5. The method of claim 1, wherein dimension level commands include creating a dimension, altering the dimension, and dropping the dimension.

6. The method of claim 1, wherein implementing the IW level commands and the dimension level commands includes implementing support for both structured and unstructured data.

7. A method for data loading in an information warehouse, comprising:

providing a language specification for data loading in an information warehouse, the language specification having source mapping commands and data loading commands; and

loading data into an information warehouse based on a script comprising at least one of the source mapping commands and data loading commands according to the language specification for data loading.

8. The method of claim 7, wherein the source mapping commands and data loading commands define a language syntax wherein system and database level tasks required to perform the commands are transparent to a user of the source mapping commands and data loading commands.

9. The method of claim 7 wherein implementing the source mapping commands and data loading commands includes performing a data extraction using an xpath to locate a desired data field.

10. The method of claim 7 wherein implementing the source mapping commands and data loading commands includes performing a data format transformation wherein details of database level operations for structured data and details of text-indexing engine operations for unstructured data are hidden from a user of the source mapping commands and data loading commands.

11. The method of claim 7 wherein implementing the source mapping commands and data loading commands includes performing source-to-target complex schema matching wherein details of database level operations for structured data and details of text-indexing engine operations for unstructured data are hidden from a user of the source mapping commands and data loading commands.

12. The method of claim 7 wherein implementing the source mapping commands and data loading commands includes performing complex data value mapping wherein details of database level operations for structured data and details of text-indexing engine operations for unstructured data are hidden from a user of the source mapping commands and data loading commands.

13. A method for data maintenance of an information warehouse, comprising:

providing a language specification for information warehouse (IW) maintenance in an information warehouse, the language specification having failure recovery commands and error correction commands; and

performing an IW maintenance operation on the information warehouse based on a script comprising at least one of the failure recovery commands and error correction commands according to the language specification for IW maintenance.

14. The method of claim 13, wherein the failure recovery commands and error correction commands define a language syntax wherein system and database level tasks required to perform the commands are transparent to a user of the failure recovery commands and error correction commands.

15. The method of claim 13, wherein the failure recovery commands include an abort command that uses a pending log to return the information warehouse to a consistent break point prior to a data load into the information warehouse so that operational details of maintaining data integrity for both structured and unstructured data are hidden from a user of the abort command.

16. The method of claim 13, wherein the failure recovery commands are implemented so that a load command automatically resumes loading data into the information warehouse from a last consistent breakpoint of a data load, while maintaining data integrity for both structured and unstructured data by performing system level tasks that are hidden from a user of the load command.

17. The method of claim 13, wherein the error correction commands include an undo command, operating with regard to a source file, that uses a checkpoint log to automatically return the information warehouse to a state of the information warehouse prior to loading of the source file, while maintaining data integrity for both structured and unstructured data by performing system level tasks that are hidden from a user of the undo command.

18. The method of claim 13, wherein the error correction commands include a redo command, operating with regard to a source file, that uses a checkpoint log to automatically return the information warehouse to a state of the information warehouse after loading of the source file, wherein the loading of the source file has previously been undone, and while maintaining data integrity for both structured and unstructured data by performing system level tasks that are hidden from a user of the redo command.

19. An information warehouse system comprising:

a front-end graphical user interface that accepts inputs from a user and generates commands in a declarative information warehouse language;

an information warehouse language processor that receives the commands from the user interface and provides system level, database level, and indexing engine level commands for performing tasks specified by the commands from the interface; and

a warehouse builder that receives the system level, database level, and indexing engine level commands from the information warehouse language processor and performs tasks specified by the system level, database level, and indexing engine level commands to build an information warehouse according to the commands from the user interface, the system level, database level, and indexing engine level commands being hidden from the user.

20. The system of claim 19, further comprising:

a data loader that receives the system level, database level, and indexing engine level commands from the information warehouse language processor and performs tasks specified by the system level, database level, and indexing engine level commands to load data from a source file into the information warehouse according to the commands from the user interface, the system level, database level, and indexing engine level commands being hidden from the user.

21. The system of claim 19, further comprising:

a warehouse maintainer that receives the system level, database level, and indexing engine level commands from the information warehouse language processor and performs tasks specified by the system level, database level, and indexing engine level commands to provide failure recovery for a failed data load from a source file into the information warehouse according to the commands from the user interface, the system level, database level, and indexing engine level commands being hidden from the user.

22. The system of claim 19, further comprising:

a warehouse maintainer that receives the system level, database level, and indexing engine level commands from the information warehouse language processor and performs tasks specified by the system level, database level, and indexing engine level commands to provide error correction for data loaded from a source file into the information warehouse according to the commands from the user interface, the system level, database level, and indexing engine level commands being hidden from the user.

23. The system of claim 19, wherein the front-end graphical user interface further comprises:

a warehouse design manager that, based on the inputs from the user, generates declarative information warehouse language commands for building the information warehouse.

24. The system of claim 19, wherein the front-end graphical user interface further comprises:

a source mapping manager that, based on the inputs from the user, generates declarative information warehouse language commands for schema matching; and

a data loading manager that, based on the inputs from the user, generates declarative information warehouse language commands for data loading.

25. The system of claim 19, wherein the front-end graphical user interface further comprises:

a maintenance manager that, based on the inputs from the user, generates declarative information warehouse language commands for information warehouse failure recovery and error correction.

26. A computer program product for use with an information warehouse, the computer program product comprising a computer useable medium including a computer readable program, wherein the computer readable program when executed on a computer causes the computer to:

accept commands of a declarative information warehouse language provided by a script;

parse the commands from the script;

generate data description language commands from the parsed commands; and

execute the data description language commands to construct an information warehouse.

27. The computer program product of claim 26, wherein the computer readable program when executed on a computer further causes the computer to:

generate, from the parsed commands, database independent designs for tables and indices according to the script;

translate the database independent designs into database-specific data description language statements for a user-selected target database using input from a database knowledge base; and

execute the data description language statements to construct the information warehouse.

28. The computer program product of claim 26, wherein:

the commands of the declarative information warehouse language are generated by a user interface that hides operational details from the user.

29. The computer program product of claim 26, wherein:

the commands of the declarative information warehouse language include information warehouse level commands for creating the information warehouse, using the information warehouse, and dropping the information warehouse.

30. The computer program product of claim 26, wherein:

the commands of the declarative information warehouse language include dimension level commands for creating a dimension, altering the dimension, and dropping the dimension.

32. The computer program product of claim 30, wherein:

the extract process includes;

using input from a database and full text indexer knowledge base; and

locating data in an XML source file using an xpath; and

the transform process includes;

using input from the database and full text indexer knowledge base; and

using a matching profile specified by the script.

33. The computer program product of claim 30, wherein:

the commands of the declarative information warehouse language in the script are generated by a user interface that hides operational details from the user.

34. The computer program product of claim 30, wherein:

the commands of the declarative information warehouse language in the script include a source mapping command for creating a matching profile.

35. The computer program product of claim 30, wherein:

the commands of the declarative information warehouse language in the script include a data loading command for loading a source file using a matching profile.

31. A computer program product for use with an information warehouse, the computer program product comprising a computer useable medium including a computer readable program, wherein the computer readable program when executed on a computer causes the computer to:

accept commands of a declarative information warehouse language provided by a script;

parse the commands from the script;

extract desired data from a source file according to the parsed commands;

transform the extracted data into a target format according to the parsed commands; and

load the transformed data into information warehouse tables and file system locations according to the parsed commands.