Data warehouse programs architecture

• US 6,418,450 B2
• Filed: 01/07/1999
• Issued: 07/09/2002
• Est. Priority Date: 01/26/1998
• Status: Expired due to Term

• Current Assignee: X Corp. (f/k/a Twitter, Inc.) (X Holdings Corp.)
• Original Assignee: International Business Machines Corporation
• Inventors: Daudenarde, Jean-Jacques Paul
• Primary Examiner(s): Corrielus, Jean M.
• Application Number:
  US09/226,557
• Publication Number:
  US 20010049694A1
• Time in Patent Office: 1,279 Days
• Field of Search: 707/104, 707/102, 707/10, 707/201, 707/101, 707/200, 703/22, 717/107, 717/109, 712/109
• US Class Current: 707/602
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

A method, apparatus, and article of manufacture for a data warehouse programs architecture. Data in a database stored on a data storage device is transformed. Initially, a program template is retrieved that contains parameters. A business view is invoked with the program template. Under control of the business view, a transformation program is invoked that transforms source data to generate target data using the parameters provided in the program template.

Claims

1. A method of transforming data in a database stored on a data storage device, comprising:

defining a program template containing default parameter values corresponding to parameters of a pre-existing transformation program;

defining at least one business view having an association with the program template, said business view including scheduling information and a parameter string initialized to the default parameter values;

creating at least one scheduled instance of operation of the transformation program by receiving selective modifications of the parameter string and the scheduling information of the at least one business view from the user; and

invoking the at least one scheduled instance of operation of the transformation program under control of the at least one business view.

2. The method of claim 1, wherein the program template contains tokens, which are replaced by system data at the time the transformation program is invoked.

3. The method of claim 1, wherein the transformation program is a command.

4. The method of claim 1, wherein the transformation program is a utility.

5. The method of claim 1, wherein the business view is invoked based on a predefined schedule.

6. The method of claim 1, wherein the program template comprises information about the transformation program.

7. The method of claim 1, wherein the source is identified in the business view.

8. The method of claim 1, wherein the transformation program is identified in the business view.

9. The method of claim 1, wherein the target is identified in the business view.

10. The method of claim 1, wherein the source is identified in the program template.

11. The method of claim 1, wherein the target is identified in the program template.

12. The method of claim 1, wherein the transformation program is identified in the program template.

13. The method of claim 1, further comprising the step of storing results of the invocation of the transformation program in a history file.

14. The method of transforming data in a database stored on a data storage device as set forth in claim 1, wherein the creating of at least one scheduled instance of operation of the transformation program includes:

displaying the parameter values of the parameter string;

receiving modifications of the displayed parameter values from the user; and

updating the parameter string based on the received selective modifications.

15. The method of transforming data in a database stored on a data storage device as set forth in claim 1, wherein the defining of said program template includes:

defining a program template using a graphical user interface.

16. The method of transforming data in a database stored on a data storage device as set forth in claim 1, wherein:

the defining of said program template containing default parameter values corresponding to parameters of a pre-existing transformation program includes defining at least one default parameter value as a token; and

the invoking of the at least one scheduled instance of operation of the transformation program under control of the at least one business view includes replacing the token with a system data value corresponding to the token.

17. The method of transforming data in a database stored on a data storage device as set forth in claim 1, wherein the defining of said program template containing default parameter values corresponding to parameters of a pre-existing transformation program includes:

defining a program template for a pre-existing transformation program, said pre-existing transformation program being one of an SQL query and a data warehouse program.

18. The method of transforming data in a database stored on a data storage device as set forth in claim 1, wherein the defining of said program template includes:

storing metadata relating to the pre-existing transformation program in the program template.

19. The method of transforming data in a database stored on a data storage device as set forth in claim 18, wherein the storing of metadata relating to the pre-existing transformation program in the program template includes:

storing at least one of a business name, a physical name, an executable file type, a description, and a server agent at which the data warehouse program is installed with a specified physical name.

