Relationships between reference data sets

Hierarchies and relationships

You can create hierarchies and other relationships for your reference data sets that establish logical connections between the reference data sets and their values.

Hierarchies make searches for reference data sets or values within a set easier and faster. For example, if you were searching data sets or values within a set but no relationship information was available, then you would need to remember the data set context and search the data sets one at a time. However, with hierarchy information you can start with a specific data set and navigate through all its related sets in and around the context of that data set only.

You can also create relationships between values in a reference data set and values in one or more different reference data sets. These relationships are known as value mappings or cross walks.

Setting up relationships this way can help you to understand more easily how values interconnect across reference data sets and reduce the time that you might use searching for these values manually. For example, in the following image you can see that the value United States of America maps to two different values in a different reference data set (soybean farming and agriculture) and another country value of India maps to a currency value in yet another reference data set.

Reference data value mapping

You can work with four different types of relationships between reference data sets and their values:

Reference data set to reference data set hierarchies
Use these hierarchies to create relationships between reference data sets. For example, you might be working with a reference data set for continent codes. You might add a dependent reference data set of country codes to the continent codes set. You might also add a parent reference data set of global codes to the reference data set continent codes. You can assign any number of parent reference data sets.
Reference data set value to reference data set value parent or dependent hierarchies
Use these hierarchies to create relationships between reference data set values within a reference data set. For example, you might be working with a reference data set for North American Industry Classification System (NAICS) codes. The higher-level code values in the set might indicate the industry to which the product belongs. You can create a hierarchy within the set that flows down into sub levels to indicate what specific type of the parent industry the code value represents. An example of this hierarchy from higher to lower might be 11: Agriculture > 1111: Oil, seed, and grain > 111110: Soybean.
Reference data set value to reference data set value one-to-one mappings
Use these mappings to create relationships between one value in a reference data set and one value in another reference data set. When a 1:1 mapping is selected, the system ensures that no multiple mappings for a value between reference data sets are permitted.
Reference data set value to reference data set value one-to-many mappings
Use these mappings to create relationships between one value in a reference data set and two or more values in another reference data set.

Creating a reference data set-level hierarchy

To create a hierarchy at the level of a reference data set, complete the following steps:

  1. Click the Set-level hierarchy tab in the reference data set that you want to create a hierarchy for.

  2. Click Add parent set or Add dependent set.

  3. Select one or more reference data sets from the list. You can find reference data sets by scrolling through the list or by entering a search term.

  4. Click Add. A draft preview of the new hierarchy appears.

    • You can add parent or dependent reference data sets to a reference data set in the preview by clicking the corresponding menu icon in the reference data set tile.
    • You can remove a reference data set from the hierarchy by clicking the delete icon on its tile.
    • The reference data set that you are working on can be acted upon in the preview for editing and will be moved through the workflow process.
    • The reference data set stays in a draft state until you submit it for approval or delete it.
  5. Click Submit for approval or Publish when you are finished. The available actions depend on how the workflow is defined.

    • When you have a set-level data set hierarchy open, you can edit it by clicking the three dot menu and adding a parent set or a dependent set.
    • To remove a parent or dependent data set from a set-level hierarchy, click the Trash can icon on its tile.
    • To reposition a data set in a set-level hierarchy, you must delete one or more other data sets from the hierarchy and then add them back as parent or dependent data sets to the data set that you are in.

Creating a reference data set value-level hierarchy

To create a hierarchy between values in a reference data set, complete the following steps:

  1. Select the reference data set that you want to work with, then select a value in the data set. In the data set detail section, click the + icon next to Parent value. The corresponding window opens to allow you to make your selection.
  2. Choose the value that you want to add as parent. You can scroll through the list of values or enter search terms to search the list. Alternatively, you can reimport values from a CSV file, mapping the parent column from the CSV file during the reimport process.

When you have a reference data set open, you can add or remove values from the set by clicking the + icon or the delete icon.

Creating a reference data set value-mapping relationship

To add value mapping to an existing reference data set, complete the following steps:

  1. Click the value that you want to add a related value to from the Reference data set tab of your reference data set.
  2. Click the + sign in the Related values section. The Related values dialog box appears.
  3. Select the reference data set that contains the value that you want to add as related, then click Next. You can scroll through the list or type in search terms.
  4. Select a relationship type of one-to-one or one-to-many.
  5. Select the values that you want and choose a relationship type. If you selected a one-to-one type, then you can choose only one value. If you selected a one-to-many type, you can choose one or more values.
  6. Click Add.

To edit or change a value-mapping relationship, delete it, and then add a new one with the changes that you want.

To add related values by importing a CSV file, complete the following steps.

  1. Open the reference data set for which you want to add related values.
  2. From the three dot menu click Upload related values.
  3. Add the file that you want to upload by dragging it to the Import related values window or by browsing for the file. Then, click Next.
  4. Choose the column from your file that has the code values that you want to map to one or more other code values.
  5. Choose the type of relationship (one-to-one or one-to-many) that you want between each value and related value.
  6. Choose the column from your file that has the related codes of the reference values that you want to map to, and which reference data set the related values belong to.
  7. Click Save.

See the following example of a CSV file with value mappings.

code,value,description,map_1_n_to_states,map_1_1_continents
IND,India,Asian country,"KA,AP,MP,DL,GJ",ASIA
USA,America,North American Country,"CA,FL,NY,TX",NorthAmerica
GER,Germany,European Country,,Europe

Learn more

Parent topic: Reference data