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

SQL Select transform configure dialog:

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

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

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.

Note
The list of available Aggregate Function choices 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.

SQL Select Configure Options:

  • Add Counter Column - enabling will cause other columns to be grouped.
  • Timeout - the length of time, in seconds, before terminating the attempt to execute a command.
  • 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.

    Using Scalar-Valued function:

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

  • Define parameters - define parameters for this transform.
  • Rename 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