20. The method of transforming data in a database stored on a data storage device as set forth in claim 1, wherein the defining of said program template containing default parameter values corresponding to parameters of a pre-existing transformation program includes:

defining a program template containing default parameter values specifying default source and target parameter values relating to the pre-existing transformation program.

21. The method of transforming data in a database stored on a data storage device as set forth in claim 1, further comprising:

storing information corresponding to results of the invoking step in a history file.

22. The method of transforming data in a database stored on a data storage device as set forth in claim 1, wherein:

the defining of at least one business view includes defining at least a first business view and a second business view;

the creating of at least one scheduled instance of operation of the transformation program includes selectively scheduling the first business view and the second business view to run concurrently; and

the invoking of the at least one scheduled instance of operation of the transformation program includes invoking a first scheduled instance of operation of the transformation under control of the first business view and invoking a second scheduled instance of operation of the transformation under control of the second business view, said first and second invoked scheduled instances running concurrently.

23. The method of transforming data in a database stored on a data storage device as set forth in claim 1, wherein the invoking of the at least one scheduled instance of operation of the transformation program under control of the at least one business view includes:

retrieving a copy of the program template;

passing the parameter string of the business view to the retrieved copy of the program template and thereby selectively modifying the default parameters of the retrieved copy of the program template; and

starting an instance of operation of the transformation program, said instance receiving the selectively modified default parameters of the retrieved copy of the program template.

24. An apparatus for transforming data in a database, comprising:

a computer having a memory and a data storage device coupled thereto, wherein the data storage device stores the database;

one or more computer programs, performed by the computer, retrieving a program template containing parameters corresponding to parameters of a pre-existing transformation program, invoking a business view with the program template, the business view including scheduling information and a parameter string initialized to default parameter values, and, under control of the business view, based on said scheduling information and said parameter string, invoking said transformation program that transforms source data to generate target data using the parameters provided in the program template.

25. The apparatus of claim 24, wherein the program template contains tokens which are replaced by system data at the time the transformation program is invoked.

26. The apparatus of claim 24, wherein the transformation program is a command.

27. The apparatus of claim 24, wherein the transformation program is a utility.

28. The apparatus of claim 24, wherein the business view is invoked based on a predefined schedule.

29. The apparatus of claim 24, wherein the program template comprises information about the transformation program.

30. The apparatus of claim 24, wherein the source is identified in the business view.

31. The apparatus of claim 24, wherein the transformation program is identified in the business view.

32. The apparatus of claim 24, wherein the target is identified in the business view.

33. The apparatus of claim 24, wherein the source file is identified in the program template.

34. The apparatus of claim 24, wherein the target file is identified in the program template.

35. The apparatus of claim 24, wherein the transformation is identified in the program template.

36. The apparatus of claim 24, further comprising means for storing results of the invocation of the transformation program in a history file.

37. An article of manufacture comprising a program storage medium readable by a computer and embodying one or more instructions executable by the computer to perform method steps for transforming data in a database operation on a database stored on a data storage device, the method comprising the steps of:

retrieving a program template containing parameters corresponding to parameters of a pre-existing transformation program;

invoking a business view with the program template, the business view including scheduling information and a parameter string initialized to default parameter values; and

under control of the business view based on said scheduling information and said parameter string, invoking said transformation program that transforms source data to generate target data using the parameters provided in the program template.

38. The article of manufacture of claim 37, wherein the program template contains tokens which are replaced by system data at the time the transformation program is invoked.

39. The article of manufacture of claim 37, wherein the transformation program is a command.

40. The article of manufacture of claim 37, wherein the transformation program is a utility.

41. The article of manufacture of claim 37, wherein the business view is invoked based on a predefined schedule.

42. The article of manufacture of claim 37, wherein the program template comprises information about the transformation program.

43. The article of manufacture of claim 37, wherein the source is identified in the business view.

44. The article of manufacture of claim 37, wherein the transformation program is identified in the business view.

