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.
Steps to configure the SQL Select transform:
- Timeout - the length of time, in seconds, before terminating the attempt to execute a command.
- Select the columns to be included in the output.
- Click the Edit icon for the column you want to configure (e.g. OrderQty).
- 3.1 Group By - Check the Group By box if this is a grouping column.
- 3.2 Aggregate Function - Select the desired Aggregator (available for numeric columns only).
- Optional - if you require additional columns using an expression, click on the New icon just below the Select Expression field.
- 4.1 Name - the column name.
- 4.2 Description - Description of this column.
- 4.3 Expression - SQL Expression
- 4.4 Aggregate - Check this box if this column is an aggregated column.
Using Scalar-Valued function:You can also use a Scalar-Valued function in the Select Expression.
- Repeat Steps 3 and 4 for other columns, if applicable. Note: 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.
The figure below illustrates the output from the SQL Select transform.