Business information warehouse toolkit and language for warehousing simplification and automation

• US 8,479,158 B2
• Filed: 06/05/2008
• Issued: 07/02/2013
• Est. Priority Date: 06/07/2007
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Chen, Ying, He, Bin, Lelescu, Ana, Rhodes, James J., Wang, Rui
• Primary Examiner(s): Vu, Tuan A
• Application Number:
  US12/134,011
• Publication Number:
  US 20080306987A1
• Time in Patent Office: 1,853 Days
• Field of Search: 717/106, 717/143, 717/175, 717/117, 709/206, 719/328, 702/84, 714/47.3, 358/403, 707/999.102, 707/999.104, 707/999.107, 707/999.204, 707/4, 705/2
• US Class Current: 717/117
• 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 in memory of a hardware computer processor, comprising:

accepting input from a user at a front-end graphical user interface;

generating a plurality of commands employing declarative statements in an information warehouse language and performing loading or copying of data using;

information warehouse (IW) level commands that when implemented provide support for both structured and unstructured data,

indexing engine level commands configured to perform tasks specified by the commands from a user interface,

source mapping commands configured to perform at least one of;

a data format transformation, source-to-target complex schema matching, and complex data value mapping,

data loading commands configured to perform at least one of;

a data format transformation, source-to-target complex schema matching, and complex data value mapping, and

dimension level commands that when implemented provide support for both structured and unstructured data and include at least one of;

creating a dimension, altering the dimension, and dropping the dimension; and

constructing an information warehouse based on a script comprising the IW level commands and the dimension level commands according to the language specification for information warehouse construction; and

providing error correction for data loaded from a source file into the information warehouse using;

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 the structured and the unstructured data are hidden from a user of the abort command, such 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the load command;

an undo command, operating with regard to the 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the undo command; and

a redo command, operating with regard to the source file, that uses the 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the redo command.

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. A method for data loading in an information warehouse stored in memory of a hardware computer processor ,comprising:

accepting input from a user at a front-end graphical user interface;

generating a plurality of commands employing declarative statements in an information warehouse language and performing loading or copying of the data using;

information warehouse (IW) level commands that when implemented provide support for both structured and unstructured data,

indexing engine level commands,

dimension level commands that when implemented include at least one of;

creating a dimension, altering a dimension, and dropping the dimension, and provide support for both structured and unstructured data,

source mapping commands configured to perform at least one of;

performing a data format transformation, performing source-to-target complex schema matching, and performing complex data value matching, and

data loading commands configured to perform at least one of;

performing a data format transformation, performing source-to-target complex schema matching, and performing complex data value matching;

loading data into an information warehouse based on a script including both the source mapping commands and the data loading commands according to the language specification for data loading; and

providing error correction for data loaded from a source file into the information warehouse according to at least one of;

the information warehouse level commands, the dimension level commands, the source mapping commands and the data loading commands,

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 the structured and the unstructured data are hidden from a user of the abort command, such 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the load command;

an undo command, operating with regard to the 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the undo command; and

a redo command, operating with regard to the source file, that uses the 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the redo command.

6. The method of claim 5, 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.

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

8. A method for data maintenance of an information warehouse stored in memory of a hardware computer processor, comprising:

providing a language specification for information warehouse (IW) maintenance in the information warehouse;

accepting input from a user at a front-end graphical user interface;

generating a plurality of commands employing declarative statements in an information warehouse language and performing loading or copying by;

implementing indexing engine level commands configured to perform tasks specified by the commands from an interface;

implementing dimension level commands that when implemented provide support for both structured and unstructured data and include at least one of;

creating a dimension, altering the dimension, and dropping the dimension;

implementing IW level commands that when implemented provide support for both structured and unstructured data;

implementing source mapping commands configured to perform at least one of;

a data transformation, source-to-target complex schema matching, and complex data value mapping; and

implementing data loading commands configured to perform at least one of;

a data format transformation, source-to-target complex schema matching, and complex data value mapping; and

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

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, such 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the load command,

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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the undo command, and

a redo command, operating with regard to the source file, that uses the 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the redo command.

9. The method of claim 8, 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.

10. An information warehouse system comprising:

a hardware computer processor including memory;

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

an information warehouse language processor stored on the memory that receives a plurality of user interface commands from the user interface and performs loading using at least one of;

system level commands;

database level commands that when implemented provide support for both structured and unstructured data;

