SQL Select

Contents[Hide]

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.

1. Input

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:

Input data
Input 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.

Adding the SQL Select transform
Adding the SQL Select transform

3. Configure

To configure the transform, double-click it, or select it and click Configure in the toolbar.

Opening the configuration dialog
Opening the configuration dialog

The SQL Select configuration dialog appears with a list of the available input elements.

Use the checkbox next to each element to select which ones should be included in the transform's output.

SQL Select transform configuration
SQL Select transform configuration

Enabling 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.

Tip
In version 5.0.1 and above, the Default Query Timeout application configuration setting determines the initial timeout setting.

3.1. Transform element

Click the Edit icon next to a column name to configure it and access different options that may be available depending on the data type and data provider.

Transform element configuration
Transform element configuration

The list of available Aggregate Function options reflects the functions supported by the data provider. For example, numeric columns from SQL Server support STDEVSTDEVPVAR, and VARP in addition to the Dundas BI built-in aggregate functions.

Note
When a numeric column is assigned an aggregator, the output will be grouped by other included columns even if their Group By options are not checked unless they are aggregated.

There may also be Date Trimming Functions available for date/time data. For example, these can be used along with the Group By & Aggregate Function options or an Aggregate transform to group by the selected time period.

3.2. Add expression

Add a new element by inputting a provider expression.

Sample expression
Sample expression

You can also use a Scalar-Valued function in the Expression:

dbo.ISOweek(DATEADD(month,3,(ModifiedDate)))

3.3. Define parameters

Parameters allow you to filter the data, or for transform options to be set from outside of the transform. The parameter's value can be changed by a bridge parameter in this data cube, or if you make the parameter public you can set it from outside the data cube, for example using a filter on a dashboard.

Click Parameters in the transform configuration dialog. In the Transform Parameters dialog that opens, 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. 
    Transform setting parameter
    Transform setting parameter
  • Output Element Filter – Add a filter to a column in the transform. 
    Output element filter parameter
    Output element filter parameter
    Tip
    The token menu can be used to select options including attributes or custom attributes describing the logged-in user, or whether ranges are inclusive or exclusive.
  • Select Structure Parameter – Allows you to switch between multiple structures such as tables from the same data connector, provided they have the same column names and types (available in Dundas BI version 11 and higher). When creating this type of parameter, click to select which data structures (e.g., tables) are compatible and should be accepted as valid values, then this parameter can be set to the ID of the data structure you want to provide data. To find the structure's ID, find and expand its data connector in the Explore window to list its structures below, then right-click the structure and open its Properties dialog to find its ID field. 
    A select structure parameter with two available structures
    A select structure parameter with two available structures

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.

4. Output

Open the Data Preview window to view the transform output.

Sample output
Sample output

5. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri