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 TSQL statement and sent to the data source.

1. Input

The structure of the table dropped on the Data Cube canvas is used as input. For example, the structure is:

Input Data: SQL Select transform
Input Data: SQL Select transform

2. Add the Transform

Drag the table from the Data Connector structure to the Data Cube Designer.

Adding the SQL Select transform - Step 1
Adding the SQL Select transform - Step 1

3. Configure

To Edit/Configure the transform, double-click it or select the transform node and click Configure from the toolbar.

Opening the Configuration dialog
Opening the Configuration dialog

SQL Select transform configuration
SQL Select transform configuration

3.1. Add Counter Column

Include a counter column in the output. Enabling this will cause other columns to be grouped.

3.2. Timeout

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

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 the Group By box is not checked.

3.4. Add Expression

Add a new element by inputting a provider expression.

SQL Select transform configuration - Sample Expression
SQL Select transform configuration - Sample Expression

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

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

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. 
    Transform Setting parameter
    Transform Setting parameter
  • 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.

4. Output

The figure below illustrates the output from the SQL Select transform.

Select - Sample Output
Select - 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