The SQL Select transform is created when a structure is dragged onto the canvas from a data connector that supports relational queries. The columns to be used in the data cube can then be selected or unselected and aggregators can be set up per element. When retrieving data, these settings will then be translated into a TSQL statement and sent to the data source.
The structure of the table dropped on the Data Cube canvas is used as input. For example, the structure is:
2. Add the Transform
Steps to add the transform:
- Drop the table from the Data Connector structure to the Data Cube Designer.
- To Edit/Configure the transform, select the transform node, and click the Configure menu.
SQL Select transform configure dialog:
Click the Edit icon for the column you want to configure (e.g. OrderQty)
If a non-numeric column is selected, and an aggregator is assigned to a numeric column, the output will be automatically grouped by this column even if the Group By box is not checked. To illustrate, in the sample above, the Group By checkbox is not selected for the ProductID column, but the output is grouped by ProductID.
SQL Select Configure Options:
- Add Counter Column - enabling will cause other columns to be grouped.
- Timeout - the length of time, in seconds, before terminating the attempt to execute a command.
- Add Expression - add a new element by inputting a provider expression.
You can also use a Scalar-Valued function in the Select Expression.
Using Scalar-Valued function:
- Define parameters - define parameters for this transform.
- Rename output elements - rename the output elements of this transform.
The figure below illustrates the output from the SQL Select transform.