The SQL Select transform is created when a structure is dragged onto the canvas from a data connector that supports relational queries. You can select the columns to be used in the data cube and set up aggregators. When retrieving data, these settings will then be translated into a SQL query and sent to the data source.
The structure of the table dropped on the data cube canvas is used as input. The examples in this article are based on the following data:
2. Add the transform
Expand the data connector in the Data Connectors folder in the Explore window, then drag a structure (e.g., table or view) to the canvas.
To configure the transform, double-click it, or select it and click Configure in the toolbar.
The SQL Select configuration dialog appears.
The Add Counter Column option will group by the values in each existing column and add a new column containing the corresponding number of records for each set of values.
The Timeout (in seconds) can be customized to determine how long to wait before terminating a long-running database command.
3.1. Transform element
Click the Edit icon next to a column name to configure it.
The list of available Aggregate Function options reflects the aggregator functions defined by the data provider. For example, SQL Server data will have STDEV, STDEVP, VAR, and VARP in addition to the Dundas BI built-in aggregate functions.
There may also be date trimming functions available for date/time data depending on the provider.
3.2. Add expression
Add a new element by inputting a provider expression.
You can also use a Scalar-Valued function in the Expression:
3.3. Define parameters
Define parameters if you want to allow options to be set from outside of this transform. This could be elsewhere in the data cube such as in a bridge parameter, or outside the data cube (e.g., on a dashboard) if you make the parameter public.
Click Add Parameter and select a parameter type:
- Transform Setting – Add a counter column or command timeout parameter to customize the configuration options above from outside the transform.
- Output Element Filter – Add a filter to a column in the transform.
When parameters are defined, a gear icon will appear in the corner of the transform when viewing the data cube process.
3.4. Edit input elements
This option in the configuration dialog allows you to change the source element behind each transform output element.
3.5. Edit output elements
Use this option to rename the output elements of this transform.
Open the Data Preview window to view the transform output.