Creating reference data sets with composite keys

Reference data values in a set are typically identified by a unique code. However, you might need to identify data by using more than one column. A composite key is a combination of the code column and up to 5 custom columns in a reference data set. Composite key is then used to uniquely identify each reference data value.

Required permissions To create, edit, or delete reference data sets, you must have this user permission:

• Access governance artifacts

Additionally, you must have one of these category collaborator roles in the primary category for the reference data set:

• Admin
• Owner
• Editor
• A custom role with the permission to create reference data sets.

A composite key is used to uniquely identify each reference data value. With a composite key, the values in the code column no longer need to be unique. Uniqueness is only guaranteed when the values of all the specified columns are combined - the values in individual columns are not necessarily unique.

When you define a composite key for the set, each reference data value in such a set is identified in the system by a physical representation of the composite key, which is a concatenation of the code column and the composite key custom columns in the order that was specified when the set was initially created. The values are delimited by |, for example: CODE|CC1|CC2|CC3. This physical representation is used to identify reference data values in the system (for example, to track relationships) and it cannot be changed.

The following restrictions apply for the custom columns that are used to create a composite key:

• Composite key columns can only be added to new or empty reference data sets. If a set already has reference data values added, you cannot define a composite key.
• Custom columns that you want to validate must be of type Text.
• There can be up to 5 custom columns added to the Code column to comprise a composite key. The code column is always first.
• If the custom column is part of the composite key, it must be set as mandatory.
• When reference data values are added to the set, the composite key can no longer be modified. If change is needed, you must create a new reference data set.
• The composite key column can be up to 200 characters long. Custom columns that are not part of the composite key have the limit set to 2000.
• Characters | and UNIT SEPARATOR (ASCII code 0x31) must not be used in the code column or in the custom columns used to create the composite key. This character is used to separate composite key values when creating a physical representation of the composite key in the database. This restriction only applies to API v4.

To create a reference data set with a composite key using a CSV file with values:

1. Choose Governance > Reference data, then click Add reference data set.

2. Click New reference data set and specify the following in the Details panel:

   1. Upload the CSV file.
   2. Provide a name for the reference data set.
   3. Specify the type of the values: Text, Number, or Date.
   4. Select the category to create the reference data set in.
   5. Optionally, provide a description.

3. In the Map columns panel, map the columns from your CSV file to the target columns:

   1. Specify whether the first row in a CSV file should be used as a column header instead of the first reference data value.

   2. Map the columns from your file one by one. The preview of the uploaded column is displayed. If the predefined columns do not meet your needs, select New custom column.

   3. For each custom column, specify:

      • Name: The column name must be unique within a given reference data set.
      • Type: Text, Number, or Date.
      • Description: optional.
      • The maximum number of characters to use for a record in a column. The limit for a composite key column is 200. For any other columns the limit is 2000. This limit can be changed only when the reference data set is empty. When values are added in the set, you can only increase the limit.
      • Optionally, select the reference data set that you want to use for validation - the values in the column will be validated against the CODE column in the selected reference data set. If a custom column value is invalid for any reason, the reference data value is not created or imported and a warning is added to the report.

      Note:

      • The reference data set that you choose to be a validator can no longer be removed unless validation against this set is removed.
      • The validator for a column can be set or modified only if the reference data set is empty.
      • You can only validate the columns of type Text, because the CODE column that you are validating against is always of this type.

   4. If you want the custom column to be part of the composite key, select Part of composite key. The column is then automatically marked as Mandatory, which means that the values cannot be empty.

4. When all columns are mapped, review the information in the Review panel. Note that at this point you can reorder the composite key columns to match your requirements. The order of the columns set in this panel influences how the physical representation of the composite key is formed in the system. For example, if the columns in this panel are listed in this order:

   • custom column 1
   • custom column 2
   • custom column 3

   Then the physical representation of the composite key for this reference data values is formed as CODE|CC1|CC2|CC3. When you add values to the reference data set, this order can no longer be changed. You can rearrange the columns in the UI when viewing the set, but it does not influence the actual composition of the composite key. Compare the order of the columns to the order specified in the CSV file and modify it if needed.

5. Click Create. The reference data set is saved as draft.

Note: When working with the CSV files with reference data values, ensure that the parent relationship between the values is represented as concatenation of CODE and composite key custom column values delimited by |. Code always goes first, then composite key column values. Ensure that the order of these columns is the same in the CSV file and in the summary panel of the creation or import wizard.

When you have finished, decide whether to:

• Close the new reference data set in its draft state.
• Click Delete draft to delete the new reference data set.
• Depending on your workflow definition, click Publish or Send for approval to process the reference data set.