Packaged warehouse solution system

• US 20070239769A1
• Filed: 06/30/2006
• Published: 10/11/2007
• Est. Priority Date: 04/07/2006
• Status: Active Grant

• Current Assignee: Twitter Inc (X Holdings Corp.)
• Original Assignee: Cognos Incorporated (International Business Machines Corporation)
• Inventors: Adendorff, Michael, Fazal, Tom, Palmer, Simon, Evans, Simon, Janneteau, Carm, Dorval, Gregory, Tulchinsky, Anatoly
• Granted Patent: US 7,720,804 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A data warehouse solution system comprises a metadata model, a user interface and an engine. The metadata model has an information needs model including metadata regarding information needs for building reports by users, and a data information model including metadata describing data that is available for building reports. The user interface has a customer user interface for presenting the information needs model to the users for report generation, and a modeling user interface for presenting the data information model to the users for manipulating data warehouse objects. The engine has a report management service unit for providing report management service using the information needs model, and a data management service unit for providing data management service including generation of a data warehouse using the data information model.

Claims

1. A data warehouse solution system comprising:

a metadata model having a data information model including metadata that describes models for generating a data warehouse, and an information needs model including metadata regarding information needs for building reports by users;

an engine having a data management service unit for providing data management services including generation of a data warehouse using the data information model, and a report management service unit for providing report management services including generation of reports using the information needs model and the data warehouse; and

a user interface having a modeling user interface for presenting the data information model to the users for manipulating data warehouse objects, and a customer user interface for presenting the information needs model to the users for report generation.

2. The data warehouse solution system as claimed in claim 1, wherein the data information model contains metadata describing data that is available for building reports and satisfies the information needs indicated in the information needs model.

3. The data warehouse solution system as claimed in claim 1, wherein the data information model contains metadata describing star schema models of the data warehouse, mapping of source systems to the star schema models, and data transformation rules for transforming data of the source systems for the star schema models.

4. The data warehouse solution system as claimed in claim 1, wherein the metadata model further includes a content library for storing the information needs model and the data information model.

5. The data warehouse solution system as claimed in claim 1, wherein the modeling user interface provides views of data warehouse objects and allows the user to modify the data warehouse objects.

6. The data warehouse solution system as claimed in claim 1, wherein the report management service unit of the engine includes:

a report manager for managing generation of reports; and

a target framework manager for providing a semantic layer to the data warehouse.

7. The data warehouse solution system as claimed in claim 6, wherein the target framework manager automatically generates a practically usable target framework model based on a logical metadata model of the data warehouse.

8. The data warehouse solution system as claimed in claim 7, wherein the target framework manager automatically generates the target framework model using applicable business rules including general rules, database layer rules, business view rules, dimensional view rules, and metadata rules.

9. The data warehouse solution system as claimed in claim 6, wherein the target framework manager preserves extensions made to a target framework model to a regenerated target framework model.

10. The data warehouse solution system as claimed in claim 1, wherein the engine includes a source model generator for managing generation of a source framework model from one or more data source systems, the source framework model is a semantic layer containing a logical business representation of the source systems.

11. The data warehouse solution system as claimed in claim 10, wherein the source model generator has a source framework manager for accessing and reading logical information of the source systems, and reflecting the logical information in the source framework model.

12. The data warehouse solution system as claimed in claim 11, wherein the source model generator provides a correction of the logical information.

13. The data warehouse solution system as claimed in claim 1, wherein the data management service unit of the engine includes:

an extract-transform-load (ETL) code generator for generating code for extracting data from one or more source systems, transforming the data and loading the data into the data warehouse using the data information model.

14. The data warehouse solution system as claimed in claim 13, wherein the ETL code generator automatically determines an order of loading data, and loads the data in the order.

15. The data warehouse solution system as claimed in claim 13, wherein the ETL code generator extracts data that has been added or changed since the last load.

16. The data warehouse solution system as claimed in claim 13, wherein the ETL code generator allows the user to set a load interval to control a size of data to be loaded at a time.

