The SQL tab

Query mode

Use this setting to configure the SQL statement query mode when loading the record. The following options are available:

Insert select
Single select
Auto (recommended)

The query mode is used only to load data. The Auto mode is always used to select data from the dashboard regardless of the selected query mode.

Insert select

This query type can be executed if all document sources and the Data DB connections are compatible, which means that both of these conditions are met:

All connection keys are identical or left blank.
All data sources have the same database type as the Data DB: MS SQL, MySQL, or Oracle.

Single select

This query type can be executed if all document sources are compatible, which means that both of these conditions are met:

All connection keys are identical or left blank.
The same database types are used: MS SQL, MySQL, or Oracle (depends on connection settings). If you use other database types, such as Excel, Hadoop, and more, connection settings must be identical.

Auto

Insight detects the defined settings and applies one of the modes that suits the environment: Insert select, Single select, or Multiple. We recommend to use this setting.

Show unique values

Select this option to exclude all duplicate rows from the query, so that only distinct rows are used for the query.

Query

If the query mode is set to Auto and it results in the "Insert select" or "Single select" query types, the list contains only one item: Main query (Data DB). If the execution is run by means of "Multiple" queries, it contains queries for reading data from all data sources (one query per each data source) and also one query for writing data to the Data DB.

If the query mode is set to Insert select or Single select, the list also contains only one item: Main query (Data DB).

Use custom query

Custom queries can only be used for query modes other than Auto. Select this option to make the SQL window editable. Insight supports defining custom queries for various databases. For example, you can provide separate queries for the Oracle, MySQL, and Hadoop databases if the project supports different databases and the queries are different for different database types. Also, you can select Any as the database type and define a generic query.

Before executing a query, Insight compares database types and takes the corresponding custom query. If no custom query exists for the target database, the query defined for Any is attempted, and if it does not exist, an error message is shown.

To define a query, you may use the following templates.

<FromDate>, <ToDate>: The time interval from the dashboard or data load.
<UserProperties::Login>: The login of the authorized user. This property can be used only with the Insight user or Login form.
<Parameter::ParameterName>: The value of the parameter driver with the ParameterName parameter on the dashboard is used to replace the template. If no parameter driver exists, the default value from the parameter is used if defined. If there is no default value, the template is replaced with NULL.
<DashboardFilter::Dimension>: The Dimension parameter is the ID of the dimension, which is the source for the filter. Dashboard filter is used to replace the template when the data is requested for the record.
<UserFilter::FilterName>: The value of the FilterName user filter for a logged in user is used to replace the template when the data is requested for the record.
IsFilterEmpty(<UserFilter::FilterName>)/IsFilterEmpty(<DashboardFilter:Dimension>): Use this template to check if the Dashboard filter or the User filter have any values. In case the filter is empty, the function is replaced with "1=1"; otherwise, it is replaced with "1<>1".

Insight replaces the User filter and Dashboard filter expressions with string values from the filter using the Unicode format. If you strongly require to use a non-Unicode format, use custom query. If the filter contains multiple values, they are separated by commas, for example: N'A', N'B'.

If the filter option does not contain any values, the template is replaced with NULL.

Custom query for OData-based records, metrics, translation tables and R Models

To use a custom query, make sure to perform the steps to enable the Insert select option and select the Use custom query check box. It is only possible to use options supported by provider. The Test button helps to verify that the options are available and the query can be executed. Enter custom queries for OData-based records in the form of an URL or an array of query options:

https://services.odata.org/TripPinRESTierService/Airports?$select=Name,IcaoCode,IataCode,Location&$top=3

{
         "entitySet": "Airports",
         "$select": "Name,IcaoCode,IataCode,Location",
         "$top": "3"
          }