The DataBee Set Designer
Bulk Table-To-Table Rule Generator Tool

The DataBee Bulk Table-To-Table Rule form

The necessity of creating multiple rules of a similar format is a fairly common situation in extraction set design. The Bulk Table-To-Table Rule Generator can create multiple Table-To-Table extraction rules using an example rule as a template.

When would the Bulk Table-To-Table Rule Generator Tool be Used?

The Bulk Table-To-Table Rule Generator is useful when an existing rule expresses a referential relationship which could reasonably be used to build similar rules which extract rows from other target tables. The Bulk Table-To-Table Rule Generator Tool uses column name similarity to clone a selected template rule, replace the target table name in the template rule and save the cloned rule to the extraction set.

For example, assume there is a CUSTOMER table which has a column named CUSTOMER_ID. Furthermore assume there are multiple other tables ADDRESS, CONTACTLOG, INVOICES and PAYMENTS all of which have a column called CUST_ID which identifies the customer relationship of the record. If a Table-To-Table rule exists which states that for every extracted CUSTOMER record, extract the corresponding rows from the ADDRESS table where CUSTOMER_ID equals CUST_ID it can be used as a template for further rules. The general process is described below:

1. The existing template rule states: CUSTOMER.CUSTOMER_ID -> ADDRESS.CUST_ID
2. This rule is selected in the rule panel on the Extraction Rules Tab, it will be used as a template to build other rules.
3. The Bulk Table-To-Table Rule Generator Tool is started.
4. Once started, the Bulk Table-To-Table Rule Generator Tool displays all possible alternative target tables which could be used in the template rule. The tables are chosen based on only column name similarity. Thus the CONTACTLOG, INVOICES and PAYMENTS tables will all be displayed because they have a CUST_ID column which matches (in name) the CUST_ID column of the ADDRESS table used in the tempate rule.
5. The box next to the table name can be marked with a check and the Generate Bulk Rules button pressed to clone the template rule, substitute the marked table name in for the ADDRESS table and save those rules in bulk to the extraction set.
6. Assuming the CONTACTLOG, INVOICES, PAYMENTS tables are marked, then once the process is complete, the set of rules shown below will exist.

   CUSTOMER.CUSTOMER_ID -> ADDRESS.CUST_ID
   CUSTOMER.CUSTOMER_ID -> CONTACTLOG.CUST_ID
   CUSTOMER.CUSTOMER_ID -> INVOICES.CUST_ID
   CUSTOMER.CUSTOMER_ID -> PAYMENTS.CUST_ID
   

Important Note: It is not always true that just because a rule can be added with the Bulk Table-To-Table Rule Generator Tool that it is always a good idea to add it. The Bulk Table-To-Table Rule Generator Tool exists to make it easy to create a number of rules simultaneously - however the usefulness of involving each new target table in a relationship with the source table must still be carefully considered on an individual basis. Otherwise far more rows than are desired can be extracted. Please see the discussion of extraction set explosions for more information.

The Bulk Table-To-Table Rule form is started from the Extraction Rules Tab of the Set Designer application. The Bulk Table-To-Table Rule form is only available when an extraction set is open as loader sets do not require multiple rules to be created at the same time.

Before starting the Bulk Table-To-Table Rule Generator, a template Table-To-Table rule must be selected from the list on the extraction rules tab. If a suitable template rule does not exist then one should manually be created first. After startup, the two tables involved in the template rule (and their join columns) are listed on the Template Rule tab located on the right hand side of the Bulk Table-To-Table Rule Generator panel. Also on the right hand side is the Table Filter Options panel which provides methods of removing large numbers of tables from consideration through the use of exclusion criteria. Any combination of the available exclusion criteria can be selected. Changing any of the options in the restriction panel will regenerate the table list panel according to the exclusion criteria.

The Table Name panel is shown at the top left hand side of the form. The left hand column of the panel provides check boxes which can be marked to include the table in the bulk rule generation process. Other columns can also be added to the display by pressing the Options button. These columns can display the source row count, extracted row count and also a column indicating how well that table matches the extraction set plan.

Pressing the Generate Rules button will generate rules, using the template rule as a guide, from the tables selected with a check mark. Note that the Bulk Table-To-Table Rule Generator tool will never add a duplicate rule. If the rule exists it will simply be discarded during the creation process.