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.

SQL Select transform configuration
SQL Select transform configuration

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.

Transform element configuration
Transform element configuration

The list of available Aggregate Function options reflects the aggregator functions defined by the data provider. For example, SQL Server data will have 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 automatically grouped by each non-numeric column even if their Group By boxes are not checked.

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

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, 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. 
    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
    Use Include From Value and Include To Value in the range token menu to indicate whether the values are inclusive or exclusive.

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.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

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