45. The article of manufacture of claim 37, wherein the target is identified in the business view.

46. The article of manufacture of claim 37, wherein the source file is identified in the program template.

47. The article of manufacture of claim 37, wherein the target file is identified in the program template.

48. The article of manufacture of claim 37, wherein the transformation is identified in the program template.

49. The article of manufacture of claim 37, further comprising storing results of the invocation of the transformation program in a history file.

50. A system for creating and scheduling data transformation operations using an associated transformation program in a data warehouse, comprising:

a template corresponding to the associated transformation program, said template including a set of default parameter values for parameters of the transformation program;

a business view in operative communication with the template and including a parameter string comprising values for parameters of the transformation program; and

means for invoking an instance of operation of the transformation program under the control of the business view.

51. The system as set forth in claim 50, further comprising:

a business view generator having;

means for initializing the parameter string to comprise the default parameter values of the template, and

means for selective user modification of the parameter string of the business view.

52. The system as set forth in claim 51, wherein the means for selective user modification of the parameter string of the business view includes:

an interactive graphical user interface by which the user selectively modifies the parameter values contained in the parameter string.

53. The system as set forth in claim 50, wherein the business view further includes:

scheduling information whereby the means for invoking an instance of operation of the transformation program under the control of the business view invokes the instance at one or more times according to the scheduling information.

54. The system as set forth in claim 50, further comprising:

an operations file that stores information on the success or failure of the invoked instance of operation of the transformation program.

55. An apparatus for transforming data in a database, comprising:

a computer having a memory and a data storage device coupled thereto, wherein the data storage device stores the database; and

one or more computer programs, performed by the computer, for performing a method comprising;

defining a program template containing default parameter values corresponding to parameters of a pre-existing transformation program,

defining at least one business view having an association with the program template, said business view including scheduling information and a parameter string initialized to the default parameter values,

creating at least one scheduled instance of operation of the transformation program by receiving selective modifications of the parameter string and the scheduling information of the at least one business view from the user, and

invoking the at least one scheduled instance of operation of the transformation program under control of the at least one business view.

56. The apparatus as set forth in claim 55, wherein the creating of at least one scheduled instance of operation of the transformation program includes:

displaying the parameter values of the parameter string;

receiving selective modifications of the displayed parameter values from the user; and

updating the parameter string based on the received selective modifications.

57. The apparatus as set forth in claim 55, wherein the defining of said program template includes:

defining a program template using a graphical user interface.

58. The apparatus as set forth in claim 55, wherein:

the defining of said program template containing default parameter values corresponding to parameters of a pre-existing transformation program includes defining at least one default parameter value as a token; and

the invoking of the at least one scheduled instance of operation of the transformation program under control of the at least one business view includes replacing the token with a system data value corresponding to the token.

59. The apparatus as set forth in claim 55, wherein the defining of said program template containing default parameter values corresponding to parameters of a pre-existing transformation program includes:

defining a program template for a pre-existing transformation program, said pre-existing transformation program being one of an SQL query and a data warehouse program.

60. The apparatus as set forth in claim 55, wherein the defining of said program template includes:

storing metadata relating to the pre-existing transformation program in the program template.

61. The apparatus as set forth in claim 60, wherein the storing of metadata relating to the pre-existing transformation program in the program template includes:

storing at least one of a business name, a physical name, an executable file type, a description, and a server agent at which the data warehouse program is installed with a specified physical name.

62. The apparatus as set forth in claim 55, wherein the defining of a program template containing default parameter values corresponding to parameters of said pre-existing transformation program includes:

defining a program template containing default parameter values specifying default source and target parameter values relating to the pre-existing transformation program.

63. The apparatus as set forth in claim 55, further comprising:

storing information corresponding to results of the invoking step in a history file.

64. The apparatus as set forth in claim 55, wherein:

the defining of at least one business view includes defining at least a first business view and a second business view;

the creating of at least one scheduled instance of operation of the transformation program includes selectively scheduling the first business view and the second business view to run concurrently; and