indexing engine level commands configured to perform tasks specified by the commands from the interface;

source mapping commands configured to perform at least one of;

a data transformation, source-to-target complex schema matching, and complex data value mapping;

data loading commands configured to perform at least one of;

a data format transformation, source-to-target complex schema matching, and complex data value mapping; and

dimension level commands that when implemented provide support for both structured and unstructured data and include at least one of;

creating a dimension, altering the dimension, and dropping the dimension;

a warehouse builder that;

receives the at least one of;

the system level commands, the database level commands, and the indexing engine level commands;

performs tasks specified by the at least one of system level, database level, and indexing engine level commands;

builds an information warehouse; and

provides error correction for data loaded from a source file into the information warehouse using;

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 the structured and the unstructured data are hidden from a user of the abort command, such 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the load command;

an undo command, operating with regard to the 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; and

a redo command, operating with regard to the source file, that uses the 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the redo command.

11. The system of claim 10, further comprising:

a data loader that receives the at least one of;

system level, database level, and indexing engine level commands from the information warehouse language processor and performs tasks specified by the at least one of;

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 at least one of;

user interface, the system level, database level, and indexing engine level commands being hidden from the user.

12. The system of claim 10, further comprising:

a warehouse maintainer that receives at least one of;

the system level, database level, and indexing engine level commands from the information warehouse language processor and performs tasks specified by the at least one of;

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 at least one of;

system level, database level, and indexing engine level commands being hidden from the user.

13. The system of claim 10, further comprising:

a warehouse maintainer that receives the at least one of;

system level, database level, and indexing engine level commands from the information warehouse language processor and performs tasks specified by the at least one of;

system level, database level, and indexing engine level commands.

14. The system of claim 10, 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.

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

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

17. A computer program product for use with an information warehouse, the computer program product comprising a non-transitory computer readable medium including a computer executable program, wherein the computer executable program when executed on a computer causes the computer to:

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

parse the commands from the script;

generate data description language commands from the parsed commands including information warehouse level commands that when implemented provide support for both structured and unstructured data;

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

perform loading or copying of the structured and unstructured data, using;

indexing engine level commands configured to perform tasks specified by commands from an interface;

source mapping commands configured to perform at least one of;

a data format transformation, source-to-target complex schema matching, and complex data value mapping;

data loading commands configured to perform at least one of;

a data format transformation, source-to-target complex schema matching, and complex data value mapping;

dimension level commands that when implemented include at least one of;

creating a dimension, altering the dimension, and dropping the dimension, and provide support for both structured and unstructured data; and

provide error correction for data loaded from a source file into the information warehouse using;

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 the structured and the unstructured data are hidden from a user of the abort command, such 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the load command;

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; and

a redo command, operating with regard to the source file, that uses the 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.

18. The computer program product of claim 17, 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.

19. The computer program product of claim 17, wherein:

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

20. The computer program product of claim 17, 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.

21. The computer program product of claim 17, wherein:

the commands of the declarative information warehouse language include dimension level commands for at least one of;

creating a dimension, altering the dimension, and dropping the dimension.

22. A computer program product for use with an information warehouse, the computer program product comprising a non-transitory computer readable medium including a computer executable program, wherein the computer readable program when executed on a computer causes the computer to:

accept commands of an information warehouse language using declarative statements 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;

load the transformed data into information warehouse tables, file system locations according to the parsed commands;

perform loading or copying of the transformed data, using;

information warehouse level commands that provide support for both structured and unstructured data,

indexing engine level commands configured to perform tasks specified by the commands from an interface;

source mapping commands configured to perform at least one of;

a data format transformation, source-to-target complex schema matching, and complex data value mapping;

data loading commands configured to perform at least one of;

a data format transformation, source-to-target complex schema matching, and complex data value mapping; and

dimension level commands that include at least one of;

creating a dimension, altering the dimension, and dropping the dimension and provide support for both structured and unstructured data; and

provide error correction for data loaded from a source file into the information warehouse using;

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 the structured and the unstructured data are hidden from a user of the abort command, such 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the load command;

an undo command, operating with regard to the 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the undo command; and

a redo command, operating with regard to the source file, that uses the 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 the structured and the unstructured data by performing system level tasks that are hidden from a user of the redo command.

23. The computer program product of claim 22, 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.

24. The computer program product of claim 22, 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.

25. The computer program product of claim 22, wherein:

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

26. The computer program product of claim 22, 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.