Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Accepted Solutions
A method I've used in the past to evaluate the potential for synthetic keys BEFORE incorporating a variety of tables with very large field lists into the application, is to make a table around the metadata itself.
At a minimum, a two column table listing TABLE and FIELD of all the fields you are targeting for a data model should be enough to create a pivot table or list box which illustrates which of your field names are repeating across multiple target source tables. From there the concept could be expanded to incorporate SCHEMA and ENVIRONMENT but for a simple exercise the TABLE/FIELD combo will be sufficient. This becomes very useful if your data model could span thousands of distinct fields using potentially repeating names across any number of tables. What this can also do, is if you are new or unfamiliar to the data model, yield some insight as to which are the likely components of compound keying systems across tables.
The example in the image above has more than two possible TABLES in selection. Now if the number of possible tables were reduced to just two, it should produce an exact listing of the synthetic key components between them:
Once you have identified a listing of repeating field names from your targeted table list, it becomes a matter of evaluating the repeaters for whether you want to deliberately include synthetic (compound) associations, whether it is time to create single field keys based on combining compound values into a single field, whether aliasing will deactivate the synthetic possibilities, or whether the repeating fields are redundant or unnecessary and can be dropped from the data model altogether
I think we all at one point or another have experienced a script execution that has frozen upon completion, not because the script syntax was invalid in any way, but because the creation of very large synthetic keys consumes the cpu/memory upon completion and freezes the application. It can either take a very long time to release the script, or the application has to be restarted. If you have never experienced this, then you can skip this last section.
Even if you've been very diligent about checking for synthetic keys, a safe way to test the data model would be to run the script in the debugger with a zero row reload. This may allow simulating very large or complicated synthetic key structures without consuming processor or memory.
