Abstract:
Techniques for cross referencing data are presented. A first database object and a second database object are linked together. The linkage is automatically cross referenced to a third database object. Access to any of the database objects can be achieved via any of the remaining database objects and vice versa. Additionally, the link and cross reference can be visualized and visually manipulated and modified.

Description:
COPYRIGHT 
       [0001]    A portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever. The following notice applies to the example screen shots, images, and source code as described below and in any drawings hereto: Copyright © 2009, Teradata, Inc. of Miamisburg, Ohio—All Rights Reserved. 
       BACKGROUND 
       [0002]    Enterprises are increasingly capturing, storing, and mining a plethora of information related to communications with their customers. Often this information is stored and indexed within databases. Once the information is indexed, queries are developed on an as-needed basis to mine the information from the database for a variety of organizational goals. 
         [0003]    The enterprise data can originate from a myriad of sources and even external feeds, but ultimately the goal for the enterprise is that all the data be consolidated, indexed, and related within a central enterprise data warehouse. 
         [0004]    A major portion of data warehouse management is defining and managing data relationships. A data relationship is data needed to control an association of one piece of data within the data warehouse with another piece of data within the data warehouse. Often this control requires enforcement of enterprise business processes; some processes are automated and some processes are manual. 
         [0005]    Thus, it can be seen that improved techniques for data relationship management are desirable. 
       SUMMARY 
       [0006]    In various embodiments, techniques for cross referencing data are presented. According to an embodiment, a method for cross referencing data is presented. Specifically, . . . . 
     
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
         [0007]      FIG. 1  is a diagram of a method for cross referencing data, according to an example embodiment. 
           [0008]      FIG. 2  is a diagram of a method for cross referencing data, according to an example embodiment. 
           [0009]      FIG. 3  is a diagram of a data cross referencing system, according to an example embodiment. 
           [0010]      FIGS. 4-11  are sample Graphical User Interface (GUI) screens for cross referencing data described in  FIGS. 1-3 , according to an example embodiment. 
           [0011]      FIG. 12  is a relationship data model for a data cross referencing system, according to an example embodiment. 
       
    
    
     DETAILED DESCRIPTION 
       [0012]      FIG. 1  is a diagram of a method  100  for cross referencing data, according to an example embodiment. The method  100  (hereinafter “data cross-referencing service”) is implemented in a machine-accessible or computer-readable storage medium as instructions that is executed by a machine (processing device (processor-enabled with memory; can also be a multiprocessor device) performs the processing depicted in  FIG. 1 . The machine specifically configured to process the data cross-referencing service. Moreover, the data cross-referencing service is optionally accessible over a network. The network may be wired, wireless, or a combination of wired and wireless. 
         [0013]    A “data store” as used herein may include a database, a collection of databases organized as a data warehouse, a directory, a collection of directories cooperating with one another, or various combinations of the same. According to an embodiment, the data store is a Teradata® warehouse product or service distributed by Teradata, Inc. of Miamisburg, Ohio. 
         [0014]    The data store includes a variety of enterprise information. One type of information is referred to as an “entity.” An entity is something that can be uniquely identified (e.g., a customer account, a customer name, a household name, a logical grouping of certain types of customers, etc.). 
         [0015]    The use of “entity” and “element” may be used interchangeably herein and below. Additionally, the use of “data store” and “database” may used interchangeably herein and below. 
         [0016]    A table within the data store may include a schema that defines the relationship between one or more elements in the data store. For example, the relationship between data store element “household” to element “individual” and to element “account” (household-&gt;individual-&gt;account). The schema defines the fields or elements of the data store. The data store includes a plurality of different tables and different schema&#39;s. Schema relationships may be hierarchical or many-to-many relationships. 
         [0017]    A “relational object” or a “relationship object” is a special grouping of database elements selected and defined by a user. The phrases “database object,” “relational object,” and “relationship object” may be used interchangeably herein. 
         [0018]    Each element or relational object includes attributes and/or properties. Attributes and properties are values associated with an element or object, types of values, metadata about values, and the like. For example, an attribute/property of an element “house” may include a “color type” for the house, such as red. Metadata may indicate the house was painted red on a certain date and by a certain company. 
         [0019]    It is also noted that tables can be viewed as “objects” as presented herein and below. 
         [0020]    It is within this context that the processing associated with the data cross-referencing service is now described in detail with reference to the  FIG. 1 . 
         [0021]    At  110 , the data cross-referencing service interacts with a user to identify a link between a first element of a first database table or first database object and a second element of a second database table or second database object. So, two different database elements are linked to one another via instruction from the user. 
         [0022]    According to an embodiment, at  111 , the data cross-referencing service interacts with the user via a Graphical User Interface (GUI) tool that includes user-guided fields to receive the first element and the second element to link them together or to receive the link. Some example screen shots of such a GUI tool are provided below with reference to the  FIGS. 4-11 . 
         [0023]    In another case, at  112 , the data cross-referencing service inspects metadata associated with the link to identify the first element and the second element and their relationship (linkage) to one another. So, metadata maintained with the link can uniquely identify the first element and the second element. 
         [0024]    Continuing with the embodiment of  112  and at  113 , the data cross-referencing service acquires a first key for the first element and a second key for the second element from the metadata. The keys are the data components that uniquely identify each particular element and its particular source database table. 
         [0025]    At  120 , the data cross-referencing service cross references both the first element and the second element to a master element in a master table. The first database table, the second database table, and the master database table are all different from one another. 
         [0026]    In an embodiment, which is a continuation of  113  and at  121 , the data cross-referencing service inserts the first key and second key in the master table with a master key assigned to the master element. This metadata association of keys provides the cross referencing mechanism. 
         [0027]    In another situation, at  122 , the data cross-referencing service receives an indication from the user to associate the link with the master element. Thus, the user can instruct the data cross-referencing service to make the cross reference assignment. This can be done via a GUI tool, such as the GUI tool discussed below with reference to the system  300  of the  FIG. 3  and the screen shots provided below with the discussion of the  FIGS. 4-11 . 
         [0028]    According to an embodiment, at  123 , the data cross-referencing service user to add new attributes or new properties to the master element that is then automatically associated with both the first and second elements. It is noted that the new attributes or new properties may not have been associated with either the first element or the second element. 
         [0029]    It is also noted that there can be more then two elements mapped and cross referenced to the master element. The discussion of two elements was presented for purposes of ease of comprehension and illustration only. So, in some cases there can be a third element from a third database table that may be linked to just the first element, just the second element, or both the first and second elements. The third element is also cross referenced with the master element in the master table. This can be expanded to any N elements. 
         [0030]      FIG. 2  is a diagram of a method  200  for cross referencing data, according to an example embodiment. The method  200  (hereinafter “cross-referencing service”) is implemented in a machine-accessible or a computer-readable storage medium as instructions that are executed by a machine (one or more processors) and perform the processing reflected in  FIG. 2 . Again, the machine is specifically configured to process the cross-referencing service. The cross-referencing service may also be accessible over a network. The network may be wired, wireless, or a combination of wired and wireless. 
         [0031]    The cross-referencing service presents an alternative and in some cases an enhanced processing perspective to the data cross-referencing service represented by the method  100  of the  FIG. 1 . Specifically, the cross-referencing service provides user interaction with a graphical presentation of data from a database to cross reference data. 
         [0032]    At  210 , the cross-referencing service presents a graphical presentation showing a link between a first database object from a first database table to a second database object from a second database table. The graphical presentation is the data represented a graph objects, such as boxes, circles, etc. and labeled for user comprehension. 
         [0033]    According to an embodiment, at  211 , the cross-referencing service uses visual distinctions to highlight the link in the graphical presentation for the user to readily identify. The visual distinctions can include unique colors, unique shapes, unique visual effects, and the like. 
         [0034]    At  220 , the cross-referencing service receives, as directed by the user, a cross reference between the link and a master database object from a master database table. 
         [0035]    In an embodiment, at  221 , the cross-referencing service permits the user to interact with the graphical presentation to draw a line within the graphical presentation from the link to the master database object. So, the user, via a selection device, draws on the graphical presentation to establish the cross reference from the link to the master database object. 
         [0036]    According to an embodiment, at  222 , the cross-referencing service permits the user to select the link, the first database object, or the second database object within the graphical presentation and display attributes or properties associated with that selection. So, the component graphical objects of the graphical presentation can be selected and brought into focus by the user and then information associated therewith presented in a popup window to the user. 
         [0037]    Continuing with the embodiment of  222  and at  223 , the cross-referencing service allows the user to modify or delete selective ones of the attributes or properties from within the graphical presentation. 
         [0038]    Similarly and continuing with the embodiment of  222  and at  224 , the cross-referencing service allows the user to add a new attribute or a new property to the selection. 
         [0039]    At  230 , the cross-referencing service automatically updates metadata in the master database table to associate the first database object and the second database object with the master database object. 
         [0040]    In a particular situation, at  231 , the cross-referencing service adds a first database object identifier that uniquely identifies the first database object and a second database object identifier that unique identifies the second database object to the metadata. This information is then associated with a master database object identifier that uniquely identifies the master database object. 
         [0041]      FIG. 3  is a diagram of a data cross referencing system  300 , according to an example embodiment. The data cross referencing system  300  is implemented in a machine-accessible and/or computer-readable storage medium that is executed by one or more processors and is operational over a network. The network may be wired, wireless, or a combination of wired and wireless. 
         [0042]    In an embodiment, portions of the data cross-referencing system  300  implements, among other things the data cross-referencing service and the cross-referencing service represented by the methods  100  and  200  of the  FIGS. 1 and 2 , respectively. 
         [0043]    The data cross referencing system  300  includes a GUI tool  301  and a cross-referencing service  302 . Each of these and their interactions with one another will now be discussed in turn. 
         [0044]    The GUI tool  301  is implemented in a computer-readable storage medium and is to execute on one or more processors of a network. Example aspects of the GUI tool  301  were presented above with reference to the methods  100  and  200  of the  FIGS. 1 and 2 , respectively. Moreover, example screen shots of the GUI tool  301  for sample data are provided below with reference to the  FIGS. 4-11 . 
         [0045]    The GUI tool  301  is configured to interact with a user for identifying a link between a first relational object and a second relational object. 
         [0046]    According to an embodiment, the first relational object is selected from a first database table and the second relational object is selected from a second database table. The first and second database tables different from one another. The first and second database tables are also different from a master database table having the master database object (discussed below). 
         [0047]    In one situation, the GUI tool  301  is further configured to receive indications from the user to cross reference the first relational object and the second relational object with the third relational object. 
         [0048]    Additionally, in some cases, the GUI tool  301  is configured to permit the user to modify existing attributes or existing properties associated with the first relational object, the second relational object, and the link. 
         [0049]    In still more situations, the GUI tool  301  is configured to permit the user to add new attributes or new properties to the master relational object. The new attributes or new properties are automatically associated with the first relational object and the second relational object on the direction of the user. 
         [0050]    In another scenario, the GUI tool  301  is configured to permit the user to add new attributes or new properties to the master relational object. In this scenario, the new attributes or new properties are just associated with the master relational object and not associated with the first relational object and also not associated with the second relational object on the direction of the user. 
         [0051]    The cross-referencing service  302  is implemented in a computer-readable storage medium and is to execute on one or more processors of the network. Processing aspects of the cross-referencing service  302  were presented above with reference to the methods  100  and  200  of the  FIGS. 1 and 2 , respectively. 
         [0052]    The cross-referencing service  302  is configured to interface and interact with the GUI tool  301  to identify the link and cross reference the first relational object and the second relational object to a third relational object. 
         [0053]    Now an example scenario for cross referencing data according to the teachings presented herein is presented for further illustration. 
         [0054]    Assume that in an enterprise, a single item paper is referenced as copying accessories and is also referenced as office stationary. Unless there is a cross system reference maintained and duplicates identified correctly, the enterprise would identify these items uniquely hence resulting in a wrong accounting/reporting. 
         [0055]    The techniques presented herein provide a feature where the data cross referencing system identifies and stores such cross references. 
         [0000]    
       
         
               
             
               
               
               
             
               
             
               
               
               
             
               
             
               
               
               
             
               
             
               
               
               
             
           
               
                   
               
             
             
               
                 Item Master 
               
             
          
           
               
                   
                 Item_ID 
                 Item_Desc 
               
               
                   
                   
               
               
                   
                 999 
                 Paper 
               
               
                   
                   
               
             
          
           
               
                 Item Legacy 
               
             
          
           
               
                   
                 Item_ID 
                 Item_Desc 
               
               
                   
                   
               
               
                   
                 777 
                 Copying Accessories 
               
               
                   
                   
               
             
          
           
               
                 Item MF 
               
             
          
           
               
                   
                 Item_ID 
                 Item_Desc 
               
               
                   
                   
               
               
                   
                 666 
                 Office Stationary 
               
               
                   
                   
               
             
          
           
               
                 Item CrossReference 
               
             
          
           
               
                   
                 Golden_Item_ID 
                 Item_id 
               
               
                   
                   
               
               
                   
                 999 
                 666 
               
               
                   
                 999 
                 777 
               
               
                   
                   
               
             
          
         
       
     
         [0056]    The techniques provided herein support the following for cross referencing data:
       1. Create, edit, delete of cross reference.   2. Add, edit, delete of cross reference data.   3. Add additional attributes.       
 
         [0060]      FIGS. 4-11  are sample Graphical User Interface (GUI) screens for cross referencing data described in  FIGS. 1-3 , according to an example embodiment. 
         [0061]      FIG. 4  depicts a GUI tool screen shot for defining a cross reference between objects (master tables) involved. One is a golden master and one or more source tables. Columns are also defined for the source tables, which can be used for value comparisons (matching) and for data linkages. 
         [0062]      FIG. 5  depicts a GUI tool screen for defining additional attributes for the cross reference defined in the sample depicted in the  FIG. 4 . 
         [0063]      FIG. 6  depicts a GUI tool screen for selecting the golden record and then selecting the record from the source tables and linking them. 
         [0064]      FIG. 7  depicts a GUI tool screen for selecting valid data from the source records and combining the data collected from the various source records and creating a golden record, then linking this to the source records. It is noted that the cross reference can also happen through a record linking User Interface (UI). 
         [0065]      FIG. 8  depicts a GUI tool screen for adding data reflecting additional attribute properties. 
         [0066]      FIG. 9  depicts a GUI tool screen for viewing the cross reference data; here, in the  FIG. 9  the cross reference is selected. 
         [0067]      FIG. 10  depicts a GUI tool screen to actually view the data associated with the selected cross reference of the  FIG. 9 . 
         [0068]      FIG. 11  depicts a GUI tool screen for viewing additional attributes of the cross reference selected in the  FIG. 9 . 
         [0069]      FIG. 12  is a relationship data model for a data cross referencing system, according to an example embodiment. 
         [0070]    The Cross Reference table contains all the cross reference details along with its golden table. XREF REF Rel table contains the various relationships that would constitute a cross reference. 
         [0071]    Now some example source code that can be used to implemented portions of the techniques presented herein are presented. It is noted that other implementations that achieve the teachings presented herein and above can be used without departing from the embodiments of the invention. The sample JAVA® code is used to populate cross reference tables. 
         [0072]    Java Class Application Programming Interface (API) code sample: 
         [0000]    
       
         
               
             
           
               
                   
               
             
             
               
                 public class CrossReferenceOperations 
               
               
                 { 
               
               
                 public static XMLform createCrossReference(XMLform 
               
               
                 crossReferenceInformation, OmxContext context) 
               
               
                   { 
               
               
                     //This Method will call addCrossReference( ) to populate cross 
               
               
                     ref //and cross ref relation table. 
               
               
                   } 
               
               
                 private static 
               
               
                 String addCrossReference(XMLform goldenObjectForm,String 
               
               
                 strGoldenRold,OmxContext context) 
               
               
                   { 
               
               
                     //This will insert Record in CrossReference and return Cross 
               
               
                     //Reference Id. 
               
               
                   } 
               
               
                 private static 
               
               
                 XMLform addRelationalObjectMap(String sROMID ,String 
               
               
                 sParentRold,String sChildRold,String sRelationName,XMLform 
               
               
                 additionalAttributeForm,String strXRefId,OmxContext context) 
               
               
                   { 
               
               
                     //This will add one record in ROM in case of cross reference 
               
               
                     //creation 
               
               
                   } 
               
               
                 private static 
               
               
                 XMLform addCrossReferenceRelation(String strXRefId,String 
               
               
                 sROMID,OmxContext context) 
               
               
                   { 
               
               
                     // This will add Record in CrossRefRelation 
               
               
                   } 
               
               
                 private static XMLform add RelationalObjectProperties(XMLform 
               
               
                 additionalAttributeForm,String sROM_ID,OmxContext context) 
               
               
                   { 
               
               
                     //This will insert Record in RelationalObjectProperties for Cross 
               
               
                     //Reference creation 
               
               
                   } 
               
               
                 private static String generateId(String strSequenceName ,OmxContext 
               
               
                 context) 
               
               
                   { 
               
               
                     //This will generate Sequence Id for a given sequence name 
               
               
                     from 
               
               
                     // ID_SQR Table 
               
               
                   } 
               
               
                 private static int getKeyColumnSequenceUsingROKTId(String 
               
               
                 sROKTId,OmxContext context) 
               
               
                   { 
               
               
                     //This will add Record in CrossRefRelation 
               
               
                   } 
               
               
                 public static XMLform createCrossReferenceFromUI(XMLform 
               
               
                 crossReferenceInformation, OmxContext context) 
               
               
                   { 
               
               
                     //This will create cross reference in case of customRodv 
               
               
                   } 
               
               
                 private static XMLform addRelationalObjectMapFromUI(String sROMID 
               
               
                 ,String sParentRoId,String sChildRoId,String sRelationName,XMLform 
               
               
                 additionalAttributeForm,String strXRefId,OmxContext context,XMLform 
               
               
                 goldenObjectMapColumnForm,XMLform 
               
               
                 crossReferenceMapColumnXml,XMLform goldenObjectForm) 
               
               
                   { 
               
               
                     //This will add one record in ROM in case of cross reference 
               
               
                     //creation for customRodv 
               
               
                   } 
               
               
                 } 
               
               
                         © Teradata, Inc. 2009 
               
               
                   
               
             
          
         
       
     
         [0073]    One now fully appreciates the improved techniques for cross referencing presented herein and above. Specifically, the techniques presented herein directly integrate the management of cross referencing data. The allows database viewers to view, visualize, and interact with data cross referencing from a single data warehouse, while maintaining automated control of the underlying data of the database. 
         [0074]    The above description is illustrative, and not restrictive. Many other embodiments will be apparent to those of skill in the art upon reviewing the above description. The scope of embodiments should therefore be determined with reference to the appended claims, along with the full scope of equivalents to which such claims are entitled. 
         [0075]    The Abstract is provided to comply with 37 C.F.R. §1.72(b) and will allow the reader to quickly ascertain the nature and gist of the technical disclosure. It is submitted with the understanding that it will not be used to interpret or limit the scope or meaning of the claims. 
         [0076]    In the foregoing description of the embodiments, various features are grouped together in a single embodiment for the purpose of streamlining the disclosure. This method of disclosure is not to be interpreted as reflecting that the claimed embodiments have more features than are expressly recited in each claim. Rather, as the following claims reflect, inventive subject matter lies in less than all features of a single disclosed embodiment. Thus the following claims are hereby incorporated into the Description of the Embodiments, with each claim standing on its own as a separate exemplary embodiment.