Optimizing federated and ETL'd databases with considerations of specialized data structures within an environment having multidimensional constraint

• US 8,712,955 B2
• Filed: 07/02/2010
• Issued: 04/29/2014
• Est. Priority Date: 01/02/2008
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Friedlander, Robert R., Kraemer, James R.
• Primary Examiner(s): Rostami, Mohammad S
• Application Number:
  US12/829,886
• Publication Number:
  US 20100268684A1
• Time in Patent Office: 1,397 Days
• Field of Search: 707/601, 707/602, 707/603, 707/604, 707/686, 707/713, 707/803, 707/809, 707/812
• US Class Current: 707/602
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: Y10S 707/955 Object-oriented
• CPC Class Codes: Y10S 707/96 Object-relational

Abstract

A method for creating a data warehousing scheme having optimally selected components. A mathematical model of a goal for the data warehousing scheme is input into an optimization engine. At least one constraint on the data warehousing scheme is input into the optimization engine. A mathematical optimization algorithm is performed using the optimization engine, wherein an output of the optimization engine is an optimized data warehousing scheme having optimally selected components. The optimized data warehousing scheme can be stored.

Claims

1. A computer-implemented method for creating a data warehousing scheme having optimally selected components, the computer-implemented method comprising:

inputting into an optimization engine a mathematical model of a goal for the data warehousing scheme, wherein the data warehousing scheme is a system of data processing systems, networking systems, software, data storage types, and data types used to maintain data and operations of an organization;

inputting into the optimization engine at least one constraint on the data warehousing scheme and an affinity matrix, wherein the affinity matrix indicates relationships between groups of data sources and groups of output objects, and relationships between groups of available logical processes and groups of output objects, wherein each relationship is represented by a cell containing a binary value in which a zero indicates that a data source is not needed or is incomplete for a particular output object data structure and a one indicates that the data source exists and is used for the particular output object data structure and wherein upon completion of a major information technology project associated with the affinity matrix, every cell of the affinity matrix has a one;

performing a mathematical optimization algorithm using the optimization engine, the at least one constraint, and the affinity matrix, wherein an output of the optimization engine is an optimized data warehousing scheme having the optimally selected components in a specified order; and

storing the optimized data warehousing scheme.

2. The computer-implemented method of claim 1 further comprising:

after performing the mathematical optimization algorithm, inputting feedback and the optimized data warehousing scheme into the optimization engine; and

performing a second mathematical optimization algorithm using the optimization engine to generate a second output, wherein the second output comprises a second optimized data warehousing scheme having second optimally selected components; and

storing the second optimized data warehousing scheme.

3. The computer-implemented method of claim 1 wherein the goal comprises a quantitative assessment of desires of an organization for the data warehousing scheme.

4. The computer-implemented method of claim 1 wherein the optimally selected components comprise a quantitative report on how data is to be stored and in what format the data is to be stored.

5. The computer-implemented method of claim 4 wherein the quantitative report comprises at least one recommendation regarding which portions of data should be federation, ETL'"'"'d (Extract, Transform, and Loaded), be stored in at least one type of database, and combinations thereof.

6. The computer-implemented method of claim 4 wherein the quantitative report comprises at least one recommendation regarding which portions of data should be stored in a format selected from at least one of:

unstructured text, a markup language, a genomic array, image data, multi-media data, and combinations thereof.

7. The computer-implemented method of claim 4 wherein the quantitative report comprises a recommendation that at least a portion of all available data be stored as a combination of unstructured text format and markup language format, and wherein the quantitative report specifies relative proportions of unstructured text format to markup language format.

8. The computer-implemented method of claim 4 wherein the quantitative report comprises a recommendation that at least a specific portion of all available data be stored in unstructured text format, wherein the specific portion is not currently stored as unstructured text, and wherein the report further comprises a recommendation that a first application be used for converting the specific portion to unstructured text.

9. The computer-implemented method of claim 1 wherein the at least one constraint comprises a mathematical characterization of political considerations relating to the data warehousing scheme.

10. The computer-implemented method of claim 9 wherein the political considerations comprise at least one of:

a requirement that a first user receive a first output of a first query, a requirement that a data type be avoided for a first portion of data, a requirement that a data storage method be avoided for a second portion of data, a requirement that a data type be used for a third portion of data, a requirement that a data storage method be used for a fourth portion of data, a requirement that a software product from a particular company be used relative to a fifth portion of data, a requirement that a sixth portion of data be restricted from a set of users, a requirement that a seventh portion of data and an eight portion of data be isolated from each other, a requirement that a physical site be used when implementing the data warehousing scheme, a quantitative estimation of an impact that a data warehousing scheme plan may require approval of an organization before implementation, and combinations thereof.

