Method and apparatus for automatically populating a data warehouse system

• US 6,167,405 A
• Filed: 04/27/1998
• Issued: 12/26/2000
• Est. Priority Date: 04/27/1998
• Status: Expired due to Term

• Current Assignee: Bull HN Information Systems Incorporated (Atos SE)
• Original Assignee: Bull HN Information Systems Incorporated (Atos SE)
• Inventors: Guhr, Jerry T, Picone, Joseph K., Rosensteel, Kenneth R. Jr.
• Primary Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US09/067,101
• Time in Patent Office: 974 Days
• Field of Search: 707/6, 707/101, 707/102, 395/785
• US Class Current: 1/1
• 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

Abstract

A method and system for facilitating the creation of warehouse requests in a data warehouse system. During the design of the data warehouse tables, a repository tool is used for storing a number of new objects such as source and target databases, source and target tables and warehouse requests that are graphically defined and linked together by an administrator with the repository tool. The resulting visual design is so drawn so as to serve as input for each warehouse request to be generated. The administrator invokes a data replication component that operatively couples to the repository tool signaling that the warehouse request is to be implemented. The data replication component automatically creates the different subcomponents of the request by accessing various links stored by the repository tool and displays a visual representation of the subcomponents and their relationships to each other to the administrator. Thereafter, the replication component provides access to menu screens for enabling the administrator to visualize each of the subcomponents of the request and their properties for enabling modifications to be made to such subcomponents for completing configuration of all request subcomponents. Subsequently, the warehouse request can be scheduled to execute and populate the warehouse tables.

Claims

1. A method for facilitating the creation of data warehouse requests for populating data warehouse tables defining a particular warehouse design in a data warehouse system comprised of a number of data source systems and a target system, a repository component for storing information representative of the warehouse design and a warehouse management interface component operatively coupled to the repository for enabling development of warehouse requests required to populate the warehouse tables, each warehouse request having a plurality of subcomponents specifying a process of extracting data from source tables of a source database located in one of the data source systems, moving the data to the target system, transforming the data to match target system requirements and then storing the data into a target database of the target system, the method comprising the steps of:

(a) during the design phase, generating and storing in the repository, information defining reference links between each target data warehouse table and the source tables from which instances must be extracted, identification of the source databases and target database, reference links between corresponding portions of the source and target tables, and identification of those warehouse request entities related to a number of target tables to be populated by a particular warehouse request;

(b) upon completion of the design phase, invoking a data replication management (DRM) component included within the warehouse management interface component in response to a selection of a warehouse request to be implemented;

(c) in response to the selection, automatically creating the different subcomponents of a data warehouse request by the DRM component accessing the previously created reference links from the repository and displaying a visual representation of the subcomponents of the request; and

,

(d) providing access to menus of a menu facility for enabling visualization of the automatically created data warehouse request and for making any required modifications to information related to the each of the subcomponents selected for display prior to scheduling the request for execution.

2. The method of claim 1 wherein the method further includes the step of:

(e) the DRM component being operative in response to being supplied properties pertaining to a particular subcomponent object, to signal when the subcomponent or event has been configured.

3. The method of claim 2 wherein the method further includes the steps of:

(f) in response to an indication that all events of the particular warehouse request having been configured, the DRM component is operative to activate a scheduling control button through the menu facility for enabling the scheduling of the warehouse request for execution.

4. The method of claim 1 wherein step (a) further includes providing extensions in the repository for creating and storing:

objects identifying the data source entities and source data table entities and attributes from which information is to be extracted, objects identifying each target warehouse table entity and attributes and objects identifying the warehouse request entities that are to perform the extraction; and

,

links between the data source and source data table objects defining the references between the data source and source table entities, links between the source data table objects and the target table objects defining the references between corresponding portions of the source data and target table entities and links between each target table object and the particular warehouse request object for identifying those target tables to be populated by the warehouse request.

5. The method of claim 4 wherein step (a) further includes:

(1) using the extensions in the repository to create a number of objects identifying applications that could be activated for providing data to be used in populating the warehouse target tables; and

(2) for creating links between each application object and source table object for indicating that the source table is to be accessed by an application.

6. The method of claim 1 wherein the DRM component includes an application program interface (API) for tightly coupling the repository to the DRM component, the DRM component including a management request section operatively available through the API and wherein step (b) further includes the steps of

(1) invoking the DRM component by selecting a control function provided by the graphical interface, the control function invoking the API causing the activation of the DRM management request section;

