Taming the animal – Qualified Tables

Where do we use the Qualified tables:

At times data is stored in such a way that duplication is unavoidable due to the storing mechanism and other factors. It may also happen that the data is sparse. The efficient way of storing data in such scenarios is the use of Qualified tables as it reduces the size of the main table and removes the unnecessarily created duplicates.

Difference between Qualified lookup table and Flat lookup table:

Flat lookup tables normally work on only a single field. It contains the set of legal values to which corresponding lookup field in the main table is assigned.

E.g. suppose we have a flat lookup table for Material Group for an automobile manufacturing company. Here we have a fixed set of legal values that can be looked up into the main table (for example Cars, Bikes, SUVs, etc.)

Qualified tables work on the combination of two types of fields where value of one or more fields (qualifiers) changes depending on the values of one or more other fields (non-qualifiers).

E.g. suppose we have a field “Price” whose values varies with the region for the same product. Here we have a fixed set of related legal values (combination of fields) that can be looked up into the main table. (For example Product A has a price of $30 in Southern region whereas $40 in Central region, then we get a set like Product A | Southern | $30, Product A | Central | $40 in the Lookup Qualified table)

Watch out for the definition of Qualifiers and non-Qualifiers:

This was something which took a long time for me to hunt down. Qualifiers – are those fields whose values change based on the value of some other field(s) and whose value is different for each main table record Non Qualifiers – These fields are only the part of qualified lookup table, but they apply not only to the qualified table but also to each association of the qualified table record to the main table record. Simply speaking, they are the fields that will decide the values in the Qualifier (main table)fields.

Our Scenario:

Suppose we have been provided with the following source data –

Manufacturer Name of Drug Region Price Storage Temp

A Crocin North 280 27 Celsius

A Crocin South 300 14 Celsius

A Crocin Central 260 30 Celsius

A Disprin North 380 30 Celsius

A Disprin South 360 27 Celsius

A Disprin Central 320 14 Celsius

Observation:

Values of ‘Price’ and the ‘Storage Temp’ field change based upon the ‘Name of Drug’ and ‘Region’ field values and also their values are different for each main table record; hence these should be madequalifiers in the qualified lookup table Since ‘Name of Drug’ and ‘Region’ fields are deciding the qualifier values hence these should be kept as non qualifiers in the qualified lookup table. ‘Manufacturer’ and ‘Price’ will be part of the main table.

Design of repository:

We will discuss only the design of the main table ‘Products’ and the Qualified lookup table ‘Prices’ as they are of our immediate concern. Products

Note: Caching of qualifiers is strongly recommended as it dramatically improves search performance. Once the design of the repository is through, we now move on to the trickiest area – the one of the Import Manager

Importing records:

As per the standard practice, we load the lookup table first, here ‘Prices’ and then the main table ‘Products’. The source preview is as shown: Loading the Qualified lookup table data:Select the corresponding tables in the source and destination hierarchy in the Import manager. Go to Map Fields/Values tab and Map the corresponding fields in the field mapping grid. Note: Here we can see only two fields (non qualifiers) in the destination field grid, however in the repository design we have created four fields. The qualifiers (fields) never appear in the destination field of the qualified lookup table in the Import Manager.Go to Match Records tab and select the non-qualifiers as matching field and Add it as Combination. Select Import Action as Create as it is an Initial load of data. Loading the Main table data:Map all the source fields to corresponding fields in the repository. Notes:Here we see many fields in destination (including qualifiers (Q) which are defined in the qualified table and not in the main table of the repository structure in Console). Minimum required field mapping: All non-qualifier fields of the qualified lookup table should be mapped. Qualifier field (Q) is optional. Now we are left with one field (Lookup [Price]) in destination which is still unmapped and we have no corresponding field left in the source to map it with. For this, go to source field tab and right click to create compound field as shown – Lookup [Price] will be available in the source field and will get automatically mapped to the corresponding destination field. Note: If any one of the non-qualifier fields of the qualified lookup table is not yet mapped, we are not allowed to create the compound field. Do the Record Matching and Import the data into Data Manager Client by selecting the appropriate Import action.