System and method for optimizing federated and ETL'd databases with considerations of specialized data structures within an environment having multidimensional constraints

• US 7,779,051 B2
• Filed: 01/02/2008
• Issued: 08/17/2010
• Est. Priority Date: 01/02/2008
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Kraemer, James R., Friedlander, Robert R.
• Primary Examiner(s): Vital; Pierre M
• Assistant Examiner(s): Rostami; Mohammad S
• Application Number:
  US11/968,233
• Publication Number:
  US 20090171889A1
• Time in Patent Office: 958 Days
• Field of Search: None
• US Class Current: 707/810
• 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;

inputting into the optimization engine at least one constraint on the data warehousing scheme;

performing a mathematical optimization algorithm using the optimization engine, wherein an output of the optimization engine is an optimized data warehousing scheme having optimally selected components;

storing the optimized data warehousing scheme, 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; and

creating an optimized project having the optimally selected sub-projects, wherein creating the optimized project comprises;

determining value clusters, wherein value clusters are the 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 an affinity matrix based on the value clusters, wherein the affinity matrix is a matrix, wherein the matrix indicates a relationship between groups of data sources and groups of output objects, and indicates a relationship between groups of available logical processes and groups of output objects;

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 the affinity matrix; and

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

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 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 output objects comprise one of screens, reports, queries, applications, deliverables, an interactive graphical user interface, and combinations thereof.

13. A computer program product comprising:

a computer usable storage medium having computer usable program code for creating a data warehousing scheme having optimally selected components, the computer program product including;

computer usable program code for inputting into an optimization engine a mathematical model of a goal for the data warehousing scheme;

computer usable program code for inputting into the optimization engine at least one constraint on the data warehousing scheme;

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

computer usable program code for storing the optimized data warehousing scheme, 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; and

computer usable program code for creating an optimized project having the optimally selected sub-projects, wherein the computer usable program code for creating the optimized project comprises;

computer usable program code for determining value clusters, wherein value clusters are the discrete intersection of selected data and an ability of the selected data to add value to an organization;

computer usable program code for organizing data objects into data models of future data structures;

computer usable program code for mapping the data models of future data structures to existing data sources;

computer usable program code for determining additional processes, wherein the additional processes move the selected data from a source to a target;

computer usable program code for creating an affinity matrix based on the value clusters, wherein the affinity matrix is a matrix, wherein the matrix indicates a relationship between groups of data sources and groups of output objects, and indicates a relationship between groups of available logical processes and groups of output objects;

computer usable program code for 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 the affinity matrix; and

computer usable program code for 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 program product of claim 13 wherein the goal comprises a quantitative assessment of desires of an organization for the data warehousing scheme.

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

16. 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;

input into the optimization engine at least one constraint on the data warehousing scheme;

perform a mathematical optimization algorithm using the optimization engine, wherein an output of the optimization engine is an optimized data warehousing scheme having optimally selected components;

store the optimized data warehousing scheme, 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; and

create an optimized project having the optimally selected sub-projects, wherein the at least one processor adapted to carry out the set of instructions to create the optimized project comprises the at least one processor adapted to carry out the set of instructions to;

determine value clusters, wherein value clusters are the discrete intersection of selected data and an ability of the selected data to add value to an organization;

organize data objects into data models of future data structures;

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

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

create an affinity matrix based on the value clusters, wherein the affinity matrix is a matrix, wherein the matrix indicates a relationship between groups of data sources and groups of output objects, and indicates a relationship between groups of available logical processes and groups of output objects;

input 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 the affinity matrix; and

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

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

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