Configuring column dependencies

After you define a column dependency group and a hierarchy group, you can configure the column dependency settings for individual columns.

The following configuration settings are available:

  • Unique Unique icon or Repeating Repeating icon

    This setting specifies whether each row value is unique or repeating. Typically, all levels except for the lowest level in a hierarchy have repeating keys. Unique means that the key doesn’t repeat for any row in the data.

  • Dependent Dependent icon or Independent Independent icon

    This setting specifies whether the parent level value is required to identify the key of the current level. For example, a month key that is defined as a number in the range 1 - 12, requires the parent level keys to identify which year and quarter the key belongs to. Conversely, a month key that is defined as 20190101 doesn’t require the parent keys to identify it because the month (01), quarter (01), and year (2019) values are included in the key.

  • Minimum Min icon, Maximum Maximum icon, and Group by Group by icon or Average Average icon

    This setting specifies if the SQL must be generated with the Min, Max, Avg, or Group by clause when aggregating the data. Minimum is the default setting. Use Minimum, Maximum, or Average for data attributes where there is more than one value for a particular key. For example, the key value of YOW might have the airport name values of Macdonald Cartier Airport, Ottawa International Airport, Ottawa/Macdonald–Cartier International Airport, or Macdonald–Cartier International Airport. In this case, select the Minimum or Maximum setting to prevent double-counting.

    When the data attributes don't repeat, which means that they are consistent throughout the data for each key, the Group by setting can be used. This setting doesn’t apply to measures.

    For measures, you might want to use the Average setting when the numeric values are similar. For example, when the values are 1000001, 1000002, and 1000003.

    If the column dependency is set to Minimum or Maximum, changing the column Usage property from identifier or attribute to measure, or the opposite, doesn't affect the column dependency. However, if the column dependency is set to Group by (identifiers or attributes) or Average (measures) changing this property sets the column dependency to Minimum.

Procedure

  1. Open the table Column dependencies view.
  2. Inside the columns, click the icons that represent the different column dependency settings, and adjust the settings as required. For example, click the Unique Unique icon or Repeating Repeating icon setting icon.
  3. Save the data module.
  4. Optional: To see how the data is aggregated after column dependency is specified, you can create a report that is based on your saved data module. Compare the aggregated results with the report from step 3 in the topic Defining column dependencies.

Results

The following scenario 1 example shows how to configure columns in a denormalized table. In this case, all keys have repeating Repeating icon values except for Day Key, which has a unique Unique icon value for every row of data. Each key is independent Independent icon, and doesn’t require the parent level key to identify it. Finally, all attributes for each column dependency group are set to Group by Group by icon because the values are consistent.

Figure 1. Example of column dependencies configuration for scenario 1
Example of column dependencies

The following scenario 2 example shows how columns in a Time dimension can be configured. In this case, all keys have repeating Repeating icon values except the Day Key which is unique Unique icon for every row in the data. The Quarter Key and Month Key values can't be identified without the Year key level. Quarters and months are represented by numbers in the range 1 - 4 and 1 - 12. Therefore, these columns must be set to Dependent Dependent icon. Year and Day Key are set to Independent Independent icon because their values can identify them. All the attribute values are consistent except Month En so they are set to Group by Group by icon. However, Month En has values such as August, Aug., Aug, and August 08 so it must be set to Minimum Min icon.

Figure 2. Example of column dependencies configuration for scenario 2
Example of column dependencies

The following scenario 3 example shows how to configure columns in a dimension table that contains measures. The Training Key is unique Unique icon and independent Independent icon. The Course Code and Course Name values are all consistent, and can be set to Group by Group by icon.

Figure 3. Example of column dependencies configuration for scenario 3
Example of column dependencies