Set Key Columns to Uniquely Identify Records

Set one or more columns as keys to uniquely identify records in an entity. Key columns cannot contain null values and each value (or set of combined values for multi-column compound keys) must be unique.

Set Columns as Keys

To set a column as a key, select the checkbox in its Key column. You can set one column as a key or, if necessary, set multiple columns to define a compound key (see Compound Keys and Representative Keys).

Key columns cannot contain null values and each value (or set of combined values for multi-column compound keys) must be unique. To validate the data in your key columns, see "Key Validation Rules" in Validating View Data.

Keys and Associations

It is important to set the necessary keys for your entity, based on its Semantic Usage, and to be aware of the requirements for mapping to these keys when creating associations:

Semantic Usage

Keys Required

Mapping Keys When Creating Associations

Fact

See Create a Fact to Contain Measurable Data.

You may set one or more key attributes for a Fact, but none are required.

A Fact can point to a:
  • Dimension - One attribute in the (source) Fact must be mapped to each (target) Dimension key column so that all target key columns are mapped.

  • Text Entity - One attribute in the (source) Fact must be mapped to each (target) Text Entity identifier key column.

Dimension

See Create a Dimension to Categorize Data.

You must set one or more key attributes for a Dimension:
A Dimension can point to a:
  • Dimension - One attribute in the (source) Dimension must be mapped to each (target) Dimension key column so that all target key columns are mapped.

  • Text Entity - One attribute in the (source) Dimension must be mapped to each (target) Text Entity identifier key column.

  • Hierarchy - Each key attribute in the (source) Dimension must be mapped to a (target) Hierarchy child attribute key column.

Fiscal Time Dimension

See Create a Fiscal Time Dimension.

You must set exactly two key attributes for a Fiscal Time Dimension:
A Fiscal Time Dimension can point to a:
  • Dimension - One attribute in the (source) Dimension must be mapped to each (target) Dimension key column so that all target key columns are mapped.

  • Text Entity - One attribute in the (source) Dimension must be mapped to each (target) Text Entity identifier key column.

Text Entity

See Create a Text Entity for Attribute Translation.

You must set two or more key attributes for a Text Entity:

A Text Entity must not point to other entities.

Hierarchy

See Create an External Hierarchy for Drill-Down.

You must set one or more key attributes for a Hierarchy:
  • Child attributes of the parent-child hierarchy structure

A Hierarchy will generally not point to other entities.

Hierarchy with Directory

See Create a Hierarchy with Directory.

You must set exactly two key attributes for a Hierarchy with Directory:
  • Child attribute of the parent-child hierarchy structure

  • Hierarchy name attribute

A Hierarchy with Directory must point to:
  • A Dimension acting as its directory - The hierarchy name attribute in the (source) hierarchy entity must be mapped to the primary key column in the (target) dimension.

  • Any Dimension (other than the data node dimension) providing nodes to the hierarchy - The appropriate node type values columns in the (source) hierarchy must be mapped to the key columns in the (target) Dimension.

Relational Dataset

You may set one or more key attributes for a Relational Dataset, but none are required.

A Relational Dataset can point to any other entity and should generally follow the rules for dimensions.

See also Create an Association to Define a Semantic Relationship Between Entities.

Compound Keys and Representative Keys

If you set more than one key column to uniquely identify records in a dimension, you should, in addition, specify a representative key, which is the column containing the most granular level of data. Specifying a representative key allows analytic tools to correctly consume the dimension.

For example, in the Cost Center table, both the Controlling Area and Cost Center column are set as key columns and the Cost Center column is set as the representative key:

Controlling Area

Cost Center

Name

1

1000

Development

1

1001

Support

1

1002

Sales

1

1003

Training

2

2000

Development

To define a compound key and set its representative key:
  1. Open your table or view and set its Semantic Usage to Dimension.

  2. Set two or more columns as keys.

  3. In the Attributes section toolbar, click (Edit Compound Key) to open the Compound Key dialog.

  4. Select a key in the Key Columns section and click Add to identify it as the representative key and move it to the Representative Key section.

  5. [optional] Reorder keys in the Key Column(s) section by clicking (Move Up) and (Move Down).

    The Compound Key field shows the order of the key columns, with the representative key shown last and in bold.

  6. Click Close to complete the definition of the compound key.