Generating and reusing transformations for evolving schema mapping
A system and method of use resolves the frustration of repeated manual work during schema mapping. The system utilizes a transformation graph—a collection of nodes (unified attributes) and edges (transformations) in which source attributes are mapped and transformed. The system further leverages existing mappings and transformations for the purpose of suggesting to a user the optimal paths (i.e., the lowest cost paths) for mapping new sources, which is particularly useful when new sources share similarity with previously mapped sources and require the same transformations. As such, the system also promotes an evolving schema by allowing users to select which unified attributes they want to include in a target schema at any time. The system addresses the technical challenge of finding optimal transformation paths and how to present these to the user for evaluation.
1. A method for computer assisted mapping of source attributes of an unmapped data source into an existing transformation graph, comprising:
operating a computer system having at least one central processing unit and storing a transformation graph that maps source attributes from at least one data source to nodes within the transformation graph, wherein the transformation graph is an evolving graph of nodes, and the transformation graph includes edges which represent transformations between the nodes, and wherein the transformation graph allows for building reusable transformation paths;
accessing a second data source, wherein the second data source is distinct from the at least one data source;
selecting one or more source attributes from the second data source;
for each selected source attribute, checking for an exact match between the selected source attribute and existing nodes within the transformation graph;
when an exact match is found, automatically mapping the matched source attribute to the matched node, and when an exact match is not found, creating a new node in the transformation graph and mapping the unmatched source attribute to the new node;
identifying existing nodes in the transformation graph that are similar to the new node;
selecting one of the identified similar nodes;
calculating a cost for each path from the new node to the selected node;
presenting, for user selection, all paths in order of calculated cost;
receiving user selection of one of the presented paths; and
creating a new edge from the new node to a first existing node sequentially in the selected path,
the source attributes of the second data source thereby being mapped to the transformation graph.
2. The method of claim 1 , wherein creating a new edge further comprises:
displaying a user interface control to configure transformation rules between two nodes directly connected by the new edge; and
defining, by a user through the user interface control, transformation rules between the two nodes directly connected by the new edge.
3. The method of claim 2 , wherein the user interface control presents visualizations of transformation actions for user selection.
4. The method of claim 2 , wherein the user interface control presents a text box for user input of programming language commands to execute a transformation.
5. The method of claim 1 further comprising:
tracking one or more selected nodes in a target schema; and
exporting the target schema to a file, database, or server.
6. The method of claim 5 , further comprising:
upon receiving a query based on the target schema, applying mappings and transformation paths defined in the transformation graph to data from the source attributes to provide data as defined at the selected nodes of the target schema.
7. The method of claim 1 , wherein identifying existing similar nodes further comprises:
comparing data and metadata of the unmatched source attribute to data and metadata associated with each existing node in the transformation map.
8. The method of claim 7 , further comprising:
clustering semantically related nodes;
identifying which clusters are most similar to the new node; and
pruning to reduce a search scope when identifying similar existing nodes.
9. The method of claim 1 , further comprising:
storing metadata associated with source attributes at nodes that the source attributes map into; and
comparing the stored metadata with metadata of new source attributes when checking for exact matches.
10. The method of claim 1 , wherein calculating a cost further comprises:
determining processing time and memory usage for existing edges;
creating an edge cost based on the determined processing time and memory usage; and
summing the created edge costs for all existing edges for each path.
11. A computer system for assisted mapping of source attributes of an unmapped data source into an existing transformation graph, comprising a central processing unit operating software stored on non-transitory computer media and a data storage source storing a transformation graph that maps source attributes from at least one data source to nodes within the transformation graph, wherein the transformation graph is an evolving graph of nodes, and the transformation graph includes edges which represent transformations between the nodes, and wherein the transformation graph allows for building reusable transformation paths, the software comprising instructions to:
access a second data source, wherein the second data source is distinct from the at least one data source;
select one or more source attributes from the second data source;
for each selected source attribute, check for an exact match between the selected source attribute and existing nodes within the transformation graph;
when an exact match is found, automatically map the matched source attribute to the matched node, and when no exact match is found, create a new node in the transformation graph and map the unmatched source attribute to the new node;
identify existing nodes in the transformation graph that are similar to the new node;
select one of the identified similar nodes;
calculate a cost for each path from the new node to the selected node;
present, for user selection, all paths in order of calculated cost;
receive user selection of one of the presented paths; and
create a new edge from the new node to a first existing node sequentially in the selected path,
the source attributes of the second data source thereby being mapped to the transformation graph.
12. The system of claim 11 , wherein instructions to create a new edge further comprise instructions to:
display a user interface control to configure transformation rules between two nodes directly connected by the new edge; and
define, by a user through the user interface control, transformation rules between the two nodes directly connected by the new edge.
13. The system of claim 12 , wherein the user interface control presents visualizations of transformation actions for user selection.
14. The system of claim 12 , wherein the user interface control presents a text box for user input of programming language commands to execute a transformation.
15. The system according to claim 11 , wherein the software further comprises instructions to:
track one or more selected nodes in a target schema; and
export the target schema to a file, database, or server.
16. The system of claim 15 , wherein the software further comprises instructions to:
receive a query based on the target schema, apply mappings and transformation paths defined in the transformation graph to data from the source attributes, and provide data as defined at the selected nodes of the target schema.
17. The system of claim 11 , wherein instructions to identify existing similar nodes further comprise instructions to:
compare data and metadata of the unmatched source attribute to data and metadata associated with each existing node in the transformation map.
18. The system of claim 17 , wherein the software further comprises instructions to:
cluster semantically related nodes;
identify which clusters are most similar to the new node; and
prune to reduce a search scope when identifying similar existing nodes.
19. The system of claim 11 , wherein the software further comprises instructions to:
store metadata associated with source attributes at nodes that the source attributes map into; and
compare the stored metadata with metadata of new source attributes when checking for exact matches.
20. The system of claim 11 , wherein instructions to calculate a cost further comprise instructions to:
determine processing time and memory usage for existing edges;
create an edge cost based on the determined processing time and memory usage; and
sum the created edge costs for all existing edges for each path.