(v24) SQL Select

SQL Select

This applies to: Managed Dashboards, Managed Reports

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 SOL query and sent to the data source.

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:

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.

Configure

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

The SQL Select configuration dialog appears.

Optionally update the Name, select a Color for the transform, and add a Description.

Select the Add Counter Column option to 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.

Note: The Default Query Timeout application configuration setting determines the initial timeout setting.

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.

The list of available Aggregate Function options reflects the functions supported by the data provider. For example, numeric columns from SQL Server support STDEV, STDEVP, VAR, and VARP in addition to the Symphony 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.

Add Expression

Add a new element by inputting a provider expression.

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

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

If you're running Symphony v24.4 and later, you can insert placeholders similar to those available for a Manual Select to insert values via parameters. Include a name in your expression enclosed by dollar signs ($), then select Placeholders to set up the corresponding parameter with a matching Identifier. See Using Manual Select Placeholders.

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.

• Output Element Filter - Add a filter to a column in the transform.

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

When parameters are defined, a gear icon will appear in the corner of the transform when viewing the data cube process.

Edit Input Elements

This option in the configuration dialog allows you to change the source element behind each transform output element.

Edit Output Elements

Use this option to rename the output elements of this transform.

Output

Open the Data Preview window to view the transform output.

Note: Certain data types such as Array from PostgreSQL or Vertica are not available in any following transforms or the data cube output, but may be available to use in this transform's configuration to filter by its output values using parameters.

For more information, see:

• Transform: Stored Procedure Select

• Transform:Manual Select

• Using Transforms

• Data Cubes

• The Managed Dashboards Data Model

• Re-Wire the Data Flow