11. The computer-implemented method of claim 1 wherein the at least one constraint comprises a mathematical characterization of at least one of:

political constraints, available funds, available hardware resources, available networking resources, available skills, development time allotted to developing the data warehousing scheme, expected network traffic, available storage space, expected usage of one or more processors, expected maintenance, minimum query response time, maximum query response time, expected query response time, complexity of query writing, memory residence, disk delays, requirement to outsource desired skills, an expected average skill level of average users of the data warehousing scheme, a skill level of a user of a specific portion of the data warehousing scheme, a requirement that an output of a query be in a particular format, a type of query, a user'"'"'s ability to change a type of data that is to be output in response to a query, and combinations thereof.

12. The computer-implemented method of claim 1 wherein the data warehousing scheme is part of a major information technology project, and wherein the optimally selected components comprise a subset of optimally selected sub-projects of the major information technology project.

13. The computer-implemented method of claim 12 further comprising creating an optimized project having the optimally selected sub-projects, wherein creating the optimized project comprises:

determining value clusters, wherein each value cluster is a discrete intersection of selected data and an ability of the selected data to add value to an organization;

organizing data objects into data models of future data structures;

mapping the data models of future data structures to existing data sources;

determining additional processes, wherein the additional processes move the selected data from a source to a target;

creating the affinity matrix based on the value clusters;

inputting into an optimization engine boundary conditions, the existing data sources, the data objects, logical processes used to create output objects, the value clusters, the data models of future data structures, the mapping of the data models of future data structures to the existing data sources, the additional processes for moving data from the source to the target, formats of data to be used in the optimized project; and

storing the optimized project in a storage device, wherein the optimized project is derived from an optimization process using the optimization engine.

14. The computer-implemented method of claim 13 wherein the output objects comprise one of screens, reports, queries, applications, deliverables, an interactive graphical user interface, and combinations thereof.

15. A computer program product comprising:

a non-transitory computer storage device having computer usable program code stored thereon for creating a data warehousing scheme having optimally selected components, the computer usable program code including;

computer usable program code for inputting into an optimization engine a mathematical model of a goal for the data warehousing scheme, wherein the data warehousing scheme is a system of data processing systems, networking systems, software, data storage types, and data types used to maintain data and operations of an organization;

computer usable program code for inputting into the optimization engine at least one constraint on the data warehousing scheme and an affinity matrix, wherein the affinity matrix indicates relationships between groups of data sources and groups of output objects, and between groups of available logical processes and groups of output objects, wherein each relationship is a cell containing a binary value in which a zero indicates that a data source is not needed or is incomplete for a particular output object data structure and a one indicates that the data source exists and is used for the particular output object data structure and wherein upon completion of a major information technology project associated with the affinity matrix, every cell of the affinity matrix has a one;

computer usable program code for performing a mathematical optimization algorithm using the optimization engine, the constraint, and the affinity matrix, wherein an output of the optimization engine is an optimized data warehousing scheme having the optimally selected components in a specified order; and

computer usable program code for storing the optimized data warehousing scheme.

16. The computer program product of claim 15 wherein the goal comprises a quantitative assessment of desires of an organization for the data warehousing scheme.

17. The computer program product of claim 15 wherein the components comprise a quantitative report on how data is to be stored and in what format the data is to be stored.

18. A data processing system comprising:

a bus;

at least one processor coupled to the bus;

a computer usable medium coupled to the bus, wherein the computer usable medium contains a set of instructions for creating a data warehousing scheme having optimally selected components, wherein the at least one processor is adapted to carry out the set of instructions to;

input into an optimization engine a mathematical model of a goal for the data warehousing scheme, wherein the data warehousing scheme is a system of data processing systems, networking systems, software, data storage types, and data types used to maintain data and operations of an organization;

input into the optimization engine at least one constraint on the data warehousing scheme and an affinity matrix indicating relationships between groups of data sources and groups of output objects, and between groups of available logical processes and groups of output objects, wherein each relationship is a cell containing a binary value in which a zero indicates that a data source is not needed or is incomplete for a particular output object data structure and a one indicates that the data source exists and is used for the particular output object data structure and wherein upon completion of a major information technology project associated with the affinity matrix, every cell of the affinity matrix has a one;

perform a mathematical optimization algorithm using the optimization engine, the constraint, and the affinity matrix, wherein an output of the optimization engine is an optimized data warehousing scheme having the optimally selected components in a specified order; and

store the optimized data warehousing scheme.

19. The data processing system of claim 18 wherein the goal comprises a quantitative assessment of desires of an organization for the data warehousing scheme.

20. The data processing system of claim 18 wherein the components comprise a quantitative report on how data is to be stored and in what format the data is to be stored.