The DataBee Chain Finder Tool
Extraction set rules tell the DataBee software how the tables involved in the set are related to one another. A rule says "for every row in this table, make sure all supporting rows are retrieved from that table". Rules, particularly those derived from the database foreign key constraints, usually form a web of inter-related dependencies. The complexity of these dependencies and the relationships they form is such that, unassisted, it can be very difficult to understand the interactions between tables more than one or two levels distant.
The purpose of the Chain Finder tool is to provide a visual indication of the relationships between tables and to suggest possible candidates for manual rules. The Chain Finder is a very important tool which can greatly speed up the building of extraction sets. The Set Designer application also contains a Who-Loads-What Tool which presents an alternative perspective on the table relationships inherent in the extraction set rules.
The Chain Finder Tool is quite easy to use and the button to launch it can be found on the Rule Tools tab of the Set Designer application. Shortly after the tool starts, the panel at the left will fill with a list of each table involved in the extraction set. Associated with each table is the "Children" column. The Options button below the table name panel can be used to include the source row count, extracted row count and plan information as columns in the table display.
Clicking on a table name in the left hand panel will cause a tree display to appear in the right hand panel. This tree shows all dependent tables and their relationships. In the screen shot above, the DTB_INVOICE_LINE table has been selected. The right hand panel indicates that the DTB_INVOICE_LINE table is directly related to the DTB_CUSTOMER, DTB_INVENTORY_ITEM, DTB_INVOICE and DTB_WAREHOUSE tables. In other words, Table-To-Table relationships exist in which the DTB_INVOICE_LINE table is the source and the DTB_CUSTOMER, DTB_INVENTORY_ITEM, DTB_INVOICE and DTB_WAREHOUSE are the targets. It is possible that reverse relationships also exist between these tables (ie DTB_INVOICE is the source and DTB_INVOICE_LINE is the target). The Chain Finder tool does not indicate this on the above display and could only do so if the DTB_INVOICE table was selected in the left hand panel.
The display shows that DTB_INVOICE_LINE table is directly related to the DTB_CUSTOMER, DTB_INVENTORY_ITEM, DTB_INVOICE and DTB_WAREHOUSE tables, however, the subsequent dependents of each directly dependent table are also shown as a tree. Thus we can see that there is a many direct chains of relationship. In complex schemas it is not uncommon to see chains of 50 or 100 tables. One example of such a relationship in the screen shot above is the chain: DTB_INVOICE_LINE -> DTB_CUSTOMER -> DTB_POSTCODE. One way to think of this relationship is to note that if rows are extracted for the DTB_INVOICE_LINE table then rules will automatically activate to extract rows for the DTB_CUSTOMER and then the DTB_POSTCODE table.
The symbol [+...] which appears in the tree view indicates that the table has previously appeared at a higher level and will not have its dependant tables listed again. In practice the [+...] symbol indicates the presence of a "loop". Such loops are quite common in complex extraction sets. In the above example, it can be seen that the DTB_INVENTORY_ITEM table is related to itself via a self-referential relationship.
The View Rule button below the right hand panel will launch a form which displays the contents of the relationship between the two tables. A new rule, the opposite of the existing relationship, can be created by pressing the Clone and Reverse Rule button. This is an extremely useful way of building new Table-To-Table rules which enforce logical relationships.
Some rules are based on foreign keys while others are manually added. The default display illustrates table relationships derived from both sources. If you wish to see the table relationships which are based on foreign keys check the Show only FK Based Relationships option.
How to use the Chain Finder Tool
The Children column indicates the number of dependencies a table has. The Children column is a basic indicator of the number of tables likely to get populated if the parent table is populated. A review of the number of Children column will quickly isolate the "fundamental" tables of the rule set. A highly effective technique in designing extraction sets is to build Table-To-Table rules which populate a small number of the tables with the largest number of children. Once those tables have extracted rows the dependent rules must activate and they will then automatically extract rows and populate all of the dependent tables.
To implement the above technique, first sort the Children column by clicking on it with the mouse. This will move the tables involved in the most number of relationships (both direct and indirect) to the top of the panel. The table with the largest number of children, which does not have any rows extracted for it is then selected. This causes the relationship chains of that table to be displayed in the right hand panel. If there are any directly related tables which have extracted rows, then that table can be selected in the right hand panel. The Clone and Reverse Rule button is used to bring up the New Table-To-Table Rule form. The relationship will be reversed. The target table from the right hand panel will be used as the source table in the proposed new Table-To-Table rule. If the rule seems sensible, it can be added. Once the new rule has been added, the extraction set is usually run and the effectiveness of the rule is determined. In theory, the newly added rule should extract rows for the table at the top of a large chain of dependencies. The many rules in the relationship chains below it should then activate and the addition of a single rule could cause multiple tables to obtain referentially related extracted rows.