17. The data warehouse solution system as claimed in claim 13, wherein the ETL code generator provides a control of a loading phase.

18. The data warehouse solution system as claimed in claim 1, wherein the data management service unit of the engine includes:

a data warehouse objects manager for managing creation and modification of data warehouse objects using the data information model and the modelling user interface.

19. The data warehouse solution system as claimed in claim 18, wherein the data warehouse objects manager, in response to user'"'"'s action on a data warehouse object, automatically processes data warehouse objects that are impacted by the user'"'"'s action.

20. The data warehouse solution system as claimed in claim 19, wherein the data warehouse objects manager automatically identifies the data warehouse objects that are impacted by the user'"'"'s action, and updates the data warehouse objects impacted or presents a list of the data warehouse objects impacted to the user for a further action.

21. The data warehouse solution system as claimed in claim 1, wherein the data management service unit of the engine includes:

a data loading manager for providing the user control of data load into the data warehouse from the source systems.

22. The data warehouse solution system as claimed in claim 1, wherein the data management service unit of the engine includes:

a database table manager for managing creation and modification of warehouse tables in the data warehouse; and

a database data manager for managing loading of the data into the warehouse tables from one or more source systems.

23. The data warehouse solution system as claimed in claim 22, wherein the database table manager indexes materialized views in the warehouse database.

24. The data warehouse solution system as claimed in claim 22, wherein the database data manager includes rules defining one or more data movement or transformations that move or transform data extracted from one or more data source systems.

25. The data warehouse solution system as claimed in claim 24, wherein the transformations include one or more of pivot transformation, hierarchy flattening transformation, aggregation transformation and combining data transformation.

26. The data warehouse solution system as claimed in claim 22, wherein the database table manager, in response to user'"'"'s action on a warehouse table, performs a table management process for updating warehouse tables that are impacted by the user'"'"'s action.

27. The data warehouse solution system as claimed in claim 26, wherein the table management process includes a tables and columns management for updating warehouse tables and columns impacted by the users action, an indices management for updating indices relating to the warehouse tables and columns impacted, and foreign keys management for updating foreign keys associated with the warehouse tables impacted.

28. The data warehouse solution system as claimed in claim 1, wherein the data warehouse solution system is upgradeable while preserving modifications made to an existing data warehouse.

29. The data warehouse solution system as claimed in claim 1, herein the data warehouse solution system is a packaged single system.

30. A method of generating and maintaining a data warehouse, the method comprising the steps of:

providing a metadata model having a data information model including metadata describing models for generating a data warehouse, and an information needs model including metadata regarding information needs for building reports by users;

providing data management services by a data warehouse solution system engine to automatically generate a data warehouse from one or more source systems using the data information model; and

providing a modeling user interface for presenting the data information model to a user for allowing the user to manipulate objects of the data warehouse.

31. The method as claimed in claim 30 further comprising the steps of:

providing report management services by the system engine to generate reports using the information needs model and the data warehouse; and

providing a consumer user interface for presenting the information needs model to a user for report generation using the report management services.

32. The method as claimed in claim 30, wherein the metadata model providing step comprises the step of including, in the data information model, metadata describing data that is available for building reports and satisfies the information needs indicated in the information needs model.

33. The method as claimed in claim 30, wherein the metadata model providing step including, in the data information model, metadata describing star schema models of the data warehouse, mapping of source systems to the star schema models, and data transformation rules for transforming data of the source systems for the star schema models.

34. The method as claimed in claim 30, wherein the metadata model providing step provides the metadata model as a predefined content library storing the information needs model and the data information model.

35. The method as claimed in claim 30, wherein the report management services providing step comprises the step of providing a target framework model which provides a semantic layer to the data warehouse.

36. The method as claimed in claim 35, wherein the target framework model providing step automatically generates a practically usable target framework model based on a logical metadata model of the data warehouse.

37. The method as claimed in claim 36, wherein the target framework model providing step automatically generates the target framework model using applicable business rules including general rules, database layer rules, business view rules, dimensional view rules, and metadata rules.

