About key fields

Several operations in Analytics make use of key fields:

• joining
• relating
• merging
• sorting
• indexing

Depending on which operation you are performing, the term ‘key field’ can have a different meaning, and key fields can have a different function. Key fields in Analytics also differ somewhat from the typical definition of a key field in relational database terminology.

Key fields when joining, relating, or merging

Joining, relating, and merging in Analytics are all data combining operations involving two or more tables. The term ‘key field’, in this context, means the common field in two tables being combined from which values are compared and matched – or in the case of merging, compared and interfiled.

Appending, another method for combining data in Analytics, does not make use of key fields.

Primary and secondary tables and key fields

The first table you open when joining or merging becomes the primary table, and the key field you choose becomes the primary key field. The second table you open becomes the secondary table, and the key field you choose becomes the secondary key field.

When you relate tables, primary is referred to as ‘parent’, and secondary is referred to as ‘child’.

You are free to choose whatever primary and secondary tables and key fields you want when combining data. Analytics does not enforce any particular choice of field, although it does require that key field pairs have an identical data structure.

Unique key and foreign key designation not retained from source data

Data imported into an Analytics table, either locally or on a server, is stored in a non-relational flat file (a .fil file). In a .fil file, fields that may previously have functioned as primary keys, unique keys, foreign keys, or secondary keys in a relational database are not treated any differently from non-key fields.

A primary key from a relational database, such as employee ID, only becomes a primary or parent key in an Analytics table when you designate it as such in an Analytics command. As a user making a decision about how to construct a join or a relation in Analytics, you may need to know which fields were primary or unique keys in a source database. However, Analytics does not contain this information.

The same situation is true when you directly access database tables using an Analytics database profile. Analytics retains no information about which fields are key fields in the database, and you may need to know this information yourself when constructing a database query.

Uniqueness of Analytics key fields not enforced

Analytics does not enforce uniqueness in the key fields you designate in Analytics commands. Identical values can exist in both the primary and the secondary key fields.

Key fields when sorting or indexing

Sorting and indexing in Analytics are single-table operations that impose a sequential order on a table. In this context, the term ‘key field’ means the field upon which the sorting or indexing is based, containing the values that are sorted or indexed.

Equivalent to a ‘sort key’ or an ‘index key’

The sorting or indexing key field in Analytics is equivalent to the ‘sort key’ or ‘index key’ in general computing or database terminology. Uniqueness is not enforced.

You are free to choose whatever key field you want when sorting or indexing data. Analytics does not contain any information about fields that may have been sort or index keys in the original source data, although the values in those fields may still be in sequential order.

Keys and nested sorting or indexing

In the case of nested sorting or indexing, a ‘primary’ sort or index key takes precedence over a ‘secondary’ sort or index key. Primary and secondary keys are established simply by the order in which you select them.