Create a Child Dimension with Values Collected Automatically
In this example, a "Product Subcategory" Dimension is created with a parent of "Product Category". While a SQL-based Data Source is specifically is used to fetch Dimension values, the process described below can be used for any SQL or Plug-in Data Source.
The key to defining a Child Dimension is the additional requirement of obtaining the Parent Dimension's Key Value when fetching or creating your Dimension Values. The Parent Dimension's Key Value must match the key value defined for the Parent Dimension, not the Display Value.
In this example, the Child Dimension (red wine, white wine, champagne) is mapped to a Parent Dimension Value of (wine).
Before values for a Child Dimension can be collected, the Parent Dimension must first be defined and populated with values. In this example, the 'Product Category' Dimension has already been defined. See Create a Dimension with Automatically Collected Values
CAUTION: If you grant a User access to a Dimension that has children, grandchildren or other generations, the user receives cascading access to all of the Dimension's descendants in the Family tree
1. Access Content > Dimensions
Scroll down to the bottom of the page and click [+ New Dimension].
2. Enter Basic Dimension Information
- Enter a unique name for the Dimension
- Select the Parent Dimension from the drop-down
- Select whether the Dimension key is stored as a numeric or text value (This should match the Dimension key value format in your source system)
- Choose a Data Source from the Value Source drop-down
- Specify a Bind Parameter for your new Dimension (Setting this to match the column name for the data in the source system makes writing fetch commands for Dimensioned elements easier)
3. Write and Validate a Dimension Value Data Fetch Command
- Write the SQL Statement for collecting Dimension data. This command must return exactly 3 columns:
- Dimension Key Value
- Dimension Display Value
- <Parent Dimension Key Value>: This is how you identify which parent Dimension each child Dimension value falls under.
- Validate: click this button to verify that your SQL statement is valid
4. Review Sample Values
- If your statement is valid, the statement box is green; if there are any errors, the box is colored in red and errors will be explained in the field below.
- Examples of data records are displayed below the SQL statement. Confirm that sample values shown in post-validation are the ones you have expected.
- Collect Values
5. Click-Through Collected Data Count Notification
ERROR PROCESSING FOR PARENT/CHILD DimensionS:
If you attempt to validate the fetch command when the Parent Dimension was not populated.:
- The first few rows of the result set are displayed
- 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:
- This is considered a successful validation (green) with the first 3 matching values shown in the results preview grid.
- 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.
6. Review the Dimension Values List
Scrolling down the Dimension Editor reveals the list of Dimension Values. Note that each Child Dimension Value appears in the table sorted by Parent Dimension Value.