38. The method as claimed in claim 35, wherein the target framework model providing step preserves extensions made to a target framework model to a regenerated target framework model.

39. The method as claimed in claim 30, wherein the data management services providing step comprises the step of managing generation of a source framework model from one or more data source systems, the source framework model is a semantic layer containing a logical business representation of the source systems.

40. The method as claimed in claim 39, wherein the source model generation managing step comprises the steps of:

accessing and reading the logical information of the source systems; and

reflecting the logical information in the source framework model.

41. The method as claimed in claim 40, wherein the source model generation managing step further comprises the step of providing a correction of the logical information.

42. The method as claimed in claim 30, wherein the data management services providing step comprises the step of generating extract-transform-load (ETL) code for extracting data from the source systems, transforming the data, and loading the data into the data warehouse based on the data information model.

43. The method as claimed in claim 42, wherein the ETL code generating step automatically determines an order of loading data, and loads the data in the order.

44. The method as claimed in claim 42, wherein the ETL code generating step extracts data that has been added or changed since the last load.

45. The method as claimed in claim 42, wherein the ETL code generating step allows the user to set a load interval to control a size of data to be loaded at a time.

46. The method as claimed in claim 42, wherein the ETL code generating step provides a control of a loading phase.

47. The method as claimed in claim 30, wherein the data management services providing step comprises the step of, in response to user'"'"'s action on a data warehouse object in the data warehouse, automatically processing data warehouse objects that are impacted by the user'"'"'s action.

48. The method as claimed in claim 47, wherein the data management services providing step comprises the steps of:

automatically identifying the data warehouse objects that are impacted by the user'"'"'s action; and

updating the data warehouse objects impacted or presenting a list of the data warehouse objects impacted to the user for a further action.

49. The method as claimed in claim 30, wherein the data management services step comprises the steps of:

managing creation and modification of warehouse tables in the data warehouse; and

managing loading of the data into the warehouse tables from the source systems.

50. The method as claimed in claim 49, wherein the data loading managing step comprises the step of applying predefined rules defining one or more data movement or transformations for moving or transforming data extracted from the data source systems.

51. The method as claimed in claim 50, wherein the predefined rules include one or more of pivot transformation, hierarchy flattening transformation, aggregation transformation and combining data transformation.

52. The method as claimed in claim 49, wherein the database table managing step comprises the step of, in response to users action on a warehouse table, performing a table management process for updating warehouse tables that are impacted by the user'"'"'s action.

53. The method as claimed in claim 49, wherein the table managing step further comprises the step of indexing materialized views in the data warehouse.

54. The method as claimed in claim 52, wherein the table management process performing step comprises the steps of:

updating warehouse tables and columns impacted by the user'"'"'s action;

updating indices relating to the warehouse tables and columns impacted; and

updating foreign keys associated with the warehouse tables impacted.

55. The method as claimed in claim 30 further comprising the step of preserving modifications made to an existing data warehouse during upgrading.

56. A computer readable medium storing instructions or statements for use in the execution in a computer of a method of generating and maintaining a data warehouse, the method comprising the steps of:

providing a metadata model having a data information model including metadata describing models for generating a data warehouse, and an information needs model including metadata regarding information needs for building reports by users;

providing data management services by a data warehouse solution system engine to automatically generate a data warehouse using the data information model from one or more source systems; and

providing a user interface for presenting the data information model to users for allowing users to manipulate objects of the data warehouse.

57. A propagated signal carrier carrying signals containing computer executable instructions that can be read and executed by a computer, the computer executable instructions being used to execute a method of generating and maintaining a data warehouse, the method comprising the steps of:

providing a metadata model having a data information model including metadata describing models for generating a data warehouse, and an information needs model including metadata regarding information needs for building reports by users;

providing data management services by a data warehouse solution system engine to automatically generate a data warehouse using the data information model from one or more source systems; and

providing a user interface for presenting the data information model to users for allowing users to manipulate objects of the data warehouse.