Artificial Key
By default, Normalized Tables in QAD Data Warehouse Designer do not have an artificial key (artificial keys can be added manually, see
Normalized Table Artificial Keys (on page
413) for more details).
An artificial key is the unique identifier that can be used to join a Normalized Table record to other Normalized Tables. When joining Normalized Tables it would be possible to perform the join using the business key. For Normalized Tables that satisfy one of more of the following conditions, joining with business keys could result in slow query times and excessive use of database storage:
Multiple column business keys (excessive storage and multiple column joins)
One or more large character business key columns (excessive storage)
Very large tables (excessive storage - integer artificial keys often use less space than one small character field)
History Normalized Tables (complex joins involving a between dates construct)
As query time is one of our key drivers in data warehouse implementations the best answer is often to use some form of artificial key. A price is paid in the additional processing required doing key lookups, but this is offset by the reduced query times and reduced complexity.
The artificial key is an integer and is built sequentially from 1 upwards. See the section on artificial keys for a more detailed explanation. An artificial key is sometimes referred to as a "surrogate" key.
Building a Normalized Table
Normalized tables are often sourced from one table in the base application. The process for building a normalized table begins with the drag and drop of the load or stage table that contains the bulk of the normalized table's information.