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 its Parent Dimension, not the Display Value.
In this example, the Child Dimensions (red wine, white wine, champagne) are 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 Dimension Values. In this example, the 'Product Category' Dimension has already been defined.
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. See Dimension Security 5.3.0 for more information.
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 MUST 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 to be used in the fetch command
3. Write and Validate a Dimension Value Data Fetch Command
- Write the SQL 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
- Validate: click this button to verify the SQL statement
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 to allow the user to confirm that sample values shown in post-validation are as 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.