and,

(2) selecting the object identifying the warehouse request to be implemented.

7. The method of claim 1 wherein the DRM component includes an application program interface (API) for operatively coupling the repository to the DRM component, the DRM component including a management request section operatively available through the API and the menu facility including an action menu and a warehouse request menu, step (b) further including the steps of

(1) invoking the management request section through the action menu using the menu facility;

and,

(2) selecting a particular warehouse request to be implemented from items contained in the warehouse request menu.

8. The method of claim 6 or 7 wherein step (c) further includes the steps of:

(1) retrieving from the repository, information defining the characteristics/attributes of the target table entity and associated source table entities and characteristics;

(2) based on the information retrieved in step (1), the DRM management section automatically constructing the sequence of subcomponents or events for implementing the warehouse request and

(3) the DRM management request section causing the graphical interface to display automatically, subcomponent icon objects representing the events to be used in implementing the request in accordance with the constructed sequence.

9. The method of claim 8 wherein the DRM management section causes the display of subcomponent icon objects specifying an event sequence containing Extract, Transfer, Transform and Load icon objects.

10. The method of claim 9 wherein the event sequence of icon objects consists of a number of Extract icon objects, a number of Transfer icon objects, a Transform icon object and a number of Load icon objects as defined by information retrieved from the repository.

11. The method of claim 9 wherein the DRM management request section in response to information for a particular warehouse request defining multiple extraction operations from different data source entities causes the display of an Extract icon object for each different data source and the display of a separate Transfer icon object for each Extract icon object wherein the Extract/Transfer icon objects are displayed in parallel indicating to the administrator that work will be carried out in parallel to maximize system throughput.

12. The method of claim 11 wherein the Extract icon object is either a first or second type of Extract object.

13. The method of claim 8 wherein step (d) further includes the step of:

(1) in response to selecting a particular subcomponent icon object, the DRM management request section being operative to display through the menu facility, a menu for providing access to properties pertaining to the event associated with the selected subcomponent icon object.

14. The method of claim 13 wherein in response to selecting the first type of Extract icon object, the DRM management request section provides through the menu facility, a first menu for displaying properties pertaining to a database event that include a database programming language statement, a database name, a database type and an Event description.

15. The method of claim 14 wherein the DRM management request section through the menu facility, enables modification of properties relating to the database programming language statement and the Event description of the selected Extract icon object.

16. The method of claim 15 wherein the database programming language is SQL and the properties relate to a WHERE clause that may be appended to the SQL statement.

17. The method of claim 13 wherein the selection of the Transfer icon object provides through the menu facility, a second menu for displaying properties that include transfer type, nodes involved in the transfer, directories used for data movement and Event description.

18. The method of claim 17 wherein the DRM management request section through the menu facility, enables modification of properties including the Event description of the selected Transfer icon object.

19. The method of claim 13 wherein the warehouse system further includes a transformation client component operatively coupled to the DRM client component, the DRM management request section in response to selecting a Transform icon object operates to invoke the transformation client component and provides through the menu facility, a third menu for displaying properties that include descriptions of the mapping of individual fields provided by the transformation client component.

20. The method of claim 13 wherein in response to selecting a Load icon object, the DRM management request section provides through the menu facility, a fourth menu for displaying properties defining parameters required for loading the target tables that include target system name, database name, database type and Event description.

21. The method of claim 20 wherein the DRM management request section includes menu facilities for enabling modification of properties including the Event description of the selected Load icon object.

22. The method of claim 13 wherein in response to selecting a second type of Extract icon object, the DRM management request section provides through the menu facility, a fifth menu for displaying the properties pertaining to an application that include a path to the application and location of application output files and description files, USERID/password required to execute the application and node of the application.

23. The method of claim 7 wherein the DRM component further includes a view status section and wherein the method further includes the steps of:

(1) invoking the viewing status section through the action menu using the menu facility; and

,

(2) displaying status by the view status section of a particular warehouse request selected from items contained in the warehouse request menu.

24. Apparatus for facilitating the creation of data warehouse requests for populating a number of warehouse tables defining a particular warehouse design in a data warehouse system comprised of a number of data source systems and a target system, a repository component operatively coupled to a design component for storing information representative of the warehouse design, and a warehouse management interface component operatively coupled to the repository for enabling an administrator to develop warehouse requests required to populate the warehouse tables, each warehouse request having a plurality of subcomponents for implementing execution of the request to populate tables of the data warehouse tables wherein subcomponents are used in specifying a process of extracting data from source tables of a source database located in one of the data source systems, moving the data to the target system, transforming the data to match target system requirements and then storing the transformed data into a target database of the target system, the apparatus comprising:

