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

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

Steps to add the transform:

  1. Drop 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

  2. To Edit/Configure the transform, select the transform node, and click the Configure menu.

    Opening the Configuration dialog
    Opening the Configuration dialog

3. Configure

Steps to configure the SQL Select transform:

SQL Select transform configuration - Screen 1
SQL Select transform configuration - Screen 1

  1. Timeout - the length of time, in seconds, before terminating the attempt to execute a command.
  2. Select the columns to be included in the output.
  3. Click the Edit icon for the column you want to configure (e.g. OrderQty).

    SQL Select transform configuration - Screen 2
    SQL Select transform configuration - Screen 2

    • 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).
  4. Optional - if you require additional columns using an expression, click on the New icon just below the Select Expression field.

    SQL Select transform configuration - Add Select Expression
    SQL Select transform configuration - Add Select Expression

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

    You can also use a Scalar-Valued function in the Select Expression.
    Using Scalar-Valued function:
    dbo.ISOweek(DATEADD(month,3,(ModifiedDate)))
    

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

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