Create a Child Dimension with Values Collected Automatically

In Metric Insights there is a functionality that allows collecting Dimension Values automatically. However, when it comes to Child Dimension, there is an additional requirement of obtaining the Parent Dimension's Key Value when fetching or creating Dimension Values. The Parent Dimension Key Value must match the key value defined for its Parent Dimension, not the Display Value.

This article describes how to set the Child Dimension with automatically collected Dimension Values.

PREREQUISITES:

Before values for a Child Dimension can be collected, the Parent Dimension must first be defined and populated with Dimension Values.

NOTE: If you grant the User access to a Parent Dimension, that User receives cascading access to all of the Dimension's descendants.  See Dimension Security article for more information.

1. Start Creating a Child Dimension

Access Content > Dimensions

  1. [+New Dimension]
  2. Name: Give the Dimension a descriptive name
  3. Parent Dimension: Select the Parent Dimension from the dropdown menu
  4. Dimension Key Values are: Select whether the Dimension key is stored as a numeric or text value (This MUST match the Dimension key value format in your source system)
  5. Value Source: Choose a Data Source from the dropdown list
  6. Bind Parameter: Specify a Bind Parameter for your new Dimension to be used in the fetch command
  7. [Save]

2. Write and Validate a Dimension Value Data Fetch Command

  1. SQL Statement: Enter the statement for collecting Dimension data. This command must return exactly three columns:  
    • Dimension Key Value
    • Dimension Display Value
    • <Parent Dimension Key Value>: This identifies the parent Dimension to which each child Dimension Value belongs
  2. [Check Data] to validate the SQL statement
  3. [Collect Values]

3. Error Processing

If you attempt to validate the fetch command when the Parent Dimension was not populated.:

  1. The first few rows of the result set are displayed
  2. You receive a message "You can validate this fetch command because the parent Dimension does not have any Values"

If some (but not all) Values fetched can not be validated:

  1. This is considered a successful validation with the first 3 matching Values shown in the results preview grid
  2. Below the grid, the following text is shown in black if any values are discarded on validation: "One or more returned records were discarded because they did not match a parent Dimension Value. Click on the 'Collect Dimension Values' button below to see a list of discarded values."

If no records can be validated, the error message "None of the returned rows could be matched with existing parent Dimension Values" is displayed followed by: "First Row Returned: ......." and show the result set from the first fetched record.

4. Review the Dimension Values List

Scrolling down the Dimension Editor reveals the list of Dimension Values. Each Child Dimension Value appears in the table sorted by Parent Dimension Value.