(a) extensions added to the repository, the extensions being used during the design phase for storing in the repository, information defining reference links between each target data warehouse table and the source tables from which instances must be extracted, identification of the source databases and target database, reference links between corresponding portions of the source and target tables, and identification of those warehouse request entities related to a subset of target tables to be populated by a particular warehouse request; and

(b) a data replication management (DRM) component included within the warehouse management interface, the DRM component being operatively coupled to the repository, the DRM component being operative upon being invoked upon completion of the design phase to automatically create the different subcomponents of a data warehouse request by accessing the previously created reference links from the repository and displaying a visual representation of the subcomponents of the request; and

,

the DRM component including a menu facility for providing access to a set of menus for enabling visualization of the automatically created data warehouse request and making any required modifications to information related to the each of the subcomponents selected for display prior to scheduling the request for execution.

25. The apparatus of claim 24 wherein the DRM component menu facility further includes:

(c) first control means operative in response to being supplied properties pertaining to a particular subcomponent object, to signal when the subcomponent object or event has been configured by the DRM component.

26. The apparatus of claim 25 wherein the DRM component further includes:

(d) a second control means operative in response to an indication that all events of the particular warehouse request having been to activate a scheduling control button of the menu facility for enabling scheduling the warehouse request for execution.

27. The apparatus of claim 24 wherein the repository extensions enable creation and storage of information pertaining to:

objects identifying the data source entities and source data table entities and attributes from which information is to be extracted, objects identifying each target warehouse table entity and attributes and objects identifying the warehouse request entities that are to perform the extraction; and

,

links between the data source and source data table objects defining the references between the data source and source table entities, links between the source data table objects and the target table objects defining the references between corresponding portions of the source data and target table entities and links between each target table object and the particular warehouse request object for identifying those target tables to be populated by the warehouse request.

28. The apparatus of claim 27 wherein the repository extensions enable creation and storage of information pertaining to:

(1) objects identifying applications to be activated for providing data to be used in populating the warehouse target tables; and

(2) links between each application object and source table object for indicating that the source table is to be accessed by an application.

29. The apparatus of claim 24 wherein the DRM component further includes an application program interface (API) for tightly coupling the repository to the DRM component, the DRM component further includes a management request section operatively coupled to the API, the DRM component being invoked by selecting a control function provided by the repository extensions through the menu facility and,

the repository extensions enabling selection of the object identifying the warehouse request to be implemented.

30. The apparatus of claim 24 wherein the DRM component further includes an application programming interface (API) operatively coupling the repository to the DRM component, the DRM component including a management request section operatively coupled to the API and wherein the menu facility further includes an action menu and a warehouse request menu, the action menu enabling the invoking of the management request section;

and, the warehouse request menu enabling selection of a particular warehouse request to be implemented.

31. The apparatus of claim 29 or 30 wherein the DRM management request section upon being invoked:

retrieves from the repository, information defining the characteristics/attributes of the target table entity and associated source table entities and attributes;

automatically constructs the sequence of events for implementing the warehouse request based on the retrieved information; and

through the menu facility, automatically displays subcomponent icon objects representing the events to be used in implementing the request in accordance with the constructed sequence.

32. The apparatus of claim 31 wherein the DRM management request section causes the display of subcomponent icon objects specifying an event sequence containing Extract, Transfer, Transform and Load icon objects.

33. The apparatus of claim 31 wherein the event sequence of icon objects consists of at least one Extract icon object, at least one Transfer icon object, a Transform icon object and at least one Load icon object as defined by information stored and retrieved from the extensions to the repository.

34. The apparatus of claim 30 wherein the DRM component further includes a viewing status section, the viewing status section being invoked through the action menu using the menu facility and operative to display status of a particular warehouse request selected from items contained in the warehouse request menu.

35. The apparatus of claim 32 wherein the DRM management request section in response to information for a particular warehouse request defining multiple extraction operations from different data source entities causes the display of an Extract icon object for each different data source and the display of a separate Transfer icon object for each Extract icon object wherein the Extract/Transfer icon objects are displayed in parallel indicating to the administrator that work will be carried out in parallel to maximize system throughput.