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 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
Drag the table from the Data Connector structure to the Data Cube Designer.
To Edit/Configure the transform, double-click it or select the transform node and click Configure from the toolbar.
3.1. Add Counter Column
Include a counter column in the output. Enabling this will cause other columns to be grouped.
The length of time, in seconds, before terminating the attempt to execute a command.
3.3. Transform Element
Click the Edit icon for the column you want to configure (for example, OrderQty).
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.
3.4. Add Expression
Add a new element by inputting a provider expression.
You can also use a Scalar-Valued function in the Select Expression:
3.5. Define Parameters
Define parameters for this transform. Click Add Parameter and select the type of parameter to create a new parameter on the server.
- Transform Setting – Add a counter column or command timeout (similar to the configuration options above) that can be controlled from outside the transform.
- Output Element Filter – Add a filter to a column in the transform.
3.6. Edit Input elements
Change the source native elements used for the transform.
3.7. Edit output elements
Rename the output elements of this transform.
The figure below illustrates the output from the SQL Select transform.