the invoking of the at least one scheduled instance of operation of the transformation program includes invoking a first scheduled instance of operation of the transformation under control of the first business view and invoking a second scheduled instance of operation of the transformation under control of the second business view, said first and second invoked scheduled instances running concurrently.

65. The apparatus as set forth in claim 55, wherein the invoking of the at least one scheduled instance of operation of the transformation program under control of the at least one business view includes:

retrieving a copy of the program template;

passing the parameter string of the business view to the retrieved copy of the program template and thereby selectively modifying the default parameters of the retrieved copy of the program template; and

starting an instance of operation of the transformation program, said instance receiving the selectively modified default parameters of the retrieved copy of the program template.

66. An article of manufacture comprising a program storage medium readable by a computer and embodying one or more instructions executable by the computer to perform a method for transforming data in a database operation on a database stored on a data storage device, the method comprising:

defining a program template containing default parameter values corresponding to parameters of a pre-existing transformation program;

defining at least one business view having an association with the program template, said business view including scheduling information and a parameter string initialized to the default parameter values;

creating at least one scheduled instance of operation of the transformation program by receiving selective modifications of the parameter string and the scheduling information of the at least one business view from the user; and

invoking the at least one scheduled instance of operation of the transformation program under control of the at least one business view.

67. The article of manufacture as set forth in claim 66, wherein the creating of at least one scheduled instance of operation of the transformation program includes:

displaying the parameter values of the parameter string;

receiving selective modifications of the displayed parameter values from the user; and

updating the parameter string based on the received selective modifications.

68. The article of manufacture as set forth in claim 66, wherein the defining of said program template includes:

defining a program template using a graphical user interface.

69. The article of manufacture as set forth in claim 66, wherein:

the defining of said program template containing default parameter values corresponding to parameters of a pre-existing transformation program includes defining at least one default parameter value as a token; and

the invoking of the at least one scheduled instance of operation of the transformation program under control of the at least one business view includes replacing the token with a system data value corresponding to the token.

70. The article of manufacture as set forth in claim 66, wherein the defining of said program template containing default parameter values corresponding to parameters of a pre-existing transformation program includes:

defining a program template for a pre-existing transformation program, said pre-existing transformation program being one of an SQL query and a data warehouse program.

71. The article of manufacture as set forth in claim 66, wherein the defining of said program template includes:

storing metadata relating to the pre-existing transformation program in the program template.

72. The article of manufacture as set forth in claim 71, wherein the storing of metadata relating to the pre-existing transformation program in the program template includes:

storing at least one of a business name, a physical name, an executable file type, a description, and a server agent at which the data warehouse program is installed with a specified physical name.

73. The article of manufacture as set forth in claim 66, wherein the defining of said program template containing default parameter values corresponding to parameters of a pre-existing transformation program includes:

defining a program template containing default parameter values specifying default source and target parameter values relating to the pre-existing transformation program.

74. The article of manufacture as set forth in claim 66, further comprising:

storing information corresponding to results of the invoking step in a history file.

75. The article of manufacture as set forth in claim 66, wherein:

the defining of at least one business view includes defining at least a first business view and a second business view;

the creating of at least one scheduled instance of operation of the transformation program includes selectively scheduling the first business view and the second business view to run concurrently; and

the invoking of the at least one scheduled instance of operation of the transformation program includes invoking a first scheduled instance of operation of the transformation under control of the first business view and invoking a second scheduled instance of operation of the transformation under control of the second business view, said first and second invoked scheduled instances running concurrently.

76. The article of manufacture as set forth in claim 66, wherein the invoking of the at least one scheduled instance of operation of the transformation program under control of the at least one business view includes:

retrieving a copy of the program template;

passing the parameter string of the business view to the retrieved copy of the program template and thereby selectively modifying the default parameters of the retrieved copy of the program template; and

starting an instance of operation of the transformation program, said instance receiving the selectively modified default parameters of the retrieved copy of the program template.