Complex data models with hunderds of tables can be challenging to classify correctly! Luckily DATPROF Subset comes with some handy tools to improve your classification. Often the Suggest wizard will come up with a good first classification, but there are some other features we can use as well. 

  • Move all the table to the Subset classification, except the start table
  • Select all tables and right click Count rows

In the Gasware Training database there are no empty tables., but typically you should move all the tables that contains no rows to the empty classification. Often a small amount of rows is a good identifier for a type/category table that should be classified as Full. 

Under each classification is a small validate button. Clicking this button will validate the classification in relation to other tables

Subset validation: If a subset classified table has no direct or indirect relation with the start or another subset table, this will result in a warning. In that case, there is no criteria on how this table can be filtered. Classify the table as full or empty, or manually add the missing foreign key.

Full validation: If a full classified table references tables that are classified as start, subset or empty, this will result in a warning. DATPROF Subset will copy all the data, but this will probably include data that reference data that is filtered out by the start, subset or empty classification. In database terms this is a referential integrity issue. The child records is referring to missing parent records. This will cause foreign key violations. 

Empty validation: If an empty classified table is referenced by tables that are classified as start, subset or full, this will result in a warning. Subset will not copy any data, but others table probably include data that reference data in the original empty table. This will also lead to referential integrity issues. The parents records are missing, but the child records are copied fully or subsetted. 

Examples

For learning purposes, let's try to manipulate the classifications in such a way, that we get classification warnings. 

Subset classification warning

  • Make sure that all tables are classified as Subset and the CUSTOMERS is selected as Start Table
  • Right click the CONTRACT_DETAILS table and click Properties...
  • Go to the Foreign Keys tab
  • Select the FK_CONTRACT_DETAILS_CONTRACTS foreign key and click Delete and Yes to confirm
  • Click Close
  • Now click the Validate button 
    under the subset classification
  • Notice that icon for the CONTRACT_DETAILS table has changed into a warning icon
  • In the upper menu bar, click Visualize → Process model...
  • Click Visualize in the popup confirmation. 
  • Notice that CONTRACT_DETAILS is directly under or above the CUSTOMERS start table and has no dependencies with any other table. 


To fix this, we will add the 'missing' foreign key

  • Close the visualization window
  • Right click the CONTRACT_DETAILS table and click Properties...
  • Go to the Foreign Keys tab
  • Click the Add foreign key button
  • Enter as Foreign key name: FK_CONTRACT_DETAILS_CONTRACTS
  • Choose the parent table schema 
  • Choose CONTRACTS as Parent table 
  • Double click the empty cell under This table and choose CONTRACT_ID
  • Double click the empty cell under Parent table and choose CONTRACT_ID
  • Click OK to add the foreign key and close the constraint editor
  • Click Close to close the properties window
  • Now validate
    the subset classification again. The warning icon will be removed
  • Visualize the process model to confirm that the CONTRACT_DETAILS has a dependency with the CONTRACTS table

Full classification warning

  • Move the CONTRACT_DETAILS to the full classification 
  • Click the Validate button 
         under the full classification and notice how the Warning icon appear for the CONTRACT_DETAILS table

CONTRACT_DETAILS refers to CONTRACTS which is being subsetted (filtered). This means that with the current classification we will probably copy data into the CONTRACT_DETAILS table that refers to the CONTRACT data that is filtered out. This can lead to foreign key violations on the CONTRACT_DETAILS records. 

There are multiple ways of fixing this issue

  • Let's move the CONTRACT table to the full classification and validate
    the full classification again.
  • Notice that CONTRACT_DETAILS is not in warning anymore. The CONTRACTS table however is now in warning. 
  • Right click the CONTRACTS table and go to Properties...
  • Go to the Foreign Keys tab and investigate the different foreign keys on the CONTRACTS table

There are four foreign keys that references the CUSTOMERS, LOCATIONS, PRODUCTS and SERIALS tables. Those tables are currently classified as start and subset. The best option is to move CONTRACTS and CONTRACT_DETAILS to the subset classification

  • Move CONTRACTS and CONTRACT_DETAILS tables back to the subset classification 

Empty classification warning

  • Move the CONTRACTS table to the empty classification
  • Click the Validate button 
         under the empty classification and notice how the Warning icon appear for the CONTRACTS table

The TASKS and CONTRACT_DETAILS tables reference to data in the CONTRACTS table. This means that probably data in the TASKS table misses the parent records in the CONTRACTS table, because it's classified as empty. The CONTRACT_DETAILS in the subset classification also gives a warning when validating, because it has no link to the start or subset tables.

  • To fix this, move the CONTRACTS table back to the subset classification


 



 




If you got stuck in validating your classification, just watch the following video.