Create a Dimension with Automatically Collected Values
This article outlines the process used to create a Dimension whose values are populated automatically based on a data fetch command. While SQL specifically is used to fetch Dimension Values, the process described below can be used for any automated fetch method.
Before starting this process, you should have a SQL statement or other fetch command ready that returns two columns for the Dimension:
- Key Value
- Display Value
If you are defining a Child Dimension (Dimension with a parent) or a Compound Dimension different columns are required. See Creating a Child Dimension or Create a Compound Dimension using Selected Dimension Values for more details.
1. Access Content > Dimensions > [+ New Dimension]
2. Enter Dimension Information
Provide the basic Dimension definition information, paying close attention to:
- Name: should be unique and descriptive
- Parent Dimension: for more information on Parent/Child Dimensions, refer to Parent/Child Dimensions.
- Dimension Key Values are: specify whether numeric or text. Set this based on how the Dimension key is defined in your source system.
- Value Source: from the drop-down list select how Dimension Values should be collected. In this example, we are selecting 'Demo DB (SQL)' as a Value source
- Specify a Bind Parameter name for your new Dimension. Setting this to match the column name for the data in the source system makes writing fetch commands for Dimensioned element easier.
- Save this basic information to proceed with creating a Dimension.
The Dimension Editor opens.
3. Create your Fetch Command
To define Dimension values using SQL:
- SQL Statement: Write the data fetch command that should be used to collect the Dimension Value data. The command should return one Key value and one Descriptive value columns. If your Dimension does not have a key value, select the descriptive value for both columns.
- Validate your statement
4. Validation the Sample Dimension Values and Collect values
- Upon successful validation, sample Dimension values will appear under the data fetch command field
- Click Collect values if the sample dimension values shown are as expected